VBa filter by date

[crododo]

Hi,

It there is one thing I have in programing it is date conversions...

I have this line of code:
If Not filterDate.Value = vbNull Then
strCounter = strCounter + 1
If strCounter > 1 Then strChosen = strChosen & "AND "
strChosen = strChosen & "(([qrySchoolAttendancePerDay].[DATE]) = '" & filterDate.Value & "') "
End If

And when I run this I get an error: Data type mismach in criteria expression

filterDate.Value is: shortdate chosen from acceess calendar
date in qry is stored in table as: DATE/TIME shortdate as well

Any idea?

Thx

 

[namliam]

1) Date is a reserved word, strongly suggest changing your column name... or keep in mind for your next project to not use it.
2) To filtter dates you dont use ' around it, that is for text... instead use #
3) keep in mind that working with dates is "touchy" strongly suggest using Format around your value to put it in YYYY-MM-DD value or in the US order MM-DD-YYYY ... if you stick to the EU date.... it will cause problems.

 

[crododo]

Hi,

Thanks for anwesr

When I manualy enter date as #08/13/2013# it filters OK, but when I use inbuild calendar I get and error.

How do I format resoult from calendar to be displaed as data mm/dd/yyyy but also a question

How can I know in my table is date stored as dd/mm/yyyy od mm/dd/yyyy and will this work when database is copied on a machine with different time settings?

I am from EU but I am volontering in Africa, so all things with dates is extrimly confusing

Thanks for clarification

 

[namliam]

in sql, using the ## syntax is always US format (mm/dd/yyyy), ISO format always works too because the "conversion" always works.... euro dates guarantee problems becuase 12-9-2013 can be 9 dec or 12 Sept just as easily.
13-9-2013 can only be interperted one way though....

You format the date from the datepicker using the function format
Format(filterDate.Value, "YYYY-MM-DD")

Instead of the convoluted
strCounter = strCounter + 1
If strCounter > 1 Then strChosen = strChosen & "AND "
way of doing things, why not make things a bit easier to manage...
Simply always add an " AND " (note the spaces) in front of your statement
If Not filterDate.Value = vbNull Then
strChosen = strChosen & " AND (([qrySchoolAttendancePerDay].[DATE]) = '" & filterDate.Value & "') "
End If

and before applying it remove any leading AND:
strChosen = mid(strchosen, 5,999)