If Statement

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

Guest

I need help writing code for an Excel IF function in Access VBA code.
In a field called REQ DEL DATE I need this result(s):
If [DATE APPROVED BY CUST] HAS A DATE, THEN [REQ DEL DATE] = [CUST PO DATE]
+ 70 DAYS(DATE).
OR
If [DATE APPROVED BY CUST] IS BLANK, THEN [REQ DEL DATE] = ADA.
I truly appreciate any assistance in getting this field set-up correctly.
 
Hi Sam,

To check blank (zero length string is [Field] = ""). To check for Null (
Isnull( [Field] ).
To add # of date use "DateAdd" function. Lookup "IsNull" and/or "DateAdd"
for more help.

To answer your question:
If Isnull(Me![DATE APPROVED BY CUST]) or Me![DATE APPROVED BY CUST] = "" Then
Msgbox "[DATE APPROVED BY CUST] is blank."
Else
Me![REQ DEL DATE] = DateAdd("d", 70, Me![CUST PO DATE])
End If

Or

If IsNull(Me![DATE APPROVED BY CUST]) or Me![DATE APPROVED BY CUST] = "" Then
[REQ DEL DATE] = ADA
End If

Help this will help.
 
Hi, Sam.

Generally speaking, there is no need to store a calculation in an Access
field, as it can be calculated on-the-fly faster than looking it up. It is,
moreover, undesirable, since it is not possible to simultaneously define a
calculation for a form control and bind it to a field in the form’s
underlying table. Both operations are defined in the control's Control
Source property. So to store a calculation requires VBA code to take the
calculation result and write it to a field.

Should a user be able to interact with the table directly rather than
through the form where this code is executed, he or she will be able to
change values that would render the stored calculation obsolete.

Also, a table field has a FieldType and can contain only one kind of data
whereas a textbox on a form is just a container, much like an Excel cell, and
so can either display a date or the string "ADA".

The normal approach, then, is to simply place a textbox control on the form,
and place the calculation in its Control Source property. Should you ever
need the calculation for a report or some other use, you may enter the same
expression in a query.

For this situation, the Control Source code is:

=IIf(Nz([DateApprovedBy])<>0,[CustPODate]+70,"ADA")

Note that the textbox will display nothing at all if CustPODate is Null.

If yours is one of the *rare* cases where storing this calculation is truly
desirable, you can add code to the AfterUpdate event procedures of each
control which could affect the calculation, here DateApprovedBy and
CustPODate:

If your textbox is named txtReqDelDate, the code is:

[ReqDelDate] = Me![txtReqDelDate], which writes the value of the form
control to the underlying field.

Hope that helps.
Sprinks
 
Thank you sooo much! Your formula worked and then after spending days on
this my boss decides that there are too many variables so we will just
manually enter our date!!! I truly appreciate the help from this board.
Sam

Sprinks said:
Hi, Sam.

Generally speaking, there is no need to store a calculation in an Access
field, as it can be calculated on-the-fly faster than looking it up. It is,
moreover, undesirable, since it is not possible to simultaneously define a
calculation for a form control and bind it to a field in the form’s
underlying table. Both operations are defined in the control's Control
Source property. So to store a calculation requires VBA code to take the
calculation result and write it to a field.

Should a user be able to interact with the table directly rather than
through the form where this code is executed, he or she will be able to
change values that would render the stored calculation obsolete.

Also, a table field has a FieldType and can contain only one kind of data
whereas a textbox on a form is just a container, much like an Excel cell, and
so can either display a date or the string "ADA".

The normal approach, then, is to simply place a textbox control on the form,
and place the calculation in its Control Source property. Should you ever
need the calculation for a report or some other use, you may enter the same
expression in a query.

For this situation, the Control Source code is:

=IIf(Nz([DateApprovedBy])<>0,[CustPODate]+70,"ADA")

Note that the textbox will display nothing at all if CustPODate is Null.

If yours is one of the *rare* cases where storing this calculation is truly
desirable, you can add code to the AfterUpdate event procedures of each
control which could affect the calculation, here DateApprovedBy and
CustPODate:

If your textbox is named txtReqDelDate, the code is:

[ReqDelDate] = Me![txtReqDelDate], which writes the value of the form
control to the underlying field.

Hope that helps.
Sprinks


Sam said:
I need help writing code for an Excel IF function in Access VBA code.
In a field called REQ DEL DATE I need this result(s):
If [DATE APPROVED BY CUST] HAS A DATE, THEN [REQ DEL DATE] = [CUST PO DATE]
+ 70 DAYS(DATE).
OR
If [DATE APPROVED BY CUST] IS BLANK, THEN [REQ DEL DATE] = ADA.
I truly appreciate any assistance in getting this field set-up correctly.
Click to expand...
Click to expand...
 
Back
Top