lookup and calculate

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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 & '"))

Where:
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,
 
You just need the DateAdd function:
Add the Months:
CurrentDate = DateAdd("m",[timeframe],CurrentDate)
Add the Days:
CurrentDate = DateAdd("d",[addl days],CurrentDate)
 
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 said:
NewDate = DateSerial(Year(OrigDate),
Month(OrigDate)+Dlookup("[Timeframe]","StateTable","[State] = '" &
StateValue & '"), Day(OrigDate) + DLookup("[AddlDays]","StateTable","[State]
= '" & StateValue & '"))

Where:
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 said:
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?
Click to expand...
Click to expand...
 
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 said:
You just need the DateAdd function:
Add the Months:
CurrentDate = DateAdd("m",[timeframe],CurrentDate)
Add the Days:
CurrentDate = DateAdd("d",[addl days],CurrentDate)

dchristo said:
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?
Click to expand...
Click to expand...
 
Back
Top