Where Clause in VBA for Sub Command_Click()

[Bill6868]

I'm having trouble with the syntax of a WHERE clause.

I need to open rptAvery5160 in print preview WHERE tblConstituents.Profile Type = "Individual" AND tblConstituents.Status = "Active" AND tblConstituents.EmailOK = "Yes" AND tblConstituents.SCP-M = "Yes"

The two yeses (EmailOK and SCP-M) are Yes/No data type fields.

I"ve tried this but with no luck...


Private Sub Command118_Click()

DoCmd.OpenReport "rptAvery5160", acViewPreview, , (((tblConstituents.ProfileType)="Individual") AND ((tblConstituents.Status)="Active") AND ((tblConstituents.EmailOK)=Yes) AND ((tblConstituents.[SCP-M])=Yes))"

I just can't seem to get the syntax correct.

Any suggestions would be greatly appreciated.

 

[jrbarnett]

A yes/no field is boolean, represented with True for yes and false for No in your VBA. Therefore the following should do the trick:

DoCmd.OpenReport "rptAvery5160", acViewPreview, , (((tblConstituents.ProfileType)="Individual") AND ((tblConstituents.Status)="Active") AND ((tblConstituents.EmailOK)=True) AND ((tblConstituents.[SCP-M])=True))"

John

 

[Bill6868]

Thanks jr.

I wrote this:

DoCmd.OpenReport "rptAvery5160", acViewPreview, , "(((tblConstituents.ProfileType) = "Individual") AND ((tblConstituents.Status) = "Active") AND ((tblConstituents.EmailOK) = True) AND ((tblConstituents.[SCP-M])= True))"

but I'm still getting that nasty compile error where the entire clause turns red. Something is not clicking...is it the in the "'s or the (('s or should for example use 'Individual' instead of "Individual"

Any suggestion?

 

[Bill6868]

JR..

I figured this out:

DoCmd.OpenReport "rptAvery5160", acViewPreview, , "(((tblConstituents.ProfileType) = 'Individual') AND ((tblConstituents.Status) = 'Active') AND ((tblConstituents.EmailOK) = True) AND ((tblConstituents.[SCP-M]) = True))"

"Individual" needed to be 'Individual' as well as 'Active'. Also when I tried ((tblConstituents.SCP-M = True it did not like the notion that I used a hyphen in the field name so I had to put brackets around it. I.e. ((tblConstituents.[SCP-M] = True))"

Thank for pointing me in the right direction. I will need to attach a variation of this clause to about 20 more command buttons.

Much appreciated.