The only thing I can think of is that it's a regional date formatting problem since I'm using ISO dates, but that shouldn't really matter because it's based on actual date values. That's a weird one.

I just added an item manually to todays date. As soon as I clicked requery the item disappeared from the form, but it was recorded in the table and shows up in the query! I'm at a loss.

Navigate the form back to 10/03/2025. If the records show up on the form for that date that will tell you if it is a regional date problem as Richard suggested.

Hi Len again, this should fix your issue, ISO dates will also fix this.

FoodLogF

Private Sub UpdateFilter()

    Dim strSQL As String
    Dim d1 As String
    Dim d2 As String

    d1 = "" & Format(LogDate, "yyyy/mm/dd")
    d2 = "" & Format(LogDate + 1, "yyyy/mm/dd")

    strSQL = "FoodDateTime >= #" & d1 & "# AND " & _
        "FoodDateTime < #" & d2 & "#"

'    strSQL = "FoodDateTime >= #" & LogDate & "# AND " & _
'        "FoodDateTime < #" & LogDate + 1 & "#"

    Filter = strSQL
    FilterOn = True

End Sub

Me again, last post was my test

you can just do this

    Filter = "FoodDateTime >= #" & Format(LogDate, "yyyy/mm/dd") & "# AND " & _
        "FoodDateTime < #" & Format(LogDate + 1, "yyyy/mm/dd") & "#"

Hi Kevin, thanks for your suggestion. I tried it and sure enough the records showed up. To convert to ISO dates is it just a matter of formatting the Date/Time field in the table or should I go through Richards ISO date tutorial. Meanwhile I am going to use Darrins code. Regards

Hi Darrin, thanks for your help. I inserted your first longer code (Didn't scroll down enough) and it worked fine. Not quite sure where I should put your revised code. Is it in the update filter and does it replace Richards code. Sorry if I seem a bit slow, I've got an old brain, a bit like the rest of me!

ISO dates need to be change in windows, when I changed to ISO, some of my databases had to be fix.

https://www.youtube.com/watch?v=kNlCqW_yXqg or
https://599cd.com/blog/display-article.asp?ID=2201

There is about 7 videos on ISO dates?

ISO Date Format
ISO Date Format in Access
Convert ISO Date Time
Convert ISO Date Time 2
ISO Date Addendum 1
ISO Date Format in Excel

If ISO has to be in Windows I'll have to give that some thought. Darrin I'm still not sure how to deal with that code.

Access will use the default date setting for your system, which is set in Windows. That's if you stick to the short date format. If you specify your own format, you can force Access to use ISO dates, but I don't recommend it. Because then you have to set that format everywhere instead of just using short date.

If you have a situation where you have to give people data in a specific format, or if you have to display things in a specific format, or if you have to generate reports in a specific format, then you can always format that date result at that time. For example, here in the United States everyone expects month/day/year, and I know in Europe you guys use day/month/year. But I still recommend using ISO dates internally for your system. And then if you have to provide data to people in a specific format that's regional, then use the regional settings. But ISO date makes the best sense internally.

Len

FoodLogF

Private Sub UpdateFilter()

    Filter = "FoodDateTime >= #" & Format(LogDate, "yyyy/mm/dd") & "# AND " & _
        "FoodDateTime < #" & Format(LogDate + 1, "yyyy/mm/dd") & "#"
    FilterOn = True

End Sub

Yeah, that should work, but I really hate formatting dates like that until the final step when you're displaying them. That can cause problems, but hey, if it works, it works.

Richard

What would be a better fix then?

This problem has been around since Fitness 31.

Remember Edwin Sulsters, I'm not the only one having this problem, love your work, enjoying the Fitness database series.
Edwin can't change to ISO Dates.

Cheers

I guess I'm not understanding why you're having the problem in the first place because I haven't hard-coded any formatting in the database at all. It's all based on whatever your local settings are. I'm just using short dates. Aside from a few places where I display a date a specific way (like the top of the FoodLogF) none of my queries or VBA code requires a specific date format. I have no formatting specified in the tables or queries (as you shouldn't). So the whole database should run fine with whatever your local date settings are. Show me specifically where it's not working for you.

