Data type mismatch error (1 Viewer)

JYK

New member
Local time
Today, 15:13
Joined
Jun 17, 2022
Messages
9
Hi there
I can not fix this error for about 2 hours.
What am I doing wrong?

when I query like below, I get 'Data type mismatch in criteria expression' or 'invalid use of null'. The message changes when I change where clause.

select * from my_table where cdate('2022-05-24 23:07:07') < cdate(nz(mycolumn,'2000-01-01 00:00:00'));
or
select * from my_table where mycolumn is not null and cdate('2022-05-24 23:07:07') < cdate(mycolumn);

mycolumn is varchar type and has null values. and data format is like yyyy-MM-dd HH:mm:ss

I can not find anything wrong
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 14:13
Joined
May 7, 2009
Messages
19,169
What fieldType are you adding the criteria.
Show your sql query.
 

JYK

New member
Local time
Today, 15:13
Joined
Jun 17, 2022
Messages
9
What fieldType are you adding the criteria.
Show your sql query.
sorry my mistake. I updated the post please refresh page
 

Minty

AWF VIP
Local time
Today, 06:13
Joined
Jul 26, 2013
Messages
10,355
Replace the single quotes with double quotes.
From the immediate window;

? cdate("2022-05-24 23:07:07")
24/05/2022 23:07:07
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 14:13
Joined
May 7, 2009
Messages
19,169
varchar is a String equivalent in Access?
if it is you can compare it as String.
 

JYK

New member
Local time
Today, 15:13
Joined
Jun 17, 2022
Messages
9
Replace the single quotes with double quotes.
From the immediate window;

? cdate("2022-05-24 23:07:07")
24/05/2022 23:07:07
Thank you for reply.
I tried it but still throws error.

every thing ok when I run like
select cdate("2022-05-24 23:07:07"); or
select cdate('2022-05-24 23:07:07');
select cdate("2022-05-24 23:07:07");


or even

SELECT CDate("2022-05-24 23:07:07")<CDate(Nz(mycolumn,"2000-01-01 00:00:00")) FROM my_table;


Oh now I found something,
There is '#Error' value from
SELECT CDate("2022-05-24 23:07:07")<CDate(Nz(mycolumn,"2000-01-01 00:00:00")) FROM my_table;

some of mycolumn is '' (empty string).
How can I handle #Error in this situation
 

CJ_London

Super Moderator
Staff member
Local time
Today, 06:13
Joined
Feb 19, 2013
Messages
16,553
varchar implies you are using sql server. so is this query being run in sql server or in access against a linked table?
 

JYK

New member
Local time
Today, 15:13
Joined
Jun 17, 2022
Messages
9
varchar implies you are using sql server. so is this query being run in sql server or in access against a linked table?
Nope I'm using both MS Access application (SQL View) and C# OLEDB Access Driver.
I checked the type of mycolumn using C# connection GetSchema() function
 

Minty

AWF VIP
Local time
Today, 06:13
Joined
Jul 26, 2013
Messages
10,355
In that case you were nearly right with

select * from my_table where (mycolumn is not null or mycolumn not = "") and cdate('2022-05-24 23:07:07') < cdate(mycolumn);
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 14:13
Joined
May 7, 2009
Messages
19,169
I'd tried it as string but the output was not what I expected
show your new sql.
it is a String so no need conversion to Date since it is "arranged" as "yyyy-mm-dd hh:nn:ss", it will
retrieve it if the criteria is correct or your SQL is well formed.
 
  • Like
Reactions: JYK

CJ_London

Super Moderator
Staff member
Local time
Today, 06:13
Joined
Feb 19, 2013
Messages
16,553
I checked the type of mycolumn using C# connection GetSchema() function
why? why not just open the access table in design view?

and as arnel said, you should just be able to use a string comparison

'2022-05-24 23:07:07' <mycolumn
 
  • Like
Reactions: JYK

JYK

New member
Local time
Today, 15:13
Joined
Jun 17, 2022
Messages
9
In that case you were nearly right with

select * from my_table where (mycolumn is not null or mycolumn not = "") and cdate('2022-05-24 23:07:07') < cdate(mycolumn);

SELECT *
FROM my_table
where mycolumn is not null and mycolumn <> '' and CDate("2019-05-24 23:07:07")<CDate(mycolumn)

This failed but this works

SELECT *
FROM my_table
where CDATE("2020-08-24 23:07:07") < CDATE(NZ(IFF(mycolumn = '', NULL, mycolumn), "2000-01-01 00:00:00"))


Thank you :)
 

JYK

New member
Local time
Today, 15:13
Joined
Jun 17, 2022
Messages
9
why? why not just open the access table in design view?

and as arnel said, you should just be able to use a string comparison

'2022-05-24 23:07:07' <mycolumn
I'm super newbie 😿
 

JYK

New member
Local time
Today, 15:13
Joined
Jun 17, 2022
Messages
9
show your new sql.
it is a String so no need conversion to Date since it is "arranged" as "yyyy-mm-dd hh:nn:ss", it will
retrieve it if the criteria is correct or your SQL is well formed.

My mistake. I tried again, found this works.

SELECT *
FROM my_table
where mycolumn is not null and "2020-08-24 23:07:07" < mycolumn

the correct way to compare string type of datetime in access is this THANK YOU!!
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 14:13
Joined
May 7, 2009
Messages
19,169
can you test if you get same result?

SELECT *
FROM my_table
where NZ(mycolumn , "2000-01-01 00:00:00") > "2020-08-24 23:07:07"
 
  • Like
Reactions: JYK

CJ_London

Super Moderator
Staff member
Local time
Today, 06:13
Joined
Feb 19, 2013
Messages
16,553
using C# connection GetSchema() function
in that case, sounds like this function is not reliable, at least in the context of Access - access does not have varchar as a datatype, it's equivalent would be text. They are only equivalent to a certain extent. varchar can have a size of 8000 bytes - which can typically represent around 4000 or 8000 characters depending on the encoding of the character set. Text on the other hand is limited to 255 characters regardless of encoding so might by 255 bytes or it might be around 510 bytes. (these figures ignore the additional bytes required to store the physical size)
 
  • Like
Reactions: JYK

Pat Hartman

Super Moderator
Staff member
Local time
Today, 02:13
Joined
Feb 19, 2002
Messages
42,981
The problem seems to be that the string field allows ZLS. ZeroLengthStrings are NOT the same as Null. That is why the expression that converted the ZLS to null worked and the plain dDate() failed with a type conversion error.

I agree with arnelgp. There is no need to convert either field to an actual date as long as they are actually formatted as you showed.
 

Users who are viewing this thread

Top Bottom