extracting date & time from a string 2

[jshanoo]

Hi all,
i have a field 'fld3' it has the following value as
'200403010005' in string format;
this is used in access database.
I want to put th edate to 'dt1' field and time in 'tm1' field,
with a single upadte query,

can any one please help me.

Regards
John


*** Even the Best, did the Bad and Made the Best ***

John Philip

 

[lupins46]

You have to use Left, Mid, right to get the different elements and then concatenate them.

mydate = dateserial(left(fld3, 4) & "/" & mid(fld3,5,2) & "/" & mid(fld3,7,2))

MyTime = timeserial(mid(fld3,9,2) & ":" & right(fld3,2))

 

[BobJacksonNCI]

John,

You can load the fields separately as date and time using the following, assuming 0005 means 12:05 AM:

dt1 criteria = datevalue(left([fld3], 4) & "/" & mid([fld3], 5, 2) & "/" & mid([fld3], 7, 2))

tm1 criteria = timevalue(mid([fld3], 9, 2) & ":" & mid([fld3], 11, 2))


However, most folks here would recommend you store both as a date/time field and adjust queries as needed. You could do that using the following:

DtTm1 criteria = datevalue(left([fld3], 4) & "/" & mid([fld3], 5, 2) & "/" & mid([fld3], 7, 2)) + timevalue(mid([fld3], 9, 2) & ":" & mid([fld3], 11, 2))



HTH,
Bob

 

[jshanoo]

thnak guys ,it really worked
i was clueless about datevalue and timevalue.

regards
John

*** Even the Best, did the Bad and Made the Best ***

John Philip

 

[d1004]

Suppose I have two types of time field. One is in military time, and another is regular.
For example

1610 ---- military time for 4:10
435 ---- regular time for 4:35

I need to make this two fields consistent in order to subtract them to get the completed time. But I don't know how to approach in getting the regular time into military time or vice versa.

Any comments is greatly appreciated.

 

[lupins46]

Are you still referring to fld3 as in your original question?

Some values like:
200403010005

and some like
20040301005

If so test the length of the string:
MyTime = iif(len(fld3)=12,timeserial(mid(fld3,9,2),timeserial(mid(fld3,9,1)) & ":" & right(fld3,2))

 

[H1004]

The situation is this.. I have two fields. One is in regular time & another is military time
And I need for it to be consistent in order for me to do computation.