Thanks, June7. I'll try to solve the problem!
How to sum and calculate from tables? (1 Viewer)
- Thread starter tihmir
- Start date
Hello again, I am still looking for a solution to my problem with reporting in the appropriate form. Here's what I've built so far with the huge help of
isladogs and June7:
I have query "qryTypes_Crosstab1" and "Query1". NowI'm trying to set the "Query1" as RecordSource to report but it shows "The specified field tbl_CodeObjects.ObjectType could not more than one"
isladogs and June7:
I have query "qryTypes_Crosstab1" and "Query1". NowI'm trying to set the "Query1" as RecordSource to report but it shows "The specified field tbl_CodeObjects.ObjectType could not more than one"
Attachments
Maybe I should explain a little more why I added those additional fields from tbl_Code_Objets in Query 1. My idea is when I generate a report, it must be displayed all the entries in the fields "CodeObjectID" and "ObjectType" аnd then if there are records for the specifically filtered worker and/ or date to show the Inspections for the corresponding to the respective objects. If there are no records (inspections) for the relevant period or worker show value "0" for relevant Objects.
Attachments
-
234234.png
Thank you again, isladogs. Works great. That's what I wanted.
Now I try to set up the second criteria - Date range in the "qryTypes_Crosstab1"but it doesn't work:
Code:
Between [Forms]![fm_Report2]![txt_DateFrom] And [Forms]![fm_Report2]![txt_DateTo]If txt_DateFrom and txt_DateTo are null, don't apply any filter
If txt_DateFrom and txt_DateTo are not null, filter between those two
If txt_DateFrom is null and txt_DateTo is not null, filter from the beginning of time to txt_DateFrom
If txt_DateFrom is not null and txt_DateTo is null, filter from txt_DateFrom to the end of time
(txt_DateFrom and txt_DateTo are the text box fields on the form "fm_Report2")
Last edited:
isladogs
MVP / VIP
- Local time
- Today, 00:10
- Joined
- Jan 14, 2017
- Messages
- 18,209
Tihmir
Please take the time to study and understand how I got that to work.
As for filtering by date range, please look at the incident analysis example I provided in this thread. https://www.access-programmers.co.uk/forums/showthread.php?t=302189.
That (and the other example provided) show how I build sql strings dynamically to manage one or many filter criteria. If you use that approach you will be able to do any example yourself, no matter how complex.
Good luck with your project
Please take the time to study and understand how I got that to work.
As for filtering by date range, please look at the incident analysis example I provided in this thread. https://www.access-programmers.co.uk/forums/showthread.php?t=302189.
That (and the other example provided) show how I build sql strings dynamically to manage one or many filter criteria. If you use that approach you will be able to do any example yourself, no matter how complex.
Good luck with your project
isladogs
MVP / VIP
- Local time
- Today, 00:10
- Joined
- Jan 14, 2017
- Messages
- 18,209
Hmm. Never seen that before with this app.
The attached screenshot is what you should see when you click the Day Of Week button.
Which version/bitness of Access are you using?
Check for missing references in the VBE
If that doesn't solve it, debug and see what it shows.
Also see if an error occurs if you click another button such as Location
The attached screenshot is what you should see when you click the Day Of Week button.
Which version/bitness of Access are you using?
Check for missing references in the VBE
If that doesn't solve it, debug and see what it shows.
Also see if an error occurs if you click another button such as Location
Attachments
-
Capture.PNG
I use MS Access 2016, 64-bit. For every button, the error is the same as the one I showed in the picture. Just the exit button does not show me an error!
By the way, I should mention that when I looked carefully at the latest version you built - Database_EN_v7.4, if I don't apply folter "cbo_Worker" No records appear in the report, but all records should appear when the filter is not applied. It does not work properly!
isladogs
MVP / VIP
- Local time
- Today, 00:10
- Joined
- Jan 14, 2017
- Messages
- 18,209
With regard to my app, you didn't tell me whether there were reference issues.
Did you try debugging as I requested? If so what did it show?
As I don't see those errors, I'm unable to advise unless you help me out.
There are some 64-bit API declaration issues in the module modResizeForm but I can't see how that would cause the error you described.
EDIT: Just tested it in A2016 64-bit with no errors
With your app, I started with your version 7.2 which didn't work and did what you asked for v7.4. The issue you've now raised wasn't mentioned before. Its caused by the filter in the Worker field of your crosstab query which I had nothing to do with. What have you done to try and solve this yourself?
Last edited:
I tried with Debug, but when I start it no error occurs.
I'm sorry I didn't mention it in the specific comment, because I wrote about it in the comments above.
I'm quite a beginner and trying to learn from the things you do and the suggestions you offer. This task is impossible for me and my capabilities. This is the reason I ask for help!
I need help making a report with the query in which report to be able to filter by date and/ or by worker. But when no filter is used, all records are displayed, as I mentioned in the posts above. I apologize if I have not explained correctly
isladogs
MVP / VIP
- Local time
- Today, 00:10
- Joined
- Jan 14, 2017
- Messages
- 18,209
Tihmir
That still didn't answer either of my questions. Perhaps you didn't understand what I was asking?
I dropped out of the thread a long time ago as I didn't have the time to write your app for you. I came back in because it seemed there was a simple solution I could fix quickly. It seems I was mistaken
If you rely on others you will never learn to become self reliant.
I recommend you spend time studying the Access tutorials by Steve Bishop on You Tube. It is a complete course from beginner to advanced and well worth the time
I'm going to drop back out of this thread again now as I have a number of my own clients to support and other projects needing attention.
Good luck with your project
That still didn't answer either of my questions. Perhaps you didn't understand what I was asking?
I dropped out of the thread a long time ago as I didn't have the time to write your app for you. I came back in because it seemed there was a simple solution I could fix quickly. It seems I was mistaken
If you rely on others you will never learn to become self reliant.
I recommend you spend time studying the Access tutorials by Steve Bishop on You Tube. It is a complete course from beginner to advanced and well worth the time
I'm going to drop back out of this thread again now as I have a number of my own clients to support and other projects needing attention.
Good luck with your project
isladogs,
I tried to understand what you were asking me and give you an answer, but I might have missed something.
Still, if anyone has an idea how this report could be compiled (which I wrote about a little earlier), please write. I will be grateful for your advice. This is the last step of my project to be completed. Thank you all.
I tried to understand what you were asking me and give you an answer, but I might have missed something.
I will certainly continue to rely on other people (as well as you), (especially here in this forum) because I know that they have a lot of knowledge, skills and extensive experience in Access and development.
I will listen to your advice and look at the tutorials by Steve Bishop on You Tube.
Good luck with your own clients and other projects that need more attention!
Still, if anyone has an idea how this report could be compiled (which I wrote about a little earlier), please write. I will be grateful for your advice. This is the last step of my project to be completed. Thank you all.
