Query records 30 days and greater from Form input

[BR549]

Salutations,

I have created a query to look for records which are older than 30 days old. The user input a date into a form, and then it runs a bunch of queries to create tables for various needs. There is one query I am super frustrated with. I am trying to run another query using the same input from one of the form inputs, where it takes the first date "[Forms]![Run Files]![txt Start Date]" and pull everything 30 days and older. I have tried the following:

"between #1/1/14# and [Forms]![Run Files]![txt Start Date]<30" - This pulls everything for the year both before the Start date and the end date.
<="CAMdate" - created a DIM statement as a date in the query to put the date into the VBA code, which show it is 30 days after the start date.

Please help.

[June7]

30 days older than what - the user input?

You want records where a value is between the StartDate and StartDate+30?

Try:

BETWEEN [Forms]![Run Files]![txt Start Date] AND [Forms]![Run Files]![txt Start Date]+30

[BR549]

I want to pull records which are Start date -30 days and older. So if the start date was 7/1/14, the query would pull records which are 6/1/14 and older.

[June7]

Older how far back? Just to beginning of year?

Maybe:

WHERE [date field] BETWEEN "1/1/" & Year([Forms]![Run Files]![txt Start Date]) AND [Forms]![Run Files]![txt Start Date] - 30

[BR549]

The suggested Where statement did not ask for the [Forms]![Run Files]![txt Start Date] input and when I ran it provided no result, which I would love to be accurate, but I know there are results which should be provided.

I would like it to be for all time.

I have been googling for hours and looking at my cookbook and still

[June7]

Why should the query ask for input? The input is provided by reference to the control on open form.

Post the complete query SQL statement.

Then maybe:

WHERE [date field] < [Forms]![Run Files]![txt Start Date] - 30

If you want to provide db for analysis, follow instructions at bottom of my post.

[BR549]

ok, I have cleaned up the database and removed a lot of the data to make it small enough, lol, did not realize how big it was. Please be gentle.


Raw Data test.zip

[June7]

I get an error from Windows that it cannot open the zip folder - "Invalid".

[BR549]

I use winrar and just tried to rename it, try this one.

[June7]

Why is the query a MAKE TABLE?

Personally, I don't like dynamic parameterized queries. I prefer to open a form or report and apply filter to the form or report as it opens. Why open query and not form or report?

Problem with the query is WHERE word is used twice. Try this instead:

SELECT [tbl CAM Data].Customer, [tbl CAM Data].[Date Scheduled]
FROM [tbl CAM Data]
WHERE ((([tbl CAM Data].Customer) Like "AT&T") AND (([tbl CAM Data].[Date Scheduled]) Between "1/1/" & Year([Forms]![Run Files]![txt Start Date]) And [Forms]![Run Files]![txt Start Date]-30))
ORDER BY [tbl CAM Data].[Date Scheduled] DESC;

[BR549]

That worked, WOO HOO.

I make the tables because I export the data into excel, so there may be 200 tables created by this form, then I export up to 20 tables per excel file. Its all used monthly to review with external suppliers on their performance for the month. It was taking one person 8 hours to make these excel files via pivot tables and this will reduce the number of hours from 8 to 10 minutes.