Report Selection

[klp]

I've put a post on 2 other website forums and I get no reply so hopefully
someone can help me here.

Okay I have an unbound form that has approx. 10 cbo boxes on it. This form is
for the user to select what they want to view/print on a report. The report
is based from a query w/ criteria input. Previously the query looked at my
table, testingresults, and all the fields(that are cbo boxes) were filled.
Well we have added a couple new fields into the table and these fields are
cbo boxes as well and need to be on the unbound form, or Report dialog as I
call it. But now when I add those fields - run the form and select the
criteria, it prints out a blank report. Because these fields are null still
it reads it and pulls nothing back to the report. Here is the logic I have in
the criteria & or part of the qry:
Like "*" & [Forms]![ReportDialog]![PlasticType] Or Like "*" & [Forms]!
[ReportDialog]![PlasticType]. This was working previously as I mentioned
before there were additional fields added to my report dialog. So I tried
this logic

Like IIf(IsNull([Forms]![ReportDialog]![PlasticType]),"*","*" & [Forms]!
[ReportDialog]![PlasticType] & "*").

This still doesn't work. Say I have a field for PlasticType(which is a new
field I just added) and SurfaceMaterial(this field has data in it). I have
that logic above in each field. If I take out the logic in PlasticType it
will give me my data, but puts out a blank report if it's in there. What am I
doing wrong?

Thanks in advance.

 

[Marshall Barton]

I have an unbound form that has approx. 10 cbo boxes on it. This form is
for the user to select what they want to view/print on a report. The report
is based from a query w/ criteria input. Previously the query looked at my
table, testingresults, and all the fields(that are cbo boxes) were filled.
Well we have added a couple new fields into the table and these fields are
cbo boxes as well and need to be on the unbound form, or Report dialog as I
call it. But now when I add those fields - run the form and select the
criteria, it prints out a blank report. Because these fields are null still
it reads it and pulls nothing back to the report. Here is the logic I have in
the criteria & or part of the qry:
Like "*" & [Forms]![ReportDialog]![PlasticType] Or Like "*" & [Forms]!
[ReportDialog]![PlasticType]. This was working previously as I mentioned
before there were additional fields added to my report dialog. So I tried
this logic

Like IIf(IsNull([Forms]![ReportDialog]![PlasticType]),"*","*" & [Forms]!
[ReportDialog]![PlasticType] & "*").

This still doesn't work. Say I have a field for PlasticType(which is a new
field I just added) and SurfaceMaterial(this field has data in it). I have
that logic above in each field. If I take out the logic in PlasticType it
will give me my data, but puts out a blank report if it's in there. What am I
doing wrong?

Do you want to match any value in the table (including Null)
when the combo box on the form has nothing in it?

Or is the problem that you want to match table fields that
are Null regardless of what was in the combo box?

Or is it both situations?

Generally it is cleaner to use VBA code (in the form's
button that opens the report) to construct the appropriate
WhereCondition argument for the OpenReport method. Trying
to deal with all these situations in the query's criteria
can rapidly spin out of control.

 

[klp via AccessMonster.com]

Say there is a manufacturer, product, adhesive and test weight cbo box on the
form. And I only want to see a specific Manufacturer. So I select that
manufacturer, in the meantime when it looks at the table and sees all the
tests w/ that manufacturer, there could be a field such as the adhesive field
that is null. I still want it to pull that test(s) for me to see on the
report. Instead it sees nothing in that field regardless of what's in the
other fields and pulls no data. The cbo boxes on the form are based on tables.
So there is something in each one of those tables, but not necessarily in the
master table. Does that make sense?

I have an unbound form that has approx. 10 cbo boxes on it. This form is
for the user to select what they want to view/print on a report. The report

[quoted text clipped - 19 lines]

will give me my data, but puts out a blank report if it's in there. What am I
doing wrong?

Do you want to match any value in the table (including Null)
when the combo box on the form has nothing in it?

Or is the problem that you want to match table fields that
are Null regardless of what was in the combo box?

Or is it both situations?

Generally it is cleaner to use VBA code (in the form's
button that opens the report) to construct the appropriate
WhereCondition argument for the OpenReport method. Trying
to deal with all these situations in the query's criteria
can rapidly spin out of control.

 

