Text Date

[steve21nj]

I am importing an excel text field into a table. I am then appending the data to another table. I am having a hard time with the Cdate function

Excel Date Field:
YYYY.MM.DD HH:MM:SS

Example Value:
2014.04.21 18:24:30

My Query Code:

NewDate: CDate(Left([DateQ],4)+"/"+Mid([DateQ],6,2)+"/"+Right([DateQ],2))

My Query Result:
4/30/2014

What it should be:
04/21/2014

Any suggestions?

 

[pbaldy]

The Right() function is pulling the seconds.

 

[bob fitz]

EDITED
Try:

NewDate: CDate(Replace([DateQ],".","/"))

 

[GohDiamond]

Combined: Format(cdate(Replace("2014.04.21 18:24:30",".","/")),"Short Date")

For just the DATE portion.

Cheers!
Goh

 

[Galaxiom]

I would suggest DateSerial instead of CDate. It is independent of the regional date format.

 

[steve21nj]

Thanks for the quick responses!

I ended up putting it together this way to cycle through the hundreds of excel records.

Combined: Format(CDate(Replace([DateQ],".","/")),"Short Date")

Galaxiom - I tried using DateSerial but couldn't get it to work. I was following this logic http://www.techonthenet.com/access/functions/date/dateserial.php

thank you again!

 

[Galaxiom]

DateSerial(Left([DateQ],4),Mid([DateQ],6,2),Mid([DateQ],9,2))

This is a lot cleaner because actually produces a date datatype. The Format techniques produce a string which is then implicitly case to a date using the regional date format settings.

 

[namliam]

Thanks for the quick responses!

I ended up putting it together this way to cycle through the hundreds of excel records.

Combined: Format(CDate(Replace([DateQ],".","/")),"Short Date")

Dont tell me you are storing this field as a text value??? Lose the format part and save it as a date/time field ffs....

 

[GohDiamond]

CDate()converts a value of any data type, string or integer, into a date type; however, the regional date setting for the desired output (mask/format) differs from the source data, ergo the reformatting using standard MSA date format attribute "SHORT DATE" applies to the desired result requested by steve21nj = "What it should be."

Goh

 

[namliam]

Goh,

If and only IF the Combined column is of a text field, otherwize you are relying on implicit conversion to convert the short date text string back to a date.

CDate already makes it a date type, so if you want a date type why format it again?
If you want a string, BIG NO NO! Dates are dates and should remain dates!

 

[GohDiamond]

Conceded, as we don't know whether the new table's [NEW DATE] field is already formatted as a date which, btw, would accept the appended, formatted string as if it were a date and treat is as a date going forward.

Rather move the formatting inside. Doing so will strip the TIME data from the modified string and produce the desired result even if using a MAKETABLE query to produce ANOTHER table.

CDate(Format(Replace([DateQ],".","/"),"Short Date"))

Plus I agree on Let the dates be dates, but then Excel doesn't care which is why this issue originally occured.

Cheers!
Goh

 

[steve21nj]

Dont tell me you are storing this field as a text value??? Lose the format part and save it as a date/time field ffs....

Thank you for the response....

The new table has DateQ has a date field, not text.

The original issue I was having was a 3rd party machine was spiting out data to a usb via a csv/xls file. Of the 45 data fields (dates, times, weights, etc.), each was stored as a text field. So my prebuilt queries would error out.

The end result was to append the imported data to a new table. I was able to use the same logic used for the date field for other fields as well. I can now run my queries without erroring out.

 

[namliam]

Glad you got it working, though I hope your not using the Format(cdate solution, implicit conversions can cause havoc in the future....

However for now, if it aint broke dont fix it.