I am wondering how I can make a query to return the records for today and the past (x) number of days based off the date field. What I mean by x is where when the query is opened, the user will type a number for to make the query go back that number of days. For example, if I wanted to see the last 30 days, the query will ask me when I open to type a number.

Thanks in advance for the help!

You could use the following if your field doesn't have future dates:-

>=Date()-[enter number of days]

Or if your field does have future dates and you only want to return records with todays date - x days you could use:-

>=Date()-[enter number of days] And <=Date()

I am wondering how I can make a query to return the records for today and the past (x) number of days based off the date field. What I mean by x is where when the query is opened, the user will type a number for to make the query go back that number of days. For example, if I wanted to see the last 30 days, the query will ask me when I open to type a number.

Thanks in advance for the help!

It sounds like you are wanting to display a range of data. The Date() statement will get you today's date, and the BETWEEN statement will allow you to define the range.
BETWEEN Date()-[Enter the number of Days to Display] AND Date()

Id like to say thanks it works.... which it probably does... But I think I have to find another route to do what I want to. My date field is the reference field to another table. Big mess....

But thank you anyway!

Id like to say thanks it works.... which it probably does... But I think I have to find another route to do what I want to. My date field is the reference field to another table. Big mess....

But thank you anyway!

That should not make too much difference.

Instead of:
BETWEEN Date()-[Enter the number of Days to Display] AND Date()
Use:
BETWEEN tblReferenceData.ReferenceDate-[Enter the number of Days to Display] AND tblReferenceData.ReferenceDate
Note1: tblReferenceData and ReferenceDate are dummy names that I created and you will need to substitute the appropriate names for your application.

Note2: If this does not resolve the issue, then a more detailed description of what you are doing and what your objective is will probably be required.

Thanks again for trying to help but that didn't work either...

This is what I have maybe this will explain it better. I am working with two tables...

tblEventLogMain
tblEventLogSub

tblEventLogMain has the folowing fields
ID Type: Auto Number and is a Key Field
Todays_Date Type: Date/Time
The rest are irrelevant so I'll skip

tblEventLogSub has the following fields
Reference_Number which links to the Main tbl with this row source

SELECT [tblEventLogMain].[ID], [tblEventLogMain].[Todays_Date] FROM [tblEventLogMain] ORDER BY [ID];


So what happens is when I design my forms, I create an event log for the day with a single date and some info that goes througout the entire day. Then within that I have the sub log which is just events throughout the day.

My goal is from that event log (sub log) do a query where I look back the past 30 days. If I simply open the tblEventLogSub, it visually shows the Date which is not the true linked field. Hopefully this explains it better.



Thanks again for all your help!

Does anybody have any further response to help me out with this issue?