Need help with SQL Where Clause in VBA 1

[NicoleOB]

I have a form that requests from the user a start and end date and then a device type. When the user enters the information, then clicks "View Available" - I would like to display devices available for the request timeframe. It should display all devices that either have no reservations entered or those with reservations that do not exist during this request. I am using MS Access 2003. I have searched for something similar on many sites & not found anything that works.

I have done this with actual date values & it works - just can't figure out how to substitute the variables in. This is what I had using actual dates:

DoCmd.OpenForm "fReservation", , , "[device] = '" & devtype & "' And (([BeginDate]) Not Between [#7/25/2006#] And [#7/28/2006#]) AND ([EndDate]) Not Between [#7/25/2006#] And [#7/28/2006#])"

This is my edit where I try to use variables (do not have the EndDate check in here as it is above)

Private Sub Command9_Click()
Dim start As Date
Dim finish As Date
Dim devtype As String



devtype = Me.Combo14
start = Me.begin
finish = Me.end



DoCmd.OpenForm "fReservation", , , "[device] = '" & devtype & "' And (([BeginDate] Is Null)) OR ([BeginDate] Not Between '"& start and & finish" ')"
Forms!fReservation.startfill.Value = start
Forms!fReservation.endfill.Value = finish



DoCmd.Close acForm, "fReservationSearch"

Thanks for any help.

Nicole

 

[PHV]

DoCmd.OpenForm "fReservation", , , "device='" & devtype & "' AND (BeginDate Is Null OR BeginDate Not Between #"& start & "# And #" & finish & "#) AND (EndDate Is Null OR EndDate Not Between #"& start & "# And #" & finish & "#)"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[NicoleOB]

Thank you so much - it worked like a charm!

 

[Melagan]

I'm becoming a stickler for full dot notation. I would suggest populating your variables as such:

devtype = Me.Combo14.Value
start = Me.begin.Value
finish = Me.end.Value

This way there is never any confusion and it might help to avoid some problems later on. Take this with a grain of salt though, because I'm just a beginner myself =)

~Melagan
______
"It's never too late to become what you might have been.