Access: table>macro HELP!

Question

Access: table>macro HELP!

Patty Smeby 20

I am running a Windows PC, a table in access and opening it as a macro. I'm able to run the macro but have trouble with getting correct information out for the year 2026. Specifically January 1, 2026 through January 31, 2026. I know it's not a corrupt file or the date format because I can back date it to 12/31/2025 and then information pulls across, but only the December, no January information. I have the macro set up to select a beginning date and end date. Once I set the beginning date to 1012026 the dates change in my file. *If I set the beginning date to 12312025 I get the information I need from 2025, but nothing in 2026. I have also tried beginning date of #1012026# and 1/01/2026, same results-no information for 2026. See attached documents for screen shots of what is happening...

George Hepworth 22,855 Reputation points Volunteer Moderator

2026-02-05T20:20:21.45+00:00

As is usually the case, giving us exact details, including the code or SQL you are using is a much better way to help us understand your problem. We know nothing about the table, for example. Show us the table in design view, please. Show us some sample data including records where the date criteria does work correctly and records where it does not.

Also, you say "...set the beginning date to 12312025...."
That is a string of digits, not a date, technically speaking. What we need to know about it is how is whether this field is actually a date/time data type and why you are using strings of digits instead of actual dates.

In short, the more context you provide, the better our chance to get to the bottom of the actual problem so we can offer suggestions.

Thank you.

Answer accepted by question author

Anonymous

Hi Patty Smeby

Thank you for reaching out to Microsoft Q&A Forum and sharing your experience.

In Microsoft Access, this type of issue may be related to how date values are interpreted in criteria, especially when filtering across a year boundary. Meanwhile, you may want to verify the following:

1, Use unambiguous U.S. date literals in criteria

Access evaluates date criteria using the mm/dd/yyyy format with # delimiters, regardless of regional settings. Numeric values such as 1012026 can be misinterpreted.

Please ensure your criteria uses an explicit format, for example: #01/01/2026#

2, Avoid Between when time values may exist

If your date field includes a time component, criteria such as: Between #01/01/2026# And #01/31/2026# can exclude records on January 31 after midnight.

You may consider this approach: >= #01/01/2026# AND < #02/01/2026#

3, Confirm the field is a true Date/Time data type

If the field is stored as text or wrapped in Format(), Access will compare values as strings rather than dates, which can lead to unexpected results. Date criteria should be applied directly to the original Date/Time field.

4, Test the criteria in a simple query first

Before running the macro, try applying the same date filter directly in a query. If the query returns January 2026 records correctly, the issue is likely within the macro logic rather than the data itself.

For reference:

Additionally, you mentioned attaching screenshots, but they don’t appear to be included with the post. If you would still like to share them, please note that this is a public forum. For safety and privacy reasons, I recommend sending any supporting documents or screenshots via Private Message to protect your personal data.

User's image

When you have a chance, please share an update on the outcome as your feedback can be valuable to others in the community who might be experiencing the same challenge.

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Anonymous

2026-02-06T07:09:00.3433333+00:00

Hi Patty Smeby

I’m writing to check in on how things are going with this issue.

Have you had a chance to review the replies provided?

Any update would be appreciated.

1 additional answer

Your answer

George Hepworth 22,855 Reputation points Volunteer Moderator

2026-02-05T20:20:21.45+00:00

As is usually the case, giving us exact details, including the code or SQL you are using is a much better way to help us understand your problem. We know nothing about the table, for example. Show us the table in design view, please. Show us some sample data including records where the date criteria does work correctly and records where it does not.

Also, you say "...set the beginning date to 12312025...."
That is a string of digits, not a date, technically speaking. What we need to know about it is how is whether this field is actually a date/time data type and why you are using strings of digits instead of actual dates.

In short, the more context you provide, the better our chance to get to the bottom of the actual problem so we can offer suggestions.

Thank you.
Anonymous

2026-02-06T07:09:00.3433333+00:00

Hi Patty Smeby

I’m writing to check in on how things are going with this issue.

Have you had a chance to review the replies provided?

Any update would be appreciated.

Answer 1

It seems like you are experiencing issues with retrieving data for January 2026 in your Access macro. Here are a few things to check:

Date Format: Ensure that the date format you are using in your macro is consistent with how Access expects dates to be formatted. You mentioned trying #1012026# and 1/01/2026, which should generally work. However, try using #01/01/2026# to ensure the format is correct.
Criteria Setup: Double-check how you have set up the criteria in your macro for the date range. Make sure that the criteria for both the beginning and end dates are correctly specified to include the entire month of January 2026. For example, your criteria might look like this:
```
   Forms!YourFormName.Filter = "[YourDateField] >= #01/01/2026# AND [YourDateField] <= #01/31/2026#"
```
Data Availability: Confirm that there are indeed records in your table for January 2026. Sometimes, the absence of data can lead to confusion about whether the macro is functioning correctly.
Macro Execution: Ensure that the macro is executing correctly and that there are no errors in the macro logic that might prevent it from pulling data for the specified date range.

If you continue to face issues, consider testing the macro with a simplified version or running a query directly to see if it retrieves the expected results for January 2026.

References:

Share via

Access: table>macro HELP!

1 additional answer

Your answer