converting text to datetime

[bimmer5]

Is it possible to convert the text field that comes in this form:

:80421

to the following datetime format: MM/DD/YY 12:00:00AM

Please note that the text field contains a colon at the beginning. The number 8 represents a year (2008), the 04 represents a month (April) and the last two characters are days in the month (21). The converted field should look like:

04/21/08 12:00:00AM

Thank you.
Peter

 

[pbaldy]

You can use the DateSerial() function along with the Mid() function to convert the string to a date value. You can ignore the time, as zero will be assumed if you don't specify it (and zero is 12am). More info in Help on those functions; post back if you get stuck.

 

[bimmer5]

Thank you, Paul. I would need the entire formula since I am not expert in Access. Do you mind, please? Tx.

 

[pbaldy]

Have you looked in Help at those 2 functions? You can use the Mid function to provide the info needed for the 3 DateSerial function arguments. Give it a whirl and post back if you get stuck.

 

[bimmer5]

O.K. so I had a partial success; I used the Mid formula as suggested and got the new string:
Expr1: Mid([Field1],3,2) & "/" & Mid([Field1],5,2) & "/" & "0" & Mid([Field1],2,1)
The remaining challenge is the DateSerial() function that I don't know how to apply in my case. Yes, I did read Help and googled it but couldn't quite get it.

Update:... the cDate function worked well, instead! The final formula is:
Expr1: CDate(Mid([Field1],3,2) & "/" & Mid([Field1],5,2) & "/" & "0" & Mid([Field1],2,1))

Thank you Paul for pushing my limits... and I appreciate your time and effort, too. Cheers!

 

[pbaldy]

Glad you found a solution. The DateSerial method:

DateSerial(Mid(":80421",2,1),Mid(":80421",3,2),Mid(":80421",5,2))

results in a date value of:

4/21/2008

 

[bimmer5]

Mr. MVP,
I appreciate your time and effort in helping me with this solution.
All the best to you!
Peter

 

[pbaldy]

Happy to help, Peter. Welcome to the site by the way! I didn't notice this was your first post.

 

[bimmer5]

Paul,
thank you for your kind words.
Heh, I thought my worries are gone but my input file got changed and now I have to deal with another format, see the following sample:

Mar 1, 2008

I need to convert it to the same format mentioned above. My challenge is the first three characters that need to be converted to MM format.
Any idea where to start?
Tx,
Peter

 

[pbaldy]

Try this:

DateValue("Mar 1, 2008")

 

[bimmer5]

It doesn't work when I enter the field name in the bracket. I have to apply the new formatting to the entire column that contains different dates.

 

[pbaldy]

Did you try it like this?

DateValue([Field1])

If that doesn't work, can you post a sample db?

 

[bimmer5]

Yes, Paul. I tried exactly like that and it didn't work. Please find the sample attached.
Tx.

 

[pbaldy]

Maybe I'm misunderstanding the goal. This query returns a date in the second column:

SELECT PRNT9311.Date, DateValue([date]) AS Expr1
FROM PRNT9311;

 

[bimmer5]

Paul,
I probably haven't explained to you properly and I appologize for that. The end result is to convert the entire text field from the table to the following date field format in the query: MM/DD/YY.

I pasted the above expression in the query but it returned an error: "The syntax of the subquery in this expression is incorrect".

I noticed the expression contains "AS Expr1" entry. Am I suppose to create this expression somewhere else? If so, what would the expression state?

If it worked on your side, could you pls attach the file?

Again,I am sorry for any missunderstanding.
Thank you for your patience, Paul.
Peter

 

[namliam]

Is your regional setting set to American?? Did you paste the SQL as SQL or did you paste it into the design window??

The "as Expr1" part happens when you put "Expr1:" into the design window which automaticaly happens when you use a function in a query (like Datevalue)

Lastly let me confirm that (offcourse) Paul's solution is valid!

 

[bimmer5]

Mr. Mailman,
Yes, my regional settings are American. The short date format in my settings is MM/DD/YY and long date format is MMMM d, yyyy.
I pasted the expression in the design window; I am not familiar with the SQL expressions. Was I supposed to paste it as a SQL command, instead?
I am a rookie in this area and this is only one time requirement from me.
Thank you for your input.

 

[bimmer5]

Gentlemen,
after your suggestions, I learned that I was suppose to paste it into the SQL mode and it worked! You made me a hero here, I truly appreciate for your time and effort in this matter.
Cheers.
Peter

 

[namliam]

I learned that I was suppose to paste it into the SQL mode and it worked!

Yes good for you !

You made me a hero here, I truly appreciate for your time and effort in this matter.

Now you have to make it stick Peter