Date problem Urgent please

[Guest]

I have a imported file that reads all fields as text.

My problem is that I have 3 text fields and need to turn answer into 2 date
fields by an append query.

eg

Date = 10 July 2006
Start = 21:00
End = 07:00


new answer in date/time fields as:-

(if over midnight needs to be next day if not same date)

StartShift = 10 July 2006 21:00:00
EndShift = 11 July 2006 07:00:00

Thanks in advance

Trev

 

[Rob Parker]

Hi Trever,

If your imported text fields are named F1, F2, and F3 respectively (and I
certainly hope you don't have one named "Date", as that's a reserved word
and is likely to cause problems - particularly when you're trying to do
date/time manipulations), then you can get what you want with:

StartShift: (CDate([F1])+CDate([F2]))
EndShift:
IIf(CDate([F3])>CDate([f2]),(CDate([F1])+CDate([F3])),(CDate([F1])+CDate([F3])+1))

The second expression is based on the assumption that if the end time is
earlier than the start time, then you've crossed midnight.

You'll get errors if you have imported data which cannot be converted to a
valid date/time.

HTH,

Rob

 

[John W. Vinson]

I have a imported file that reads all fields as text.

My problem is that I have 3 text fields and need to turn answer into 2 date
fields by an append query.

eg

Date = 10 July 2006
Start = 21:00
End = 07:00


new answer in date/time fields as:-

(if over midnight needs to be next day if not same date)

StartShift = 10 July 2006 21:00:00
EndShift = 11 July 2006 07:00:00

Thanks in advance

Trev

Run an Update query updating StartShift to

CDate([Date]) + CDate([Start])

and EndShift to

CDate([Date]) + CDate([End])

Note that Date is a reserved word (for the built in Date() function)
and will very likely cause problems.

John W. Vinson [MVP]

 

[John Spencer]

One minor change to John Vinson's example.

EndShift to

CDate([Date]) + Abs(CDate([Start])> CDate([End])) + CDate([End])

Abs(CDate([Start])> CDate([End])) will add 1 to the date when the start time
occurs after the end time. This takes care of the example you gave of work
starting at 21:00 and Ending at 07:00.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I have a imported file that reads all fields as text.

My problem is that I have 3 text fields and need to turn answer into 2
date
fields by an append query.

eg

Date = 10 July 2006
Start = 21:00
End = 07:00


new answer in date/time fields as:-

(if over midnight needs to be next day if not same date)

StartShift = 10 July 2006 21:00:00
EndShift = 11 July 2006 07:00:00

Thanks in advance

Trev

Run an Update query updating StartShift to

CDate([Date]) + CDate([Start])

and EndShift to

CDate([Date]) + CDate([End])

Note that Date is a reserved word (for the built in Date() function)
and will very likely cause problems.

John W. Vinson [MVP]