SQL Subquery on Previous Record

[GoVols02]

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?

[z1efuller1]

Could be wrong, but I do not believe that Access wil let you do subqueries.

You can create a cascading combo box on a form view that will display this information for you after selecting the date however.

This link will show you how to set one up. (Thanks Rural Guy)