creating an if-than statement that will take from 2 fields 1

[crystal0streaks]

My employer wants to have a Due Date automatically inputted if the information in the field [TERMS] is "NET 30 DAYS" he wants it to add 30 days to the date in the [CURRENT_DATE] field. We know we have to make as an Expression, we just don't quite know how.

Any help at all will be greatly appreciated! Thanks!

-crys

 

[Golom]

In design view

Due Date: IIF([TERMS]="NET 30 DAYS",
          Format([Current_Date] + 30,"mm/dd/yyyy"),
          "")

or in SQL view

Select IIF([TERMS]="NET 30 DAYS",
          Format([Current_Date] + 30,"mm/dd/yyyy"),
          "") As [Due Date], ...

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[PHV]

A starting point:
[CURRENT_DATE] + IIf([TERMS]="NET 30 DAYS", 30, 0)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Golom]

PHV

Won't that return Dec 30, 1899 if [Terms} is not "NET 30 DAYS"?

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[Golom]

Sorry ... of course it will return [Current_Date]

(I really must learn to read!)

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[PHV]

Won't that return Dec 30, 1899 if [Terms} is not "NET 30 DAYS"?
No, but [CURRENT_DATE]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[crystal0streaks]

Ok, I tried

IIF([TERMS]="NET 30 DAYS",Format([Current_Date] + 30,"mm/dd/yyyy"),"")

And the message I recieved from my "loveing" pc, was

The database engine does not recognize either the field 'TERMS' in a validation expression, or teh default value in the table 'DATES'

-crys

 

[crystal0streaks]

I recieved the same message for [CURRENT_DATE] + IIf([TERMS]="NET 30 DAYS", 30, 0) too.

-crys

 

[Golom]

Can you post your complete SQL?

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[crystal0streaks]

I have no idea, I'm still kinda new to access, and I have no idea how to get to SQL let alone how to edit it.

-crys

 

[Golom]

When in query design view, look at the top left of the screen and you will see a dropdown box. From the dropdown, select "SQL View". Highlight and copy the whole SQL statement and paste it here.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[crystal0streaks]

Does it matter that i'm trying to do this in a table?

-crys

 

[Golom]

Actually ... it matters a great deal.

