Query Criteria Lookup Date Range from Table

[cirix20]

Hello,
I have an issue I'm trying to solve,

I have a Table Called [Date_Variable] with one Field called [Date]



I have about 15 Queries that all have a Field Called [CREATE_TS] in the Criteria section i have to manually update the Criteria Every week and it gets a little annoying. Criteria is Between #1/1/2018# And #1/31/2018 11:59:59 PM# and every week i have to update each of the 15 queries and would rather update the 1 [Date] Field on the [Date_Variable] Table 1 time and it update all of the queries. When i attempt to enter the criteria above into the Date Field of the Date Variable table and attempt to run the Query i get a data type mismatch error. the field type will only accept the date range criteria as a text format but the data is Date format

I have attempted in the Criteria section instead of the between date range DLookUp("[Date]","Date_Variable","") which is giving the data type mismatch

[Micron]

the field type will only accept the date range criteria as a text format

the field type of what - the table? I guess you're saying the table field is text because you're trying to modify the whole expression in the table (which has to be text then) and use that?
Many ways you could handle this. A couple -
- a form with 2 textboxes that you pick dates from the built in calendar and add 11 hours and 59 minutes in code, or use < the date plus 1 day. The table field would have to be date type.
- build the date criteria and execute the query in code. I presume you're not using the same dates you've shown each time but I don't know what dates are needed
- have a start and end date fields in the table (date data type) and use either of the two above approaches
I don't see why you have the same expression in the table as well as the query. Basically, you can't use that table field as criteria because it's text.

BTW, date is a poor choice for any db object name - it is a reserved word.

[cirix20]

the field type of what - the table? I guess you're saying the table field is text because you're trying to modify the whole expression in the table (which has to be text then) and use that?
Many ways you could handle this. A couple -
- a form with 2 textboxes that you pick dates from the built in calendar and add 11 hours and 59 minutes in code, or use < the date plus 1 day. The table field would have to be date type.
- build the date criteria and execute the query in code. I presume you're not using the same dates you've shown each time but I don't know what dates are needed
- have a start and end date fields in the table (date data type) and use either of the two above approaches
I don't see why you have the same expression in the table as well as the query. Basically, you can't use that table field as criteria because it's text.

BTW, date is a poor choice for any db object name - it is a reserved word.

The Reason the Date is in the Query Criteria Field is that's what i would like the Criteria to fetch from the Table Screenshot. I tried to put the DLookUp("[Date]","Date_Variable","") in the Criteria Field instead hoping it would fetch the Date range from the table Date_Variable. I have to change the Date for Each Month on 15 Queries.

each new month for example
March the range would be Between #1/1/2018# And #3/31/2018 11:59:59 PM#
April the range would be Between #1/1/2018# And #4/30/2018 11:59:59 PM#
etc

I want to be able to update one field and have it fetch this field from the criteria of each query cause i have some date automated to excel that wont work if the access database isn't open to pick the dates

[Micron]

This would be far easier if you had StartDate and EndDate fields. Then you could (should) use a form with 2 textboxes bound to those fields and use the built in date picker. Then alter your code to suit either of these 2 approaches:
For the EndDate form field
- training & form label to state "choose a day 1 day after the end date", in which case your sql is >=StartDate AND <=EndDate. This will take care of the time portion.
- training & form label to state "choose the actual end date" and your form code can either add 1 day and use the operators from above, or use BETWEEN along with the DateAdd function to add the desired time to the EndDate

Regardless of either one of those approaches or your idea of storing an expression as text, I can only see a code solution but the code required when using 2 date fields is far less than what your way will require. Your actual query cannot deal with a date expression stored as a string. It will return only that string; it cannot evaluate each part of it and make dates out of it. If you must take that route, then you will have to construct your entire sql in code and concatenate your date expression into it. If it would only ever be the only record in that table, DLookup without criteria could return the string, but again, you'll still have to deal with it as part of a longer string (sql statement) in code.