Between Date() and Date()-6 is not working in criteria

[MFS]

"To take a step back, why is it that you are writing calculated records to a new table?"
"The general rule of thumb is to not write anything to a table which can be calculated on the fly."

An Engineer is trying to pull data into an Excel spreadsheet, but the issue is that the data is in different tables of my database.
So I wrote a query that would collect needed data from these tables and the query now sends that to one table which he can be linked to Excel.
Probably not the best way of doing it, but the only way I new how.

[pbaldy]

Just to add a thought I haven't noticed being addressed. I have had instances where Between didn't work if the higher value was placed first, so I always structure it with the lower value first. In this instance:

Between Date()-6 And Date()

I'm not saying it won't/can't work the other way, just that it can fail if reversed.

[JoeM]

An Engineer is trying to pull data into an Excel spreadsheet, but the issue is that the data is in different tables of my database.
So I wrote a query that would collect needed data from these tables and the query now sends that to one table which he can be linked to Excel.
Probably not the best way of doing it, but the only way I new how.

Usually, anything you can use a Table for you can also use a Query for. So if you can link the Select Query to Excel, there may be no need to write it to a table (and that would help keep your database size down if you can avoid adding unnecessary tables and records).

Just to add a thought I haven't noticed being addressed. I have had instances where Between didn't work if the higher value was placed first, so I always structure it with the lower value first. In this instance:

Between Date()-6 And Date()

I'm not saying it won't/can't work the other way, just that it can fail if reversed.

Interesting. I thought of that myself, and tested that, but it seemed to work for me either way. Maybe it depends on which version of Access one is running...

[MFS]

Usually, anything you can use a Table for you can also use a Query for. So if you can link the Select Query to Excel, there may be no need to write it to a table (and that would help keep your database size down if you can avoid adding unnecessary tables and records)...

I receive an error stating that it could not access the query due to someone having the database open. Is there a way around this?

[JoeM]

If you have your Access database open at the same time you are trying to access the query in Excel, that may be problematic (just like two people trying to open the same Excel file). I would expect the same kind of behavior if you went the Table route also.
You could explicitly export the Access Query to an Excel file. Then it is its own copy, no longer tied to the Access query and you would not have any conflicts.

[MFS]

Thank You all for your help in solving.

Speck

[JoeM]

You are welcome!