Just trying to make a daily report from my database. Just lets me do ranges. How do I run just one day.
Just trying to make a daily report from my database. Just lets me do ranges. How do I run just one day.
Why just lets you do ranges? What is the query the report is based on?
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Depends. Is the report based on a table or query? It should be a query. Then you have criteria in a WHERE clause, like
WHERE SomeDateYouSupply = [DATA SHEET].[PRODUCTION DATE]
as long as the production date field doesn't contain time part of the date. If it does, you will probably have to tweak this.
If not, you will have to add a filter to the report.
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.
Yes I am running a query from the table - but I am unsure how to make sure it is looking at just the date and not date and time. I believe it must be looking at time because if i try and run a report for yesterday nothing populates and there is data there. How do I correct this? I am using this criteria. Between [Forms]![Date Picker]![txtBeginProductionDate] And [Forms]![Date Picker]![txtEndProductionDate]
Start by looking at the table in design view. Is the date field Date/Time data type, and if so, what is the format? Is there a default value specified, such as Now()?
No point in delving into how to fix a problem that may not exist; i.e. if that field is text or doesn't contain the time. If it does contain time, then comparing 10/09/2017 to 10/09/17 11:51:47 PM will never be equal so a work around is needed. Not too difficult.
Had you selected the date field in question, the properties would have been displayed for it. As it is, you're showing the ID field, so that doesn't answer all the questions I asked.
Yes. So it is Short Date, which contains no time component and the default is Date(), so time should not be an issue based on the field you show.
The criteria expression looks OK to me. However, I don't see how a date range solves your issue. You said you wanted a report for just one day?Why not =SomeDate to get one day/date?if i try and run a report for yesterday...I am using this criteria...
If you get no results for a particular day and you know data exists for that day, and my question about a single date doesn't apply for some reason, then I'll presume you have joined tables in your query. I would start with a simple test before testing the joins.
Copy the query, and in this copy change the date to an actual date from your table; e.g. #10/15/2017# and run it.
If OK, clear that criteria value and try again using your forms! reference but pick the same date in one control only, and reference that control (this form is open when you're doing all of this, right?). If not OK now, something's happening to the single date being passed from the form to the query. If neither are OK, then test the joins.
Assuming you have more than one table with equal joins between any/all of them (if there's only 1 table, joins are not the problem, but still do the next test)
- put the hard coded date back in that you used (e.g. #10/15/2017#)
- remove one field at a time from the query and run to test. If it eventually works, then either the last field you removed or that one and one or more others are causing a situation where the query cannot be satisfied as you think it should. That means the combination of fields and the criteria you're supplying results in no records that match.
Another possibility is that you have outer joins on the query tables which along with the criteria, don't allow for expected matches.
Last edited by Micron; 10-09-2017 at 10:46 PM. Reason: clarification
Yes - No Joins
Should I just change my criteria?
what/How should the criteria be/look?
Thanks
The answer is in post 10
Copy the query,...