Problem with Time

[TheSearcher]

‘If I run this query it returns what I expect.
SELECT tbl_Note_COG_BasicInfo.Client_Id, tbl_Note_COG_BasicInfo.DateOfService, tbl_Note_COG_BasicInfo.Time_In
FROM tbl_Note_COG_BasicInfo
WHERE (((tbl_Note_COG_BasicInfo.Client_Id)="600045") AND ((tbl_Note_COG_BasicInfo.DateOfService)=#8/29/2024#));

However, after adding the Time_In criteria in the Query Designer the VBA SQL looks like below. Notice the Time_In criteria.
This query doesn’t return anything.

‘The VBA SQL looks like this. Notice the Time_In criteria.
SELECT tbl_Note_COG_BasicInfo.Client_Id, tbl_Note_COG_BasicInfo.DateOfService, tbl_Note_COG_BasicInfo.Time_In
FROM tbl_Note_COG_BasicInfo
WHERE (((tbl_Note_COG_BasicInfo.Client_Id)="600045") AND ((tbl_Note_COG_BasicInfo.DateOfService)=#8/29/2024#) AND ((tbl_Note_COG_BasicInfo.Time_In)=#12/30/1899 8:0:0#));

Tbl_Note_COG_BasicInfo is a Datetime field in a SQl Server database table. The table is linked to my Access interface.
What would I need to do to use Time_In criteria and have it return a record?

Thanks in advance,
TS

 

[CJ_London]

it is not returning a record because your time_in value contains a date. Remove all formatting in your table and check the actual value, not the formatted value you are seeing. The date of 12/30/1899 means a date part of 0.


In the short term, try converting your query to use the timevalue function to remove the date element

((timevalue(tbl_Note_COG_BasicInfo.Time_In))=#12/30/1899 8:0:0#));

 

[TheSearcher]

Thanks CJ_London. I used the Timevalue function as suggested. The msgbox value depicts the correct Time_In value as is in my database table. However, no record is being returned in the rsD recordset. Below is my complete code. I'm trying to determine if another record exists as to prevent a duplicate note.

Dim sql1 As String
Dim db As Database
Dim rsD As Recordset

Set db = CurrentDb

sql1 = "SELECT tbl_Note_COG_BasicInfo.Client_Name, tbl_Note_COG_BasicInfo.DateOfService, tbl_Note_COG_BasicInfo.Time_In, tbl_Note_COG_BasicInfo.Created_By  "
sql1 = sql1 & "FROM tbl_Note_COG_BasicInfo "
sql1 = sql1 & "WHERE (((tbl_Note_COG_BasicInfo.Client_Name)='" & cmb_Client & "') "
sql1 = sql1 & "AND ((tbl_Note_COG_BasicInfo.DateOfService)=#" & txt_DOS & "#) "
sql1 = sql1 & "AND ((tbl_Note_COG_BasicInfo.Time_In)=#" & TimeValue(Me.cmb_Time_In.Value) & "#));"

MsgBox sql1

Set rsD = db.OpenRecordset(sql1)

If rsD.RecordCount > 0 Then
    MsgBox "A Note has already been created for " & rsD("Client_Name") & " on " & rsD("DateOfService") & " with a start time of " & Format(rsD("Time_In"), "h:nn am/pm") & " by " & rsD("Created_By") & ". You cannot create a duplicate note.", vbCritical, "Duplicate Note"
    rsD.Close
    Exit Sub
End If

The msgbox returns this. The Time_In value matches what I have in the database table. But still nothing is returned. I have no date formatting in my table.
Any ideas?

 

[isladogs]

No. You need to combine date and time into the same field.

 

[TheSearcher]

In the back end SqlServer table the date and time are in the same field. However, when it's linked to Access only the time appears. I have no formatting in that field.

 

[Pat Hartman]

In the back end SqlServer table the date and time are in the same field. However, when it's linked to Access only the time appears. I have no formatting in that field.

This is Access being "helpful". Since datetime ALWAYS includes BOTH date and time, Access assumes that if the date is 12/30/1899 you really are only storing time and so it is "helping" you by only displaying time.

Keep in mind that datetime is a double precision number. The integer portion is the number of days since 12/30/1899 for Access and 1/1/1900 for SQL Server. You don't need to worry about this, the query correctly converts the data to the current date based on whether it is coming from ACE or SQL Server. The time is the fraction of the day that has elapsed so 1.5 = Noon on 12/31/1899. 1.75 = 6 pm and 1.25 = 6 am. Depending on how you populate the date part, you can end up with long fractions like 1.253445. This will round to 6 am for display purposes but will not be returned if you look for = 6 am. Always use a range to pull in a time.

 

[CJ_London]

I used the Timevalue function as suggested

actually, you didn't

I suggested

((timevalue(tbl_Note_COG_BasicInfo.Time_In))=#12/30/1899 8:0:0#));

you used

sql1 = sql1 & "AND ((tbl_Note_COG_BasicInfo.Time_In)=#" & TimeValue(Me.cmb_Time_In.Value) & "#));"

Also look like your sql server field is using a datetime2 field type. This provides a higher level of accuracy than the normal datetime field - do you really need to know the time in to the nearest 1000th of a second? Access does not use datetime2 - it converts it to text, see this link discussing the issue

Date Format Not Respected

I've upgraded an Access 2003 application to Access 2016 and migrated the back-end to SQL Server. Now all of my date fields are in the format yyyy-dd-mm hh:mm:ss despite the text box Format being set to Short Date. I suspected this was due to the regional settings in Windows but when I checked...

www.access-programmers.co.uk

see this link

Date and Time Data Types and Functions - SQL Server (Transact-SQL)

Links to Date and Time data types and functions articles.

learn.microsoft.com