Working with Date and time fields

[wjburke2]

I have a repair log. The operator enters the date and time the machine goes down. Repairman called time, arrived time, start time and finish time. I then calculate the response time, repair time and down time from these fields. This application was build way before I got here. The dates are formatted as a text field "20000101" and times are 1.3 and 13.3. Conversion is going th be a bear. I am having an problem with the date time fields. I am slowly converting some of the fields to Date/Time. having not worked with date and time before I tried creating a Date textbox and a Time textbox on a form. The Date field works but the time shows the time 12:00 AM but when I click on it shows date and time. I would like to have one text box for date another for time. Is there a way to do this? I have code for a neat time picker if anyone wants it. Didn't want to use it because it takes a lot of real estate on the screen but it would to work. it roll’s the sec, min, hours, as you click the arrows. I just want a field that displays 12:00 AM or 1:00 PM...

 

[Galaxiom]

Format the field or control as Medium Time.

Conversion won't be difficult at all. Access can do this for you.
Enter these expressions in the new date and time fields of your update query.
(textdate and texttime are your old field names)

DateSerial(Left([textdate],4),Mid([textdate],5,2),Right([textdate],2))
 
TimeSerial(IIF(Mid([texttime],2,1)=".", Left([texttime],1), Left([txttime],2)), {depends on what ".3" means} ,0)

But you get the picture.
http://www.techonthenet.com/access/functions/

 

[wjburke2]

Thanks Galaxiom, that might be easier than the way I did it, I just did a find and replace .1=:06, .2=:12, .3=:18... That way I ended up with a text field of 1:06, 2:18, 14:54 ... you get the idea. The results of conversion to a time field varied. Some values 700 of 4400 did not have decimals others were just 0 the conversion is going to be a long process. I will need to figure out how to compute the date for all the times entered without dates. Since that are just times it will be hard to figure out if it service was completed the same day or three days later. I am about ready to scrap most of the data and have them start over. I still need to figure out how to display the date and time in two distinct fields that update the same date/time data field.

 

[Galaxiom]

The problem with the find and replace method is you still have a text field for your time. To convert this new time formatted as a text string to a proper time use:
TimeValue([time as string])

As the original time figure was a decimal representation of hours it could have been been converted with:
TimeSerial(Int([texttime]), [texttime] - Int([texttime])*60), 0)

Access can do a lot of things but of course inconsistent data formatting and data that is not entered at all will defy any kind of conversion.

It appears you have your date and time controls on the form bound to a single date/time formatted field in the table. Even though they are set to display just the time or date they do hold the combined figure and so will display it all when they get the focus.

Have you tried using two separate fields in the table with their format set to Short Date and Medium Time? This would not expand the the display of the bound controls on focus. Of course that would make your times between events more complex to calculate.

To separate the fields if you go that way the expressions to extract parts of a Date/Time formatted field:

TimeSerial(DatePart("h",[datetime]),DatePart("n",[datetime]),DatePart("s",[datetime]))
DateSerial(DatePart("yyyy",[datetime]), DatePart("m",[datetime]), DatePart("d",[datetime]))

Another way would be to use unbound controls for the date and time on the form and set the combined date/time formatted table field using update commands run from VBA in the After Update event of the controls.

Incidentally you should try to use the correct terminology as it makes your descriptions easier to understand.
Fields are in tables. The boxes that hold the field values etc on forms are called Controls.

 

[wjburke2]

Galaxiom, once again I thank you, I didn’t realize the table format would actually change the display of fields on the form. I believed it would only change it on the table view. I will try that. The calculations don’t seen that hard I believe I was reading something about a number of days function. Should be able to use that * 24 and add the difference between the times.
Sorry about the terminology. I have been programming since 1985 on a variety of systems and they all have their own dialect. For the last 12 I have been on the HP3000 using UNIX and the IBM360 doing CICS, dB2, COBOL, JCL my last MS Access project was in 95 with AC97 so I am really a newbie at this. I try to stay generic but sometimes that leads to confusion. The stories I could tell you about that LOL.

 

[calvinshardy09]

Wow, good information to give you everything. I think these areas as far as I can be configured so that only the date or time is visible.

 

[vbaInet]

calvinshardy09 - link in sig - spammer reported.

 

[wjburke2]

This is a weird example but the final solution was a combination of calvinshardy09's logic Thanks again Calvin. This now converts

20000915 16.9 - into - 09/15/2000 04:54:00 PM

' Convert Date
IR_Date = DateSerial(Left([MyRstOld]![Date], 4), Mid([MyRstOld]![Date], 5, 2), Right([MyRstOld]![Date], 2))

' Convert Time
Cnvt_Time = TimeSerial(IIf(Mid([MyRstOld]![Called_Time], 2, 1) = ".", Left([MyRstOld]![Called_Time], 1), Left([MyRstOld]![Called_Time], 2)), ([MyRstOld]![Called_Time] - Int([MyRstOld]![Called_Time])) * 60, 0)

' Add the Date and time to get the Log date
Cnvt_Date_Time = IR_Date + Cnvt_Time

 

[ravencrt]

Hello
I have a problem and I don't know how to sort it out. Why when I convert a date value to double in access vba I receive a value and when I convert the same data value to decimal in sql I receive a diferent value.

Example:
Access vba
dim data as date
data=date (today is 04/01/2011)
msgbox(cdbl(data))
The resuls of this code is 40547

SQL
set dateformat 'dmy'
seclare @data datetime, @variabila decimal
set @data='04/01/2011'
set @variabila=convert(decimal,@data)
print @variabila
the result returned is 40545

I realy don't understand why is this happening, in my opinion it should return the same result in both cases.

 

[Rabbie]

Hello
I have a problem and I don't know how to sort it out. Why when I convert a date value to double in access vba I receive a value and when I convert the same data value to decimal in sql I receive a diferent value.

Example:
Access vba
dim data as date
data=date (today is 04/01/2011)
msgbox(cdbl(data))
The resuls of this code is 40547

SQL
set dateformat 'dmy'
seclare @data datetime, @variabila decimal
set @data='04/01/2011'
set @variabila=convert(decimal,@data)
print @variabila
the result returned is 40545

I realy don't understand why is this happening, in my opinion it should return the same result in both cases.

It will all depend on what each defines as date = 0. Some systems use 1/1/1900 others use 31/12/1899. There is also an issue with Excel that it thinks that the year 1900 was a leap year(it wasn't). Not sure if this applies to Access or not.

 

[ravencrt]

I found out that access has the date=0 in 30/12/1899 and sql has date=0 in 01/01/1900. I don't know if this date applies to all version of access