Weird date calculation help

[matey56]

Hi all,
I have a table lookup (tlkp_Category_Description) and in that table there are two fields called Category and Days. Each category has a corresponding amount of days. In my form I have the Category field based on that table lookup. Also in the form I have two date fields (Actual_Date and Expected_Date).

What I need to do is create a calculation: when the user selects a category from the drop down in the form, the Expected_Date field will add the corresponding amount of days from the table lookup to the Actual_Date field and give me a date.

Does that make sense? How could I do that?

[Welshgasman]

Use DateAdd() ?

[matey56]

Use DateAdd() ?

I got that much. I'm just not sure of the syntax of the days to add if it's not the record source for the form. It's stored in a table lookup

[Welshgasman]

It does not matter where they come from as long as you have what you need, which would be the interval type, a start date and a number to add/subtract.
Google the syntax.

If you have to look it up in a table then look at DlookUp().

[davegri]

It appears that you could get the number of days from the category combobox.
The combobox rowsource should be "Select Category, Days from tlkp_Category_Description Order By Category;"
Something like
MyNewDate=Me.cboCategory.column(1)

[matey56]

It appears that you could get the number of days from the category combobox.
The combobox rowsource should be "Select Category, Days from tlkp_Category_Description Order By Category;"
Something like
MyDays=Me.cboCategory.column(1)

Ok, I changed my form's query and got it working. I used =DateAdd("d",[MOSP_Days],[Actual_Submission_Date])

However, now if the form has no data the field says #Type! How do I get the field to be blank until MOSP_Days and Actual_Submission_Date are both populated?

[davegri]

Ok, I changed my form's query and got it working. I used =DateAdd("d",[MOSP_Days],[Actual_Submission_Date])

However, now if the form has no data the field says #Type! How do I get the field to be blank until MOSP_Days and Actual_Submission_Date are both populated?

I think you'll need VBA. In the after_update event of the combobox, you'll need to check if the proper date field is within the form textbox. If not, messagebox the user that the date needs to be filled in first.
You'll need to remove the calculation from the form's textbox and move it to the after_update combobox event to populate the textbox.
Sorry can't be more concise as I don't have information on your form controlnames.

[señor_sriracha]

Maybe this?

=Nz(DateAdd("d",[MOSP_Days],[Actual_Submission_Date]),NULL)

[Welshgasman]

Probably need the NZ() around the date fields?

[señor_sriracha]

Probably need the NZ() around the date fields?

You're right, my bad.