Where condition in opening report

  • Thread starter Thread starter Struggling
  • Start date Start date
S

Struggling

Can anyone help me with the following I have a report
based on a table and I want to filter out Jobs which are
complete. I have a field in the table
called 'JobStatus'which is a text field and is set through
a form with a list box of options one of which
is 'Complete' I have tried allsorts of combinations but
why does the following not work?

"Tables!tblJob.JobStatus <> "Complete""
I know I can use the filter option in the properties of
the report but I am trying to get to grips with producing
the where clause when opening a report
 
Try this code:
Dim strWhere As String
strWhere = "JobStatus <> 'Complete'"
DoCmd.OpenReport "MyReport", acViewPreview, , strWhere
 
Many thanks Allen
Can I ask what was causing the problem Was it the ""
around Complete or referencing Tables!tblJob.JobStatus
Thanks in anticipation
 
1. The Tables references is incorrect. You could use:
tblJob.JobStatus
if more than 1 table in the source query has a JobStatus. Otherwise the
"tblJob" is superflous.

2. The string:
"JobStatus <> "Complete""
is not correct. Access thinks the string ends when it hits the quote marks
before Complete, and does not know what to do with the rest of the line.

Inside a string, you can use the single quote as a delimiter, e.g.:
"JobStatus <> 'Complete'"
Alternatively, you can double-up the quotes to get one of them in a string,
i.e.:
"JobStatus <> ""Complete"""
Doubling them is the convention for telling VBA, "This isn't the end of the
string; I want a quote mark inside the string here". So:
"This string contains a ""word"" in quotes."

In general, the doubled up double-quote marks are better than the
single-quote, because apostrophies often mess up the single-quotes.
 
Thankyou for your time it's appreciated , I am new to
these newsgroups and can't believe how helpful they are. I
read all the books etc.. but still struggle!
 
Back
Top