Syntax Error (1 Viewer)

[forms_are_nightmares]

I've been monkeying around with this for a while and can't figure out where the syntax error is. Maybe a set of fresh eyes can help.

Scenario:
Form with multiple combo boxes. A button on the form that when clicked, will open a report based on the values in the combobox.

ComboBox1 lists names of individuals
ComboBox2 lists the regions the individuals are in.

When I click the button I get "Run-time error 3075: extra ) in expression"

I just can't see where that is.

Any help is appreciated - Code is below

If Not IsNull(Me.RegionCmbo) Then
DoCmd.OpenReport "rpt_rollout_specialist", acViewPreview, , "[Region] = '" & Me.RegionCmbo & "') And"
End If

If Not IsNull(Me.SpecialistCmbo) Then
DoCmd.OpenReport "rpt_rollout_specialist", acViewPreview, , "[Specialist] = '" & Me.SpecialistCmbo & "' And "
End If


Thanks

 

[dkinley]

You have two conditions that are independant of each other. If one is null and the other is not - there is the bit about the AND and the ( hanging out there doing nothing.

Perhaps something like ...

Dim sWhere As String
 
If Not IsNull(Me.RegionCmbo) Then
      sWhere = "[Region] = '" & Me.RegionCmbo & "'
End If
 
If Not IsNull(Me.SpecialistCmbo) Then
     sWhere = "[Specialist] = '" & Me.SpecialistCmbo & "' 
End If
 
DoCmd.OpenReport "rpt_rollout_specialist", acViewPreview, , sWhere

This is all aircode but something to get you thinking. Depending on the status of the combo boxes, you can set the where condition to what is needed as the system iterates through the code.

Of course, because of the If logic, this order is a ranking ... or a preference for the report to hinge off of the specialist combo box.

HTH,
-dK

 

[forms_are_nightmares]

Thanks,

I was trying to think of a different approach and your's was the direction I wanted to go but couldn't figure out how. I've made a few changes but overall this works.

Thanks

 

[dkinley]

I see where you were going and it could be a difficult road. For instance, writing some extra code to clip off the bits at the end you already have added.

Now, depending on how your report is built you could modify it for both conditions ....

If Not IsNull(Me.RegionCmbo) Then
      sWhere = "[Region] = '" & Me.RegionCmbo & "'
End If
 
If Not IsNull(Me.SpecialistCmbo) Then
     sWhere = "(" & sWhere & ") And ([Specialist] = '" & Me.SpecialistCmbo & "' )"
Else
     sWhere = [Specialist] = '" & Me.SpecialistCmbo & "' 
End If

This is 'building up' the sWhere to get a more exacting report (and inserting/correcting parenthesis, etc). Again, this also gives preference to the Specialist because it is last in the sequence. With more logic, you can give it an either/or type of preference. Basically sitting down with some paper and doing some diamonds in the good ole logic path way.

I would suggest that you could use If cbo1 = something AND cbo2 = something but you could potentially run into errors evaluating a null - but nonetheless might be worth a try for a more elegant solution.

HTH,
-dK

 

[forms_are_nightmares]

Initially, I was going to do the if box1 and box 2 solution but there could eventually be 5 or 6 boxes (like filters, in a way) and then the amount of combinations would get a bit much. I'm still having a few issues but they are mainly the "exacting" part of the report you mentioned. But at least I'm getting the code to work. Just need a few tweaks.

 

[dkinley]

Ah ... aye, see what you mean. I've abused and have had a lot of success with Allen Browne's method?

Here is a link to it (I couldn't find it on his site).

Most of it could probably be deleted but calling your attention to the last 30 lines. If you could understand this, then it would be matter of copying a pasting an If-Then statement for each of your criteria. The last few lines clip any outstanding 'And' and stuff so the where statement should act accordingly.

HTH,
-dK

 

[forms_are_nightmares]

This was absolutely spot on. I was able to modify that code to work exactly how I wanted it to.

Thanks for all your help.

 

[dkinley]

You betcha. I probably would have referred that initially but thought you were only dealing with two criteria. Once you mentioned the complexity bit, that one sprung to mind.

Apologies, but glad you got it working straightaway!

-dK