date format

[tpoettker]

I am fairly new to MS Access and VBA. I have an SQL I built searching a query I created. The sql is searching for records where BegDate >= ARCallDate and EndDate <= ARCallDate.

The SQL does not give an error but does not return any records. I use 1/1/2010 for the start date and 2/1/2010 for the end date and there are records with ARCallDate equal to 1/29/2010.

I placed FORMAT(SDATE, "MM/DD/YYYY") and that didn't do the trick either. The actual SQL is

"Select ARCallID, ARCallDate, ARCallTypeID, ARCustID, ARCallPerson, ARCallNote from fqry_ARCall_list where ARCustID = 20287 and (ARCallDate >= 01/01/2010 and ARCallDate <= 02/02/2009)"

When I run this SQL as a query, it returns 3 records. When I run this SQL in my VBA code, nothing returns. What am I missinig?

[si_the_geek]

Welcome to VBForums

To start with, the SQL statement you have showed does not actually contain any dates (or anything that could be interpreted as one), only calculations that return very small numbers (02/02/2009 = 0.0049).

For an explanation and examples of delimiters to use around values within SQL statements, see the article How do I use values (numbers, strings, dates) in SQL statements? from our Database Development FAQs/Tutorials (at the top of this forum)


Next up, there are no dates that will satisfy both of these criteria:

(ARCallDate >= 01/01/2010 and ARCallDate <= 02/02/2009)"

...I presume you typed one of those incorrectly.