search date from date time field query not working

Question

I have below query

 sql_query = "select * from bericht where fromtime = " & Me.von & " and b_date = #" & Me.l_date & "#"

it print following line by debug.

select * from bericht where fromtime = 6 and b_date = #1/30/2020#

in table the b_date is dateTime field. ( linked table from SQL server in Msaccess)

in this table there is data exist with 1/30/2020 2:00:00 PM where fromtime is also 6.

why query didn't return any data?

can msaccess cannot search date in datetime field?

PS: fromtime is intiger not time field.

Answer 1

Because #1/30/2020# <> 1/30/2020 2:00:00 PM.

Convert the column to a date, rather than a datetime, before you do the comparison.

... and DateValue(b_date) = #" & Me.l_date & "#"

This will return all rows from that date that meet your other condition.

Answer 2

Similarly, consider searching all datetimes within the date range as DateValue can have efficiency issues:

select * from bericht 
where fromtime = 6 
  and b_date >= CDate("2020-01-30") 
  and b_date < CDate("2020-01-30") + 1

And to point directly to a form/report field. Save below as a stored query and call it as needed in VBA with DoCmd.OpenQuery:

select * from bericht 
where fromtime = Forms!myForm!von 
  and b_date >= [Forms!myForm!l_date]
  and b_date < ([Forms!myForm!l_date] + 1)

If using query for recordset builds, still use the preferred parameterization method and still use a stored query and in VBA open it with QueryDefs and bind needed parameters at runtime:

SQL

parameters [myint] int, [mydate] date;
select * from bericht 
where fromtime = [myint] 
  and b_date >= [mydate]
  and b_date < ([mydate] + 1)

VBA

Dim qDef As QueryDef
Dim rst As Recordset

Set qdef = CurrentDb.QueryDefs("mySavedQuery")

qdef!myint = Me.von
qdef!myDate = Me.l_date

Set rst = qdef.OpenRecordset()

...