Query between 2 dates

[nick941741]

Hi, I have a search from that searches a table called 'events' (which contains booking details for customers events)

On the search page I did originaly have just a date field and in my search query I had the following

EventDate] Like "*" & [Forms]![frmEventSearch]![txtEventDate] & "*" Or [Forms]![frmEventSearch]![txtEventDate] Is Null

So even if that field was NULL then the search would still look at the other fields and run.

Now though I need to do a search based on a date range, so have create 2 boxes on my search page txtEventStart and txtEventEnd, but I am having lots of problems getting the search query to work I have tried several different versions now but it only ever seems to work on the first box and not both.

Any help would be apprecaiated.

This is as far as I got [EventDate] Between ([Forms]![frmEventSearch]![txtEventStart] & "*" Or [Forms]![frmEventSearch]![txtEventStart] Is Null) And ([Forms]![frmEventSearch]![txtEventEnd] & "*" Or [Forms]![frmEventSearch]![txtEventEnd] Is Null)

 

[DCrake]

How do you expect to create a range of dates if only one date is supplied?

Using the Between method implies Between One date And another date.

The only way around this would be to write a function to handle this senario.

Using an If ElseIf method

If Me.StartDate Is Not Null And Me.EndDate Is Not Null Then

    ' Use Between x And y
Else If Me.StartDate Is Null And Me.EndDate Is Not Null Then

   'Use End Date <= 

Else If Me.StartDate Is Not Null And Me.EndDate Is Null Then

   'Use Start Date > =

Else
   'Use Is Not Null (all records)

End If

You would pass the two dates to the function which would then use the decision tree above to determine the combination and create the condition string which would be returned to the query

David

 

[nick941741]

Hi, thanks for the reply, I shall have a go at this and see if I can get it to work.

I have 2 search boxes on my new form txtEventStart and txtEventEnd, this will give the range needed to run the query against.

I shall let you know how I get on.