Querying Access on Dates

[da_beano]

I'm having real problems with this, for the last few days. It beat out the general vb forum too.

Basically I want to enter 2 dates on a form and have the program query a database and show all flights between those 2 dates. The problem is that when the value entered for the Day part of the date is over 12, the date seems to be ignored (as well as the remainder of the SQL statement).

Private Sub cmdSrch_Click()

date1 = txtMonth1 & "/" & txtDate1 & "/" & txtYr1
date2 = txtMonth2 & "/" & txtDate2 & "/" & txtYr2

MsgBox "Flights between " & Format(date1, "dd/mmm/yy") & " and " & Format(date2, "dd/mmm/yy")

datFlights.RecordSource = "SELECT * FROM tblFlight WHERE tblFlight.DDate BETWEEN #" & date1 & "# AND #" & date2 & "# ORDER BY tblFlight.DDate"
datFlights.Refresh
End Sub

The result is that if the day of date2 is > 12, all the dates greater than date1 are returned. Something else that's confusing the hell out of me: if txtDate1 > 12 then date2 is treated as the low date.

[Negative0]

It looks ok to me, I would double check your string in access.

Do something like this:

strSQL = "SELECT * FROM ..."
Debug.Print strSQL

You can then copy the string into access and play with it in the query builder and figure out where your problem may lie. You may also want to check the format of the dates you are passing to access.

Just my thoughts and hope they help,

[honeybee]

Originally posted by da_beano
I'm having real problems with this, for the last few days. It beat out the general vb forum too.

Basically I want to enter 2 dates on a form and have the program query a database and show all flights between those 2 dates. The problem is that when the value entered for the Day part of the date is over 12, the date seems to be ignored (as well as the remainder of the SQL statement).

Private Sub cmdSrch_Click()

date1 = txtMonth1 & "/" & txtDate1 & "/" & txtYr1
date2 = txtMonth2 & "/" & txtDate2 & "/" & txtYr2

MsgBox "Flights between " & Format(date1, "dd/mmm/yy") & " and " & Format(date2, "dd/mmm/yy")

datFlights.RecordSource = "SELECT * FROM tblFlight WHERE tblFlight.DDate BETWEEN #" & date1 & "# AND #" & date2 & "# ORDER BY tblFlight.DDate"
datFlights.Refresh
End Sub

The result is that if the day of date2 is > 12, all the dates greater than date1 are returned. Something else that's confusing the hell out of me: if txtDate1 > 12 then date2 is treated as the low date.

Try using the Format() function in the query itself. If you are using DAO this may be possible.

"SELECT * FROM <YourTable> WHERE FORMAT(<DateField>, 'dd/mm/yy') BETWEEN '" & _
    Format(Date1, "dd/mm/yy") & "' AND '" & Format(Date2, "dd/mm/yy")"

.

[PilgrimPete]

Sounds like a date settings issue. Access does some pretty odd things with dates.

Try this:

Private Sub cmdSrch_Click()
 
date1 = txtMonth1 & "/" & txtDate1 & "/" & txtYr1
date2 = txtMonth2 & "/" & txtDate2 & "/" & txtYr2
 
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

[Ecniv]

Nothing wrong with the dates..

Have you looked at this :

date1 = txtMonth1 & "/" & txtDate1 & "/" & txtYr1
date2 = txtMonth2 & "/" & txtDate2 & "/" & txtYr2

These are NOT dates

They are strings, thus format won't work on them.
If you wanted dates, you should've defined them as dates and enclosed the whole lot in CDate().

This means :

MsgBox "Flights between " & Format([b]CDate(date1)[/b], "dd/mmm/yy") & " and " & Format([b]CDate(date2)[/b], "dd/mmm/yy")
 
datFlights.RecordSource = "SELECT * FROM tblFlight WHERE tblFlight.DDate BETWEEN #" & [b]date1[/b] & "# AND #" & [b]date2[/b] & "# ORDER BY tblFlight.DDate"

...would suffice.


Also, someone else on the forum mentioned this before and I've found it helps clear up confusions; Use dd mmm yyyy formating from the text boxes as you won't get confused (nor will the user or the database) about american or english formatting.

Regards

Vince

[da_beano]

If you mean I should have defined them when I swas declaring the variables, they were declared like

Dim date1 As Date
Dim date2 As Date

I don't understand the Cdate function, what's that about?

[Ecniv]

CDate(<string version of date>) returns the date specified or an error if its not a date.

If you open the debug window and do :
Print CDate("10 Jan 2002") you'll get 10/01/2002 - which is a date.

How the strings were being stored into the date without erroring I dunno.

But try putting the CDate around the string dates :

date1 = CDate(txtMonth1 & "/" & txtDate1 & "/" & txtYr1)
date2 = CDate(txtMonth2 & "/" & txtDate2 & "/" & txtYr2)

The rest of the (original) code should be ok.

Vince

[da_beano]

Was worth a try, but still hasn't worked. I'm thinking of giving up and just storing the dates as numbers (possibly a set of 3 numbers... I hope I dont have to resort to that) because I'm running out of time to get this finished.

I know I'm only a newbie, but I still cant believe I've tried so much and nothing works. It's not like the code is that comlicated... I'm close to tears LOL

[Ephesians]

It's nothing silly like the fact that dates are reversed in the East? Like in Canada we handle month day year and over there you handle day month year? Could your system not be taking anything in the day field over 12 because it's really the month field?

Just a thought.

[da_beano]

That was one of the first things I tried. In fact that's why the MsgBox is in there - you'll notice it prints the dates as dd/mmm/yy just to make sure that that ISN'T the problem, it does in fact interpret the dates correctly. Presumably VB and Access and other programs just lookup some system variable that sets what format the dates should default to.

[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