Query dates

[kevcar40]

hi
I am having a problem query dates,
i am importing a spredsheet from Excel with serval fields on it on of
which is a date field

on the Excel sheet the column is formated as short date

i am trying to query over a date range
i am using the fuction below

Function SQLDate(vDate As Variant) As String
If IsDate(vDate) Then
SQLDate = "#" & Month(vDate) & "/" & Day(vDate) & "/" &
Year(vDate) & "#"
End If
End Function

txtStartDate and txtEndDate are both Text Boxes on a form

the line that queries the dates is
"WHERE All_Areas_Result.Date Between" &
SQLDate(Me.txtStartDate.Value) & "and " & SQLDate(Me.txtEndDate.Value)



Unfortunately all the dates are being returned


Can anyone help please


Thanks

Kevin

 

[Andy Hull]

Try passing All_Areas_Result.Date through your function also like:

"WHERE SQLDate(All_Areas_Result.Date) Between " &
SQLDate(Me.txtStartDate.Value) & " and " & SQLDate(Me.txtEndDate.Value)

(Note also, I have added a space after the Between & before the and in your
where clause although I suspect that was only a typo when you posted the
message)

Also, be careful how the date is entered in Excel - it doesn't matter what
it is formatted as. Click on a cell and look at the data displayed in the
formula bar. I would spot check a few to make sure your formula returns the
day & month the right way round.

Hope this helps
Regards