MS Access - formatting Short Text to Datetime with CDate - problem

[pio92]

Hi,



I try to formatting TEXT into DATE.
I have a column F9 with date but as "Short Text".
Example 11.03.2022 23:59:03
I want to convert it to datetime in my query.

SELECT *
FROM myTable
WHERE CDate(Format(F9, "dd.MM.yyyy HH:mm:ss")) > #20.03.2022 11:12:13#

here: Syntax error in date in query expression CDate(Format(F9, "dd.MM.yyyy HH:mm:ss")) > #20.03.2022 11:12:13#

SELECT CDate(Format(F9, "dd.MM.yyyy HH:mm:ss")) as test
FROM myTable

here show all results as #error

[ranman256]

Convert to date, THEN format it.

format(cdate(field),”dd/mm/yyyy”)

[pio92]

Hi,
I get #ERROR as result :C
I have no idea why, I tried many combinations

[CJ_London]

it may be to due with your date structure of dd.MM.yyyy HH:mm:ss

depends on your local area for dates but usually you would expect to see

dd/mm/yyyy hh:nn:ss
using / in the datepart and nn for the minutes

CDate will reference your local settings for what it expects to look like a date

suggest try

format(cdate(replace(field,".","/",1,2)),”dd/mm/yyyy”)

also need to clarify if 11.03.2022 is 11th March or 3rd November as sql always wants the format US style of mm/dd/yyyy or the sql style of yyyy-mm-dd

[pio92]

it may be to due with your date structure of dd.MM.yyyy HH:mm:ss

depends on your local area for dates but usually you would expect to see

dd/mm/yyyy hh:nn:ss
using / in the datepart and nn for the minutes

CDate will reference your local settings for what it expects to look like a date

suggest try

format(cdate(replace(field,".","/",1,2)),”dd/mm/yyyy”)

also need to clarify if 11.03.2022 is 11th March or 3rd November as sql always wants the format US style of mm/dd/yyyy or the sql style of yyyy-mm-dd

Hi, also I thins its work, if I try:

SELECT format(cdate(replace(Field16,".","/",1,2)),"dd/mm/yyyy")
FROM myTable

I become correctly result.


But how should I use it in WHERE clausule?

SELECT * FROM myTable
WHERE format(cdate(replace(Field16,".","/",1,2)),"dd/mm/yyyy") > #20/03/2022#

I become:
Data type mismatch in criteria

[Micron]

If it's true that Format function converts data to a string, then it looks to me like you're converting to a date, then formatting as a string and then trying to compare that string to a date.
Try removing the Format part, as I suspect even using format function on #20/03/2022# is not going to work.

EDIT
- text date field, query will return a value without using criteria. That value is text (it is left aligned in the query); also tested with TypeName function.
- tried ever variation I could think of on the criteria: Cstr(), CDate with and without Format. I guess at some point the criteria is not meshing with the CDate in the expression regardless of the fact that the end result of using Format is a string. Can't even compare the Format result against a string date.

So maybe the question is, why not stick with string date values, or convert them to dates in the table?

[CJ_London]

this works for me

SELECT PK, CDate(Replace([dateDT],".","/",1,2)) AS Expr1
FROM Table1
WHERE (((CDate(Replace([dateDT],".","/",1,2)))>#3/25/2022#))

with this data

pk	dateDT
1	11.03.2022 23:59:03
2	12.03.2022 23:59:03
3	11.04.2022 23:59:03

returns

PK	Expr1
3	11/04/2022 23:59:03

or use the datevalue function instead will exclude the time element

SELECT PK, datevalue(Replace([dateDT],".","/",1,2)) AS Expr1
FROM Table1
WHERE (((datevalue(Replace([dateDT],".","/",1,2)))>#3/25/2022#))

returns

PK	Expr1
3	11/04/2022

as I mentioned before with sql date strings have to be in the format mm/dd/yyyy or yyyy-mm-dd so

WHERE format(cdate(replace(Field16,".","/",1,2)),"dd/mm/yyyy") > #20/03/2022#

will work (once your remove the format) for 20/03/2022 because sql is clever enough to know there are not 20 months in the year. but if the value was say 10/03/2022, that will be interpreted as 3rd October, not 10th March

[pio92]

its work thanks