Menu
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.
721
You can compare dates to see which one is more recent, you can add and subtract a certain amount of days month, years to a specific date. You can get the current date ….
To format a date in VBA, we use the inbuilt FORMAT function itself. It takes input as the date format and returns the desired format required. The arguments required for this function are the expression and the format type. Formatting date and time are sensitive things in Excel, and the same applies to VBA.
But usually, these values are dates, not string values. So, even though the data type assigned is “String,” we can still convert to date using the data type conversion function CDATE VBA.
In VBA dates are represented as sequential whole numbers. The Date data type supports dates from 1 January 1900 to 31 December 9999. A whole number represents the number of days since 1 January 1900. A negative whole number represents dates before 30 December 1899.
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)
80
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
