Convert data type

[skwilliams]

I have a table with the wrong formatted field which will be re-imported each week through an ODBC connection and a make-table query.

Can I run a VB procedure to automatically convert the text data type for this field into a date/time data type using CDate?

 

[The_Doc_Man]

Yes, but...

If there is nothing to do but just convert the field, you should be able to change the MakeTable query to include the CDate as part of the query. VB wouldn't enter into this at all.

Perhaps a better way, since you are doing a MakeTable, is to do this as a sequence of operations that starts with a Delete query to empty an existing table. Then, instead of doing the MakeTable, do an Append that includes all conversions in the query. That should still work correctly for an ODBC linkage.

 

[Pat Hartman]

You can format the field in the make-table query -

CDate(YourDateField) As RealDate

 

[skwilliams]

Where would I enter the CDate () in the query?

This is actually a time format that I need "12:00:00".

Any help is greatly appreciated.

 

[skwilliams]

I've used the following expression in the Update To column of the Update query.

CVDate(Format(Elapsed, "hh:mm:ss"))

I received a data type mismatch error.

Any ideas??

 

[Pat Hartman]

This is from help:

A CVDate function is also provided for compatibility with previous versions of Visual Basic. The syntax of the CVDate function is identical to the CDate function, however, CVDate returns a Variant whose subtype is Date instead of an actual Date type. Since there is now an intrinsic Date type, there is no further need for CVDate. The same effect can be achieved by converting an expression to a Date, and then assigning it to a Variant. This technique is consistent with the conversion of all other intrinsic types to their equivalent Variant subtypes.

You are getting the type mismatch because the Format() function is expecting Elapsed to be a date/time data type and it is not. Try it again using CDate() with no embedded format() function.

This presumes that the text field you are importing "looks" like a valid time - 03:35:29. If it does not, you may need to make it look that way by using the Left() and Mid() functions to extract the characters from a 6 byte string.

However, the name of your field - Elapsed leads me to believe that it is simply some number of minutes or seconds that you want to convert into hh:mm:ss format. If that is the case, you'll need to use division and the mod operator to accomplish your purpose.

What EXACTLY does Elapsed contain?

 

[skwilliams]

Elapsed does contain the amount of elapsed time.

 

[skwilliams]

I just tried the CDate and removed the format, but still get the same data type mismatch error.

 

[Pat Hartman]

How would Access be able to convert some string of numbers such as 945 into hh:mm:ss format when the number is stored as text and Access has no idea what 945 represents. Is it seconds? Is it minutes? Is it supposed to be interpreted as 9:45?

If Elapsed time is actually a number of seconds, you would need to divide by 3600 to get the number of hours. Then divide the remander by 60 to get the number of minutes. The remainder of the second division is seconds.

 

[skwilliams]

The time is represented like the actual time in the text field.

Example:
9:45:21

 

[Pat Hartman]

Date/Time fields are intended to represent a point in time, NOT elapsed time. However, if your elapsed time field NEVER contains more than 24 hours, you can convert it. I don't know what you're doing wrong. I just typed this in the immediate window:

print cDate(#9:45:21#)
9:45:21 AM

Post your query/code

Once you get the field converted you're not out of the woods. Working with it will be problematic. Search the archives. There are numerous posts on the problem.

 

[skwilliams]

The time will never exceed 24 hours.

Here's the SQL statement for the query.

UPDATE tblNewActivity SET tblNewActivity.Elapsed = CDate("Elapsed");

Thanks.