Query OK in Access 2000 and not in Access 2007

try this:
GrandTotal: Format(DSum("[Price_Per_Unit]*[Quantity]","[OrdersTotal_Subquery]","[Project_Num] = " & Forms![BOM_Queries]![cboProjects]),"$#.00")

Sorry, no luck. I get a can't find name error on this part:
"[Project_Num] = " & Forms![BOM_Queries]![cboProjects]

try each of the syntax  variations below:
Allen Browne documents  that there are expressions in prior access versions that are not understood by Access 2007.    See compatibility issues. http://allenbrowne.com/Access2007.html#Bugs

I am assuming that cboProjects returns numeric and that Project_Num is the bound column of combo box

"Orders.[Project_Num] = " & Forms![BOM_Queries].[cboProjects]
___________________________________________________
"Orders.[Project_Num] = " & Forms![BOM_Queries].[cboProjects].Column(0)

I should have used the bang operator (!) instead of dot operator (.) in above expressions

No luck with either of those.  I'm wondering if the Format or DSum functions still work in Access 2007.

Bob,
can you post the code as you entered them, so I can double check. Also, provide details as to what you mean by didn't work....error message? wrong result?  DSum and Format still work in 2007.

Thanks

Also, my database back-end was Access 2000 and now it is SQL Server 2005.  Here is the error message:

The expression you entered as a query parameter produced this error: 'DTLDB *Access2007  to SQL Server 2005*  Development from Rev. 3.7 to Rev. 3.8 3/12/09 can't find the name 'CR60075' you
entered in the expression'


Below is the entire query.

SELECT Orders.Project_Num, Orders_Subtable.Component_Num, Orders_Subtable.Quantity, Orders_Subtable.Price_Per_Unit, Sum([Price_Per_Unit]*[Quantity]) AS Total, Format(DSum("[Price_Per_Unit]*[Quantity]","[OrdersTotal_Subquery]","Orders![Project_Num] = " & [Forms]![BOM_Queries]![cboProjects]),"$#.00") AS GrandTotal
FROM Orders INNER JOIN Orders_Subtable ON Orders.Orders_ID = Orders_Subtable.Orders_ID
GROUP BY Orders.Project_Num, Orders_Subtable.Component_Num, Orders_Subtable.Quantity, Orders_Subtable.Price_Per_Unit, Format(DSum("[Price_Per_Unit]*[Quantity]","[OrdersTotal_Subquery]","Orders![Project_Num] = " & [Forms]![BOM_Queries]![cboProjects]),"$#.00")
HAVING (((Orders.Project_Num)=[Forms]![BOM_Queries]![cboProjects]));

Select allOpen in new window

Rob,
I don't believe it is Access 2007 that is giving you the problem....I believe the problem is that SQL Server requires parameters to be presented differently in order to process it (pass-thru query??).  I am not real familiar with sql server syntax, but I gave it a try.  Change the last line of your query to the following.
              (((Orders.Project_Num)=CStr([Forms]![BOM_Queries]![cboProjects].Value)));

see this link:   http://msdn.microsoft.com/en-us/library/aa337293(SQL.90).aspx 

p.s.
If cboProjects is numeric, remove the CStr function.

I tried your suggestion above but get the same error.

I tried that and everything else I could think of.  Nothing works.

I hope you don't mind but I'm going to up this up to the SQL Syntax people.

Don't mind at all.  It does not make sense that you can't get it to work even if you replace parameter with a hard value.

Thanks for getting me in the right direction. It was the double quotes around [Price_Per_Unit]*[Quantity]

Bob,
wow!! that explains things.  So, the parameter problem was a symptom,not a cause......it was the cryptic messages that had you fixated on the parameter as the causing  The query, as posted, could not have run in Access 2000 because of the quotation marks, either.  Glad I was able  to help you resolve this.  Thanks for the points and grade.  

pDog