Auto insert criteria

[Thompyt]

What I am attempting is to do some settings in the criteria of a control



ChngReQry.Date_Closed is the control

I would like to be able to insert the outcome of the following code:

Date_Clos: IIf([Commex Date]>=Date(),"Between Date()-6 and Date",Date())

How would I be able to have the Date() come out like #3/15/2016#

[NTC]

I don't understand your post. Date() is the PC's current date, by definition.

The IIF clauses are: IF, True, False

So as you have written: If Commex >= the PC's date is true then you are trying to display:"Between Date()-6 and Date" - - what is this suppose to be with the term 'between' and ending with the word Date?? Perhaps you should give an example....

[Thompyt]

How would I insert this into the criteria?

If Settings_QRY.Type control does not equal "Field" then "Between Date()-6 and Date() is true and should be inserted into the criteria section of Query_QRY.mycontrol (red box) If it is false then Date() should be inserted. This then gives me a range for the query to look at.

What I am trying to achieve is not having to go to several different queries and change the criteria - Pure laziness on my part I know.

[NTC]

There are 2 approaches;

a. build your query using SQL syntax in VBA (not query design view) with if/then logic as to which SQL clauses are to be used
b. build 2 separate queries (in query design view), and use if/then logic to decide which query to use

approach b is simpler and easier to manually test individual queries; but if you need a single record source for a form/report then one must do it via the first approach

[Thompyt]

OK,
Simplified: I would put this in the Field:
Date_Closed: IIf([Commex Date]>=Date(),Between Date()-6 and Date(),Date())

How do I get access to recognize Between Date()-6 and Date() without making it a text in quotes? Text wouldn't work.

Datediff, Dateadd wouldn't work I am assuming since I want to get records in a range, and these would enumerate what the range is.

[ssanfu]

If you want a string with the dates, you can concatenate the dates:

"Between " & Date()-6 & " and " & Date()

or

"Between #" & Date()-6 & "# and #" & Date() & "#"

[Thompyt]

Thanks ssnafu, I'll give it a shot