Access Database Date issues

Question

Access Database Date issues

Jennet Knuckey 45

Hi

I am waiting to show ALL dates in a calender and sum amounts from another date field.

Screenshot of query below.

Please note the joins is option 2. Include all from master calender and only those dates from the other that match.

It runs but the amounts are not showing up.

User's image

Jennet Knuckey 45 Reputation points

2026-08-14T05:37:05.9566667+00:00

Problem solved by Duane!

Answer accepted by question author

Duane Hookom 26,935 Volunteer Moderator

Are there amounts stored in the amt due field? If you remove the group by, do you see amounts in the field?

Can we assume you are seeing dates and a blank Sum column?

Jennet Knuckey 45 Reputation points

2026-08-01T19:00:26.3966667+00:00

Hi Duane, thanks for your prompt reply.

Yes, there are amounts in the amount due field - they work in other queries. I also checked my amounts are for dates in the future; so I know what I expect to see.

If I remove group by I get the error message 'Your query does not include the specified expression 'The Date' as part of an aggregate function'.

Yes, I am seeing all calendar dates and a blank sum column.
Duane Hookom 26,935 Reputation points Volunteer Moderator

2026-08-02T11:13:54.25+00:00

Does the Est Deposit Date include a time portion or is it just the date? If you change the join to an equal join, does the query return any records?
Jennet Knuckey 45 Reputation points

2026-08-03T06:49:18.0933333+00:00

Hi Duane, thank you,

It comes from a Date/Time field, but it's just a date as shown in the snip. When changing to Option 3 an equal join, it shows the date and amount I am expecting. The issue is I want to show all dates, so a cashflow can be exported.
Jennet Knuckey 45 Reputation points

2026-08-03T06:51:33.45+00:00

I also want to be able to bring in another date fields such as Final Amount Due.
Duane Hookom 26,935 Reputation points Volunteer Moderator

2026-08-07T21:29:02.7733333+00:00

I would probably need to see/review a copy of your file or stripped to the required tables/queries. What do you see if you add the Est Deposit Date to the query? It would help to see the SQL property of this query as well as the Cashflow Purchase... query.

I can't imagine which Final Amount Due date you would expect unless it's always the same as the Est Deposit Date.
Jennet Knuckey 45 Reputation points

2026-08-07T21:59:52.52+00:00

Hi Duane

Would love to just send you file, but this platform won't enable me to do that.

If I add 'Est Deposit Date' to the query (with nothing in the Total row) I get the error message 'Your query does not include the specified expression 'Est Deposit Date' as part of an aggregate function.

If I add 'Est Deposit Date' to the query (with default Total line being 'Group by') I get (as per pic below)

I decided to try taking 'Group by out from 'The Date' and change the join properties to Option 2, but I got errors, so changed these back.

This is what I have now, plus the datasheet snip as above

SQL's requested

Query - Cashflow Export File

Query - Cashflow Purchases Query for Cashflow Report

Thanks Duane
Duane Hookom 26,935 Reputation points Volunteer Moderator

2026-08-07T22:09:36.6166667+00:00

It looks like you have chained a number of queries to get to the most recent. Is the amount field/column always right-aligned in each query?
Jennet Knuckey 45 Reputation points

2026-08-07T22:24:09.3133333+00:00

No, how do I fix these fields properly?
Jennet Knuckey 45 Reputation points

2026-08-07T22:27:53.76+00:00

One field I looked at the Format option does give me any choices in the Properties box.
Jennet Knuckey 45 Reputation points

2026-08-07T23:16:20.91+00:00

Their is just one field that is not right-aligned. As below

Currency Deposit Amount: Format(Switch([Carpet Purchase Order Data ex Cin7]![Supplier]="Zenova",[Currency Amount][Deposit Rate],[Carpet Purchase Order Data ex Cin7]![Supplier]="Lusotufo",[Currency Amount][Deposit Rate],True," "),"Standard")
Duane Hookom 26,935 Reputation points Volunteer Moderator

2026-08-08T15:43:10.6366667+00:00

I don't format columns in queries. Save the formatting for the forms and reports.
Jennet Knuckey 45 Reputation points

