Querying Access on Dates

[Ecniv]

Originally posted by da_beano

public function Nnz(Byval varA as variant, ByVal varB as variant) as variant
  nnz=iif(isnull(vara),varb,vara)
end function
 
Private Sub cmdSrch_Click()
 
Dim date1 as date, date2 as date
Dim blnValid as boolean
 
'On error resume next
 
'---- perform validation on data on form
blnvalid=true
if nnz(Len(txtdate1.text),0)=0 then blnvalid=false
if nnz(Len(txtmonth1.text),0)=0 then blnvalid=false
if nnz(Len(txtyr1.text),0)=0 then blnvalid=false
if nnz(Len(txtdate2.text),0)=0 then blnvalid=false
if nnz(Len(txtmonth2.text),0)=0 then blnvalid=false
if nnz(Len(txtyr2.text),0)=0 then blnvalid=false
 
if not blnvalid then 
 msgbox "Data invalid!"
 exit sub
Enf if
 
'---- Process
date1 = CDate(txtDate1 & "/" & txtMonth1 & "/" & txtYr1)
date2 = CDate(txtDate2 & "/" & txtMonth2 & "/" & txtYr2)
 
'---- check dates correct
MsgBox "Flights between " & Format(date1, "dd/mmm/yy") & " and " & Format(date2, "dd/mmm/yy")
 
datFlights.RecordSource = "SELECT * FROM tblFlight WHERE tblFlight.DDate BETWEEN #" & format(date1, "dd mmm yyyy")& "# AND #" & format(date2,"dd mmm yyyy") & "# ORDER BY tblFlight.DDate;"
datFlights.Refresh
End Sub

OK, hmmm your code should look a little like that.

I've added in a validation check for the data entered and changed bits and pieces around. Can you please test and lemme know what happens.

Note : I've added my nnz function above the sub - this should ideally go into a module...

Vince