Calculation

[Diamond]

https://www.dropbox.com/s/uizefkv45g...tCityAgain.zip. I finally got the link to show in a post. I could not get the file to show by using Chrome either. I hope this works and you can help me with calculating bills that need a zero added to the end. An example is 7689(0). The reading is imported without the reading and I want to calculate the bill with the zero. Thanks.

[June7]

That 13.3mb download took forever! (I don't have greatest internet) Could have made copy and removed data (a few samples would be useful, 600,000+ is way more than needed).

Wasn't really helpful in understanding your request. The READINGS table has data ranging from null to 99867800

[Diamond]

I took your advice and I was able to import the readings, however, it did not calculate. I added the * 10 to Module 1, but I am wondering where else I need to change the information. Is it in the Fees Query? In the Process Rates form? In the Calculations Query? I have been working on this so long that my head is spinning. Sorry about the huge file. I wanted to make sure you had everything you needed. I can send a smaller version if you would like one. Thank you for your help.

[June7]

I have the file, no need for posting another unless you want to make it easier for someone else to download. I don't see * 10 in Module 1 - you did that after my post? I still don't know what data needs this adjustment nor why.

[Diamond]

I am so sorry, I am not thinking. Yes, I added the * 10 after I downloaded the file. I need this information as we have another database that does not produce very good reports and it is not easy to get them. My database stores the usage etc. and is easier to do a query and get what I am looking for. The reason I have to add the zero is because the readings are imported that way and I need to calculate it correctly. The data that needs it are the accounts with code number 3, 26, 21, 31, 36. There is code number 45 that needs two zeros.

[June7]

I see now the Code field in ACCOUNTS table but what field(s) need adjusting - READING and XREADING in READINGS table and only for codes 3, 26, 21, 31, 36, 45?

You said you already edited the VBA code (FindLoc function?) to handle this?

If you want to fix the data permanently in the table, that would be an UPDATE sql action.

[Diamond]

Yes, I put the code field in so I could calculate with the different rates that apply. The READING and XREADING in READINGS table need adjusting and then calculate the bill. I already had the FindLoc set up, but I don't think it handles the adjustment. How would the UPDATE sql action be set up?

[June7]

Try:

UPDATE Accounts SET Reading = Reading * IIf(Code=45,100,10), XReading = XReading * IIf(Code=45,100,10) WHERE Code IN (3, 21, 26, 31, 36, 45);

Be sure to test with a copy of the table.

Problem with this if you have to do this adjustment repeatedly for subsequent imported records, will need additional filter criteria so don't adjust the values already adjusted.