SQL query with an optional date field

[StevenBee]

I am trying to execute the following code:

If Not IsNull(DLookup("EventName", "tbEVENT", _

"EventName = " & qt & EventName_tb & qt & " and " _
& "EventType = " & qt & EventType_cb & qt & " and " _
& "EventDate = " & "#" & EventDate_tb & "#" & " and " _
& "Venue_ID = " & VenueName_cb & " and " _
& "EventDescription = " & qt & Description_tb & qt & " and " _
& "StartTime = " & "#" & StartTime_tb & "#" & " and " _
& "EndTime = " & "#" & EndTime_tb & "#" & " and " _
& "Price = " & qt & Price_tb & qt & " and " _
& "EventPictureURL = " & qt & EventPictureURL_tb & qt & " and " _
& "EventPicturePosition = " & qt & EventPicturePosition_frm & qt)) Then

It searches the database for the existence of a record which matches all of those conditions. All of the _tb & _cb's are textbox & controlbox controls on the form inputted by the user.



The problem I'm having is that one of those dates, "EndTime" is optional, so if the user doesn't type anything, the control is empty and Dlookup searches for "EndTime = ##" and returns an error.

The only way I've thought my way out of this is extremely awkward, such as putting two separate If statements dependent upon EndTime being blank or not, OR something just ugly like:

if EndTime_tb = "" then

tempstring = ""

else

tempstring = "EndTime = " & "#" & EndTime_tb & "#" & " and "

endif

If Not IsNull(DLookup("EventName", "tbEVENT", _

"EventName = " & qt & EventName_tb & qt & " and " _
& "EventType = " & qt & EventType_cb & qt & " and " _
& "EventDate = " & "#" & EventDate_tb & "#" & " and " _
& "Venue_ID = " & VenueName_cb & " and " _
& "EventDescription = " & qt & Description_tb & qt & " and " _
& "StartTime = " & "#" & StartTime_tb & "#" & " and " _
& tempstring _
& "Price = " & qt & Price_tb & qt & " and " _
& "EventPictureURL = " & qt & EventPictureURL_tb & qt & " and " _
& "EventPicturePosition = " & qt & EventPicturePosition_frm & qt)) Then

Can anyone suggest a better option for me?

[khalid]

so far this is acceptable solution