Data type mismatch when converting sql server date text to Microsoft Access date.
Need help with syntax of query to avoid error: Data type mismatch in criteria expression.
Please note attached image file of query being ran that gives an error.
This is a simple table with two fields that have Short Text as the data type.
I'm trying to do a between date after it's converted using CDate - what am I please doing wrong?
Please especially note the last sample - img with the values of data it's converting also. This originally was a flat Excel file out of SQL Server with the date columns as an example: 2011-06-30 00:00:00.0000000
Perhaps I need to parse out just the date without the time first ? If so , what's the most efficient means to do this?
Sample-Error.PNG
Sample-Table-Data-TypesPNG.PNG
Sample-values.PNG
Please note attached image file of query being ran that gives an error.
This is a simple table with two fields that have Short Text as the data type.
I'm trying to do a between date after it's converted using CDate - what am I please doing wrong?
Please especially note the last sample - img with the values of data it's converting also. This originally was a flat Excel file out of SQL Server with the date columns as an example: 2011-06-30 00:00:00.0000000
Perhaps I need to parse out just the date without the time first ? If so , what's the most efficient means to do this?
Sample-Error.PNG
Sample-Table-Data-TypesPNG.PNG
Sample-values.PNG
ASKER
That just throws an #Error for the value
ASKER
This worked for me.
Parse out the values to where it was just the date and then apply CDate over it.
Parse out the values to where it was just the date and then apply CDate over it.
SELECT PastTransactions.SoldDate, CDate(Left([SoldDate],10)) AS Expr1
FROM PastTransactions
WHERE (((CDate(Left([SoldDate],10))) Between Date() And DateAdd("m",-1,Date())));
If you are seeing a date in a linked SQL Server table defined as short text, Access is not able to recognize the actual date datatype that was used to create the table in SQL Server. Your best option is to redefine the date as datetime in SQL Server if possible. Your next best option is to create a view to cast the date for you. Your third option is to change to using a newer ODBC driver than "SQL Server" which has a vintage close to 2000 and so cannot handle many newer data types.
Just FYI - NEVER, EVER format a date using the FORMAT() function and try to use it as a date. The Format() function converts the date to a string. Once the date is a string, it will act like a string and that means that 01/05/2019 will be less than 02/01/2019 (assuming you are using mm/dd/yyyy) format).
Because you are using dd/mm/yyyy format rather than the default mm/dd/yyyy for SQL Server, you WILL have to format your dates but ONLY when they are being passed as strings such as you passing a variable into the where clause of a query. You need to format it as mm/dd/yyyy or a less ambiguous yyyy/mm/dd. If you are working with two actual date data type fields, formatting them only confuses things.
Just FYI - NEVER, EVER format a date using the FORMAT() function and try to use it as a date. The Format() function converts the date to a string. Once the date is a string, it will act like a string and that means that 01/05/2019 will be less than 02/01/2019 (assuming you are using mm/dd/yyyy) format).
Because you are using dd/mm/yyyy format rather than the default mm/dd/yyyy for SQL Server, you WILL have to format your dates but ONLY when they are being passed as strings such as you passing a variable into the where clause of a query. You need to format it as mm/dd/yyyy or a less ambiguous yyyy/mm/dd. If you are working with two actual date data type fields, formatting them only confuses things.
ASKER
I know you say avoid using Format but I think I solved it with using CDate.... correct?
The original post had Format(cDate()) Which results in a string that looks like a date. Your subsequent post took the first 10 characters out of a string and said to convert it to a date. This may or may not work when the day/month is ambiguous. I think it will assume the string is formatted based on your system settings but I'd check very carefully because SQL uses month/day as the standard order and you need to know what you are seeing from SQL server. Are you seeing month/day or day/month. I would suggest in all cases that when you format a date string (unless it is for printing) that you use the unambiguous yyyy/mm/dd OR dd-mmm-yyyy..
CDate will not fail as the text date has the ISO sequence: yyyy-mm-dd hh:nn:ss.
However, as the text dates also have a time part, you should create a query for your further processing:
However, as the text dates also have a time part, you should create a query for your further processing:
Select
CDate(Left([SoldDate], 19)) As DateSold,
UtilityAccountNumber
From
PastTransactionsASKER
I'm still having the same problem and error. Please note images.
How does on fix the syntax on this function to allow for this to continue without error?
overcome-error2.png
How does on fix the syntax on this function to allow for this to continue without error?
Public Function ConverTextToDate(vValue As Variant) As Date
Dim sValue As String
Dim dDateValue As Date
If IsNull(vValue) Then
Else
sValue = Str(vValue)
sValue = Left(sValue, 10)
dDateValue = CDate(sValue)
ConverTextToDate = dDateValue
End If
End Functionovercome-error2.png
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you for all your helpful replies.
You are welcome!
DateValue(YourDateField)