p2p.wrox.com: Archives - asp

asp_databases thread: Getting Data type mismatch in criteria expression.

Message #1 by "Sanju Kumar" <hawaii_sanju@y...> on Mon, 23 Apr 2001 21:29:32

Database being used: Access 2000



This is my SQL Statement that I am using:

strSQL3 

"SELECT EventID, tblEventType.EventType, EventHeader, Description, "&_

"EventPlace, EventDate, EventTime, CostOfEvent, EventPostedBy " &_

"FROM tblEventType INNER JOIN tblEvents ON tblEventType.EventTypeID = 

tblEvents.EventTypeID "&_

"WHERE EventPlace ='"&Replace(EventPlace,"'","''")&"' "&_

"AND EventDate = '" &EventDate&"' "&_

"AND EventTime = '" &EventTime&"'"



I get the following error:

Error Type:

Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)

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

expression.



And the Error is on the last two lines of my SQL statement.  If I comment 

the last two lines out, the query runs fine.



The EventDate variable is in the format ex: 07/10/01 (in Access I set the 

datatype to Date/Time, Format: Short Date.



The EventTIme variable is in the format ex: 5:30 PM (in Access I set the 

datatype to Date/Time, Format: Medium Time.



If anyone has had this problem can you please helpout.



Regards,

Sanju.

Message #2 by Kyle Burns <kburns@c...> on Mon, 23 Apr 2001 15:55:52 -0500

Do Response.Write "<!-- " & strSQL3 & " -->"



Copy the generated SQL and debug it in Access query window.



>> -----Original Message-----

>> From: Sanju Kumar [mailto:hawaii_sanju@y...]

>> Sent: Monday, April 23, 2001 4:30 PM

>> To: ASP Databases

>> Subject: [asp_databases] Getting Data type mismatch in criteria

>> expression.

>> 

>> 

>> Database being used: Access 2000

>> 

>> This is my SQL Statement that I am using:

>> strSQL3 

>> "SELECT EventID, tblEventType.EventType, EventHeader, 

>> Description, "&_

>> "EventPlace, EventDate, EventTime, CostOfEvent, EventPostedBy " &_

>> "FROM tblEventType INNER JOIN tblEvents ON 

>> tblEventType.EventTypeID = 

>> tblEvents.EventTypeID "&_

>> "WHERE EventPlace ='"&Replace(EventPlace,"'","''")&"' "&_

>> "AND EventDate = '" &EventDate&"' "&_

>> "AND EventTime = '" &EventTime&"'"

>> 

>> I get the following error:

>> Error Type:

>> Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)

>> [Microsoft][ODBC Microsoft Access Driver] Data type mismatch 

>> in criteria 

>> expression.

>> 

>> And the Error is on the last two lines of my SQL statement.  

>> If I comment 

>> the last two lines out, the query runs fine.

>> 

>> The EventDate variable is in the format ex: 07/10/01 (in 

>> Access I set the 

>> datatype to Date/Time, Format: Short Date.

>> 

>> The EventTIme variable is in the format ex: 5:30 PM (in 

>> Access I set the 

>> datatype to Date/Time, Format: Medium Time.

>> 

>> If anyone has had this problem can you please helpout.

>> 

>> Regards,

>> Sanju. 

>> ---

>> SoftArtisans helps developers build robust, scalable Web 

>> applications!

>> Excel Web reports, charts: 

http://www.softartisans.com/excelwriter.html

File uploads: http://www.softartisans.com/saf.html

Transactional file management: http://www.softartisans.com/saf1.html

Scalability: http://www.softartisans.com/saxsession.html

ASPstudio value pack: http://www.softartisans.com/aspstudiosuite.html




$subst('Email.Unsub')

Message #3 by =?iso-8859-1?Q?H=E5kan_Frennesson?= <hakan@c...> on Mon, 23 Apr 2001 23:22:29 +0200

Hi!



Have you tried to omit the ' around EventDate and EventTime? A second

suggestion is to surround the EventDate with pound signs or even better,

convert the EventDate and EventTime values to strings using the CStr

function.



Hth,



Hakan



-----Original Message-----

From: Sanju Kumar [mailto:hawaii_sanju@y...]

Sent: den 23 april 2001 21:30

To: ASP Databases

Subject: [asp_databases] Getting Data type mismatch in criteria

expression.





Database being used: Access 2000



This is my SQL Statement that I am using:

strSQL3 

"SELECT EventID, tblEventType.EventType, EventHeader, Description, "&_

"EventPlace, EventDate, EventTime, CostOfEvent, EventPostedBy " &_

"FROM tblEventType INNER JOIN tblEvents ON tblEventType.EventTypeID 

tblEvents.EventTypeID "&_

"WHERE EventPlace ='"&Replace(EventPlace,"'","''")&"' "&_

"AND EventDate = '" &EventDate&"' "&_

"AND EventTime = '" &EventTime&"'"



I get the following error:

Error Type:

Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)

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

expression.



And the Error is on the last two lines of my SQL statement.  If I comment

the last two lines out, the query runs fine.



The EventDate variable is in the format ex: 07/10/01 (in Access I set the

datatype to Date/Time, Format: Short Date.



The EventTIme variable is in the format ex: 5:30 PM (in Access I set the

datatype to Date/Time, Format: Medium Time.



If anyone has had this problem can you please helpout.



Regards,

Sanju.

---

SoftArtisans helps developers build robust, scalable Web applications!

Excel Web reports, charts: http://www.softartisans.com/excelwriter.html

File uploads: http://www.softartisans.com/saf.html

Transactional file management: http://www.softartisans.com/saf1.html

Scalability: http://www.softartisans.com/saxsession.html

ASPstudio value pack: http://www.softartisans.com/aspstudiosuite.html

Message #4 by "ASPCoder123" <sahmer@n...> on Mon, 23 Apr 2001 22:10:04

Hi,



The problem with Access is that when you try to run query against a date 

field it tries to find both data and time data in it and return un-

expected result if either of two is absent.



You can try this< i hope it will work, when comparing Date use the 

statement like



Cdate(str_Date) & " 00:00:00 AM"



and when comparing Time do something like this



FormatDateTime(Cdate(str_Time),3) 'which will assume hh:mm:ss depending    

on your web server setting



Hope this will work for you. 





> Database being used: Access 2000

> 

> This is my SQL Statement that I am using:

> strSQL3 

> "SELECT EventID, tblEventType.EventType, EventHeader, Description, "&_

> "EventPlace, EventDate, EventTime, CostOfEvent, EventPostedBy " &_

> "FROM tblEventType INNER JOIN tblEvents ON tblEventType.EventTypeID = 

> tblEvents.EventTypeID "&_

> "WHERE EventPlace ='"&Replace(EventPlace,"'","''")&"' "&_

> "AND EventDate = '" &EventDate&"' "&_

> "AND EventTime = '" &EventTime&"'"

> 

> I get the following error:

> Error Type:

> Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)

> [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria 

> expression.

> 

> And the Error is on the last two lines of my SQL statement.  If I 

comment 

> the last two lines out, the query runs fine.

> 

> The EventDate variable is in the format ex: 07/10/01 (in Access I set 

the 

> datatype to Date/Time, Format: Short Date.

> 

> The EventTIme variable is in the format ex: 5:30 PM (in Access I set the 

> datatype to Date/Time, Format: Medium Time.

> 

> If anyone has had this problem can you please helpout.

> 

> Regards,

Message #5 by "Charles Feduke" <webmaster@r...> on Mon, 23 Apr 2001 20:27:01 -0400

    Since you're using Access, you should pass dates in #s, not 's.



#January 31, 2000#



    I think that 's are still valid however so this may not solve your problem.



- Chuck



----- Original Message ----- 

From: "Sanju Kumar" <hawaii_sanju@y...>

To: "ASP Databases" <asp_databases@p...>

Sent: Monday, April 23, 2001 9:29 PM

Subject: [asp_databases] Getting Data type mismatch in criteria expression.





> Database being used: Access 2000

> 

> This is my SQL Statement that I am using:

> strSQL3 

> "SELECT EventID, tblEventType.EventType, EventHeader, Description, "&_

> "EventPlace, EventDate, EventTime, CostOfEvent, EventPostedBy " &_

> "FROM tblEventType INNER JOIN tblEvents ON tblEventType.EventTypeID = 

> tblEvents.EventTypeID "&_

> "WHERE EventPlace ='"&Replace(EventPlace,"'","''")&"' "&_

> "AND EventDate = '" &EventDate&"' "&_

> "AND EventTime = '" &EventTime&"'"

> 

> I get the following error:

> Error Type:

> Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)

> [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria 

> expression.

> 

> And the Error is on the last two lines of my SQL statement.  If I comment 

> the last two lines out, the query runs fine.

> 

> The EventDate variable is in the format ex: 07/10/01 (in Access I set the 

> datatype to Date/Time, Format: Short Date.

> 

> The EventTIme variable is in the format ex: 5:30 PM (in Access I set the 

> datatype to Date/Time, Format: Medium Time.

> 

> If anyone has had this problem can you please helpout.

> 

> Regards,

> Sanju.

Message #6 by "Sanju Kumar" <hawaii_sanju@y...> on Wed, 25 Apr 2001 01:42:31

Hi,



Ok the the pound sign around the date solved the problem. Converting the 

the date and time to String or omiting the ' didn't work.



I found the folling on another site:



"If you are using Access, you need to surround dates with pound signs (#). 

With most other databases, including SQL Server 7, dates are surrounded 

with apostrophes (') and are treated like strings. Make sure your date is 

a valid date. You can do this in VBScript using the isDate() function."



Thanks for all the input.



Regards,

Sanju.



> Hi!

> 

> Have you tried to omit the ' around EventDate and EventTime? A second

> suggestion is to surround the EventDate with pound signs or even better,

> convert the EventDate and EventTime values to strings using the CStr

> function.

> 

> Hth,

> 

> Hakan

> 

> -----Original Message-----

> From: Sanju Kumar [mailto:hawaii_sanju@y...]

> Sent: den 23 april 2001 21:30

> To: ASP Databases

> Subject: [asp_databases] Getting Data type mismatch in criteria

> expression.

> 

> 

> Database being used: Access 2000

> 

> This is my SQL Statement that I am using:

> strSQL3 

> "SELECT EventID, tblEventType.EventType, EventHeader, Description, "&_

> "EventPlace, EventDate, EventTime, CostOfEvent, EventPostedBy " &_

> "FROM tblEventType INNER JOIN tblEvents ON tblEventType.EventTypeID 

> tblEvents.EventTypeID "&_

> "WHERE EventPlace ='"&Replace(EventPlace,"'","''")&"' "&_

> "AND EventDate = '" &EventDate&"' "&_

> "AND EventTime = '" &EventTime&"'"

> 

> I get the following error:

> Error Type:

> Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)

> [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria

> expression.

> 

> And the Error is on the last two lines of my SQL statement.  If I comment

> the last two lines out, the query runs fine.

> 

> The EventDate variable is in the format ex: 07/10/01 (in Access I set the

> datatype to Date/Time, Format: Short Date.

> 

> The EventTIme variable is in the format ex: 5:30 PM (in Access I set the

> datatype to Date/Time, Format: Medium Time.

> 

> If anyone has had this problem can you please helpout.

> 

> Regards,

> Sanju.

>

Return to Index

P2P Archives