Date Search Error 2

[cumap]

Hello,

I'm having this problem query searching for date range

SELECT * FROM myTable WHERE DatePurchased = '8/8/2010' ORDER BY ID Desc

and here is the error:
Microsoft OLE DB Provider for ODBC Drivers error '80040e07'

[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.

The ONLY way that could get me output is replacing = with LIKE and that is not how I'd like to do since my next search will do BETWEEN the 2 dates.

Thanks

 

[cumap]

Just in case you wonder, my date/time field using Short Date

 

[SkipVought]

SELECT * FROM myTable WHERE DatePurchased = #8/8/2010# ORDER BY ID Desc [/quote]
## converts the STRING to a DATE.

Skip,
[sub]
[glasses]Just traded in my old subtlety...
[b]for a NUANCE![/b][tongue][/sub]

 

[PHV]

SELECT * FROM myTable WHERE DatePurchased = #2010-08-08# ORDER BY ID Desc

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[cumap]

Thanks, that works but the outcome of this query is not as I expect, hope you can help

my data include 3 dates: 6/7/1111, 8/21/1245, 7/28/1110

here is my range search query:

SELECT * FROM myTable WHERE DatePurchased >= #1/1/1111# AND DatePurchased <= #11/11/1111# ORDER BY ID Desc

output:
6/7/1111, 7/28/1110

I don't think the 7/28/1110 should be included, do you?

 

[cumap]

I forgot: the query has already included cdate() for both fromDate to toDate. This is the original for code

SELECT * FROM myTable WHERE DatePurchased >= #"&cDate(from)&"# AND DatePurchased <= #"&cDate(to)&"# ORDER BY ID Desc

 

[SkipVought]

Mine returns 6/7/1111, using your data values?????

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[cumap]

What give here...?

I send you my output image

 

[SkipVought]

If you are converting the text using CDate() then there is no need to the ## delimiters.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[cumap]

Great... now I'm having this error intermittenly when refreshed. Got to fix this first and will definitely come back later. Thanks!

ADODB.Recordset error '800a0e7d'

The connection cannot be used to perform this operation. It is either closed or invalid in this context.

 

[SkipVought]

If you're doing this in VBA, assign a variable with the SQL...

dim sSQL as string

sSQL = "SELECT * "
sSQL = sSQL & "FROM myTable "
sSQL = sSQL & "WHERE DatePurchased >= #" & Format(from,'yyyy/mm/dd') & "#"
sSQL = sSQL & "  AND DatePurchased <= #" & Format(to,'yyyy/mm/dd') & "# "
sSQL = sSQL & "ORDER BY ID Desc

using the UNAMBIGUOUS yyyy/mm/dd date text structure and use in the recordset open statement.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[cumap]

This is crazy!!!!

I understand this would have very little related to whatever we are talking about, but I just realized how strange my outputs compare to my Access Date/Time data.

the Date/Time data in the table are:
11/11/1111
1/11/1245
10/24/2010

and, respectively, my display outputs:
6/7/1111
9/9/1244
10/24/2010

 

[SkipVought]

FYI:

Dates are NUMBERS, with reference to 12/30/1899, which is day ZERO. Dates prior to this date are negative numbers.

So you ought to be able to convert your 1111 year dates to negative numbers, using CLng() to 'see' the actual numeric values and maybe see the relative differences.

Because some regions use dd/mm/yyyy or mm/dd/yyyy, it is always a better approch to use the UNAMBIGUOUS yyyy/mm/dd structure.

Good luck figuring this one out!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[cumap]

You are absolutely right, SkipVought. I totally forgot about Date/Time limited range value. Especially, SmallDateTime.

Turn out if I edit the date value to something more reasonable in our 20th century, EVERYTHING works just fine.

Thank you, SkipVough for the patience you have for me, this idiot.