Date/time data type mismatch in sql using access

[cpdjimmy]

Here is my syntax:
SELECT ListingsTable.PubID, Count(ListingsTable.ListingID) AS CountOfListingID, ListingsTable.Agent, ListingsTable.OfficeCode, ListingsTable.Company, ListingsTable.[CompPhn#], ListingsTable.ListDate
FROM ListingsTable
GROUP BY ListingsTable.PubID, ListingsTable.Agent, ListingsTable.OfficeCode, ListingsTable.Company, ListingsTable.[CompPhn#], ListingsTable.ListDate
HAVING (((Count(ListingsTable.ListingID))>50) AND ((ListingsTable.ListDate)>='2/1/2002')) <-- error!!
ORDER BY Count(ListingsTable.ListingID) DESC;


getting an error in comparing the date. basically i need all dates after 2/1/2002.
Here is the exact error &quot;Data type mismatch in criteria expression.&quot; any help would be appreciated. thank you in advance. Jimmy

 

[lespaul]

When using dates as criteria in Access they are delimited by # so you want it to be:

SELECT ListingsTable.PubID, Count(ListingsTable.ListingID) AS CountOfListingID, ListingsTable.Agent, ListingsTable.OfficeCode, ListingsTable.Company, ListingsTable.[CompPhn#], ListingsTable.ListDate
FROM ListingsTable
GROUP BY ListingsTable.PubID, ListingsTable.Agent, ListingsTable.OfficeCode, ListingsTable.Company, ListingsTable.[CompPhn#], ListingsTable.ListDate
HAVING (((Count(ListingsTable.ListingID))>50) AND ((ListingsTable.ListDate)>='#2/1/2002#')) <-- error!!
ORDER BY Count(ListingsTable.ListingID) DESC;

And no offense, but if you had done a search in this forum for date query you probably would have found hundreds of threads that answered this question and wouldn't have had to wait for a reply.

HTH



Leslie
landrews@metrocourt.state.nm.us

There are 10 types of people in the world -
those who understand binary
and
those who don't!

 

[telephoto]

SQL likes its date shown yyyy/mm/dd

If you only need that date enter it as 2002/01/02

If you have a need to specify dates check out this thread:
thread701-587445

Use an input box to set a variable, format the date and then insert it into the SQL

HTH
Telephoto

 

[scriverb]

telephoto: I have to disagree with your about SQL only likeing dates in the yyyy/mm/dd format. This may be if the dates are stoed in text fields/variables where SQL is comparing character per character from the left but ACCESS/SQL's date fields are stored as the whole number portion of a double-precision number. When a legitimate datefield format is presented with the DateValue of CDate function the value created upon conversion is compared properly in all instances. this also inluces the use the # sign bracket technique suggested by lespaul. The problem with his suggestion is that he also bracketed the #2/1/20002# with single quotes: They should be removed otherwise it will be considered a string rather than a date value:
((ListingsTable.ListDate)>='#2/1/2002#'))

Post back with any questions concerning these date comparisons.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[telephoto]

Bob
Mr Scriver
Sir

no bull, I appreciate your posts and acknowledge etc..

The only way I can get SQL to find date is to reformat as yyyymmdd and then apply it to the SQL string - is this because I am using Access 97?

If so I can only wait with anticipation the company upgrading. But i'm not holding my breath!

Telephoto

 

[Norris68]

Being British and consequently having been caught out a few times with US/UK date formatting, I always use ISO notation.

In Access, use #01 Jul 2000#
In SQL Server, use {d '2000-07-01'}

I have defined a function to use when I need to format a SQL date within VBA (in public module):

Public Enum SQLDateEnum
SQLDateOnly
SQLDateTime
SQLTimeOnly
End Enum

Public Function SQLDateFormat(InDate As Date, DateFormat As SQLDateEnum) As String
Select Case DateFormat
Case SQLDateOnly:
SQLDateFormat = &quot;{d '&quot; & Format$(InDate, &quot;yyyy-mm-dd&quot & &quot;'}&quot;
Case SQLTimeOnly:
SQLDateFormat = &quot;{t '&quot; & Format$(InDate, &quot;hh:nn:ss&quot & &quot;'}&quot;
Case SQLDateTime:
SQLDateFormat = &quot;{ts '&quot; & Format$(InDate, &quot;yyyy-mm-dd hh:nn:ss&quot & &quot;'}&quot;
End Select
End Function

 

[lespaul]

Thanks for the correction Bob, that's the problem with copy & paste and not reviewing throughly!

leslie