Seperating the time from a date 2

[rrowley345]

Hello,
I am looking to create two fields from one. One field will contain the date and the other one will contain the time. Currently the format that the field is in MM/DD/YYYY HH:MM:SS AM. What I would like to do is to divide that field into one field that has MM/DD/YYYY and another one that has HH:MM:SS AM.

Thanks for any suggestions
Ryan

 

[jjonesal]

By default the Date/Time datatype contains both the date and the time and there is no way to change that. However, you could use the format function to pull out just the date portion or just the time portion when you need either one..

Just a thought... J. Jones
jjones@cybrtyme.com

 

[JimHorton]

Ryan,
Below is quick and dirty:
Let's say dtDate is a date/time variable

X = Clng(dtDate) 'This returns Date Only
X = Csng(dtDate -Clng(dtDate)) 'This returns Time only

Or, as J jones said:
Format(dtDate,"hh:nn:ss&quot 'Time, but in a string value
Format(dtDate,"mm/dd/yyyy&quot 'Returns string of date

To permanantly strip the time, you just do:
dtDate = clng(dtDate)

--Jim

 

[rrowley345]

Thanks for the response.
I copied the data to another field and set one to format for time and one for date and that makes it appear correct but I still am having a problem when I try to query it using conditions. My initial issue is that what I am trying to do is count the number of records that happen within a time period regardless of what the day is. For example if I have 5 records between 8:00 and 9:00 on 2/20 and 5 records between 8:00 and 9:00 on 2/21 I want it to count 10. To do this I also tried using wildcards for the date portion but that did not work. Any other ideas would be great
Thanks
Ryan

 

[tekola]

It just seem to easy, but I have done this with a query to produce a scratch field called Hour calculated from the time. Hour(date)

 

[rrowley345]

Hello
In my query formatting the date would not allow me to select records based on just the time criteria, but setting up an expression X = Csng(dtDate -Clng(dtDate))that Jim suggested worked great.
Thanks for everyone’s help
Ryan