2026-08-08T20:33:00.4966667+00:00

But you asked "Is the amount field/column always right-aligned in each query?" and one isn't, so I responded to that.

2 additional answers

Your answer

Jennet Knuckey 45 Reputation points

2026-08-14T05:37:05.9566667+00:00

Problem solved by Duane!
Jennet Knuckey 45 Reputation points

2026-08-01T19:00:26.3966667+00:00

Hi Duane, thanks for your prompt reply.

Yes, there are amounts in the amount due field - they work in other queries. I also checked my amounts are for dates in the future; so I know what I expect to see.

If I remove group by I get the error message 'Your query does not include the specified expression 'The Date' as part of an aggregate function'.

Yes, I am seeing all calendar dates and a blank sum column.
Duane Hookom 26,935 Reputation points Volunteer Moderator

2026-08-02T11:13:54.25+00:00

Does the Est Deposit Date include a time portion or is it just the date? If you change the join to an equal join, does the query return any records?
Jennet Knuckey 45 Reputation points

2026-08-03T06:49:18.0933333+00:00

Hi Duane, thank you,

It comes from a Date/Time field, but it's just a date as shown in the snip. When changing to Option 3 an equal join, it shows the date and amount I am expecting. The issue is I want to show all dates, so a cashflow can be exported.
Jennet Knuckey 45 Reputation points

2026-08-03T06:51:33.45+00:00

I also want to be able to bring in another date fields such as Final Amount Due.
Duane Hookom 26,935 Reputation points Volunteer Moderator

2026-08-07T21:29:02.7733333+00:00

I would probably need to see/review a copy of your file or stripped to the required tables/queries. What do you see if you add the Est Deposit Date to the query? It would help to see the SQL property of this query as well as the Cashflow Purchase... query.

I can't imagine which Final Amount Due date you would expect unless it's always the same as the Est Deposit Date.
Jennet Knuckey 45 Reputation points

2026-08-07T21:59:52.52+00:00

Hi Duane

Would love to just send you file, but this platform won't enable me to do that.

If I add 'Est Deposit Date' to the query (with nothing in the Total row) I get the error message 'Your query does not include the specified expression 'Est Deposit Date' as part of an aggregate function.

If I add 'Est Deposit Date' to the query (with default Total line being 'Group by') I get (as per pic below)

I decided to try taking 'Group by out from 'The Date' and change the join properties to Option 2, but I got errors, so changed these back.

This is what I have now, plus the datasheet snip as above

SQL's requested

Query - Cashflow Export File

Query - Cashflow Purchases Query for Cashflow Report

Thanks Duane
Duane Hookom 26,935 Reputation points Volunteer Moderator

2026-08-07T22:09:36.6166667+00:00

It looks like you have chained a number of queries to get to the most recent. Is the amount field/column always right-aligned in each query?
Jennet Knuckey 45 Reputation points

2026-08-07T22:24:09.3133333+00:00

No, how do I fix these fields properly?
Jennet Knuckey 45 Reputation points

2026-08-07T22:27:53.76+00:00

One field I looked at the Format option does give me any choices in the Properties box.
Jennet Knuckey 45 Reputation points

2026-08-07T23:16:20.91+00:00

Their is just one field that is not right-aligned. As below

Currency Deposit Amount: Format(Switch([Carpet Purchase Order Data ex Cin7]![Supplier]="Zenova",[Currency Amount][Deposit Rate],[Carpet Purchase Order Data ex Cin7]![Supplier]="Lusotufo",[Currency Amount][Deposit Rate],True," "),"Standard")
Duane Hookom 26,935 Reputation points Volunteer Moderator

2026-08-08T15:43:10.6366667+00:00

I don't format columns in queries. Save the formatting for the forms and reports.
Jennet Knuckey 45 Reputation points

2026-08-08T20:33:00.4966667+00:00

But you asked "Is the amount field/column always right-aligned in each query?" and one isn't, so I responded to that.

Answer 1

George Hepworth 23,120 Volunteer Moderator

Show us some sample data.

I'm particularly interested in the date formats in the two sources.

