Update Query works in Access 2007 but not in 2010

[Yoni]

I have an interesting issue. I wrote a simple update query that uses a dsum formula and it worked fine in access 2007. my computer was recently upgraded to access 2010 and the query no longer does the right calculations. There are no errors, just blank cells and false results.



The query only calls one table, named PreSum. It updates one field in PreSum called Paid Cumuler.

Here is the code:

UPDATE PreSum SET PreSum.[Paid Cumuler] = DSum("[Paid]","PreSum","[ClaimNb] = " & [ClaimNb] & "AND [Treaty] = '" & [Treaty] & "' AND [Section] = '" & [Section] & "' AND [Line of Business] = '" & [Line of Business] & "' AND [Invoice Date] <= # " & [Invoice Date] & "#");

All fields are in the table PreSum
As I said, there are no errors and it works in access 2007.

Thanks

[John_G]

Hi -

The first place I would look is [Invoice Date]. Is it presenting the data in the order MS Access is expecting it to in this case, which is mm/dd/yy? If the day is <= 12, the the data would be valid to MS Access. Try using Format([Invoice Date],"mm/dd/yyyy") in between the # signs.

John

[orange]

Perhaps John is correct, but I think you also need to correct the syntax in this area

"AND [Treaty]...
You will need a space between the " and AND

Just my $.02

[Yoni]

Hi -

The first place I would look is [Invoice Date]. Is it presenting the data in the order MS Access is expecting it to in this case, which is mm/dd/yy? If the day is <= 12, the the data would be valid to MS Access. Try using Format([Invoice Date],"mm/dd/yyyy") in between the # signs.

John

Thanks John, That worked. I suspected it was something with the dates as well, but couldnt figure it out.