Updating a date field (2003)

Topic

New Reply

I have a database that runs a query every month for storage charges. If the material has not been shipped (there is a shipped date) then I want to charge for storage. There is a date in field that I use as a reference to calculate from. The item comes it, we log it. The first 30 days are free. So I create a storage date of datein +31. He is working off of 30 days only…. he also charges for an entire month even if it is only stored for 3 days. My problem is that I run the query based off of datein – I have to check for unloading charges as well. So, I look at datein and add 31. When run the query, I check to see if storage date is between the query range, if it is charge for storage. If not, bypass that charge. No charge this month, but i need to increase the storage date to another 31 days. How to I increment the storage date again every 31 days. So if I run again, the storage falls within this month, charge for storage, increase again. Run for next month, check again…etc until they ship it.

I hope this makes some sense. I am creating an invoice.

Reply | Quote

Replies

As far as I can tell, there is no need to keep on increasing the date. If the current date is more than 30 days after Date In and if the shipped date is null, you need to charge for storage this month.

Reply | Quote

Thanks…

I don’t know if you ever experience a brain relapse but I must of had one here. I knew I was making it more difficult than what it should be.

Reply | Quote

Hans,

I was thinking about this last night and I don’t think it will work in my case. I have a parameter query which looks at Date in. When they run the report, it will only extract records within that date range. That is why I generated the storage date field. However as I am sitting here typing this, it is not going to work. Let me see if I an explain and see if you can suggest something.

There are 3 times the client is charged. When they bring the load to the yard for unloading. This load is entered into the computer with the current date as date in.
The second charge is when they ship it out, there is a loading fee – I have a shipped date that is entered here.
The third charge is the storage fee. The first month is fee which I was taking the date in and adding 31 days (he is using a 30 day month) to create a storage date.

The load can stay on the yard for months before shipping. The initial unloading fee will be charged the first time and then I have to check each month for storage or shipping. I currently run the query to look at date in and extract only those records to build the invoice. This company is new but he does have data since Sept I need to bill for.

Got any ideas??? I know I am making this harder than what it is.

Reply | Quote

Let’s look at some examples – I have attached a sample database with some dummy records in a table, a query and a form. Open the form, enter a start date and end date (defaults are for October, 2007) and click the button. The last column in the query indicates whether a storage charge is due for the specified period. Does that correspond to what you expect?

Reply | Quote

When you run the query you are not filtering records between your txtstart and txtend, you are only using them to create the field for calculation. I have my fields on my form startdate and enddate to filter records based on this date range.

I guess my problem is how to I continue to charge for storage if my date in is not within the range the user enters. I think my logic is off. For some reason, I can’t seem to grasp this. Because no matter what I do to a storage date, If I use date in as my filtering field, it will not pull old records anyway.

I appreciate you patience.. my a few days off will help clear my mind.

Reply | Quote

I deliberately didn’t filter the records, so that you can see whether the conditions are calculated correctly. It’s easy to modify the query so that it only returns the records for which the combined condition is True.

I’m not sure what you want to accomplish. Do you want to calculate charges for each period of time (for example calendar month) or do you want to calculate charges at the end, when the shipping date (“DateOut”) has been entered?

Reply | Quote

TO be honest…I do not know.

I do know…the user wants to create an invoice for the previous month…so he would enter 10/01/07 – 10/31/07. Any load received for that month will be charged an unloading fee (the date they come on the yard is their date in – so any date in that fall within that range will be charged this fee). Once the fee is charged, I have a field that I mark – call unload.
The next thing I look for is any shipped out dates entered for the records filtered from the date range (again records are filtered by date in that is within this date range). If there is a date there, I charge a loading fee. Most loads are shipped within 30 days.

Then there is the charge for storage. Ideally, it will look at any records and if there is no shipped date and the date in >31, charge a fee. But since I have it filtering for this date range based off of date in – how can I do this?

Would I need to create 3 sub reports? one for each type of charge??

Reply | Quote

In the attached, modified version of the demo database, the query qryCharge calculates whether each of the three charges applies in the specified period. The query qryCharge2 filters those records for which at least one of the charges applies.

Reply | Quote

Hans,

I need you help again. The logic from the previous database is not working for me when it comes to the storage charge.
I have bundles entered in on 12/29/07, the bundles have been on the yard since so there is no date out entry. I am running my report for Feb 2/1 – 2/29 and I get nothing in my storage field. But I should.

Please help!
Thanks,
Deborah

Reply | Quote

Does this expression for StorageCharge do what you want in the sample database from post 677,805?

StorageCharge: [Forms]![frmDemo]![txtEnd]>=[ChargeStart] And ([Forms]![frmDemo]![txtStart]<=[DateOut] Or [DateOut] Is Null)

Reply | Quote

It did for January because the storage date fell within the range. I added date in for 12/28/07 and 12/31/07 and it did not work.

Reply | Quote

Could you post a stripped down copy of your database? See post 401925 for instructions.

Reply | Quote