My UpdateFilter code, for example, takes whatever your system-formatting date is and puts it in that SQL statement. Your regional settings should kick in and it should work regardless of your date format.

All right, so I'll admit my ignorance when it comes to international date formatting. This is one of the reasons why I switched to ISO dates in the first place is to avoid stuff like this. But apparently anything that you put between the # symbols in Access will automatically get evaluated as month/day regardless of whether it's the US date format month/day/year or the ISO standard which is year-month-day. It's always month first, day second, no matter what your regional settings are set to.

Access parses dates inside # # in US order by default (mm/dd/yyyy), no matter your Windows regional settings. The safe alternatives are to use ISO with hyphens (yyyy-mm-dd), for example #2025-10-03#, or to avoid literals and use functions like DateValue and DateAdd in your criteria so Access evaluates the date at runtime. This is why filters that build #dd/mm/yyyy# strings break after 12 for non-US users. Use yyyy-mm-dd or function-based criteria to stay locale-safe.

So if you're in one of those European countries that go day-month-year, then you're going to have problems with it. Since I'm not an expert on international date formats, I asked ChatGPT to give me what it thinks is a good fix, and here's what it came up with.

Why records after September 30 are missing on FoodLogF (and the fix)

Summary
If your FoodLogF shows records through September 29 but nothing on or after September 30, you are hitting a date literal parsing issue. In Access/Jet/ACE, any date wrapped in # # is parsed in US order regardless of your Windows regional settings. So a literal like #03/10/2025# is read as March 10, 2025, not 3 October, 2025. That is why your filter stops working after 29 September.

What caused it
Concatenating a filter string like:
FoodDateTime >= #10/03/2025# AND FoodDateTime < #10/04/2025#
looks fine in the UK, but Jet reads it as October 3 and October 4 only when the day is 13 or less. After 12, it fails silently by swapping month and day.

Three safe fixes (pick one)

A) Use ISO date literals with hyphens (yyyy-mm-dd) in your filter
Hyphenated ISO is consistently parsed by Jet/ACE.

Private Sub UpdateFilter()
Dim D As Date
D = Me.LogDate
Me.Filter = "FoodDateTime >= #" & Format$(D, "yyyy-mm-dd") & " 00:00:00# AND " & _
"FoodDateTime < #" & Format$(DateAdd("d", 1, D), "yyyy-mm-dd") & " 00:00:00#"
Me.FilterOn = True
End Sub

Notes:
Use hyphens, not slashes.
Format$ avoids Null-to-empty-string surprises.

B) Avoid literals entirely and let Access evaluate functions inside the filter
No string-built dates, no regional pitfalls.

Private Sub UpdateFilter()
Me.Filter = "FoodDateTime >= DateValue([LogDate]) AND " & _
"FoodDateTime < DateAdd('d', 1, DateValue([LogDate]))"
Me.FilterOn = True
End Sub

Notes:
This is concise and locale-safe.
Jet evaluates DateValue([LogDate]) and DateAdd at runtime, so there are no #date# literals to misparse.

C) Push the logic into the form RecordSource/query parameters
Use criteria in the underlying query and just requery when LogDate changes.

In the query WHERE clause:
FoodDateTime >= DateValue([Forms]![FoodLogF]![LogDate])
AND FoodDateTime < DateAdd('d', 1, DateValue([Forms]![FoodLogF]![LogDate]))

Then in the form code when LogDate changes:
Private Sub LogDate_AfterUpdate()
Me.Requery
End Sub

Which one should you use

B is the quickest safe patch on existing forms using Filter.

C is the most robust long term because it avoids string concatenation and keeps the logic in SQL.

A is fine if you prefer literals, but be sure to use yyyy-mm-dd and include a time component.

