Complex DateAdd Function

[SpindleMania]

Hi All,

i have a slight problem i have a access table which has the following fields:

Name
Photocard ID
Purchase Date
Photo
Cashsaver Zone
Valid From
Period of Validation
Expiry

the period of validation field has a lookup to another table with the folwing values: 28 Days, 3 Months, 6 Months and Annual.

the valid from date is entered manually.

basically (maybe not uite the right word!) i need the the expiry field to automatically insert the correct expiry date by looking in the valid from date and adding the correct amount of time onto it according to what is selected in the period of validation field.

example:
Valid from date is 01/01/2006
Period of validation is 6 Months
Expiry date should be 01/06/2006 (01/01/2006 + 6 Months)

i came up with the following formula although i know its not really correct and some of the words arent functions at all just to show what is should be based on:

WHERE Period of validation <= DataValue ("28 Days") then DateAdd("d",28, Period of validation)
WHERE Period of validation <= DataValue ("3 Months") then DateAdd("m",3, Period of validation)
WHERE Period of validation <= DataValue ("6 Months") then DateAdd("m",6, Period of validation)
WHERE Period of validation <= DataValue ("Annual") then DateAdd("m",12, Period of validation)

also i am unsure into what box to type this into?

i would greatly apperciate some help

Spindlemania

 

[The_Doc_Man]

If the expiration targets are fixed names, have a second column iin your lookup showing the number of days for each. Then just do a DateAdd for a number of days no matter what.

 

[SpindleMania]

Thanks for your quick reply but i am new to this and unsure what you mean?

 

[The_Doc_Man]

You are right to use DateAdd. However, to keep your life simple, look into ways to encode the expiration as fixed units. Then you don't have to figure out whether you wanted days or months or years as your unit to add.

One way that comes to mind is to use a wizard to store the number of days when you select a given drop-down value. A combo-box wizard or list-box wizard often can help with this.

 

[SpindleMania]

Thanks i will look into this

 

[raskew]

Hi -

The following uses the Choose() and Instr() functions to allow the user to select the period to be added by specifying a one-character identifier.

Public Function xdate(ptype As String, pdate As Date) As Date
'*******************************************
'purpose:   Return the result of a DateAdd()
'           process providing the user to
'           select from four options (28
'           days, 3 months, 6 months, 1 year)
'coded by:  raskew
'inputs:    1) ? xdate("A", #7/31/06#)
'           2) ? xdate("B", #7/31/06#)
'           3) ? xdate("C", #7/31/06#)
'           4) ? xdate("D", #7/31/06#)
'outputs    1) 8/28/06
'           2) 10/31/06
'           3) 1/31/07
'           4) 7/31/07
'*******************************************

Dim strtype As String

   strtype = "ABCD"
   xdate = DateAdd(Choose(InStr(strtype, ptype), "d", "m", "m", "yyyy"), _
                   Choose(InStr(strtype, ptype), 28, 3, 6, 1), pdate)

End Function

Bob

 

[SpindleMania]

Hi Bob,

thanks, i'm grateful for your code but i am unsure into which box to insert the code, sorry to be a pain