Operator / Syntax failure in coding

[karmacable]

I'm writing code where I look through more than two combo boxes (after a user has made their selections), click a command button to check to see if they match or not, and print the results in a caption.



When I work with simply two combo boxes;

 
Dim Rig1st As String
Dim Lenses1st As String
 
If Me.cmbARig & Me.cmbBRig <> 0 Then
  If Me.cmbARig = Me.cmb1stCamBRig Then
    Rig1st = "2 x " & Me.cmbARig.Column(1)
    Me.lblRigs.Caption = Rig1st
  Else
    Rig1st = "1 x " & Me.cmbARig.Column(1) & vbNewLine & "1 x " & Me.cmb1stCamBRig.Column(1)
    Me.lbl1stRigs.Caption = Rig1st
  End If
Else
  Me.lbl1stRigs.Caption = ""
End If

The above works great. As you can see in the second 'If' line, I'm simply checking to see if the combo boxes ID's (their number fields) match.

However, if I try to apply the same logic / syntax to check through four combo boxes like this (and for this example I simply stopped at what happens if you choose the same in all four boxes);

If Me.cmbALens1 & Me.cmbLens2 & Me.cmbBLens1 & Me.cmbBLens2 <> 0 Then
  If Me.cmbALens1 = Me.cmbALens2 = Me.cmbBLens1 = Me.cmbBLens2 Then
    Lenses1st = "4 x " & Me.cmbALens.Column(1)
    Me.lblLenses.Caption = Lenses1st
  Else
    Me.lblLenses.Caption = "Formula Not Working"
  End If
Else
  Me.lblLenses.Caption = "Formula Still Not Working"
End If

Then my caption box shows the "Formula Not Working". What am I doing wrong in my logic of trying to see if all boxes have the same value? And also, what would the syntax for exclusion be (e.g. cbo1 = cbo2 = cbo3 <> cbo4), because I will be coding messages for that as well?

Thanks

[pbaldy]

Your first line probably won't work correctly, and would need to be:

If Me.cmbALens1 <> 0 AND Me.cmbLens2 <> 0 AND Me.cmbBLens1 <> 0 AND Me.cmbBLens2 <> 0 Then

Your existing test is likely concatenating the 4 values together and testing that against 0. In a similar manner, the second would look like


If Me.cmbALens1 = Me.cmbALens2 AND Me.cmbBLens1 = Me.cmbBLens2 Then

In other words, you have to test two values against each other.

[karmacable]

Your logic makes sense, I'll give it a try now and report back soon.

BTW, sorry if this is a VBA 101 question, but when to use the '&' and when to use 'AND'?

[pbaldy]

& is a concatenation operator, AND is a logical operator. You'd use & to join things together:

"Paul" & " " & "Baldy"

would yield

Paul Baldy

AND (along with OR) is commonly used in code to evaluate expressions:

If ThisTestIsTrue AND ThatTestIsTrue Then

[karmacable]

Awesome, it works!! Now the next question (as I now know I'll have much more code to write), is there a more efficient way to do the above coding? I have to make responses for each possible combination of selections from the 4 boxes. Would something like an 'array' help here (something I've heard of but have no idea how to work)?

Thanks again for the first answer, I'll mark this thread as solved.

[pbaldy]

Are you just testing that something is selected, or what is actually selected? I'm not clear on what you're trying to do.

[karmacable]

I'm basically creating a summarized report based on what is actually selected. A user has a number of combo box selections and based on their choices (or lack of), I fill a label's caption to show what they've chosen. To illustrate (not my actual DB's setup, but the structure);


Group 1___Group 2____Group 3___Group 4
Sony______ Panasonic ___N/A _________N/A
Sony______N/A__________N/A_________N/A
N/A_______Sony_________N/A_________N/A
Nikon______Sony_________Panasonic____N/A
N/A_______Sony_________Nikon________N/A

So for the above project, the end result would be;

Group 1 has 2 x Sony & 1 Nikon
Group 2 has 3 x Sony & 1 Panasonic
Group 3 has 1 x Panasonic & 1 Nikon