Augment an In-Service Date in Access 2000. 1

[jamey84]

I have a database that I am using for forecasting major equpiment for projects. I am trying to augment my in-service date. I want to predict when I need to purchase the equipment, by adding on a 30 day buffer, as well as a specific lead time(in days) that differs for each piece of equipment. The equipment is shown in a combo box, and that is how I choose what equipment I want to create a report for. I've been trying to use the DateAdd function, which easily adds the 30 days. Now I'm getting stuck on trying to add the Lead Time values. The values are stored in a table in my database, but it seems not to like it when I am referencing the table for the number to add. It is only grabbing the first value in the table.

There is probably something that I am just overlooking, but if anyone can lend some expertise, I'd appreciate it. Thanks much!


jamey84

 

[PHV]

it seems not to like it when I am referencing the table for the number
Can you please be more specific ?
id est, what is your tables structure, how you tried to reference the Lead Time values ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[jamey84]

Sure,
Currently, I use a combo box to display the different types of equipment. I then have two text boxes, where you would enter the dates that I want to grab the quantity of equipment from(ie. in Q2, 2005, I will need to buy 35 boxes). For my calculations right now, I have another text box, where I am adding 30 days using the AddDate function. The name of the text box is txtBeginningDate+30, and the function I'm using is =DateAdd("d",-30,[Forms]![frm_Report_Criteria]![txtBeginningDate]).

I want to add the specific lead time value for each piece of equipment. The lead time values are stored in the same table as the type of equipment, called tbl_Item.

Right now, I have another text box where I am trying to add the lead times in. I've tried using this function into the box called txtBeginningDate+30+Lead, =DateAdd("d",[fld_Technical_Spec_Lead_Time],[Forms]![frm_Report_Criteria]![txtBeginningDate+30])

When I do this, the number that is being grabbed from my table is the first record in the table, in the fld_Technical_Spec_Lead_Time.

If there is anything else that I need to clarify, just let me know. I appreciate all the help. Thanks!

jamey84

 

[OnTheFly]

OK, let me see if I am understanding this correctly.

You have a combo box with the equipment type.
The user chooses an equipment type and then in the text box they enter the date you want the equipment to arrive by.

In the other textbox you want to calculate the date you need to order the equipment so that it arrives by the date entered in textbox 1 by the user.

If this is correct I would do the following:

In the source of the Combobox include the lead time field for each piece of equipment. You don't have to show it but you should include it in the query used to create the combobox list.

Then in the second text box you could do something like this:

=[txtBeginningDate]-(30+[ComboBoxName].column(2))

If you put that into the control source of the second text box it should work, provided the 3rd field of your combo box query is the Lead Time number.


Hope this helps.

OnTheFly

 

[jamey84]

OnTheFly,

Thanks for the help. Something so simple, I was just looking at it too closely. Thanks so much!

jamey84