By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,397 Members | 1,425 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,397 IT Pros & Developers. It's quick & easy.

Nested If with Trim function in Code Builder

P: 5
Hello Access experts,

I am just new in this field and struggling with my code. I have a nested IF and it works in query but not sure how to apply it in form. I am thinking of making an event expression under "After Update". The idea is to populate the Description field by concatenating other fields. Below is the code I did in query, all help is appreciated.

Description: IIf([Name 3] Is Not Null And [Width] Is Not Null,(Trim([Material] & " " & [Type] & " with " & [Name 1] & ", " & [Name 2] & " and " & [Name 3] & ". Approximately " & [Width] & "mm x " & [Height] & "mm.")),IIf([Name 3] Is Not Null And [Width] Is Null,(Trim([Material] & " " & [Type] & " with " & [Name 1] & ", " & [Name 2] & " and " & [Name 3] & ".")),IIf([Name 2] Is Not Null And [Width] Is Not Null,(Trim([Material] & " " & [Type] & " with " & [Name 1] & " and " & [Name 2] & ". Approximately " & [Width] & "mm x " & [Height] & "mm.")),IIf([Name 2] Is Not Null And [Width] Is Null,(Trim([Material] & " " & [Type] & " with " & [Name 1] & " and " & [Name 2] & ".")),IIf([Name 1] Is Not Null And [Width] Is Not Null,(Trim([Material] & " " & [Type] & " with " & [Name 1] & ". Approximately " & [Width] & "mm x " & [Height] & "mm.")),IIf([Name 1] Is Not Null And [Width] Is Null,(Trim([Material] & " " & [Type] & " with " & [Name 1] & ".")),Trim([Material] & " " & [Type] & ".")))))))
Sep 5 '07 #1
Share this Question
Share on Google+
2 Replies


ADezii
Expert 5K+
P: 8,669
Hello Access experts,

I am just new in this field and struggling with my code. I have a nested IF and it works in query but not sure how to apply it in form. I am thinking of making an event expression under "After Update". The idea is to populate the Description field by concatenating other fields. Below is the code I did in query, all help is appreciated.

Description: IIf([Name 3] Is Not Null And [Width] Is Not Null,(Trim([Material] & " " & [Type] & " with " & [Name 1] & ", " & [Name 2] & " and " & [Name 3] & ". Approximately " & [Width] & "mm x " & [Height] & "mm.")),IIf([Name 3] Is Not Null And [Width] Is Null,(Trim([Material] & " " & [Type] & " with " & [Name 1] & ", " & [Name 2] & " and " & [Name 3] & ".")),IIf([Name 2] Is Not Null And [Width] Is Not Null,(Trim([Material] & " " & [Type] & " with " & [Name 1] & " and " & [Name 2] & ". Approximately " & [Width] & "mm x " & [Height] & "mm.")),IIf([Name 2] Is Not Null And [Width] Is Null,(Trim([Material] & " " & [Type] & " with " & [Name 1] & " and " & [Name 2] & ".")),IIf([Name 1] Is Not Null And [Width] Is Not Null,(Trim([Material] & " " & [Type] & " with " & [Name 1] & ". Approximately " & [Width] & "mm x " & [Height] & "mm.")),IIf([Name 1] Is Not Null And [Width] Is Null,(Trim([Material] & " " & [Type] & " with " & [Name 1] & ".")),Trim([Material] & " " & [Type] & ".")))))))
You can replace the IIf() Functions with Nested If..Then..Else..ElseIf..End If structures, something similar to the following:
Expand|Select|Wrap|Line Numbers
  1. If Not IsNull(Me![Name 3]) And Not IsNull(Me![Width]) Then
  2.   Me![Description] = Trim(Me![Material]) & " " & Me![Type] & " with " & Me![Name 1] & ", " & Me![Name 2] & _
  3.                      " and " & Me![Name 3] & ". Approximately" & Me![Width] & "mm x " & Me![Height] & "mm."
  4. Else
  5.   If Not IsNull(Me![Name 3] And IsNull(Me![Width]) Then
  6.     Me![Description] = Trim(Me![Material]) & " " & Me![Type] & " with " & Me![Name 1] & ", " & Me![Name 2] & _
  7.                        " and " & Me![Name 3] & "."
  8.   ElseIf Not IsNull(Me![Name 2]) And Not IsNull(Me![Width]) Then
  9.     Me![Description] = Trim(Me![Material]) & " " & Me![Type] & " with " & Me![Name 1] & " and " & Me![Name 2] & ". " & _
  10.                        "Approximately " Me![Width] & "mm x " & Me![Height] & "mm."
  11.   'Other Nested If/Else/ElseIf/End If structures where needed
  12.   End If
  13. End If
Sep 5 '07 #2

ADezii
Expert 5K+
P: 8,669
Hello Access experts,

I am just new in this field and struggling with my code. I have a nested IF and it works in query but not sure how to apply it in form. I am thinking of making an event expression under "After Update". The idea is to populate the Description field by concatenating other fields. Below is the code I did in query, all help is appreciated.

Description: IIf([Name 3] Is Not Null And [Width] Is Not Null,(Trim([Material] & " " & [Type] & " with " & [Name 1] & ", " & [Name 2] & " and " & [Name 3] & ". Approximately " & [Width] & "mm x " & [Height] & "mm.")),IIf([Name 3] Is Not Null And [Width] Is Null,(Trim([Material] & " " & [Type] & " with " & [Name 1] & ", " & [Name 2] & " and " & [Name 3] & ".")),IIf([Name 2] Is Not Null And [Width] Is Not Null,(Trim([Material] & " " & [Type] & " with " & [Name 1] & " and " & [Name 2] & ". Approximately " & [Width] & "mm x " & [Height] & "mm.")),IIf([Name 2] Is Not Null And [Width] Is Null,(Trim([Material] & " " & [Type] & " with " & [Name 1] & " and " & [Name 2] & ".")),IIf([Name 1] Is Not Null And [Width] Is Not Null,(Trim([Material] & " " & [Type] & " with " & [Name 1] & ". Approximately " & [Width] & "mm x " & [Height] & "mm.")),IIf([Name 1] Is Not Null And [Width] Is Null,(Trim([Material] & " " & [Type] & " with " & [Name 1] & ".")),Trim([Material] & " " & [Type] & ".")))))))
You can replace the IIf() Functions with Nested If..Then..Else..ElseIf..End If structures, something similar to the following:
Expand|Select|Wrap|Line Numbers
  1. If Not IsNull(Me![Name 3]) And Not IsNull(Me![Width]) Then
  2.   Me![Description] = Trim(Me![Material]) & " " & Me![Type] & " with " & Me![Name 1] & ", " & Me![Name 2] & _
  3.                      " and " & Me![Name 3] & ". Approximately" & Me![Width] & "mm x " & Me![Height] & "mm."
  4. Else
  5.   If Not IsNull(Me![Name 3] And IsNull(Me![Width]) Then
  6.     Me![Description] = Trim(Me![Material]) & " " & Me![Type] & " with " & Me![Name 1] & ", " & Me![Name 2] & _
  7.                        " and " & Me![Name 3] & "."
  8.   ElseIf Not IsNull(Me![Name 2]) And Not IsNull(Me![Width]) Then
  9.     Me![Description] = Trim(Me![Material]) & " " & Me![Type] & " with " & Me![Name 1] & " and " & Me![Name 2] & ". " & _
  10.                        "Approximately " Me![Width] & "mm x " & Me![Height] & "mm."
  11.   End If
  12.   'Other Nested If/Else/ElseIf/End If structures where needed
  13. End If
Sep 5 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.