Why I do not recommend "yyyy/mm/dd" with slashes
Even though yyyy/mm/dd looks ISO-ish, you are still building a #date# literal with slashes, and some Jet/ACE builds can misinterpret it depending on context. Hyphens are safer, or skip literals entirely with option B or C.

If copied records appear as already eaten
If you mark items as eaten based on a comparison like:
FoodDateTime < Date() + Time()
or a Now() split, make sure any comparisons also avoid ambiguous #date# literals. Use DateValue, DateAdd, and explicit TimeValue or TimeSerial to keep everything locale-safe. Example:

IsEaten = (FoodDateTime < DateAdd("s", 0, Date + Time()))

Bottom line
This is not a Windows locale problem in your tables. It is how Jet/ACE parses #date# literals. Use ISO hyphenated literals or, better yet, eliminate literals by using DateValue/DateAdd either in the Filter (B) or in the query (C).

Darrin so yes, I guess in light of this, your Format solution is a valid one.

The good news is you should just have to use this date format in your SQL statements in your code. You can still continue to use whatever date format you want for display in your tables and forms, as long as you remember to use the ISO date standard in your SQL statements and in your VBA code.

Thankyou Richard

I've tested B and it's not working no matter what I do?

A Works fine, like mine, but mine was just a test because I change to ISO dates.
I had to do the tests on my wifes computer, witch dose not have ISO dates.

I'll take you up on the changes from \ to - thankyou again for your advice :)
Also the HasEaten seems to work fine with this change.

So Len this code is the go.

Private Sub UpdateFilter()

    Dim D As Date
    D = Me.LogDate

    Me.Filter = "FoodDateTime >= #" & Format$(D, "yyyy-mm-dd") & " 00:00:00# AND " & _
        "FoodDateTime < #" & Format$(DateAdd("d", 1, D), "yyyy-mm-dd") & " 00:00:00#"
    Me.FilterOn = True

End Sub

Your the best Richard.

Hi Darrin, the code you gave me before works fine, but with the above code I get error 3709 the search key was not found in any record. If I click debug I get Me.FilterOn = True highlighted. If I click End or reset FoodLogF has every record in the table listed.

Hi Len, hope all's good

Just use what works then

Hi Darrin, Yep that's what I'm doing. Thanks to all for the help, looking forward to F49. Cheers

Working on 49 today. :)

Hello Len Jolly

Did you watch Quick Queries 61?

No not yet will have a look today. FYI after I inserted your code all was working fine until I tried the Copy Day button, but your date code sorted that as well. Thanks again

Your welcome Len

Hi Darrin. Just watched QQ61. I haven't been watching QQs since about number16, but that' inspired me to do so, guess I'm gonna work my way backwards through the list. Be lucky, Len

Yeah - lots of good tips snuck in those QQ videos. Lots of rambling nonsense too... but hey... they can't all be gems. :)

Access CAN use dd/mm/yy date format in SQL, but only if it's unambiguous.  If you put #31/10/2025# in an SQL, Access will correctly interpret it as Oct-31-2025 because "31" is a nonexistent month, and Access can unambiguously see it as Oct-31-2025.  But if you use #11/02/2025# in an SQL, Access will always interpret it the American way, Nov-02-2025, and never Feb-11-2025.

Maybe I missed this question, but did anyone suggest changing the LOCALE settings in windows to see if that fixes the problem?   Go to Control Panel, the open the Region Settings.   From there you can change how windows behaves for different countries or for custom settings.

We are on YouTube man! Ha! Did you see us? we are naturals

SQL is its own thing, and it's not affected by any regional settings.  SQL is text-only and cannot store any info other than what is in the SQL statement itself.  The query designer does allow the use of regional date formats.  But the SQL statement itself (the only thing that counts) always uses the mm/dd/yyyy format.

Yep, and like Kevin said (and like I said in the video) SQL always looks for mm/dd or mm-dd whether the year is in front or back.

Moral of the story: ISO DATES FOR THE WORLD!!!!!!!