SQL Query between dates in VBA

sql excel vba date between
Monchou2 picture Monchou2 · Jul 31, 2014 · Viewed 26.2k times · Source

I've a problem making a sql query in VBA using excel and an access database. In mi VBA code I've two data variables with this content:

DateMin = 31/07/2014 22:00:00

DateMax = 01/08/2014 06:00:00

And I have an access database with many data with a date field. I'm trying to extract from the database, data with dates between my two variables and I'm coding this:

sql = "SELECT Date FROM Table WHERE Date BETWEEN #" & DateMin & "# AND #" & DateMax & "#

But doesn't works. Results are incorrect, with dates that aren't between my two dates.

However if I change the sentence and code this

sql = "SELECT Date FROM Table WHERE Date > #" & DateMin & "#

It works! Gives data with higher dates than DateMin but I haven't DateMax to stop it. I think the problem is in the second part of my first code, because if I code

sql = "SELECT Date FROM Table WHERE Date < #" & DateMax & "#

I have no data! Why doesn't give me data lower than DateMax? Because of that I think doesn't works the BETWEEN sentence.

Sorry for my english, i'm trying to explain better I know. Thanks.

Answer

Kai picture Kai · Jul 31, 2014

You should always use an unambiguous date format. When you have a date formated 00/00/0000, Access has to guess whether that's dd/mm/yyyy (British) or mm/dd/yyyy (American).

Your DateMin (31/07/2014 22:00:00) can only be interpreted as dd/mm/yyyy hh:nn:ss, (31 is an invalid month, so it must be a day) so this is the format that Access will use.

However, your DateMax (01/08/2014 06:00:00) is being interpreted in American format - as 8th January 2014 rather than 1st August 2014.

The easiest way to get around this is to supply your dates in ISO format (yyyymmdd) or supply the month as a short word (dd mmm yyyy hh:mm:ss - eg 01 Aug 2014 06:00:00)

Related questions

Accessing SQL Database in Excel-VBA

I am copying an VBA code snippet from MSDN that shows me how to grab results from a SQL query into excel sheet (Excel 2007): Sub GetDataFromADO() 'Declare variables' Set objMyConn = New ADODB.Connection Set objMyCmd = New ADODB.Command Set objMyRecordset = …

Read More
Using Excel VBA to export data to MS Access table

I am currently using following code to export data from worksheet to MS Access database, the code is looping through each row and insert data to MS Access Table. Public Sub TransData() Application.ScreenUpdating = False Application.EnableAnimations = False Application.EnableEvents = …

Read More
Excel VBA - connect to sql with a trusted connection (no uid/pwd)

Is there a way to have Excel connect to Sql Server using the current user's network windows authentication (trusted connection/integrated security)? I don't want to rely on a sql login or have a uid/password in my code.

Read More