Using SQL in VBA with "OR" Criteria 1

[jdgreen]

I have some code I am trying to get to run that goes through a list of e-mail addresses and adds the addresses to a string based on specific criteria. What is happening is the SQL that specifically calls out a value works, but the SQL that references a form for the value does not pull the work. In other words, it doesn't add the e-mail addresses when the values match the forms.

strSQL = "SELECT tblEmail.Email FROM tblEmail WHERE ((tblEmail.LocationType = ' & [Forms]![frmAlertLog]![sfrmAlertLog].[Form]![Location] & ' Or tblEmail.LocationType = 'Warehouse') And (tblEmail.Plant = ' & [Forms]![frmAlertLog]![sfrmAlertLog].[Form]![MfgPlant] & ' Or tblEmail.Plant = 'Plymouth' Or tblEmail.Plant = 'All'));"

strEmail = ""
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

rs.MoveFirst
Do Until rs.EOF
If strEmail = "" Then
strEmail = rs!email
Else
strEmail = rs!email & ";" & strEmail
End If
rs.MoveNext
Loop

Any help would be appreciated.

John Green

 

[earthandfire]

Try

strSQL = "SELECT tblEmail.Email FROM tblEmail WHERE ((tblEmail.LocationType = '" & [Forms]![frmAlertLog]![sfrmAlertLog].[Form]![Location] & "' Or tblEmail.LocationType = 'Warehouse') And (tblEmail.Plant = '" & [Forms]![frmAlertLog]![sfrmAlertLog].[Form]![MfgPlant] & "' Or tblEmail.Plant = 'Plymouth' Or tblEmail.Plant = 'All'));"


I've added some ". I think I've put them all in.

Hope this helps.

 

[SkipVought]

Hi,

I cn't directly answer your question BECAUSE I don't know what you INTEND for your logic.

When you combind AND and OR statements, you need to be VERY CAREFUL at the ORDER OF EXECUTION.

For instance each of these statements will yield DIFFERENT results...
[tt]
A OR B And C
(A OR B) And C
A OR (B And C)
[/tt]
So you tell me what your intent is.

strSQL = "SELECT tblEmail.Email FROM tblEmail 

WHERE ((tblEmail.LocationType = ' & [Forms]![frmAlertLog]![sfrmAlertLog].[Form]![Location] & ' 
  
  Or tblEmail.LocationType = 'Warehouse') 

  And (tblEmail.Plant = ' & [Forms]![frmAlertLog]![sfrmAlertLog].[Form]![MfgPlant] & '

  Or tblEmail.Plant = 'Plymouth' 

  Or tblEmail.Plant = 'All'));"

I THINK that what you want is

Where (LocationType= a OR LocationType= b)

  AND

      (Plant=x OR Plant =y OR Plant =z)

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[jdgreen]

That did it. Thank you!

John Green