Trouble with multiple if...then...else statements 1

[topdesk123]

Hi everyone,

I have created a procedure to take the values of check boxes and create a sentence that will eventually be used in a mail merge. In order for the "sentence" to read correctly (where does the last comma go, where does "and" go, etc) I have used many if...then statements, 50 to be exact. Unfortunately, the statement evaluates to "true" incorrectly in many cases.

Here's an example:

If Me![Walking Surfaces] = True Then
Me![sentence2] = "walking surfaces of your deck"
end if

If Me![Walking Surfaces] = True And Me![support posts] = True Then
Me![sentence2] = "walking surfaces of your deck and support posts"
end if

If Me![Steps] = True And Me![Walking Surfaces] = True Then
Me![sentence2] = "walking surfaces of your deck and steps"
end if

If Me![Walking Surfaces] = True And Me!Steps = True And Me![support posts] = True Then
Me![sentence2] = "walking surfaces of your deck, steps and support posts"
end if

If Me![Walking Surfaces] = True And Me![support posts] = True And Me![Railings] = True Then
Me![sentence2] = "walking surfaces of your deck, railings and support posts"
end if

If Me![Walking Surfaces] = True And Me![Steps] = True And Me![Fascia] = True Then
Me![sentence2] = "walking surfaces of your deck, fascia and steps"
end if

I have tried if...then....else....which seems to be even worse.

Does anyone have a better way to evaluate all of the different options? BTW: I trigger the event with a button currently - but hope to have it work after update.

Thanks so much in advance for your help!

Gina

 

[CajunCenturion]

First of all, what types of controls are [Walking Surfaces], [support posts], [Steps], [Railings], and [Fascia]? I hope I got them all.

Also, using the If-Then-Else statement in the method you have will not necessarily mean that the correct one will drive the sentence text. In this structure, the last If-Then-Else in which the conditional is true will drive which text is selected.

Good Luck
--------------
To get the most from your Tek-Tips experience, please read FAQ181-2886
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[lespaul]

So, you have these choices on your form:

Walking Surfaces
Support Posts
Steps
Railings
Fascia

It appears that Walking surfaces will always be chosen or are there situations where walking surfaces is not chosen?

If Me![Walking Surfaces] then
  if Me![Sentence2] = "" then
    Me![Sentence2] = "walking surfaces of your deck"
  end if
else if Me![Support Posts] then
  if Me![Sentence2] = "" then
    Me![Sentence2] = "support posts of your deck"
  else
    Me![Sentence2] = Me![Sentence2] + ", support posts"
  end if
else if Me![Steps] then
  if Me![Sentence2] = "" then
     Me![Sentence2] = "steps of your deck" 
  else
     Me![Sentence2] = Me![Sentence2] + ", steps"
  end if
else if Me![Railings] then
  if Me![Sentence2] = "" then
     Me![Sentence2] = "railings of your deck" 
  else
     Me![Sentence2] = Me![Sentence2] + ", railings"
  end if
else if Me![Fascia] then
  if Me![Sentence2] = "" then
     Me![Sentence2] = "fascia of your deck" 
  else
     Me![Sentence2] = Me![Sentence2] + ", fascia"
  end if
end if
'add period to end of sentence.
If Me!Sentence2 <> "" then Me![Sentence2] = Me![Sentence2] + "."

Leslie

 

[CajunCenturion]

Rather than try to individually try to account for all of the combinations between all of the various controls, you might want to consider something like the following, where each control is looked at only once.

Private Sub BuildText()

   Dim lCol_IncludeAreas   As New Collection
   Dim lStr_FullSent       As String
   Dim lInt_Idx            As Integer
   
   If (Me![Walking Surfaces] = True) Then
      lCol_IncludeAreas.Add "walking surfaces of your deck"
   End If
   
   If (Me![support posts] = True) Then
      lCol_IncludeAreas.Add "support posts"
   End If
   
   If (Me![Steps] = True) Then
      lCol_IncludeAreas.Add "steps"
   End If
   
  If (Me![Railings] = True) Then
      lCol_IncludeAreas.Add "railings"
   End If
   
   If (Me![Fascia] = True) Then
      lCol_IncludeAreas.Add "fascia"
   End If
   
   Select Case lCol_IncludeAreas.Count
      Case 0
         lStr_FullSent = vbNullString
      Case 1
         lStr_FullSent = lCol_IncludeAreas.Item(1)
      Case 2
         lStr_FullSent = lCol_IncludeAreas.Item(1) & " and " & lCol_IncludeAreas.Item(2)
      Case Else
         lStr_FullSent = vbNullString
         For lInt_Idx = 1 To lCol_IncludeAreas.Count - 1
            lStr_FullSent = lStr_FullSent & lCol_IncludeAreas.Item(lInt_Idx) & ", "
         Next lInt_Idx
         lStr_FullSent = lStr_FullSent & " and " & lCol_IncludeAreas.Item(lCol_IncludeAreas.Count)
   End Select

   Me![sentence2] = lStr_FullSent

End Sub

Good Luck
--------------
To get the most from your Tek-Tips experience, please read FAQ181-2886
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[Carina1628]

I had a lot of problems with if...then too. I needed to convert a number to text. I finally got it to work like this:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
'Converts the number from ref into text
If [Forms]![frmDeedEntry]![ref] = "1" Then
Textbox99 = "Highland"
ElseIf [Forms]![frmDeedEntry]![ref] = "2" Then
Textbox99 = "Union"
ElseIf [Forms]![frmDeedEntry]![ref] = "3" Then
Textbox99 = "Graham"
ElseIf [Forms]![frmDeedEntry]![ref] = "4" Then
Textbox99 = "St. Mary"
Else
'do nothing
End If
End Sub

Not sure if this will setup will work for you, but it seems to work better if all of your "ifs" are in one statement.

Carina

 

[BobJacksonNCI]

I'd put each complete sentence in a table with an identifier and code the logic to choose the prewritten sentence. That way, in a year when someone adds "guiding lights" to the mix, I'd be able to add it without re-comprehending all of the logic.



HTH,
Bob

 

[topdesk123]

Hey! That looks like it will work beautifully! Thank you so much (to all of you) for your quick replies!

Gina

 

[CajunCenturion]

Just out of curiosity, since several options have been presented, which one looks like it will work beautifully?

Good Luck
--------------
To get the most from your Tek-Tips experience, please read FAQ181-2886
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[topdesk123]

#3 - from you (CajunCenturion) - it is perfect! Thanks again!

Gina

 

[CajunCenturion]

Glad that you got it working.

Good Luck
--------------
To get the most from your Tek-Tips experience, please read FAQ181-2886
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein