Formatting Text to Date/Time for Calculations

[shelby55]

Hi

I am reporting off Access 2002 using Crystal 9.0.

In my Access data table I have 2 date fields and both corresponding time fields are separate and were imported as text for ease of manipulation.

I wish to combine both the date and time string into a date/time field so that I can calculate a datediff for hours.

How do I do this? Thanks.

 

[lbass]

You need to show how the fields display. Be sure to indicate the months, days, and years clearly. Is the date field also text?

-LB

 

[shelby55]

Hi LB

Thanks for replying.

The date field is a date (mm/dd/yyyy) and the time field is text (4 characters in length) without the : so showing as 1430, 1151, 0537.

Does that help?

 

[lbass]

Don't know what the time numbers refer to.

-LB

 

[fisheromacse]

Not my data, but i would hazard to guess...24-hour time? 12:01am = 0001 11:59pm = 2359

 

[shelby55]

Hi

Fisheromasce is correct, 2359 is the 24 hour clock and if it were shown as a time it would be 23:59.

What I did do was try to combine all as a text:
totext({MEDITECH_Data.AmbArrDt},"mm/dd/yyyy")+" "+
mid({MEDITECH_Data.AmbArrivTm},1,2)+":"+mid({MEDITECH_Data.AmbArrivTm},3,2)

so that I could use the DTS function within the DateDiff
datediff("n",DTSToDateTime ({@MedArrivDateTime_string}),DTSToDateTime ({@MedTOCDateTime}))

But the results of the time wasn't correct.

 

[lbass]

First convert the field to a datetime:

datetiime({MEDITECH_Data.AmbArrDt},time(val(left(MEDITECH_Data.AmbArrivTm},2)),val(mid(MEDITECH_Data.AmbArrivTm},3,2)),00))

Then you can use this formula in a datediff formula. Not sure what you intend to compare it to.

-LB