Solved setting a date automatically in VBA (1 Viewer)

[Sepp1945]

Good morning,
i need to set a date depending on weekly, monthly or yearly.
is this correct,
********************************************************
=IIf([Product_ID]="4",DateAdd("ww",1,[Order_Date]),IIf([Product_ID]="5",DateAdd("m",1,[Order_Date]),DateAdd("yyyy",1,[Order_Date])))
***********************************************
4 is weekly, 5 is monthly, all others yearly

and where should i put the code, in event "got Focus" or other

Many thanks
Sepp

 

[The_Doc_Man]

OK, two points, please take them separately.

1. Since you are asking about doing this within event code, that implies VBA context as opposed to a default value definition or something similar. The best way to do this is something like the following:

SELECT CASE [Product_ID]
    CASE 4
        X=DateAdd("ww",1,[Order_Date])
    CASE 5
        X=DateAdd("m",1,[Order_Date])
    CASE ELSE
        X=DateAdd("yyyy",1,[OrderDate])
END SELECT

You didn't show us the left-hand side of that equal-sign so I cannot tell where you wanted this to go, but X (whatever it represents) marks the spot.

2. Where you should put the code depends heavily on a lot of factors and your question was a bit weak on particulars. Two places come to mind.

Look at the Form's Current event IF AND ONLY IF [Order_Date] comes from an underlying record or a computation. This would fill in the blanks for "X" as noted above as soon as a value is loaded from the underlying (bound) record.

If you always or sometimes enter [Order_Date] manually, then a couple of other possible events come to mind, depending on exactly how the date is entered. Most likely, a control's .AfterUpdate event might be appropriate. Note that in this case, an .AfterUpdate event will NOT occur unless the control is bound to the underlying record. If the [Order_Date] is manually entered to an unbound control, the only event that might help in that limited case would be the .Lost_Focus event because no update-related events will occur.

 

[Sepp1945]

Thank you very much,

x = [Paid_Until] as per form below.

 

[arnelgp]

easier to create a Query with your Expression on it:

SELECT ID, Product_ID, Order_Date,
SWITCH( Product_ID=4, DATEADD("ww", 1, Order_Date),
Product_ID=5, DATEADD("m", 1,Order_Date), TRUE,
DATEADD("yyyy", 1, Order_Date) AS Paid_Until
FROM yourTableName;

and use the query as RecordSource of your form.

 

[Sepp1945]

Thank you very much,

x = [Paid_Until] as per form below.

View attachment 106380

Thank you very much, it works exactly as i wanted it to work

Many, many thanks

Sepp

 

[The_Doc_Man]

Glad I could help.