Data type mismatch in criteria expression error

[Pandora-Box]

select * from table where format(datevalue([time stamp]),"mm/dd/yyyy") = format(Datevalue(date()-1),"mm/dd/yyyy") and [time stamp] is not null;

This Query is giving me an error Data type mismatch in criteria expression. Not sure what is the missing link here. Could someone check and please provide me pointers on the same?

 

[CJ_London]

dates are stored as a form of decimal number so there is no need to convert them to strings (the format function) to make a comparison.

where datevalue([timestamp])=date()-1

should be sufficient.

Timestamp, by definition, should not be null so not sure you need the 'is not null' part

if this is your actual query then be aware that 'table' is a reserved word. Using reserved words can cause unexpected and misleading errors. See this link

https://support.office.com/en-us/ar...da237c63eabe?ui=en-US&rs=en-US&ad=US&fromAR=1

It is also not a good idea to have spaces in table and field names. If it is for presentation purposes, use the caption property - that is what it is for.

 

[stopher]

I agree with CJ_London's solution as a better way forward. What I don't understand though is why the OPs solution doesn't work. I'm wondering if the syntax of the time stamp is not in a format that Datevalue can interpret. This would cause the offending Data type mismatch.

What data type is the time string and what does it look like i.e. post some examples.

 

[CJ_London]

I'm wondering if the syntax of the time stamp is not in a format that Datevalue can interpret.

or as suggested it could be because of using a reserved word

 

[Pandora-Box]

Sorry for not mentioning the table name was intentionally changed

select * from test where datevalue([time stamp]) = #10/05/2016# and ([time stamp] is not null or [time stamp] <> "" or [time stamp] <> " ");

I have hard coded yesterdays date and even it gives me the same error

Time stamp data type => DATE\TIME

Changed name from Table to Test

 

[CJ_London]

you said timestamp is date/time datatype, which is not text so

or [time stamp] <> "" or [time stamp] <> " "

is the reason for the problem

it cannot be text and it cannot have a space

start with this

select * from test where datevalue([time stamp]) = date()-1

and see if you get a result

timestamps by definition should never be null but if you get errors then add

and [time stamp] is not null

or change to

datevalue(nz([time stamp])) = date()-1

 

[Pandora-Box]

Weird thing is both the below query worked

select * from Test where [time stamp] >= cdate(date()-1) and [time stamp] < cdate(date());

select * from Test where [time stamp] >= cdate(#10/06/2016#-1) and [time stamp] < cdate(#10/06/2016#);

 

[Pandora-Box]

cj_London let me try your idea as well and get back. Thanks

 

[Pandora-Box]

select * from Test where datevalue(nz([time stamp])) = date()-1 ;

This query also ended in error but the query I posted above worked.

Thanks all for your suggestions.

 

[CJ_London]

well it would do, you've removed the reason for the error.

and cdate converts a string to a date, if you pass it a date type (which you are), it just passes it back since it doesn't need converting.

so all you are doing with it is converting a date to a date i.e. no change.

You are going to be difficult to help if you don't respond to posts making suggestions and just keep moving the goalposts, so good luck with your project

 

[CJ_London]

just revising this thread and realised I missed something

try

select * from Test where int(nz([time stamp])) = date()-1 ;

 

[Pandora-Box]

cj_london,

Both these queries below worked:

select * from open_acn where int(nz([time stamp])) = date()-1 ;

select * from open_acn where int(nz([time stamp])) = #10/06/2016#-1;

Thanks for your help , time and patience.. Apologies if I offended you by any chance.

 

[CJ_London]

no offence - just frustrating when suggestions are apparently ignored. Just be aware that you get one notification of responses to threads since your last visit - you may find there have been more than one, so check the thread before responding