Replies

Ok, I played around with the code all night and some this morning and can not figure out where my problem. The form is enter data form. I want it only display the fields date in, trk, bl, lbs.

Hans,

I followed the example and copied the code and pasted. I can get it to worked correctly when I remove the default values….it fills in all – that ok. I would like to have it only popluate certain fields. The default values look funny. It is enclosing in ” “. Is this normal? I rarely use default values. When I create the text box, nothing happens so I must be doing something wrong.

Thanks

Thanks for such a speedy response.

I will give it a try in the morning.

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??

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.

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.

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.

Hans,

There are no formulas. It is just a form for them to print and fill out. I am at my wits ends. I have tried every trick but I know you have something that will help.
attached is a sample worksheet that I need to put into word on 1 page.

Thanks!

Thanks…you do not know how many countless and unproductive hours I have spent on this spreadsheet. As an IT helpdesk manager, you think that I would not assume that the user keyed in the data correctly.

I will use the macro and actually have it run when the spreadsheet is opened.

One more question, I have another spreadsheet that is shared between users. When one user is entering in numbers, Excel will automatically add a row above the total. If you add another number, another row is inserted. Under Tools/Shared workbook, the option is checked to allow changes…if I remove the check, it does not insert the additional rows. Is this the reason it is adding rows? I tried to remove some data so I could attach, but it will not allow me to delete the other spreadsheets in the workbook and if I copy the one, it does not do it.

You have got to be kidding me

I appreciate the continued search for my answer. Attached is the spreadsheet. I can sort either by last or first but not by both. Pay attention to the list of Adams. If you use the filter, it will only allow you to sort one column not a multiple column sort.

It worked great. Explain to me why you used the ADD option.

They are using outlook so can you provide me with some details on how to do this? This is completely new to me.

Thanks for the information. I have everything working and it is so great.

Thanks!

It is Friday and my mind is gone. I renamed the macro auto_open and it works great!