trouble with time (1 Viewer)

[Ziggy1]

Hi

I'm having trouble displaying time, Access is taking my time and adding the date to it...as Jan 1 1900

What I am doing is taking a selected date ( from form), and then for that date I am generating time slots using a starting value of 7:30 and adding 1 hour by looping 15 times. visually in the query result it looks OK, but if I click the field the calender pops up and I can see the older date ( 1900)...also if I export to Excel it displays as a date.

Public Sub Update()

Dim strSql As String
Dim stDate As Date
Dim x As Integer


Dim StTime As Date
Dim EnTime As Date


StTime = Format(#7:30:00 AM#, "HH:MM:SS")
EnTime = #7:30:00 PM#

stDate = Me.txtDate


DoCmd.OpenQuery "qryDelete_TimeSlot"

For x = 1 To 15
'Do Until StTime = #7:30:00 PM#

    strSql = "insert into tblTimeSlot(CurrDate,TimeSlot)"
    strSql = strSql + "Values(" & "#" & stDate & "#" & "," & "'" & StTime & "'" & ")"
      
    DoCmd.RunSQL strSql

StTime = StTime + "01:00"

    'Loop
Next x

Me.Requery

also tried.....



StTime = TimeValue(Format(#7:30:00 AM#, "HH:nn:SS"))

StTime = TimeSerial(7, 30, 0)

 

[Ziggy1]

ok, I put this in my query and it works...

Format(qryApptLog_OutBound.[Appt Time],"hh:nn:ss") AS ApptTime

 

[bulrush]

Your problem is your data type is "Date", which stores both the date and time. I think there is another data type called "Time" which you might want, if you don't also want the date.

Also keep in mind the Format function merely changes the display of the data, not the data itself, the variables stTime and enTime are still holding a date and time because it is a "Date" type. You provide the time for stTime, but not the date part, so Access supplies the default date of "Jan 1, 1990".

 

[Ziggy1]

I hear what you are saying, but I see no "time" type, the format expression in my query has stripped the time from the date ( which I don't care about), and is giving me the output I need.

If you have a VBA solution I'd consider it in the future, but this is working now.

thanks

 

[Scooterbug]

Time is a component of the Date Datatype.

As for your code:

strSql = strSql + "Values(" & "#" & stDate & "#" & "," & "'" & StTime & "'" & ")"

You are putting in quotes around the time...when it too should be #.

 

[Ziggy1]

Time is a component of the Date Datatype.

As for your code:

strSql = strSql + "Values(" & "#" & stDate & "#" & "," & "'" & StTime & "'" & ")"

You are putting in quotes around the time...when it too should be #.

Thanks Scooterbug,

I didn't even clue into that....even though I had the other variable right

....always helps to have a second set of eyes. So it works properly now, but the format expression also helped ( for anyone's Reference).