Access date comparison does not work for specific dates

Question

I made a form to update historical data and a subform that is used to check the result! Everything works fine except one small problem.

The result of my date comparison is not correct for any date that is the first date of any month in 2018!!! (it is driving me craziee)

So my code is below:

Private Sub runbtn_Click()

Me.Refresh

Dim theminimum As String
Dim theprodscID As String
Dim thepurchasedate As Date

If IsNull(Me.purchasedate) = False Then

theprodscID = Str(Me.prodscID)
thepurchasedate = Me.purchasedate.Value

'minimum textbox

    theminimum = "Select Top 1 [update value]" & _
                " From [product and shareclass level data update]" & _
              " Where [product and shareclass level data update].[dataID] =" & Str(1) & _
              " And [product and shareclass level data update].[prodscID] =" & theprodscID & _
              " And ([product and shareclass level data update].[timestamp] <= #" & thepurchasedate & "#)" & _
              " Order by [product and shareclass level data update].[timestamp] DESC"


    If CurrentDb.OpenRecordset(theminimum).RecordCount = 0 Then
    Me.minimum = Null
    Else
    Me.minimum = CurrentDb.OpenRecordset(theminimum).Fields(0).Value
    End If

So for example, if I have records update value: "hello" on 01/05/2018; "bye" on 01/08/2017. Then, when I enter the purchase date as 01/05/2018, it should give me "hello" but not "bye"! However, if I enter 12/05/2018, it gives me "hello", which is correct! I find that this error occurs for some dates that I put as timestamp, but works for other dates!

I checked my code and I think it is correct. I don't know what the problem is!

Thanks, Phylly

If you switch to a [parameter query](https://support.office.com/en-gb/article/use-parameters-to-ask-for-input-when-running-a-query-c2806d3d-d500-45a8-8507-ec6af351b6ed), you won't get tripped up by date format or need to bother with `#` delimiters. — HansUp, Jul 30 '18 at 21:04
Its not exactly clear to me what you're trying to accomplish here. But, here's a question, if you're searching specific dates with your query, why are you using `<=` instead of `=` ? If you're ordering your query by `DESC` this will always give you the highest `timestamp` value anyway, just be more specific. Without knowing more about the structure of your data this is about as specific as I can get. — ccarpenter32, Jul 30 '18 at 21:07
@Jiggles32 hey, maybe I should elaborate more on my post! But I am not trying to find the data with the exact date. Instead, my data keeps updating, and each record has a time stamp indicating the date. What I am trying to find is the data that is the latest according to the purchase date, not necessary the same date. That is why I use <= instead of = — Phillysteak, Jul 30 '18 at 21:17
You *may* need to debug this on your end, as a `timestamp` value of `01/05/2018` should be returning the row where `timestamp` = `01/05/2018`. However, it is probably not actually doing that. For instance, there may be a time value not being accounted for or something along those lines. In other words, if you used `timestamp` value of `01/04/2018` instead, it would probably work for the previous date. This is the problem when using date comparisons for datetime values and the like, it just does not work quite like you would expect. — ccarpenter32, Jul 30 '18 at 21:54
@Phillysteak If you `Debug.Print theminimum`, does it display this piece of text in the `WHERE` clause? ... `[timestamp] <= #01/05/2018#` If so, beware the db engine will interpet it as Jan 5 instead of May 1. — HansUp, Jul 30 '18 at 22:21
@HansUp hey! I am not sure how to insert this line, it keeps popping up a window asking Macro name when I run the code in VBA window... — Phillysteak, Jul 31 '18 at 15:15
@Jiggles32 hey! Can you specify how to debug this? Sorry I am pretty new to this world LOL. Also, I tested it couple times, and find that if I enter `"05/05/2018"` it works, also works for `"05/01/2018"`... I am super confused. I formatted my purchase date to be `"dd\/mm\/yyyy"` tho — Phillysteak, Jul 31 '18 at 15:17

score 1 · Accepted Answer · answered Jul 31 '18 at 13:16

1

Your problem is, that the date value must be properly formatted as a text expression. thus:

" And ([product and shareclass level data update].[timestamp] <= #" & Format(thepurchasedate, "yyyy\/mm\/dd") & "#)" & _

Alternatively, implement my function CSql, or - even better - start using parameters (bing/google for that).

answered Jul 31 '18 at 13:16

Gustav

53,498
7
29
55

I have tried formatting the date, but in `dd\/mm\/yyyy` because I am in Canada and I believe that I am using British date format, but the error still exists for certain dates. And another thing I find out is that sometimes it does work for certain dates and sometimes it doesn't for the same ones. – Phillysteak Jul 31 '18 at 15:05
hey!! I changed to your format and it somehow works! I don't understand why though, I thought the format should be the same as how I entered my date. And the way I enter my purchase date is `dd/mm/yyyy`. The timestamp date also appears in the format `dd/mm/yyyy`. Can you help me understand why? – Phillysteak Jul 31 '18 at 15:34
Your displayed format has no influence on the coding which expects the "reverse" US format or the ISO sequence yyyy-mm-dd for string expressions for date/time values. – Gustav Jul 31 '18 at 17:46

Access date comparison does not work for specific dates

1 Answers1