Several different queries use a date as criteria - each need updating manually...

[peskywinnets]

I have several different queries that use a nominated date as input criteria - each need updating manually... it gets tiring entering the same date across queries.

So what I seek is a way of entering the data once ... & then all the queries use that date as the 'criteria' across all the queries.

What would be the best way to crack this nut?

 

[cyanidem]

One idea: create a standard module with public function that returns your wanted date and use it in query. You can't use vba variables as query parameters but you can use functions.

 

[peskywinnets]

Thanks...whilst I can understand that concept...I wouldn't know how to deploy it

I was hoping to use a separate dedicated table, for example something like this.

Date
01/01/16
01/02/16
01/03/16

...then pick off the minimum value (lowest date) e.g. Min(MyTable]![Date]) ....& then use that as the input criteria on my queries....but I'm puzzled at how/if this can be done?

 

[cyanidem]

If you'd like to go this way you can use DMin function.

DMin("YourDateFieldName","YourDedicatedDatesTableName")

Don't name your fields Date, Name etc. as those are reserved words and may cause trouble later on.

 

[Minty]

if you always run the reports / queries for the last month you can fix that as calculated criteria in the query

First day of current month - Date() - (Day(Date()) -1)
First day of last month - Dateadd("m",-1,(Date() - (Day(Date()) -1)))

etc...

 

[peskywinnets]

If you'd like to go this way you can use DMin function.

DMin("YourDateFieldName","YourDedicatedDatesTableName")

Don't name your fields Date, Name etc. as those are reserved words and may cause trouble later on.

That worked a treat ....bliss

if you always run the reports / queries for the last month you can fix that as calculated criteria in the query

First day of current month - Date() - (Day(Date()) -1)
First day of last month - Dateadd("m",-1,(Date() - (Day(Date()) -1)))

etc...

The date will be every Tuesday, two weeks apart (starting with yesterday Tuesday 19th 2016) ...as much as I can see the win of going that way, it makes my head hurt & I'd need to lie down trying to work out the command. So I'm taking the sloppy way & creating a table with all the forward dates in it!