Select from which date as a variable vba access

[BasB]

I m trying to get a selection from a table by using vba code. Nothing weird at all. But somewhere i fail.

So i will give a part of my code:

Dim Z_Datum as date

Z_datum = rsTimeTable.Fields("ColumnName"). Value (this gives the dutch notation of date and time 8-10-2024 19:57:28)

I try to use this in the following statement

sql5 = "SELECT * FROM DATA_CHECK_KLW_2024 where Year = " & Jaar & " And Number = '" & TankNummerSelect & "' And InvoerDatum = #" & Format(Z_Datum, "mm/dd/yyyy hh:nn") & "#;"
Set rs5 = db.OpenRecordset(sql5)

This does not work

If I change Z_datum into a fixed datum then it works:

Z_Datum = #10/8/2024 7:57:28 PM#

So how can I rewrite the result 8-10-2024 19:57:28 into #10/8/2024 7:57:28 PM#?

 

[DHookom]

I always place a debug.print in my code for troubleshooting:

sql5 = "SELECT * FROM DATA_CHECK_KLW_2024 where Year = " & _ 
     Jaar & " And Number = '" & TankNummerSelect & _
     "' And InvoerDatum = #" & Format(Z_Datum, "mm/dd/yyyy hh:nn") & "#;"

debug.Print sql5

Set rs5 = db.OpenRecordset(sql5)

 

[MarkK]

To handle all embedded delimiters and date formats without cobbling together your SQL in code, I strongly recommend an approach more like this...

    Const SQL_SELECT As String = _
        "SELECT * FROM DATA_CHECK_KLW_2024 " & _
        "WHERE Year = pYear " & _
            "AND Number = pNumber " & _
            "AND InvoerDatum = pDate;"
            
    With CurrentDb.CreateQueryDef("", SQL_SELECT)
        .Parameters("pYear") = Jaar
        .Parameters("pNumber") = TankNummerSelect
        .Parameters("pDate") = rsTimeTable.Fields("ColumnName").Value
        Set rs5 = .OpenRecordset
        .Close
    End With

• Your SQL stays completely clean and readable.
• Date formats are completely and robustly solved regardless of Windows regional settings or formats.
• The temp QueryDef checks the table, and automatically converts (if possible) the types of any parameter assignments to match the type of the field, so you never again have to worry about delimiters.
• If you are assigning a string: because you are assigning that string's value directly to the value of the parameter, you are completely free to embedded delimiters. So if your user types the length and dimensions of lumber, like...
4 x 8' - 2" x 4"
... this user-provided string will kill almost any SQL you construct in code. A temp QueryDef handles this case effortlessly.

So yes, the code above is slightly more verbose, but if you think about the time you are now spending troubleshooting this...

sql5 = "SELECT * FROM DATA_CHECK_KLW_2024 where Year = " & Jaar & " And Number = '" & TankNummerSelect & "' And InvoerDatum = #" & Format(Z_Datum, "mm/dd/yyyy hh:nn") & "#;"

...largely unreadable and error-prone string concatenation, then I recommend you use a parameterized temp QueryDef.

Cheers,

 

[June7]

Your date value shows seconds but your criteria format does not. Really want to include time components in filter criteria?

Review http://allenbrowne.com/ser-36.html

 

[The_Doc_Man]

You have to be consistent in using time as a selection criterion. As June7 pointed out, using something in "hh:nn" format will never match something in "hh:nn:ss" format. Still, using dates formatted to strings will be the better choice in the long run for date/time comparisons.

If you left the times as DATE format rather than a formatted string, you would have issues associated with rounding. This occurs because DATE variables are what is called a "typecast" of DOUBLE, and that format is exceedingly susceptible to rounding errors since any date that includes time of day will be fractional.

 

[MarkK]

Also, if you have done any custom calculations on your date values, they may no longer be stored exactly correctly in the table. Try this in the immediate pane...

? cdate(45670.8804976852)
1/13/2025 9:07:55 PM
? cdate(45670.8804976)
1/13/2025 9:07:55 PM

Notice that 45670.8804976852 <> 45670.8804976, but they return exactly the same Date/Time when formatted. This is not an uncommon problem, that date values do not match exactly to the second.

To work around this problem, you can do something like...

    Const SQL_SELECT As String = _
        "SELECT * FROM DATA_CHECK_KLW_2024 " & _
        "WHERE Year = pYear " & _
            "AND Number = pNumber " & _
            "AND Abs(InvoerDatum - pDate) < TimeSerial(0, 0, 5);"
           
    With CurrentDb.CreateQueryDef("", SQL_SELECT)
        .Parameters("pYear") = Jaar
        .Parameters("pNumber") = TankNummerSelect
        .Parameters("pDate") = rsTimeTable.Fields("ColumnName").Value
        Set rs5 = .OpenRecordset
        .Close
    End With

So that matches dates that are within 5 seconds of each other, and it advertises how much easier it is to maintain your SQL when it doesn't look like this...

sql5 = "SELECT * FROM DATA_CHECK_KLW_2024 where Year = " & Jaar & " And Number = '" & TankNummerSelect & "' And Abs(InvoerDatum - #" & Format(Z_Datum, "mm/dd/yyyy hh:nn") & "#) < TimeSerial(0, 0, 5);"

 

[Josef P.]

[OT]
Note (date format / regional settings):
/ is the date separator (see Format - User-defined date/time formats) =>
Format(#14/01/2025#, "mm/dd/yyyy") returns 01.14.2025 with reginal setting de-at.
=>use Format(#14/01/2025#, "mm\/dd\/yyyy")

I prefer: Format(#14/01/2025#, "yyyy-mm-dd") or Format(#14/01/2025#, "\#yyyy-mm-dd\#")