Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.
crystal0streaks

crystal0streaks

Technical User
May 11, 2006
39
US
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
 
Sort by date Sort by votes
In design view
Code: 
Due Date: IIF([TERMS]="NET 30 DAYS",
          Format([Current_Date] + 30,"mm/dd/yyyy"),
          "")
or in SQL view
Code: 
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]
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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]
 
Upvote 0 Downvote
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]
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
I recieved the same message for [CURRENT_DATE] + IIf([TERMS]="NET 30 DAYS", 30, 0) too.

-crys
 
Upvote 0 Downvote
Can you post your complete SQL?

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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]
 
Upvote 0 Downvote
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]
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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"?
Code: 
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]
 
Upvote 0 Downvote
Thank you!! Just one more question, if [TERMS] does not equal "NET 30 DAYS" can we enter a date into "DUE DATE" ourselves?

-crys
 
Upvote 0 Downvote
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]
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

JayRo71
  • Locked
  • Question
Importing only unique records from excel tables 1
Replies
10
Views
538
SkipVought
S
MrStohler
  • Locked
  • Question
Beginner Q: How to separate data from Excel into related tables and maintain relationship? 1
Replies
5
Views
385
SkipVought
S
ameedoo3000
  • Locked
  • Question
Send a message to an email
Replies
2
Views
2K
ameedoo3000
ameedoo3000
Lidias13
  • Locked
  • Question
Odata contains fields from multiple tables in single table with different field names - for same
Replies
1
Views
320
dhookom
dhookom
BugZap13
  • Locked
  • Question
How to extract Access table schema with decimal places on numeric fields?
Replies
2
Views
2K
dhookom
dhookom

Part and Inventory Search

Sponsor

Back
Top