Help using parenthesis in a string value

[kelly4an]

I need some help with parenthesis. I am not sure how to use parenthesis when determining a string value. Everything I have tried doesn't seem to work. I want the results for when the cboCharacter.value is in either the [Imprint Character 1] field OR the [Imprint Character 2] field AND the cboScoring.Value is in the Scoring field.
Where should the parenthesis be placed in the code?


Option Compare Database
Option Explicit

Private Const strSQL1 = "SELECT [Product Name], NDC, [Imprint Number 1], " & _
"[Imprint Character 1], [Imprint Number 2], [Imprint Character 2], [COLOR=1], [COLOR=2], Shape, Scoring, Coating FROM ProductDescriptionTable = '"

Private Const strSQL9 = "' WHERE Scoring = '"
Private Const strSQL20 = "' AND [Imprint Character 1] = '"
Private Const strSQL21 = "' OR [Imprint Character 2] = '"

Private intCtr As Integer
Private strSQL As String


Private Sub FillList()

If Me!cboProductName.Value = "(All)" And Me!cboColor.Value = "(All)" And &
Me!cboImprintNumber.Value = "(All)" And Me!cboShape.Value = "(All)"
Then strSQL = strSQL1 & strSQL9 & Me!cboScoring.Value & strSQL20 &
Me!cboCharacter.Value & strSQL21 Me!cboCharacter.Value

Else
strSQL = strSQL1 & Me!cboProductName.Value & strSQL2 & Me!cboShape.Value &
strSQL9 & Me!cboScoring.Value & strSQL19 & Me!cboColor.Value & strSQL20 &
Me!cboCharacter.Value & strSQL21 & Me!cboImprintNumber.Value

End If

 

[abaldwin]

I am not sure of what you mean by where the parenthesis go, are you speaking of using the parenthesis to keep certain and and or's separate.

Also as a note. Your strsql1 is going to cause you a problem with the = sign at the end. Your sql will after concantenation be something like

FROM ProductDescriptionTable = ' WHERE......

That is going to cause you a problem.

Also if the if is true then you will need to add a single quote after the cboscoring.value to properly close the quotes on the string field. Check the other parts of that statement as well.

I would suggest using the immediate and or watch window while stepping through your code to see where you are going to get errors at. While using the immediate window you can copy the resulting sql out and paste it into your db system query tool to see how it runs and the results given.



Andy Baldwin

"Testing is the most overlooked programming language on the books!"

Ask a great question, get a great answer. Ask a vague question, get a vague answer.
Find out how to get great answers FAQ219-2884.

 

[TheAceMan1]

How are ya kelly4an . . .

Post the RowSource for one of the combo's . . .

See Ya! . . . . . .