Update Query Question

[Carl]

I have a table with three fields:
CustNo SubDate DelayDays
There are currently 895 records, and growing
CustNo is a text field, SubDate is a date field formatted for short date and
DelayDays is a drop-down with three values 5-day, 10-day, and 30-day created
from the lookup wizard in the table.
We need to now add the 5, 10, and 30 to the SubDate in a control in a form
to calculate Final_Date.
I need to remove -day text from the existing records and convert the field
to a number field so I can add it to the SubDate in the form. I cant figure
out how to remove it. I will then remove the -day text from the drop-down
(is that as easy as it sounds?)
Thanks for any advice.
Carl

 

[John Spencer MVP]

You should be able to use the following expression

DateAdd("d",Val(DelayDays),SubDate)

That should work as long as DelayDays has a value (null will cause an error)
and the first part of the value can be interpreted as a number.

Your other option would be to store the actual number in DelayDays field.

If delaydays can ever be blank, then I would change the expression in one of
two ways. Test for a Null value and return a blank
IIF(IsNull(DelayDays),Null, DateAdd("d",Val(DelayDays),SubDate))

Or alternatively force the value to Zero using the NZ function and return the
value that is in subdate by adding zero days to it. OR if you want change the
zero to 36500 and add roughly 10 years to subdate so it is out of the expected
range of dates.
DateAdd("d",Val(Nz(DelayDays,0)),SubDate)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Carl]

Worked perfectly, thank you very much.

You should be able to use the following expression

DateAdd("d",Val(DelayDays),SubDate)

That should work as long as DelayDays has a value (null will cause an error)
and the first part of the value can be interpreted as a number.

Your other option would be to store the actual number in DelayDays field.

If delaydays can ever be blank, then I would change the expression in one of
two ways. Test for a Null value and return a blank
IIF(IsNull(DelayDays),Null, DateAdd("d",Val(DelayDays),SubDate))

Or alternatively force the value to Zero using the NZ function and return the
value that is in subdate by adding zero days to it. OR if you want change the
zero to 36500 and add roughly 10 years to subdate so it is out of the expected
range of dates.
DateAdd("d",Val(Nz(DelayDays,0)),SubDate)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County