Hallo All,
This is a Tool i m tryin to develop at work.
I will explain how it works and then the Q
I download a report from my ERP Tool on a daily basis and upload it into the Access Tool. This report gives me the accounting bookings made on a daily basis. So it has fields like Invoice Nr, Invoice Amount, and Date.
Well, then i have a Duplicate Query set up to give me a report for all Duplicates...and then i download it into an excel. This excel table, i auto-filter it as of Today's date/specified date and view the Duplicate Bookings for Today/specified date.
Now this is a Tedious task and everyday i get the same dump + Today's duplicates, (if any).
Now, this is what i want to Develop : I want to have a form, where i can specify the date or date range from this Duplicate Query and then pull out the Duplicate report for the specified Date/Date Range.
So that i can save up on my disk space while saving these files.
Please help !
Thanks
I assume you are trying to omit exporting your Duplicate report to Excel and run it all in Access? If so:
You will have to create an unbound form with two fields for the first date of the date range and the last date of the date range. In your Duplicates query you will then set the criteria of the Date field to be between the two dates on your form. I set the Visible property of the form to No so you can't see it while you're viewing your Duplicates Query. If you view the results of the Duplicates Query in a form or report rather than just a query, you can also create a macro which closes the unbound date range form when you close the Duplicates Query form or report. Here are the steps:
Create an unbound form by clicking New Form and Design View. We'll name it DuplicatesDateRange.
Add two new text fields, call them FirstDate and LastDate.
Add a button with the OnClick performing the following macro, named DuplicatesGo:
SetValue, Forms![DuplicatesDateRange].Visible , No
OpenForm, DuplicatesQueryForm (which you will need to create)
In the Duplicates Query, for the Date criteria, enter Between Forms![DuplicatesDateRange]![FirstDate] And Forms![DuplicatesDateRange]![LastDate]
On the DuplicatesQueryForm, create a button with the OnClick performing the following macro, named CloseDuplicatesQueryForm:
Close, Object Type=Form, Object Name=DuplicatesQueryForm, Save=No
Close, Object Type=Form, Object Name=DuplicatesDateRange, Save=No
I hope this helps.