IF statement concatenation

[gmazza via AccessMonster.com]

Hi there,
I have an IF statement that I need to use some concatenation with.

I grab the VisitNo from the form.

I have 10 Visit Flag's in my code named appropriately:
Visit1Flag
Visit2Flag etc..

I want to say:
If VisitNo = "Visit & VisitNo & Flag" Then
blah blah blah

I keep getting a type mismatch. Anyone see where I went wrong?
Thanks for your help!

 

[Douglas J. Steele]

It's not clear to me what Vista1Flag, Visit2Flag etc are. Are they controls
on your form? What's VisitNo supposed to be in that example? It's supposed
to be a numeric field and you're trying to determine what's in the control
with that number, you'd refer to it as Me.Controls("Visit" & VisitNo &
"Flag")

 

[KARL DEWEY]

If you are talking SQL then it would look similar to this --
IIF([VisitNo] = "Some Text String", "Then say THIS", "If not then say
the OTHER")
Or ---
IIF([VisitNo] = [Some other Field], "Then say THIS", "If not then say
the OTHER")

 

[gmazza via AccessMonster.com]

Thanks for the reply Doug.
Visit1Flag, etc are integers I have declared in a routine. VisitNo is also an
integer I declared, but made it equal to the control I grabbed off the form
by saying VisitNo = Forms!FormName!text50
Hope this helps, thanks!

It's not clear to me what Vista1Flag, Visit2Flag etc are. Are they controls
on your form? What's VisitNo supposed to be in that example? It's supposed
to be a numeric field and you're trying to determine what's in the control
with that number, you'd refer to it as Me.Controls("Visit" & VisitNo &
"Flag")

Hi there,
I have an IF statement that I need to use some concatenation with.

[quoted text clipped - 11 lines]

I keep getting a type mismatch. Anyone see where I went wrong?
Thanks for your help!

 

[Kipp Woodard]

If I grasp what you are trying to do, then I would recommend that you put
your integers into a collection, in the order that you want to access them.

So, the code might look like:

' Instance your collection
Dim cVisitFlags As New Collection

Dim iCollectionIndex As Integer
Dim iVisitNo As Integer

' Add integers to your collection
cVisitFlags.Add 123
cVisitFlags.Add 234
cVisitFlags.Add 345
cVisitFlags.Add 456
cVisitFlags.Add 567
cVisitFlags.Add 678
cVisitFlags.Add 789
cVisitFlags.Add 890
cVisitFlags.Add 901
cVisitFlags.Add 120

' Now loop for the count of the collection.
For iCollectionIndex = 1 To cVisitFlags.Count
' Do indexed read into your collection
If VisitNo = cVisitFlag(iCollectionIndex) Then
' blah, blah, blah
End If
Next

 

[gmazza via AccessMonster.com]

its close, but I don't need a collection. Here is exactly what I am trying to
do.
I have 5 Visit buttons on my form labelled Visit 1, Visit 2, etc. When the
user chooses a visit, the number gets stored in a text box. So they choose
Visit 2, the number 2 gets stored in a textbox.
Once they click any visit button, a Visit for opens and On Open I am doing
the following:
I am setting the declared integer VisitNo = to the text box, so 2 in this
case.
I have 5 VisitFlags, declared as integers, named VisitFlag1, VisitFlag2, etc.

I have a control form where the user decides which visits they want certain
info showing on the actual Visit form by clicking a check box for which visit
they want.
I open a recordset of the control form to see which visit the user chose.
In my recordset I do something like this:
If fld.Name = "Visi2" Then
If fld.Value = False Then
Else
VisitFlag2 = 2
End If
End If

So then, from the If statement above, I will be able to see if VisitFlag2 = 0,
meaning it wasn't chosen in the control form, or it equals 2, meaning it was
chosen.
So now I need to see if the VisitNo, which is at 2 because they clicked the
Visit 2 button, equals the Visit2Flag but I can't just use the word
Visit2Flag because there are 5 Flags, I just want to say something like:
If VisitNo = VisitFlag & VisitNo Then
blah blah

meaning:
If 2 = (the value of VisitFlag2)

Hope this helps!
Thanks for everyones help!

If I grasp what you are trying to do, then I would recommend that you put
your integers into a collection, in the order that you want to access them.

So, the code might look like:

' Instance your collection
Dim cVisitFlags As New Collection

Dim iCollectionIndex As Integer
Dim iVisitNo As Integer

' Add integers to your collection
cVisitFlags.Add 123
cVisitFlags.Add 234
cVisitFlags.Add 345
cVisitFlags.Add 456
cVisitFlags.Add 567
cVisitFlags.Add 678
cVisitFlags.Add 789
cVisitFlags.Add 890
cVisitFlags.Add 901
cVisitFlags.Add 120

' Now loop for the count of the collection.
For iCollectionIndex = 1 To cVisitFlags.Count
' Do indexed read into your collection
If VisitNo = cVisitFlag(iCollectionIndex) Then
' blah, blah, blah
End If
Next

Hi there,
I have an IF statement that I need to use some concatenation with.

[quoted text clipped - 11 lines]

I keep getting a type mismatch. Anyone see where I went wrong?
Thanks for your help!

 

[Douglas J. Steele]

Sounds like you need to be using Arrays, not just scalar variables.

Dim VisitFlags(1 To 5)

You'd then refer to them as VisitFlags(1), VisitFlags(2), VisitFlags(3),
VisitFlags(4) and VisitFlags(5)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

its close, but I don't need a collection. Here is exactly what I am trying
to
do.
I have 5 Visit buttons on my form labelled Visit 1, Visit 2, etc. When the
user chooses a visit, the number gets stored in a text box. So they choose
Visit 2, the number 2 gets stored in a textbox.
Once they click any visit button, a Visit for opens and On Open I am doing
the following:
I am setting the declared integer VisitNo = to the text box, so 2 in this
case.
I have 5 VisitFlags, declared as integers, named VisitFlag1, VisitFlag2,
etc.

I have a control form where the user decides which visits they want
certain
info showing on the actual Visit form by clicking a check box for which
visit
they want.
I open a recordset of the control form to see which visit the user chose.
In my recordset I do something like this:
If fld.Name = "Visi2" Then
If fld.Value = False Then
Else
VisitFlag2 = 2
End If
End If

So then, from the If statement above, I will be able to see if VisitFlag2
= 0,
meaning it wasn't chosen in the control form, or it equals 2, meaning it
was
chosen.
So now I need to see if the VisitNo, which is at 2 because they clicked
the
Visit 2 button, equals the Visit2Flag but I can't just use the word
Visit2Flag because there are 5 Flags, I just want to say something like:
If VisitNo = VisitFlag & VisitNo Then
blah blah

meaning:
If 2 = (the value of VisitFlag2)

Hope this helps!
Thanks for everyones help!

If I grasp what you are trying to do, then I would recommend that you put
your integers into a collection, in the order that you want to access
them.

So, the code might look like:

' Instance your collection
Dim cVisitFlags As New Collection

Dim iCollectionIndex As Integer
Dim iVisitNo As Integer

' Add integers to your collection
cVisitFlags.Add 123
cVisitFlags.Add 234
cVisitFlags.Add 345
cVisitFlags.Add 456
cVisitFlags.Add 567
cVisitFlags.Add 678
cVisitFlags.Add 789
cVisitFlags.Add 890
cVisitFlags.Add 901
cVisitFlags.Add 120

' Now loop for the count of the collection.
For iCollectionIndex = 1 To cVisitFlags.Count
' Do indexed read into your collection
If VisitNo = cVisitFlag(iCollectionIndex) Then
' blah, blah, blah
End If
Next

Hi there,
I have an IF statement that I need to use some concatenation with.

[quoted text clipped - 11 lines]

I keep getting a type mismatch. Anyone see where I went wrong?
Thanks for your help!

 

[gmazza via AccessMonster.com]

Thanks for the reply!
I don't know how to use arrays, is it difficult?
So you Dim the array, but how do I write my If statement without writing 5 If
statements for each Visit?

Sounds like you need to be using Arrays, not just scalar variables.

Dim VisitFlags(1 To 5)

You'd then refer to them as VisitFlags(1), VisitFlags(2), VisitFlags(3),
VisitFlags(4) and VisitFlags(5)

its close, but I don't need a collection. Here is exactly what I am trying
to

[quoted text clipped - 78 lines]

 

[Kipp Woodard]

If you hade your 5 visit flags in a collection, then you would be able to say:

If VisitNo = cVisitFlags (VisitNo) Then
blah blah

This depends on VisitNo always having a value that is a valid index for the
collection (1-5).

It would work similarly with an array, as Douglas suggested.

its close, but I don't need a collection. Here is exactly what I am trying to
do.
I have 5 Visit buttons on my form labelled Visit 1, Visit 2, etc. When the
user chooses a visit, the number gets stored in a text box. So they choose
Visit 2, the number 2 gets stored in a textbox.
Once they click any visit button, a Visit for opens and On Open I am doing
the following:
I am setting the declared integer VisitNo = to the text box, so 2 in this
case.
I have 5 VisitFlags, declared as integers, named VisitFlag1, VisitFlag2, etc.

I have a control form where the user decides which visits they want certain
info showing on the actual Visit form by clicking a check box for which visit
they want.
I open a recordset of the control form to see which visit the user chose.
In my recordset I do something like this:
If fld.Name = "Visi2" Then
If fld.Value = False Then
Else
VisitFlag2 = 2
End If
End If

So then, from the If statement above, I will be able to see if VisitFlag2 = 0,
meaning it wasn't chosen in the control form, or it equals 2, meaning it was
chosen.
So now I need to see if the VisitNo, which is at 2 because they clicked the
Visit 2 button, equals the Visit2Flag but I can't just use the word
Visit2Flag because there are 5 Flags, I just want to say something like:
If VisitNo = VisitFlag & VisitNo Then
blah blah

meaning:
If 2 = (the value of VisitFlag2)

Hope this helps!
Thanks for everyones help!

If I grasp what you are trying to do, then I would recommend that you put
your integers into a collection, in the order that you want to access them.

So, the code might look like:

' Instance your collection
Dim cVisitFlags As New Collection

Dim iCollectionIndex As Integer
Dim iVisitNo As Integer

' Add integers to your collection
cVisitFlags.Add 123
cVisitFlags.Add 234
cVisitFlags.Add 345
cVisitFlags.Add 456
cVisitFlags.Add 567
cVisitFlags.Add 678
cVisitFlags.Add 789
cVisitFlags.Add 890
cVisitFlags.Add 901
cVisitFlags.Add 120

' Now loop for the count of the collection.
For iCollectionIndex = 1 To cVisitFlags.Count
' Do indexed read into your collection
If VisitNo = cVisitFlag(iCollectionIndex) Then
' blah, blah, blah
End If
Next

Hi there,
I have an IF statement that I need to use some concatenation with.

[quoted text clipped - 11 lines]

I keep getting a type mismatch. Anyone see where I went wrong?
Thanks for your help!

 

[gmazza via AccessMonster.com]

I did as you suggested and when I debugged, my statement:
If VisitNo = cVisitFlags(VisitNo) Then
blah
The VisitNo is 1 and I know that cVisitFlags1 is also 1, yet it went passed
the If and onto the else.
When I put the mouse over VisitNo, it says 1 while debugging. When I put the
mouse over cVisitFlags, nothing shows up at all.

If you hade your 5 visit flags in a collection, then you would be able to say:

If VisitNo = cVisitFlags (VisitNo) Then
blah blah

This depends on VisitNo always having a value that is a valid index for the
collection (1-5).

It would work similarly with an array, as Douglas suggested.

its close, but I don't need a collection. Here is exactly what I am trying to
do.

[quoted text clipped - 70 lines]

 

[Kipp Woodard]

To verify the values in your collection, use the Watch window.

When you are stopped in the code... Select the entire name of the collection
and hit Shift-F9. This will give you a little dialog and you can click the
Add button to add a watch for your collection variable. Then, in the Watch
window, you can inspect the values in your collection.

You can probably accomplish the same with the Locals window, which is
similar to the Watch window, but it autmatcially has all local variables in
it.

I did as you suggested and when I debugged, my statement:
If VisitNo = cVisitFlags(VisitNo) Then
blah
The VisitNo is 1 and I know that cVisitFlags1 is also 1, yet it went passed
the If and onto the else.
When I put the mouse over VisitNo, it says 1 while debugging. When I put the
mouse over cVisitFlags, nothing shows up at all.

If you hade your 5 visit flags in a collection, then you would be able to say:

If VisitNo = cVisitFlags (VisitNo) Then
blah blah

This depends on VisitNo always having a value that is a valid index for the
collection (1-5).

It would work similarly with an array, as Douglas suggested.

its close, but I don't need a collection. Here is exactly what I am trying to
do.

[quoted text clipped - 70 lines]

I keep getting a type mismatch. Anyone see where I went wrong?
Thanks for your help!

 

[gmazza via AccessMonster.com]

Ya I tried the debugging watch but the value of cVisitFlags(VisitNo) hasn't
changed.

To verify the values in your collection, use the Watch window.

When you are stopped in the code... Select the entire name of the collection
and hit Shift-F9. This will give you a little dialog and you can click the
Add button to add a watch for your collection variable. Then, in the Watch
window, you can inspect the values in your collection.

You can probably accomplish the same with the Locals window, which is
similar to the Watch window, but it autmatcially has all local variables in
it.

I did as you suggested and when I debugged, my statement:
If VisitNo = cVisitFlags(VisitNo) Then

[quoted text clipped - 19 lines]