lookup and calculate

I have a separate table that has the state code, the month, and day
ex.
state timeframe(months) addl days
Alabama 4 0
Colorado 7 30
In my form if the state = Alabama then I need to Calulate a date field and
add the timeframe and additional days
Ex.
07/12/2004 (if state = Colorado, then add the timeframe (7) and the
additional days (30) to equal 03/14/2005

Can you tell me how to accomplish this?

NewDate = DateSerial(Year(OrigDate),
Month(OrigDate)+Dlookup("[Timeframe]","StateTable","[State] = '" &
StateValue & '"), Day(OrigDate) + DLookup("[AddlDays]","StateTable","[State]
= '" & StateValue & '"))

Whe
OrigDate is your "starting" Date
Table called StateTable contains the fields State, Timeframe and
AddlDays
StateValue is the name of the state you want to look up

If OrigDate = 7/12/2004 and StateValue = "Colorado" then
NewDate = DateSerial(Year(OrigDate), Month(OrigDate)+7, Day(OrigDate) +
30)
NewDate = 3/14/2005


HTH,
--
George Nicholson

Remove 'Junk' from return address.


"dchristo" wrote in message
...
I have a separate table that has the state code, the month, and day
ex.
state timeframe(months) addl days
Alabama 4 0
Colorado 7 30
In my form if the state = Alabama then I need to Calulate a date field and
add the timeframe and additional days
Ex.
07/12/2004 (if state = Colorado, then add the timeframe (7) and the
additional days (30) to equal 03/14/2005

Can you tell me how to accomplish this?

You just need the DateAdd function:
Add the Months:
CurrentDate = DateAdd("m",[timeframe],CurrentDate)
Add the Days:
CurrentDate = DateAdd("d",[addl days],CurrentDate)

"dchristo" wrote:

I have a separate table that has the state code, the month, and day
ex.
state timeframe(months) addl days
Alabama 4 0
Colorado 7 30
In my form if the state = Alabama then I need to Calulate a date field and
add the timeframe and additional days
Ex.
07/12/2004 (if state = Colorado, then add the timeframe (7) and the
additional days (30) to equal 03/14/2005

Can you tell me how to accomplish this?

the State is in the Part A table and needs to equal the state in the DD
Timeframe table

ex. if [Part A]![State] = [dda timeframe].[state], then add the timeframe
and the
additional days


"George Nicholson" wrote:

NewDate = DateSerial(Year(OrigDate),
Month(OrigDate)+Dlookup("[Timeframe]","StateTable","[State] = '" &
StateValue & '"), Day(OrigDate) + DLookup("[AddlDays]","StateTable","[State]
= '" & StateValue & '"))

Whe
OrigDate is your "starting" Date
Table called StateTable contains the fields State, Timeframe and
AddlDays
StateValue is the name of the state you want to look up

If OrigDate = 7/12/2004 and StateValue = "Colorado" then
NewDate = DateSerial(Year(OrigDate), Month(OrigDate)+7, Day(OrigDate) +
30)
NewDate = 3/14/2005


HTH,
--
George Nicholson

Remove 'Junk' from return address.


"dchristo" wrote in message
...
I have a separate table that has the state code, the month, and day
ex.
state timeframe(months) addl days
Alabama 4 0
Colorado 7 30
In my form if the state = Alabama then I need to Calulate a date field and
add the timeframe and additional days
Ex.
07/12/2004 (if state = Colorado, then add the timeframe (7) and the
additional days (30) to equal 03/14/2005

Can you tell me how to accomplish this?

But, how do I get the state , the state code in my form comes from Table
"Part A" and needs to equal the state in Table "DD Timeframe" and from there
get the timeframe and the addldays

ex. [Part A].[state] = [DD Timeframe].[state], then add the timeframe and
the
additional days

"Klatuu" wrote:

You just need the DateAdd function:
Add the Months:
CurrentDate = DateAdd("m",[timeframe],CurrentDate)
Add the Days:
CurrentDate = DateAdd("d",[addl days],CurrentDate)

"dchristo" wrote:

I have a separate table that has the state code, the month, and day
ex.
state timeframe(months) addl days
Alabama 4 0
Colorado 7 30
In my form if the state = Alabama then I need to Calulate a date field and
add the timeframe and additional days
Ex.
07/12/2004 (if state = Colorado, then add the timeframe (7) and the
additional days (30) to equal 03/14/2005

Can you tell me how to accomplish this?