VBA SQL query to convert data in a field.

[freeflyer30339]

I receive data from another program which I am unable to change. I am trying to create an VBA SQL query in Access '03 that will convert a field that has a date which is in an unrecognizable format. It consists of DayMonthYear and Time. I just need to get the date to a new field called RevisedDateTimeDate. Any suggestions would be greatly appreciated.

DateTime       RevisedDateTimeDate
15OCT08 0840       10/18/08
08JAN08 1635       1/8/08
03JUL08 0119       7/3/08
03JUL08 1000       7/3/08
17AUG08 1314       8/17/08
08JAN08 1635       1/8/08
 

[NeoPa]

Try :

SELECT CDate(Left([DateTime],2) & ' ' &
              Mid([DateTime],3,3) & ' 20' &
              Mid([DateTime],6,3) &
              Mid([DateTime],9,2) & ' ' &
              Mid([DateTime],11,2) AS RevisedDateTimeDate

[freeflyer30339]

Thank you for the quick reply, I am getting a Run-time error 3075 for a Syntax error (missing operator). Any idea as to what I did wrong?

[NeoPa]

Not unless you show me what you did :S

[freeflyer30339]

Good point. Still learning, but I thought I could put it in a Docmd.runsql statement as is. Not sure what else needed to be added.

Thanks

[NeoPa]

I'll wait for you to post it then shall I?

[Stewart Ross]

Hi Freeflyer. Just in case you didn't do so, could I just check that you added a FROM section with your table name to NeoPa's SQL (making it a fully valid SQL statement in the form SELECT ... FROM yourtable).

You did not tell us what the name of the table was, and accordingly I guess it was not part of NeoPa's skeleton code.

-Stewart

[freeflyer30339]

Here is what I put into the code:

DoCmd.RunSQL "SELECT CDate(Left([DateTime],2) & ' ' & Mid([DateTime],3,3) & ' 20' & Mid([DateTime],6,3) & Mid([DateTime],9,2) & ' ' & Mid([DateTime],11,2) AS RevisedDateTimeDate FROM tblConvMTRL"

I did put the from statement in there and was getting the same error message.

[NeoPa]

You cannot execute a SELECT query. DoCmd.RunSQL is exclusively for Action queries (Update, Append, Make Table etc).

[NeoPa]

SELECT CDate(Left([DateTime],2) & ' ' & 
              Mid([DateTime],3,3) & ' 20' & 
              Mid([DateTime],6,3) & 
              Mid([DateTime],9,2) & ' ' & 
              Mid([DateTime],11,2) AS RevisedDateTimeDate 
FROM tblConvMTRL

This should work in a QueryDef.

Again, as I have no idea of the context you need this in I can only show you relative to what you've already shared with us.

You will find generally that the less effort you put into explaining your problem, the less help you get. Not only will members immediately be irritated by that attitude, but also it is impossible to help when we don't have the information required.

[freeflyer30339]

I don't mean to be difficult, this is all new to me. I have bought books and tutorials to learn. I can do this all with queries in Access, but I want to do the entire program in Script.Having queries on top of queries has gotten quite confusing.

The only thing I was hoping to do is have a script that would convert that field to a recognizable date. I truly don't know what to do with the information you provided. What more information can I provide?

[NeoPa]

Good answer :)

In answer to your question, and possibly some explanation as to why it is so difficult without the information, I need some context to know what you intend to do with it.

In Access there are so many different situations where SQL is used, many of which are very different in context. Working within a form can require SQL in various situations and a few guises. Working directly in a QueryDef (Stored Access Query) generally only deals with basic SQL, but is different from many requirements with forms.

Are you intending to display this data on a form?
Is this data coming from the Record Source of a Form or Report?
Where it is displayed (if on a form or report) is it in a bound control or an unbound one?

[NeoPa]

As an afterthought, can you explain what you mean by "script"?

If you mean VBA that's fine. Otherwise I need some explanation of what type of scripting you are talking about.

[freeflyer30339]

As an afterthought, can you explain what you mean by "script"?

If you mean VBA that's fine. Otherwise I need some explanation of what type of scripting you are talking about.

Yes, VBA code to convert the DateTime field to the RevisedDateTimeDate field in the table only. This way I can pull information by Month and Date later. This unfortunately will be working on a very large table which is why I thought it would be better to just make the conversion to a new field in the table than to just show it in a form.

The data at this moment resides all in the same table. I will be generating reports off this data as well as using forms to display the information. It just seemed easier to me to have everything in the right format in the table.

Hopes this makes a little more sense.

[NeoPa]

Ah, so you have a [DateTime] field in the table [tblConvMTRL] with the existing data in it, and you have a [RevisedDateTimeDate] field in the same table which currently has no data in it. Is that right?

[freeflyer30339]

Ah, so you have a [DateTime] field in the table [tblConvMTRL] with the existing data in it, and you have a [RevisedDateTimeDate] field in the same table which currently has no data in it. Is that right?

Yes, that is correct.

[NeoPa]

Do you need to run this code again and again, or is it a one-off task?

If it's a one-off task then a simple UPDATE query will do for you.

Otherwise you need to decide how you will trigger the code.

The SQL for the UPDATE query will be the same in either case :

UPDATE [tblConvMTRL]
 
SET [RevisedDateTimeDate]=CDate(Left([DateTime],2) & ' ' &
                                 Mid([DateTime],3,3) & ' 20' & 
                                 Mid([DateTime],6,3) & 
                                 Mid([DateTime],9,2) & ' ' & 
                                 Mid([DateTime],11,2)

[freeflyer30339]

This will work. thank you for your time and assistance. Sorry it took me a while to get my point across.

[NeoPa]

No worries. Pleased you're sorted now :)