INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Where Clause in VBA for Sub Command_Click()

Where Clause in VBA for Sub Command_Click()

(OP)
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.

RE: Where Clause in VBA for Sub Command_Click()

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:

CODE --> VBA

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

John

RE: Where Clause in VBA for Sub Command_Click()

(OP)
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?

RE: Where Clause in VBA for Sub Command_Click()

(OP)
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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close