Tricky query Criteria, between dates if textboxes are null

[Cowboy_BeBa]

Hi all

So i have a report that, among other things, shows us stock ordered between today (the current date) and the last 3 months.
The query criteria under the date field is as follows:
Between DateAdd("m",-3,Date()) And Date()

nice and simple and works perfectly

However now there is a new need. Some times they would like to run the query to get it between two certain dates.
Now i can do this easily by editing the query, however i am the only computer literate person in the office and i am the only one able to make edits (have locked the front end down so that nobody else can edit a thing unless they enter myu username and password). As im not always in the office it is not always practical for them to come to me

Ive added two text boxes to the main menu form (next to the button that opens this report) txtD1 and txtD2
the idea is for the users to click the button with the two fields blank if they wanna just rund the form as before, or to enter in two dates if they want to run the report between those dates (tried getting a date picking combo box but sadly it seemed too tricky to implement in the time i had allocated to solve this problem, so went with two text boxes)
convoluted i know, but i figured this would be the best solution (am open to suggestions), in an effort to make this work i wrote the following criteria to replace the old one:
iif(Forms![frmMainMenu]![txtD1]="", Between DateAdd("m",-3,Date()) And Date(), iif(Forms![frmMainMenu]![txtD2]="", Between DateAdd("m",-3,Date()) And Date(), Between "#"&Forms![frmMainMenu]![txtD1]&"#" And "#"&Forms![frmMainMenu]![txtD2]&"#"))

have tried using = null and ="", have tried running the query with both of those, with the text boxes blank and with them full, however in all occasions it returns no records (even though the dates im using definitely should be returning records

can anyone see what im doing wrong here?

iif(Forms![frmMainMenu]![txtD1]="", Between DateAdd("m",-3,Date()) And Date(), iif(Forms![frmMainMenu]![txtD2]="", Between DateAdd("m",-3,Date()) And Date(), Between "#"&Forms![frmMainMenu]![txtD1]&"#" And "#"&Forms![frmMainMenu]![txtD2]&"#"))


thanks,
Ben

 

[bob fitz]

Untested but try:

iif(IsNull(Forms![frmMainMenu]![txtD1], Between DateAdd("m",-3,Date()) And Date(), iif(IsNull(Forms![frmMainMenu]![txtD2]), Between DateAdd("m",-3,Date()) And Date(), Between "#"&Forms![frmMainMenu]![txtD1]&"#" And "#"&Forms![frmMainMenu]![txtD2]&"#"))

Alternatively, perhaps you could set a date in each of the textboxes on some event (perhaps when the form opens) and use criteria something like:

Between "#"&Forms![frmMainMenu]![txtD1]&"#" And "#"&Forms![frmMainMenu]![txtD2]&"#"

You might want to but some code in the OnClick event of the button to check for Null or inappropriate values in the textboxes before opening the report. Something like

If IsDate(txtD1) And IsDate(txtD2) Then
  'Code to Open Report
Else
  Msgbox"Please enter a date in each textbox."
End If

 

[arnelgp]

Between IIF(Trim([Forms]![frmMainMenu]![txtID1] & "")="", DtaAdd("m", -3, Date), [Forms]![frmMainMenu]![txtID1]) And
IIF(Trim([Forms]![frmMainMenu]![txtID2] & "")="", Date, [Forms]![frmMainMenu]![txtID2])

 

[CJ_London]

from a user perspective and simpler code, have your start and end date boxes default to be populated with date-3 months and date.

Then your code is the same whether the user just clicks the 'go' button or makes a date change first.

 

[Cowboy_BeBa]

thanks guys, tried implementing your solutions with no luck

i do prefer CJ_Londons Solution so ive sorted it out so that the text boxes autopopulate with the current date and the current date minus 3 months
that part works fine but then i use the following expression for my query criteria
Between "#" & [Forms]![frmMainMenu]![txtD1] & "#" And "#" & [Forms]![frmMainMenu]![txtD2] & "#"

I get an error message saying that "this expression is typed incorrectly or is too complex to be evaluated"

im thinking this was the problem all along, that there was something incorrect in the original expression. I just cannot figure out what
i believe ive called the form parameters correctly, but do not know what is going wrong

 

[CJ_London]

you are getting your ands and ampersands confused

Between "#" & [Forms]![frmMainMenu]![txtD1] & "# And #" & [Forms]![frmMainMenu]![txtD2] & "#"

 

[Galaxiom]

BTW It can sometimes be more readable to directly use the AND and OR facilities in the WHERE clause rather than nesting IIF()s.