Skip to main content
E
EwokStud
Known Participant
December 13, 2015
Question

I've an SQL Statement Which works in Access, but not in Coldfusion

  • December 13, 2015
  • 1 reply
  • 457 views

I've an SQL Statement Which works in MS Access 2010, but not in Coldfusion 7.  It's a way to achieve a display of most recent prices for my services, from a table of prices, which includethe date of each new price for each service; there are many services.  I have 2 tables.  the first table lists the services, one record for each service.  The 2nd table lists multiple instances of many of the services from the first table, each instance being a new price.  I do this to keep track of my price history.  The webpage throws an error, stating that: A CFML variable name cannot end with a "." character. SELECT SH1.rpr_srvc_hst_dt, rpr_srvcs.[rpr_srvc], SH1.[rpr_srvc_hst_amt_] FROM rpr_srvc_hst AS SH1 INNER JOIN rpr_srvcs ON SH1.[rpr_srvc_hst_srvc_] = rpr_srvcs.[rpr_srvcID] WHERE (((SH1.[rpr_srvc_hst_dt])=(SELECT MAX(rpr_srvc_hst_dt)     FROM rpr_srvc_hst AS SH2     WHERE SH2.[rpr_srvc_hst_srvc_] = SH1.[rpr_srvc_hst_srvc_]))); and the code on the page body:

#rpr_srvc##NumberFormat(SH1.[rpr_srvc_hst_amt_],'0.00')##DateFormat(SH1.rpr_srvc_hst_dt,'M/D/YY')#
    This topic has been closed for replies.

    1 reply

    E
    EwokStudAuthor
    Known Participant
    December 13, 2015

    Well, apparently the [] brackets cannot be used, but now, I am wondering how to use tablename.fieldname, because the following works:#NumberFormat(rpr_srvc_hst_amt_,'0.00')#  but#NumberFormat(SH1.rpr_srvc_hst_amt_,'0.00')#doesn't work.  Please help.

    Carl Von Stetten
    Carl Von Stetten
    Legend
    December 13, 2015

    I think ColdFusion drops the table prefixes once it has a recordset returned.  So if, in your query, you reference columns from multiple tables that have the same name (like table1.column1 and table2.column1), you need to use aliases to distinguish the two after ColdFusion has received the recordset back (so, maybe tbl1_column1 and tbl2_column1, etc.).

    Then, you should be able to reference your query columns in CFML statements as queryname.columname as long as you use any aliases you defined in your query (like myquery.tbl1_column1).

    I'm shooting in the dark, because I'm not sure if the error you are getting is pointing at the query itself or when you try to use the query results elsewhere in you page.  Please provide some code excerpts and specifically identify the line that is referenced in the error.

    HTH,

    -Carl V.

    E
    EwokStudAuthor
    Known Participant
    December 13, 2015

    #DollarFormat(SH1.rpr_srvc_hst_amt_)#was changed to#DollarFormat(rpr_srvc_hst_amt_)#I removed the tablename SH1 prefix and the error went away.  The fieldname rpr_srvc_hst_amt_ didn't need a prefix, because SH1 is the same table as rpr_srvc_hst, and therefore the data is the same.  I'm just interested in knowing why CF didn't like the prefix.  Very odd! Incidentally, I would like to display the dollar figures without the dollar signs.  When I do, CF rounds out the decimals automatically.  $59.99  I would like to read 59.99 and not 60.00

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices