[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Row Source error in VBA coding to filter data.

Posted on 2009-02-09
3
Medium Priority
?
173 Views
Last Modified: 2013-11-27
I am having problems with my code to dynamiclly render the row source for an unboud listbox. When I use the code below I get a compile error "Expected:)" if I remove the "" around the Yes in the And statement in the Where clause. The results in the query designer are good. However in the form the records it should filter out are still showing. Any ideas?
("SELECT DISTINCTROW tbl_reports.reportid, tbl_reports.reportname, tbl_reports.reportdescription, tbl_reports.accesslevel, tbl_reports.reportactive, tbl_reports.rptcriteria, tbl_reports.rptgroup, tbl_reports.rpttype, tbl_reports.reportsubject From tbl_reports WHERE (((tbl_reports.accesslevel)=1 Or (tbl_reports.accesslevel)=3 Or (tbl_reports.accesslevel)=4 Or (tbl_reports.accesslevel)=7 Or (tbl_reports.accesslevel)=10 Or (tbl_reports.accesslevel)=13) AND ((tbl_reports.reportactive)="Yes"));")

Open in new window

0
Comment
Question by:woodje
3 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 23592556
is the Data type of field [reportactive] Text?


("SELECT DISTINCTROW tbl_reports.reportid, tbl_reports.reportname, tbl_reports.reportdescription, tbl_reports.accesslevel, tbl_reports.reportactive, tbl_reports.rptcriteria, tbl_reports.rptgroup, tbl_reports.rpttype, tbl_reports.reportsubject From tbl_reports WHERE (((tbl_reports.accesslevel)=1 Or (tbl_reports.accesslevel)=3 Or (tbl_reports.accesslevel)=4 Or (tbl_reports.accesslevel)=7 Or (tbl_reports.accesslevel)=10 Or (tbl_reports.accesslevel)=13) AND ((tbl_reports.reportactive)='Yes'));")

if it is a Yes/No field, use this

("SELECT DISTINCTROW tbl_reports.reportid, tbl_reports.reportname, tbl_reports.reportdescription, tbl_reports.accesslevel, tbl_reports.reportactive, tbl_reports.rptcriteria, tbl_reports.rptgroup, tbl_reports.rpttype, tbl_reports.reportsubject From tbl_reports WHERE (((tbl_reports.accesslevel)=1 Or (tbl_reports.accesslevel)=3 Or (tbl_reports.accesslevel)=4 Or (tbl_reports.accesslevel)=7 Or (tbl_reports.accesslevel)=10 Or (tbl_reports.accesslevel)=13) AND ((tbl_reports.reportactive)=-1));")

0
 
LVL 77

Expert Comment

by:peter57r
ID: 23592557
If you are trying to assign this expression as rowsource then you need..

me.listboxname.rowsource = "SELECT DISTINCTROW tbl_reports.reportid, tbl_reports.reportname, tbl_reports.reportdescription, tbl_reports.accesslevel, tbl_reports.reportactive, tbl_reports.rptcriteria, tbl_reports.rptgroup, tbl_reports.rpttype, tbl_reports.reportsubject From tbl_reports WHERE (((tbl_reports.accesslevel)=1 Or (tbl_reports.accesslevel)=3 Or (tbl_reports.accesslevel)=4 Or (tbl_reports.accesslevel)=7 Or (tbl_reports.accesslevel)=10 Or (tbl_reports.accesslevel)=13) AND ((tbl_reports.reportactive)='Yes'));"
 

0
 

Author Closing Comment

by:woodje
ID: 31544628
Thank you the single quote worked. It was a text field.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question