Tables ... unlike queries ... do not support computed fields (i.e. there isn't any way that a table can automatically compute the value of a field based on values in other fields.)

You should be developing a query (based on the table) that has the computed field(s) that you want. End-Users (even your boss) should not be working directly with tables ... only with queries.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[crystal0streaks]

Ah, ok, I can have him work from queries, and here is the SQL

SELECT Customers.COMPANY, Customers.STREET, Customers.[CITY STATE ZIP], Customers.PHONE, Customers.FAX, Customers.CONTACT, DISTRIBUTOR_DUE_DATES.[INVOICE #], DISTRIBUTOR_DUE_DATES.[PO #], DISTRIBUTOR_DUE_DATES.TERMS, DISTRIBUTOR_DUE_DATES.[DUE DATE], DISTRIBUTOR_DUE_DATES.REP, DISTRIBUTOR_DUE_DATES.[SHIP DATE], DISTRIBUTOR_DUE_DATES.[SHIP VIA], DISTRIBUTOR_DUE_DATES.[ACCOUNT #], DISTRIBUTOR_DUE_DATES.[SPECIAL INSTRUCTIONS], DISTRIBUTOR_DUE_DATES.[UPS TRACKING#], DISTRIBUTOR_DUE_DATES.SHIPPING, DISTRIBUTOR_DUE_DATES.QUANTITY1, DISTRIBUTOR_DUE_DATES.[UNIT PRICE1], DISTRIBUTOR_DUE_DATES.DESCRIPTION1, DISTRIBUTOR_DUE_DATES.QUANTITY2, DISTRIBUTOR_DUE_DATES.DESCRIPTION2, DISTRIBUTOR_DUE_DATES.[UNIT PRICE2], DISTRIBUTOR_DUE_DATES.QUANTITY3, DISTRIBUTOR_DUE_DATES.DESCRIPTION3, DISTRIBUTOR_DUE_DATES.[UNIT PRICE3], DISTRIBUTOR_DUE_DATES.QUANTITY4, DISTRIBUTOR_DUE_DATES.DESCRIPTION4, DISTRIBUTOR_DUE_DATES.[UNIT PRICE4], DISTRIBUTOR_DUE_DATES.QUANTITY5, DISTRIBUTOR_DUE_DATES.DESCRIPTION5, DISTRIBUTOR_DUE_DATES.[UNIT PRICE5], DISTRIBUTOR_DUE_DATES.QUANTITY6, DISTRIBUTOR_DUE_DATES.DESCRIPTION6, DISTRIBUTOR_DUE_DATES.[UNIT PRICE6], DISTRIBUTOR_DUE_DATES.QUANTITY7, DISTRIBUTOR_DUE_DATES.DESCRIPTION7, DISTRIBUTOR_DUE_DATES.[UNIT PRICE7], DISTRIBUTOR_DUE_DATES.DESCRIPTION1B, DISTRIBUTOR_DUE_DATES.DESCRIPTION2B, DISTRIBUTOR_DUE_DATES.DESCRIPTION3B, DISTRIBUTOR_DUE_DATES.DESCRIPTION4B, DISTRIBUTOR_DUE_DATES.DESCRIPTION5B, DISTRIBUTOR_DUE_DATES.DESCRIPTION6B, DISTRIBUTOR_DUE_DATES.DESCRIPTION7B
FROM Customers INNER JOIN DISTRIBUTOR_DUE_DATES ON Customers.COMPANY = DISTRIBUTOR_DUE_DATES.COMPANY
WHERE (((Customers.[TYPE OF CUSTOMER])="DISTRIBUTOR"));


-crys

 

[Golom]

This should do it ... but I note that [Current_Date] isn't one of the fields that you are reporting. Is it in fact a field in one of the source tables or do you mean "The Current Date" as in "Today"?

SELECT C.COMPANY, C.STREET, C.[CITY STATE ZIP], C.PHONE, C.FAX, 
C.CONTACT, D.[INVOICE #], D.[PO #], D.TERMS, 
[COLOR=red]IIF(D.[TERMS]='NET 30 DAYS',Format([Current_Date] + 30,"mm/dd/yyyy"),'') 
    As [Due Date],[/color]
D.REP, D.[SHIP DATE], 
D.[SHIP VIA], D.[ACCOUNT #], D.[SPECIAL INSTRUCTIONS], 
D.[UPS TRACKING#], D.SHIPPING, D.QUANTITY1, D.[UNIT PRICE1], 
D.DESCRIPTION1, D.QUANTITY2, D.DESCRIPTION2, D.[UNIT PRICE2], 
D.QUANTITY3, D.DESCRIPTION3, D.[UNIT PRICE3], D.QUANTITY4, 
D.DESCRIPTION4, D.[UNIT PRICE4], D.QUANTITY5, D.DESCRIPTION5, 
D.[UNIT PRICE5], D.QUANTITY6, D.DESCRIPTION6, D.[UNIT PRICE6], 
D.QUANTITY7, D.DESCRIPTION7, D.[UNIT PRICE7], 
D.DESCRIPTION1B, D.DESCRIPTION2B, D.DESCRIPTION3B, 
D.DESCRIPTION4B, D.DESCRIPTION5B, D.DESCRIPTION6B, D.DESCRIPTION7B

FROM Customers C INNER JOIN DISTRIBUTOR_DUE_DATES D 
     ON C.COMPANY = D.COMPANY

WHERE C.[TYPE OF CUSTOMER]='DISTRIBUTOR'

Also in passing, your DISTRIBUTOR_DUE_DATES table appears to be severely un-normalized as evidenced by numerous repeating fields. This can cause unpleasentness when you attempt to write SQL to retrieve or modify data in the table. You may want to read through this article on relational database design.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[crystal0streaks]

Thank you!! Just one more question, if [TERMS] does not equal "NET 30 DAYS" can we enter a date into "DUE DATE" ourselves?

-crys

 

[Golom]

No. The implication of entering a due date is that there is a home for it in the underlying table ... and there isn't. It exists only in the query.

There are several strategies for handling things like this but we would need a bit more detail to understand exactly what to recommend.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[crystal0streaks]

What if we have multiple options, multiple If-than statements?

For example, [TERMS] has the option of "NET 30 DAYS"(+30 days) "NET 10 DAYS"(+10 days) "10 DAY EVAL"(+10 days) and "30 DAY EVAL"(+30 days)

-crys

 

[PHV]

Date() + IIf([TERMS] Like '*30*', 30, IIf([TERMS] Like '*10*', 10, 0))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[crystal0streaks]

Do i put that in place of the last code in the SQL?

-crys