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:
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.
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.
#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
What you observe is the expected behaviour. Consider this example:
SELECT col1, col2
The variables col1 and col2 are undefined outside the context of the query myQ. Hence,
myQ.col1 and myQ.col2 exist
<cfloop query="myQ"><!--- col1 and col2 exist---></cfloop>
<cfoutput query="myQ"><!--- col1 and col2 exist---></cfoutput>
So let us name your query as follows:
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
FROM rpr_srvc_hst AS SH2
WHERE SH2.rpr_srvc_hst_srvc_ = SH1.rpr_srvc_hst_srvc_)
EwokStudQ.rpr_srvc_hst_dt, EwokStudQ.rpr_srvc, EwokStudQ.rpr_srvc_hst_amt_ exist;
<cfloop query="myQ"><!--- rpr_srvc_hst_dt, rpr_srvc, and rpr_srvc_hst_amt_ exist---></cfloop>
<cfoutput query="myQ"><!--- rpr_srvc_hst_dt, rpr_srvc, and rpr_srvc_hst_amt_ exist---></cfoutput>
What BKBK said. Also, "SH1" is not a prefix - it's a table name alias that you set within your SQL query. If you ran the same query inside Access, the results grid does not show the table names/aliases as prefixes to columns (even if there is a column name collision between same-named columns in multiple tables - I just checked this in Access 2010). So as BKBK said, this is expected behavior.