We are assuming that the dates actually match, but if some of them have a time component and others don't, they'll never match. It may not be the problem, but we need to rule it out along with the other potential issues in these queries.

Jennet Knuckey 45 Reputation points

2026-08-08T21:00:17.25+00:00

Hi George

Sure.

Query Master Calendar is coming from the table Master Calendar and has all dates in the calendar. The data type is Date/Tiime.

Query Cashflow Purchases Query for Cashflow Report, relevant field Est Deposit Date comes from 'Carpet Purchases Calc Invoice Date and Deposits' query.

In the query 'Carpet Purchases Calc Invoice Date and Deposits' I have the field as Est Deposit Date: CVDate(Switch([Carpet Purchase Order Data ex Cin7]![Supplier]="Zenova",[Est Date of Delivery (ETD)]-[Shipping Days]-[Production Days],[Supplier Information]![Supplier]="Lusotufo",[Est Date of Delivery (ETD)]-[Shipping Days]-[Production Days],True," ")).

The fields from Table Supplier Information: Est Date of Delivery (ETD) is Date/Time, Shipping Days is a number and Production Days is a number.
Duane Hookom 26,935 Reputation points Volunteer Moderator

2026-08-08T21:07:48.96+00:00

It looks like your Est Deposit Date could return a space if the supplier is not Zenova or Lusotufo. A space isn't a date.
Jennet Knuckey 45 Reputation points

2026-08-08T21:11:39.9533333+00:00

Thanks. The Supplier will always be entered and be included in the formula.
Duane Hookom 26,935 Reputation points Volunteer Moderator

2026-08-08T22:21:05.5933333+00:00

But what value for the Est Deposit Date do you want if the supplier is not Zenova or Lusotufo?
Jennet Knuckey 45 Reputation points

2026-08-08T22:23:15.5733333+00:00

To display the word ERROR
Duane Hookom 26,935 Reputation points Volunteer Moderator

2026-08-09T15:07:49.8233333+00:00

Sorry, I don’t follow how/why you would accept ERROR as a value in a date field.
Jennet Knuckey 45 Reputation points

2026-08-10T06:20:33.7066667+00:00

1 Jan 1900 then. It doesn't matter because the supplier will always be one or the other.
Duane Hookom 26,935 Reputation points Volunteer Moderator

2026-08-10T15:19:19.4866667+00:00

I'm still not sure if this is the issue. It seems like we are nibbling around the edges. Both expressions for the suppliers are the same. Why even use Switch()?

[Est Deposit Date]: [Est Date of Delivery (ETD)]-[Shipping Days]-[Production Days]

Does every record have values form ShippingDays and ProductionDays?
Jennet Knuckey 45 Reputation points

2026-08-10T18:13:56.4466667+00:00

Hi I don't even understand switch. I did whatever to get the date to work. Every supplier in Supplier Information Table has shipping days and production days.
Duane Hookom 26,935 Reputation points Volunteer Moderator

2026-08-11T04:21:02.8366667+00:00

Do you understand that the long expression isn't needed based on your description and syntax?
Jennet Knuckey 45 Reputation points

2026-08-11T05:01:16.9666667+00:00

Obvs no. What should I use instead?
Duane Hookom 26,935 Reputation points Volunteer Moderator

2026-08-11T12:01:11.2266667+00:00
This is what I suggested earlier:

[Est Deposit Date]: [Est Date of Delivery (ETD)]-[Shipping Days]-[Production Days]
Jennet Knuckey 45 Reputation points

2026-08-14T05:35:47.1233333+00:00

Thanks Duane problem solved. I changed the Cashflow Export file to Option 2 and can see all dates and the Est Deposit Date and correct amount due. I will start a new thread if I have further issues. Thanks for sticking with me and solving my issue :-)

Answer 2

Karl Donaubauer 3,501 MVP

Hi,

Maybe we can see something else that matters when you go to the SQL view of the query and copy/past the SQL text into your next post.

Servus
Karl

Access Forever, News, DevCon
Access-Entwickler-Konferenz AEK

Jennet Knuckey 45 Reputation points

2026-08-07T07:09:39.4133333+00:00

Hi Karl

SQL info as requested.

nfo a