[Marshall Barton]

I think you are saying that you want to use the first option
I posted earlier.

I still say using code to construct the report's
WhereCondition argument is the cleanest route, but to do it
entirely in the query's criteria, try setting each of them
to this kind of expression:

=[Forms]![ReportDialog]![PlasticType] OR
[Forms]![ReportDialog]![PlasticType] Is Null

I know you were using Like with some wildcards before so
that may not cover some situations. Let me know how the
above works or what else you need it to do.
--
Marsh
MVP [MS Access]

Say there is a manufacturer, product, adhesive and test weight cbo box on the
form. And I only want to see a specific Manufacturer. So I select that
manufacturer, in the meantime when it looks at the table and sees all the
tests w/ that manufacturer, there could be a field such as the adhesive field
that is null. I still want it to pull that test(s) for me to see on the
report. Instead it sees nothing in that field regardless of what's in the
other fields and pulls no data. The cbo boxes on the form are based on tables.
So there is something in each one of those tables, but not necessarily in the
master table. Does that make sense?

I have an unbound form that has approx. 10 cbo boxes on it. This form is
for the user to select what they want to view/print on a report. The report

[quoted text clipped - 19 lines]

will give me my data, but puts out a blank report if it's in there. What am I
doing wrong?

Do you want to match any value in the table (including Null)
when the combo box on the form has nothing in it?

Or is the problem that you want to match table fields that
are Null regardless of what was in the combo box?

Or is it both situations?

Generally it is cleaner to use VBA code (in the form's
button that opens the report) to construct the appropriate
WhereCondition argument for the OpenReport method. Trying
to deal with all these situations in the query's criteria
can rapidly spin out of control.

 

[klp via AccessMonster.com]

The following worked great until I wanted to open the query up in design mode.
It now gives me an error message of: "There isn't enough memory to perform
this operation. Close unneeded programs and try the operation again". I got
this before and when I tried only a couple fields w/ the criteria below it
was able to open and then the criteria was scattered everywhere. So I guess
the best way would be to put it into VBA. What's the best way to do this?

I think you are saying that you want to use the first option
I posted earlier.

I still say using code to construct the report's
WhereCondition argument is the cleanest route, but to do it
entirely in the query's criteria, try setting each of them
to this kind of expression:

=[Forms]![ReportDialog]![PlasticType] OR
[Forms]![ReportDialog]![PlasticType] Is Null

I know you were using Like with some wildcards before so
that may not cover some situations. Let me know how the
above works or what else you need it to do.

Say there is a manufacturer, product, adhesive and test weight cbo box on the
form. And I only want to see a specific Manufacturer. So I select that

[quoted text clipped - 25 lines]

 

[Marshall Barton]

First, base the report on a query with no criteria or just
the table (if that's all that's needed).

Then add code to the Click event procedure of the form's
button that opens the report. The general outline of the
code would be:

Dim strWhere As String
' use this for numeric fields
If Not IsNull(combo1) Then
strWhere = strWhere & " AND field1 = " & combo1
End If
' use this for text fields
If Not IsNull(combo2) Then
strWhere = strWhere & " AND field2 = """ & combo2 & """"
End If
' use this for date fields
If Not IsNull(combo3) Then
strWhere = strWhere & " AND field3 = " _
& Format(combo3, "\#m\/d\/yyyy\#")
End If
DoCmd.OpenReport "reportname", , , Mid(strWhere, 6)

You will need **one** of the above If blocks for each combo
box. Be sure to use your own names for the fields and combo
boxes and adjust the OpenReport arguments for the report
name and view.
--
Marsh
MVP [MS Access]

The following worked great until I wanted to open the query up in design mode.
It now gives me an error message of: "There isn't enough memory to perform
this operation. Close unneeded programs and try the operation again". I got
this before and when I tried only a couple fields w/ the criteria below it
was able to open and then the criteria was scattered everywhere. So I guess
the best way would be to put it into VBA. What's the best way to do this?

I think you are saying that you want to use the first option
I posted earlier.

I still say using code to construct the report's
WhereCondition argument is the cleanest route, but to do it
entirely in the query's criteria, try setting each of them
to this kind of expression:

=[Forms]![ReportDialog]![PlasticType] OR
[Forms]![ReportDialog]![PlasticType] Is Null

I know you were using Like with some wildcards before so
that may not cover some situations. Let me know how the
above works or what else you need it to do.

Say there is a manufacturer, product, adhesive and test weight cbo box on the
form. And I only want to see a specific Manufacturer. So I select that

[quoted text clipped - 25 lines]

to deal with all these situations in the query's criteria
can rapidly spin out of control.

 

[klp via AccessMonster.com]

Great, thanks. I do have a question though. All my fields are text fields.
In your logic you have as follows:
strWhere = strWhere & " AND Field2 = "" & combo2 & """
My question is, the 1st quote, shouldn't that be a double quote,"" to
symolize a string? Or what does that stand for if that's not it. I used just
the single quote but the AND in my logic didn't turn that blue so I knew it
wasn't reading it correctly. Just wanted to know for my information. Thanks.

