Calculating and displaying tax on report (1 Viewer)

[Mike Krailo]

Have a client that has three different tax categories on his PO subform. The original problem was just getting the Vendor and ShipTo addresses to print out on the POReport. That's working now but I had to switch from text to number type for both of those fields to get it to work. If there is a way to preserve the text fields for Vendor and ShipTo addresses I would like to know how to do that. The larger issue is getting the proper tax category total to print out on the POReport.

I will attach a demo database and if someone can look at it, that would be great. I was thinking a temp table or using TempVars to store the final calculated tax and fill in the text box on the POReport.

When the database is opened, you select a customer. Then click OPEN JOB to go to the JobForm2. There will be a continuous view of items (in a subform). Each item has a PO button to launch the Purchase Order report.

I can't seem to get the file size down to a reasonable size. I'll try again later.

 

[jdraw]

Did you try Compact and Repair, then ZIP?

 

[Mike Krailo]

Sure did. I tried to clean up un-needed objects as much as possible and the size comes out to 5.11Mb. I may have been over complicating my issue though. I was thinking I had to pass in a calculated field on the form but all I think I need is the tax rate and just do the calculation on the report.

 

[jdraw]

Sometimes reviewing the problem with a little different focus, suggests another technique.
Good luck with your project.

Most times readers don't need the full application. Enough data/materials to highlight the issue may suffice. Perhaps there are a few tables or forms... that could be removed to create a "copy to review". Just make sure you keep the original (and its latest backup).

 

[Mike Krailo]

There is only three customers and two jobs in it so the size must be due to the many controls. I'll see what I can do to reduce the size down and post back if I come up with a solution.

 

[Micron]

Maybe it has a bunch of images embedded in controls or attachments in tables? If so, delete the attachment field(s) in the copy if they are not pertinent to the problem, and embedded/background images.

 

[Mike Krailo]

OK guys, I ended up solving the main problem I had with the tax calculation on the report and it was just quite simple. It was just a simple calculation on the report and this was easy because each PO is for only one type of tax category and each item on the list has the tax rate stored in the record. The report looks good to me now. The only issue left is seeing if there is a way to go back to text fields in the Vendor and ShipTo fields in the ItemTable so the information doesn't change latter due to an up date in either of those tables.

I'll try again to reduce the size of the file. I didn't see any images other than the company logo on the report. UPDATE: Finally got it under the limit.

 

[jdraw]

Good stuff ---perseverance!

 

[Cronk]

The only issue left is seeing if there is a way to go back to text fields in the Vendor and ShipTo fields in the ItemTable

There is no field Vendor in table ItemTable. Or did you mean VendorCompany?

If so, then both fields in your SQL providing data to report POReportTax are foreign keys to Autonumber primary keys in relevant tables. They can't be text. They have to be numeric. I can't understand your issue as you are using the corresponding text data in the linked tables.

 

[Mike Krailo]

Yes, my bad, VendorCompany. The issue is that prior to setting up the way it is now, there used to be text fields in the ItemTable and those fields linked to the text fields in the two respective tables. In that prior situation the reports had un-resolvable fields (e.g. ?Name) where the company name and vendor name should be populated. I'm actually thinking that the original table had mixed data types in it like numbers formatted as text along with regular text of the company names. I can't remember what I did during that time of the troubleshooting process.

The glaring problem that this client had was that he created three ItemTables originally. There was one for each tax category. I combined them into one table and added a field for TaxType. That simplified the query and got rid of two of the reports. Using the numerical foreign key link instead of text got the fields to populate in the report.

In any case my client was happy with the results I gave him getting the report to generate all the proper fields as they do now. I just wondered if I could use text fields in place of numerical foreign keys so that the result of the selection was not a link but the actual text. That way the data is frozen in the record and would never change.

 

[Cronk]

Yes, I know the difficulties of being asked to enhance an existing system that has flaws with the underlying design. While the desire is there to fix all the existing issues, it is often a trade off between trying to do this with an unfamiliar system given all the existing queries/forms/reports/code based on the existing structure and object names, especially where there are costing (time) constraints.