Criteria syntax

  • Thread starter Thread starter Pat
  • Start date Start date
P

Pat

I have a form that has 2 unbound textbox for To and From
date range. This connects to my query which reads:

Between [forms]![ShiftLog]![txtStartDate] And [forms]!
[ShiftLog]![txtEndDate]

Ok..no problem there. But I would like them to be able to
either enter a date range OR just a single day in the
[txtStartDate] box.

How would I setup the criteria for the query to get 1 day
or the range?
 
If you're already using two textboxes to give a range, why do you want to
also have a range in the "start" textbox? Seems redundant?

There is not an "easy" way to do what you want directly in a query. You'd
need code in your form to "parse" the range into a single date value (likely
would be the first date), write that date into a "hidden" control on the
form, and then change the query to use that hidden control as the start date
instead of the one you're using now. This is a lot of extra steps to just
reproduce what you already have.

Now, if what you want to do is to use a single textbox instead of two
textboxes, then what I've outlined above would be needed, except you'd use
two hidden textboxes and your code would need to parse the date into two
dates (start and end).

Or, if what you want is the option to just have the query return records for
a single date, your current setup will work...you could have the AfterUpdate
event of the first (start) textbox write the value from the start textbox
into the "end" textbox so that, if the user doesn't change it, it'll be used
by the query as the end date.
 
Try criteria like

Between [forms]![ShiftLog]![txtStartDate] And
NZ([forms]![ShiftLog]![txtEndDate],[forms]![ShiftLog]![txtStartDate])
 
Back
Top