8

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.

Improve this question

1 Answer 1

Reset to default
6

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)

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Thanks for the advice, I'm trying to change date fields and date variable formats in excel and access but doesn't works yet... :(
Sorry but I can't solve this. In access, in date field, i've change format to "dd/mmm/yyyy hh:mm:ss" and in excel, i'm coding with date formatted in same format and no data is displayed :(
You need to change the date format in your query (sql = "SELECT Date FROM Table WHERE Date BETWEEN #" & DateMin & "# AND #" & DateMax & "#) - DateMin and DateMax need to be formatted as dd/mmm/yyyy hh:mm:ss

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.