Converting text fields to date/time

[jmfox2]

I have a date field and a separate text field that contains the time.

Date Entered time Entered Date Removed Time Removed
12/18/02 0700 8/27/02 1200
12/4/02 1600 7/13/02 700
12/4/02 1300 7/3/02 1000
12/4/02 2400 7/17/02 1100

both of the time columns are actual text fields. I need to be able to subtract date/time entered from date/time removed to have actual minutes. I haven't worked with Access for some time now, and, am not having any luck finding how to do this. Please help. jmf

 

[KenReay]

Hi

Not sure about this since I have not actually done it but my first gues would be

MyDateTime = CDate(MyDate & " " & myTime)
Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[jmfox2]

Thank you, Ken.

I tried (or hoped I tried) what you suggested. It's doing something, but, not what I was hoping for. I added new field to my query:

Expr1: CDate([Date Removed] & "" & [Time Removed])

Result is in the 3rd column below. It did combine the 2 fields, but, not exactly the way I wanted it to. Where column 3 is blank, it says #error in access.

Date Time Expr1
8/27/02 1200
7/13/02 700 7/13/2700
7/3/02 1000
7/17/02 1100
7/31/02 500 7/31/2500
7/22/02 930 7/22/2930
7/20/02 700 7/20/2700
6/10/02 1200

any other ideas?

 

[KenReay]

Hi

You have changed the rules!! your example did not include blanks (only joking), try

MyDateTime = CDate(Nz(MyDate,Format(Date(),"dd/mm/yy&quot) & " " & Nz(myTime,"00:00:00&quot)

Nz() converts Null to another (specified) value

Remember I am in UK, hence dd/mm/yy. you may need slightly different setting (eg mm/dd/yy if in USA)
Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[jmfox2]

My example does not have any blanks in it. some of the results of my attempt to convert had blanks (really = #error in access).

Using Expr1: CDate(nz([Date Entered],Format(Date(),"mm/dd/yy&quot) & "" & nz([time Entered],"00:00:00&quot) yields #error in Expr1 column
Using Expr2: CDate(nz([Date Removed],Format(Date(),"mm/dd/yy&quot) & "" & nz([time Removed],"00:00:00&quot) yields some results (wrong) and some #error in Expr2 column

Date1 time1 Expr1 Date2 Time2 Expr2
12/18/02 0700 8/27/02 1200
12/4/02 1600 7/13/02 700 7/13/2700
12/4/02 1300 7/3/02 1000
12/4/02 2400 7/17/02 1100
12/4/02 0100 7/31/02 500 7/31/2500
12/4/02 2400 7/22/02 930 7/22/2930

I know I should be able to figure this one out, but, I'm really having no luck.

Thanks, Joann

 

[KenReay]

Hi

Whjat we are trying to mimic is 02/13/03 07:20

so I think you have a space missing

(),"mm/dd/yy&quot) & "" & nz([time Entered

should be

(),"mm/dd/yy&quot) & " " & nz([time Entered

ie

(),"mm/dd/yy&quot) & "a space character in here" & nz([time Entered Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK