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]

Yeah ... you can do that but I suggest that you create another table.
[tt]
tblTerms
Terms DaysToAdd
NET 30 Days 30
NET 10 Days 10
10 DAY EVAL 10
30 DAY EVAL 30
RIGHT NOW 0
[/tt]
Then

SELECT C.COMPANY, C.STREET, C.[CITY STATE ZIP], C.PHONE, C.FAX, 
C.CONTACT, D.[INVOICE #], D.[PO #], D.TERMS, 
Format([Current_Date] + NZ(T.DaysToAdd),"mm/dd/yyyy"),'')  As [Due Date],
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)
      LEFT JOIN tblTerms T
      ON T.Terms = D.Terms

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

Then you can have as many different TERMS as you want and you don't need to change your query every time some different TERM is created. You could also use that as a validation table to ensure that users were not entering a TERM that didn't have any meaning.

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

 

[crystal0streaks]

What did you save the table as? tblTerms?

-crys

 

[Golom]

That's how the code I posted references it (i.e. tblTerms) but you can call it whatever makes you all warm and fuzzy ... just be sure to refer to it by that name in the SQL.

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

 

[crystal0streaks]

Tried the code after adding in all the info and kept the table name you gave it, and I recieved the message that there is an "Extra ) in query expression ')'. So i deleted the last ) and it only works for the "NET 30 DAYS"/it won't allow me to click on another option...

-crys

 

[Golom]

You're running this in Query Design, right?

If so, I don't understand what you mean by "... click on another option ...".

What are you trying to "click"?

Sorry ... left some garbage hanging around in that last post

SELECT C.COMPANY, C.STREET, C.[CITY STATE ZIP], C.PHONE, C.FAX, 
C.CONTACT, D.[INVOICE #], D.[PO #], D.TERMS, 
Format([Current_Date] + NZ(T.DaysToAdd),"mm/dd/yyyy") As [Due Date],
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)
      LEFT JOIN tblTerms T
      ON T.Terms = D.Terms

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

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

 

[crystal0streaks]

IT WORKS!!!! *yay*!


Thank you *so* much!!

-crys

 

[Golom]

You're welcome.
Hope your first Tek-Tips experience was a good one.

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

 

[crystal0streaks]

real quick, sorry to do this, but i need the same thing done to two more codes...if you don't mind....


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


and

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


thank you *so* much!

-crys

 

[Golom]

Only because I happen to have a few minutes but please note ... Tek-Tips is not a code-writing (or formatting) service. The idea is that you get some help the first time you have a problem so that you can do it on your own the next time you see that problem.

Have you in fact learned anything?

SELECT C.COMPANY, C.STREET, C.[CITY STATE ZIP], C.PHONE, 
       C.FAX, C.CONTACT, D.[INVOICE #], D.[PO #], D.TERMS,
       Format([Current_Date] + NZ(T.DaysToAdd),"mm/dd/yyyy") As [Due Date],
       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 DEALER_DUE_DATES D 
      ON C.COMPANY = D.COMPANY)
     LEFT JOIN tblTerms T ON D.TERMS = T.TERMS

WHERE C.[TYPE OF CUSTOMER]="DEALER"

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

FROM (SYSTEMSINT_DUE_DATES S INNER JOIN Customers C 
      ON S.COMPANY = C.COMPANY)
     LEFT JOIN tblTerms T ON S.TERMS = T.TERMS

WHERE C.[TYPE OF CUSTOMER]="SYSTEMSINT"

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