Data Type Mismatch - When comparing using greater than/equal to

[ccoscina]

<sub>I recently had basically the same problem that I'm having right now, which was covered and solved in this thread:


https://www.accessforums.net/queries...tml#post258158

Basically, I'm trying to return records where the TransDateTime is greater than 7 am on the current day. This table is linked to a CSV that is generated by SQL server, so all the data types of the table are text.

Here is the current WHERE clause:
WHERE CDATE(LEFT(TransDateTime, 19)) >= FORMAT(Date(),"YYYY/MM/DD 07:00:00");

However, this is causing a Data type mismatch error. The interesting thing is, if I use <= instead of >= (or if I keep the same sign, but switch the Date() value to the left side and TransDateTime to the right), the query runs fine. So in other words, I can return records that occurred before 7:00 am on the current date, but not records that occurred after 7:00 am on the current date.

Does anyone have any idea why it doesn't cause a data type mismatch when using less than, but does cause a data type mismatch when using greater than? And of course, any actual solutions would be deeply appreciated. Thank you.

-Chris</sub>

[June7]

Format results in a string value. You have date value on one side with the CDate() function and string on the other with Format() function. Try wrapping the Format() expression inside CDate function.

[ccoscina]

Thanks for your reply June7.

I just tried:
WHERE CDATE(FORMAT(LEFT(TransDateTime, 19), "YYYY/MM/DD hh:mm:ss")) >= CDATE(FORMAT(Date(),"YYYY/MM/DD 07:00:00"))

Results in a Data Type Mismatch error

[ccoscina]

Also,
WHERE FORMAT(LEFT(TransDateTime, 19), "YYYY/MM/DD hh:mm:ss") >= FORMAT(Date(),"YYYY/MM/DD 07:00:00")
Results in an Overflow error

[June7]

Why did you use Format on the SQL field? Don't change the left side term. My suggestion was an adjustment to the right side only.

[ccoscina]

Thanks for clarifying , June7.

I suppose I used FORMAT on both sides instead of just the right side because I'm an idiot.

However, when only adjusting the right side, as you requested, I still get a Data Type Mismatch error when the WHERE clause is:
WHERE CDATE(LEFT(TransDateTime, 19)) >= CDATE(FORMAT(Date(),"YYYY-MM-DD 07:00:00"))

I also tried this:

WHERE LEFT(TransDateTime, 19) <= FORMAT(Date(),"YYYY-MM-DD 07:00:00")
and that works, but again - when using >=, as in:

WHERE LEFT(TransDateTime, 19) >= FORMAT(Date(),"YYYY-MM-DD 07:00:00")
it doesn't work - instead of giving me a Data Type Mismatch error, however, all the fields returned say "#Error"

[June7]

Why do the latest examples show - instead of / in the format mask? (Although it should work anyway.) What character is used in the data?

Sorry, I don't understand why this has an issue.

If you want to provide the CSV file for analysis, attach to a post.

[ccoscina]

Why do the latest examples show - instead of / in the format mask? (Although it should work anyway.) What character is used in the data?

Sorry, I don't understand why this has an issue.

If you want to provide the CSV file for analysis, attach to a post.

"Why do the latest examples show - instead of / in the format mask?"

This should also answer your question as to why I was formatting on both sides. Granted, after doing CDate(), the original format wouldn't really matter anyway. But the original format of TransDateTime uses - instead of /, in YYYY-MM-DD hh:mm:ss.

I posted Dates.csv to my google drive (I tried attaching it to this post, but it kept failing) - which has all the values for TransDateTime. There are alot of other fields in the original table, but for right now, I'm just trying to get a WHERE comparison using TransDateTime values to work - so that should be all you need.

https://drive.google.com/file/d/0B_x...ew?usp=sharing

[June7]

I can't download the CSV file here, if it's not too large maybe from home. Did you try zipping and attaching zip file?

[ccoscina]

I can't download the CSV file here, if it's not too large maybe from home. Did you try zipping and attaching zip file?

Thanks again for continuing to help me June7.

I have attached the zip file to this post.Dates.zip

[June7]

I linked to the CSV file. It has rows that are not Date/Time data. One has text "(269499 rows affected)" others are blank and import as Null. These are causing an issue. I had to delete rows at the end of the file with Notepad (also added a 2015 record).

This seems to work with either <= or >=:

SELECT CDate(Left([Field1],19)) AS Expr1
FROM Dates
WHERE (((CDate(Left([Field1],19)))>=CDate(Date() & " 7:00:00 AM")));

[ccoscina]

Nice catch! I'll try it when I come back to work on monday. Since the CSV's will be generated by a scheduled task that will be running on the SQL Server, ill also have to write another script that gets scheduled afterwards to take out the row that says the number of rows affected. The person who will be using the data will probably be annoyed if he had to manually take out that row each time he runs Access, so I have to automate the process. We're probably going to go with a different approach that I developed and was able to make work just working directly off the CSV files from the shipping software, but my boss will be happier if I can get it to work both through Access and directly off the CSV's. I had seen the "x rows effected" before, and I should've figured out that could cause a problem. Glad you could help.

I could probably write the script in VB to take out the text in the CSV about the rows effected, but I'm more comfortable in Java so I'll probably just load a jvm on the server running SQL server and run that java code in the .bat file that is scheduled to generate the CSV.

Also, I'm probably going to slightly change your query so everything is in 24 hour format rather than Am/Pm. All of the files relating to the project are on one of my computers at work, not my laptop - so I can't say for sure if everything will work as planned for the next few days. But I do really appreciate your help, and I will repost on monday with an update on the situation.

Grazie!

-Chris

[June7]

I used AM/PM on the right because CDate() returns AM/PM.

24-hour formatting results in a string so has to be yyyy/mm/dd - as in your original attempts.

SELECT Format(Left([Field1],19), "yyyy/mm/dd hh:nn:ss") AS Expr1
FROM Dates
WHERE (((Format(Left([Field1],19),"yyyy/mm/dd hh:nn:ss"))>=Format(Date() & " 7:00:00","yyyy/mm/dd hh:nn:ss")));

[ccoscina]

Sorry for responding so late - work has been really busy this week and I hadn't even got a chance to try the query til the other day.

It works as expected after removing the row that says x rows returned. Thank you very much!

There are still other issues I'm experiencing with other things related to the task - but this was the most annoying hurdle so far.