This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(198.4, 99%, 29%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EJK%3C/text%3E%3C/svg%3E)
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.
A family of Microsoft relational database management systems designed for ease of use.
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.
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.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(284.8, 62%, 37%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EDH%3C/text%3E%3C/svg%3E)
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.
Thanks. The Supplier will always be entered and be included in the formula.
But what value for the Est Deposit Date do you want if the supplier is not Zenova or Lusotufo?
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
Hi Karl
SQL info as requested.
nfo a
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?
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.
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?
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.
I also want to be able to bring in another date fields such as Final Amount Due.
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.
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
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?
No, how do I fix these fields properly?
One field I looked at the Format option does give me any choices in the Properties box.
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")
I don't format columns in queries. Save the formatting for the forms and reports.
But you asked "Is the amount field/column always right-aligned in each query?" and one isn't, so I responded to that.