I have the following setup
Table - tblDailyInformation
Field - DateSold - data type is Date/Time - Primary Key
Field - Register 1 Sales - data type is Currency
Here is some sample data....
DateSold Register 1 Sales
12/18/2009 1173.24
12/19/20093 1081.95
12/20/2009 1019.33
12/21/2009 818.65
12/22/2009 874.03
This is a parameter query and the user enters DateSold as [Date Sold:]. I am trying to retrieve Register1Sales for the date that the user enters and also Register1Sales AS OldSales from the previous record. So, if the user entered "12/20/2009", I need to retrieve the following
Register 1 Sales OldSales
1019.33 1081.95
My SQL is below…
select [register 1 sales]
from tblDailyInformation
where [Date Sold]=[Date Sold:]
(SELECT [register 1 sales] as OldSales
FROM tblDailyInformation
WHERE [Date Sold:]=dateadd("d",+1,[Date Sold]))
When I run this I get the following error message
“Undefined Function ‘[Date Sold:]’ in expression
What am I doing wrong?