First, base the report on a query with no criteria or just
the table (if that's all that's needed).

Then add code to the Click event procedure of the form's
button that opens the report. The general outline of the
code would be:

Dim strWhere As String
' use this for numeric fields
If Not IsNull(combo1) Then
strWhere = strWhere & " AND field1 = " & combo1
End If
' use this for text fields
If Not IsNull(combo2) Then
strWhere = strWhere & " AND field2 = """ & combo2 & """"
End If
' use this for date fields
If Not IsNull(combo3) Then
strWhere = strWhere & " AND field3 = " _
& Format(combo3, "\#m\/d\/yyyy\#")
End If
DoCmd.OpenReport "reportname", , , Mid(strWhere, 6)

You will need **one** of the above If blocks for each combo
box. Be sure to use your own names for the fields and combo
boxes and adjust the OpenReport arguments for the report
name and view.

The following worked great until I wanted to open the query up in design mode.
It now gives me an error message of: "There isn't enough memory to perform

[quoted text clipped - 22 lines]

 

[Marshall Barton]

You miscounted the number of quotes. The important thing to
know here is that when you need a quote inside quotes, use
two quotes. So, since the result you want is:

And field = "abc"

To enclose that in quotes, you would need to use:

" And field = """abc"""

So, to accomplish the above, you need to write the statement
as:

strWhere = strWhere & " AND Field2 = """ & combo2 & """"

that's one quote before the AND, three quotes after the =
and four quotes at the end. Think about it and it will
eventually make sense ;-)

 

[klp via AccessMonster.com]

Yes I did miscount. Thank you so much. It makes total sense once I actually
thought about it.

You miscounted the number of quotes. The important thing to
know here is that when you need a quote inside quotes, use
two quotes. So, since the result you want is:

And field = "abc"

To enclose that in quotes, you would need to use:

" And field = """abc"""

So, to accomplish the above, you need to write the statement
as:

strWhere = strWhere & " AND Field2 = """ & combo2 & """"

that's one quote before the AND, three quotes after the =
and four quotes at the end. Think about it and it will
eventually make sense ;-)

Great, thanks. I do have a question though. All my fields are text fields.
In your logic you have as follows:

[quoted text clipped - 31 lines]

 

[Marshall Barton]

Well. I am glad you sorted it out inspite of me miscounting
the quotes in my example. For the record, it should have
been:
" And field = ""abc"""
--
Marsh
MVP [MS Access]

Yes I did miscount. Thank you so much. It makes total sense once I actually
thought about it.

You miscounted the number of quotes. The important thing to
know here is that when you need a quote inside quotes, use
two quotes. So, since the result you want is:

And field = "abc"

To enclose that in quotes, you would need to use:

" And field = """abc"""

So, to accomplish the above, you need to write the statement
as:

strWhere = strWhere & " AND Field2 = """ & combo2 & """"

that's one quote before the AND, three quotes after the =
and four quotes at the end. Think about it and it will
eventually make sense ;-)

Great, thanks. I do have a question though. All my fields are text fields.
In your logic you have as follows:

[quoted text clipped - 31 lines]

boxes and adjust the OpenReport arguments for the report
name and view.