ADO SQL date criteria type mismatch

[StevK]

Hi folks,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I’m fairly new to this but have had some success working in Excel with copying of data to ACCESS.accdb tables and from ACCESS queries back to Excel. Currently I am using ADO to first copy a list of available report dates from ACCESS to Excel. This list then comprises the choices in a data validation list box from which the user can choose which report date they want. Then I am using a SQL statement (again in Excel ) to get the data I want from an ACCESS standard “select” query, and apply the previously chosen date as the criteria, such that only the data for the chosen report date will be copied over to Excel. My code works fine for any text or numeric criteria but fails as soon as I attempt to use a date, even though these dates have been copied over from the ACCESS db. Here’s the code starting after obtaining the list of report dates:<o></o>
<o>

</o>
'Activex data objects 2.8 referenced<o></o>
<o></o>
Dim DBFullName As String<o></o>
Dim Connection1 As ADODB.Connection<o></o>
Dim Recordset1 As ADODB.Recordset<o></o>
Dim rptDate As String<o></o>
Dim sSQL as String<o></o>
<o> </o>
Sheets("Weekly_Drilling_status").Activate<o></o>
<o></o>
' Connect to Access Database<o></o>
DBFullName = ThisWorkbook.Path & "\Drill progress.accdb"<o></o>
Set Connection1 = New ADODB.Connection<o></o>
Connection1.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & DBFullName & ";"<o></o>
Set Recordset1 = New ADODB.Recordset<o></o>
<o></o>
rptDate = ActiveSheet.Cells(1, 4).Value 'This is the chosen report date 'in dd.mm.yyyy and the debugger confirms this
sSQL = "SELECT * FROM [qry1d_DrillProgress_data_for_Excel_plot] WHERE mydate = '# " & rptDate & "#'"<o></o>
<o></o>
With Recordset1<o></o>
.Open sSQL, Connection1 ‘Here is where I get the error 13 “data type mismatch in criteria expression”<o></o>
<o>

</o>
Note that this works fine: 'sSQL = "SELECT * FROM [qry1d_DrillProgress_data_for_Excel_plot] WHERE mydate = #02/08/2012#"<o></o>
But this doesn’t: 'sSQL = "SELECT * FROM [qry1d_DrillProgress_data_for_Excel_plot] WHERE mydate = #08.02.2012#"<o></o>
The only difference being the choice of date separator. Oddly, in ACCESS the query works fine if I type #08.02.2012# but in SQL view the periods are changed to slashes even though I have a period set for the date separator in my regional settings.<o></o>
<o> </o>
Here are some other things I’ve tried but to no avail:<o></o>
<o> </o>
'ActiveSheet.Cells(1, 4).Value = DateSerial(Right([ActiveSheet.Cells(1, 4).Value], 4), Mid([ActiveSheet.Cells(1, 4).Value], 4, 2), Left([ActiveSheet.Cells(1, 4).Value], 2))<o></o>
'ActiveSheet.Cells(1, 4).NumberFormat = "dd/mm/yyyy"<o></o>
'rptDate = DateSerial(ActiveSheet.Cells(1, 4).Value)<o></o>
rptDate = Format([ActiveSheet.Cells(1, 4).Value)], "dd/mm/yyyy")<o></o>
'rptDate = Format(rptDate, "short date")<o></o>
'rptDate = Format([ActiveSheet.Cells(1, 4).Value)], "dd" & "/" & "mm" & "/" & "yyyy")<o></o>
'rptDate = DateValue(Day([ActiveSheet.Cells(1, 4).Value)]) & "/" & Month([ActiveSheet.Cells(1, 4).Value)]) & "/" & Year([ActiveSheet.Cells(1, 4).Value)]))<o></o>
'rptDate = Format(rptDate, "dd" & "/" & "mm" & "/" & "yyyy")<o></o>
'rptDate = Format([ActiveSheet.Cells(1, 4).Value)], "short date")<o></o>
'sSQL = "SELECT * FROM [qry1d_DrillProgress_data_for_Excel_plot] WHERE mydate = '#" & Format(rptDate, "dd/mm/yyyy") & "#'"<o></o>
<o> </o>
I know about data types and working with dates in ACCESS but I’m stymied with this. Appreciate any help.<o></o>
Cheers.

 

[pcc]

have you tried

sSQL = "SELECT * FROM [qry1d_DrillProgress_data_for_Excel_plot] WHERE mydate =#" & rptDate & "#;"

 

[Jon von der Heyden]

I tend to use Long Integer values for my dates.

rptDate = ActiveSheet.Cells(1, 4).Value2

sSQL = "SELECT * FROM [qry1d_DrillProgress_data_for_Excel_plot] WHERE mydate = " & rptDate & ";"

 

[StevK]

Yes I tried several similar options but I get a syntax error whenever I leave out the single quotes. I solved it though. See Jon's reply.

 

[StevK]

First- it works!!
Secondly, I'm not sure why. In ACCESS this field is formatted as a date, plus we have excluded the pound symbols (#) that I know must be used to denote dates, however I have noticed in the past when entering a date criteria in ACCESS that it will sometimes add the pound symbols automatically but I've never pinned down the reason it doesn't always do that.

Lesson learned: It appears that using the .value2 property will convert the date to a number, which can be entered in the criteria statement without the pound symbols, but ACCESS will recognize it as a date (??)

Many thanks Jon

 

[Jon von der Heyden]

You are absolutely right that in Access one would encapsulate a date value within pound symbols. It works a bit like a type declaration character. You pass it a date string and it knows to treat it like a date because of the pound symbols. And the same does typically work in ADO SQL strings.

But I don't like using date strings because there is confusion sometimes over whether or not it interprets the string as m/d/y or d/m/y. That's why I use Long Integers.

Value2 property is like Value property only it doesn't recognise date and currency data types; hence why we end up with the Long Integer equivalent value. If you want to know why it works, consider that in fact all dates are values that represent the # of days elapsed since 0 January 1900 (overstated by 1 because 1900 is assumed to be a leap year when in fact it was not).

It's a bit like... You have a value in an Access table that is formatted to display 2 decimal values, e.g. 6.00. Now you could query all records for 6.00, but you will get the same result if you query 6. We have similar options with date. We can pass a date (in pound symbols), or we can just feed it the date value.

 

[StevK]

Yes you are talking about the date serial number. Just now in ACCESS I tried entering the query date criteria as a serial number (40947) instead of #08.02.2012# (something I've never tried) and it worked. But still it seems to me that the potential confusion remains, since in converting the date to the serial number using .value2, how does it know whether 08.02.2012 is Feb08, 2012 or Aug02, 2012? Is it correct to assume that it takes its queue from the regional language settings?
Thanks again.

 

[Jon von der Heyden]

Any date you have in Excel is a number, so it doesn't actually try interpret it from its' format. Any formatting applied is just a user choice of how (s)he wants to display the number.

When it comes down to date entry in Excel, then yes, Regional Settings have a role to play. I am in the UK and I use UK Short Date d/m/y. Thus if I enter 08/02/2012, Excel knows that I want 8th February 2012. The long integer value for this is (40947). I can format this as dd.mm.yy and I will see 08.02.2012, but the fact remains that the underlying value is 40947. I can format it as mm-dd-yy and I will see 02-08-2012 and still the underlying value will be 40947. Value2 property directly refers to this value.

I assure you, you are quite safe to use this property. Hope this clarifies.