Converting Time variable

  • Thread starter Thread starter David Smith
  • Start date Start date
D

David Smith

I have 3 text boxes on a form 1 for date then 2 more for
start & end times. I then concantate the date & time txt
strings & use cdate() to assign value to a date field.
This however generates an error as mis match of data types.
The Time format is Short Time HH:MM but it seems this
should be in long time format hh:mm AM/PM. How can I
convert this time format. Many thanks
 
The help page that can help explain how you can setup your Date/Time field
format is the help page titled, "User-Defined Date/Time Formats (Format
Function)". THis page shows how you can setup the format of your Date/Time
fields including the use of the AM/PM portion of the format such as:

hh:nn AM/PM

If you are attempting to change the default Time format on your system, that
is located in the Control Panel under the "Regional Options" icon/menu item.
Goto the Time Tab, then change your time format (As far as W2K Pro is
concerned, there is no distinction between Short and Long Time Format).
Note, in there, instead of using the AM/PM within the time format like you
do in Access, you use the "t" or "tt" format for the am/pm symbol.

As far as how Access Stores Dates/Times, it's stored as a floating point
number that uses 8 bytes, which is the same as a Double Data Type. Dates
are stored as a serial number starting with January 1, 1900 as the serial
number of '0' (unless you are using the Mac Date System, which starts with
1904 for the year). The decimal portion of the number is the time part, so
if you see a number that's 0.25, that means it's January 1st, 1900, at
6:00:00 AM. Given this, as far as the actual storage of your dates and
times, I would suggest you only store 2 fields, Start Time (which will
include the date) and End Time (which will include the date).

If need be, you can have 2 seaprate controls within your form, but then
combine them 2 in the code itself to store as one value within your BE DB
file. Example:

Start Date:
Start Time:

End Date:
End Time:

You would just simply add the Start Date and Start Time to get the value you
need, though if these fields are setup as string formats, you will need to
convert them to date/time format such as using the CDate Function, or create
your own Date/Time function that converts the string format to the date/time
format.

Taking the Date/Time value, if you needed to find the hour that it
represented, you would you would use the following formula:

Int((Date/Time Variable - Int(Date/Time Variable))*24)

OR

You could use the HOUR function on it.

Hour(Date/Time Variable)

Hope this is of help to you.
 
David said:
I have 3 text boxes on a form 1 for date then 2 more for
start & end times. I then concantate the date & time txt
strings & use cdate() to assign value to a date field.
This however generates an error as mis match of data types.
The Time format is Short Time HH:MM but it seems this
should be in long time format hh:mm AM/PM. How can I
convert this time format.
Click to expand...


That sounds like a roundabout way to go, but without seeing
the code, I can't even guess what might be causing a type
mismatch error. Probably something about the string that
can't be deciphered as a date/time.

You can convert to any format by using the Format function

Format(datetime, "m/d/yyyy hh:nn ampm")
 
Back
Top