Microsoft 365 and Office | Access | For home | Windows
A family of Microsoft relational database management systems designed for ease of use.
It works, partially!
The query sums the totals for the first 2 dates but not the rest.
Access query field format changes
![](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(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
Anonymous
Alright so I am building a database to keep track of my employees shift cash. An issue that I didn't have earlier just randomly popped up. I have multiple records for the same day i.e. 2 - 3 shifts during a day. I run a query to get the totals cash for a day. The query works but when I try to use the query as a subform on a form to report the totals for the day, the format for "day by date" field in the query changes to text, therefore, I cannot link the date field on my daily report form to the query to get the value of the total cash of the day.
I have tried setting the format for the field in the query but that didn't help.
I have set the query to take value from the form's date field but I do not know how to run the query in the background and get the value for a date in to one of the fields on the daily form.
All I want is to grab the total cash amount for the day from 2 - 3 records (same date) and populate it in one of the text box.
Any help would be appreciated!
Thanks
Microsoft 365 and Office | Access | For home | Windows
Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.
8 answers
Sort by: Most helpful
-
Deleted
This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.
Comments have been turned off. Learn more
-
Anonymous
2014-07-06T19:26:31+00:00 -
Anonymous
2014-07-06T17:16:23+00:00 Try this and format date in your output form or report --
SELECT [Shift Cash].[Date] AS [Date By Day], Sum([Shift Cash].[Cash on hand]) AS [Sum Of Cash on hand]
FROM [Shift Cash]
GROUP BY [Shift Cash].[Date];
-
Anonymous
2014-07-06T04:38:54+00:00 Here's the code:
SELECT DISTINCTROW Format$([Shift Cash].[Date],'Long Date') AS [Date By Day], Sum([Shift Cash].[Cash on hand]) AS [Sum Of Cash on hand]
FROM [Shift Cash]
GROUP BY Format$([Shift Cash].[Date],'Long Date');
It's been a while since I worked on access.
-
Anonymous
2014-07-05T16:19:43+00:00 I have tried setting the format for the field in the query but that didn't help.
I have set the query to take value from the form's date field but I do not know how to run the query in the background and get the value for a date in to one of the fields on the daily form.
Format is for display only.
Post the SQL of your queries. This is done by changing query VIEW to SQL View, copy, and paste in a post.