Need a query to add time please

  • Thread starter Thread starter Blobby J Blobdom
  • Start date Start date
B

Blobby J Blobdom

From a CSV file Ive imported Ive two fields.

One is a standard date/time field

The other is a text field taking the format....

nnD nnH:nnM

which stores a length of time

I need a query that will take the first field and add the second field
giving a new field.

Any clues??

Thanks :-)
 
DateAdd("n", [D],
1400 * Val(Left([F], 2)) + 24 * Val(Mid([F],5,2)) + Val(Mid([F],9,2)))
 
Type the expression into the Field row of a query.

It parses the string field based on the format you gave of:
nnD nnH:nnM

The left 2 characters are the number of days. Multiply by 1440 to get
minutes.
The 5th and 6th characters are the number of hours. Multiply by 60 to get
minutes.
The 9th and 10th characters are the number of minutes.

Sum these 3 values of minutes. Use the DateAdd() function to add that number
of minutes onto the original Date/Time value, to get the end date/time
value.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Blobby J Blobdom said:
Allen,

How does this work?

I prefer SQL.

:-)

DateAdd("n", [D],
1400 * Val(Left([F], 2)) + 24 * Val(Mid([F],5,2)) +
Click to expand...
Click to expand...
Val(Mid([F],9,2)))
 
Back
Top