Then we've mucked up the where statement somehow and Access thinks we are
passing it a field name that it does not recognize.

What you should see for example would be something like following (assuming
FirstName control is "John"

[FirstName] Like "*John*"

Of course, with your field name. AUUUGH! and now that I look at it I stuck in
some very unneeded ampersands.

" [FirstName] Like ""*" & [Forms]![frmFilterDialog]![FirstName] & "*"""

It might be clearer if you use the Chr function - Chr(34) is a quote mark if I
recall correctly.

" [FirstName] Like " & Chr(34) & "*" & [Forms]![frmFilterDialog]![FirstName]
& "*" & chr(34)

Play with those and see if you can get the expected string.

I am going offline for a few days, so if this doesn't work try starting a new
thread with details as to where you are.

Jeff J wrote:

Thanks again for the new code. Using your MsgBox idea I can see that it
creates a valid where statement but now the OpenReport statement displays a
dialog entitled "Enter Parameter Value", with the string entered on the form
just below that title, and a text box to enter a string below that, for each
Like statement. If I enter the string in the text box(s) the report is
correct. Any ideas?

"John Spencer (MVP)" wrote:


Instead of " [Firstname]=""" & [Forms]![frmFilterDialog]![FirstName] & """"

Use

" [Firstname] Like ""*"" & " & [Forms]![frmFilterDialog]![FirstName] & " & ""*"""

That looks complicated, but in order to get one quote mark inside a string, you
need to have two quote marks.

If you run into problems, try using a debug.print statement or a message box to
show the string before you actually use it.

Msgbox strWhere



Jeff J wrote:

Thank you so much for the If statements below. It works very well except it
is too strict in filtering. Could you tell me how to modify the strWhere =
strWhere & statements so that they build a string more like [LastName] =
"*John*" or some other way to produce a "looser" filtering? Thanks again.

"John Spencer (MVP)" wrote:

Yeah, with seven fields the query once it is reformatted by Access gets to
complex to run.

You might try building the SQL on the fly and then setting that as the record
source for the report. How skillful are you with VBA?

Something you can try,but may be slow is to build a query like

Field: First_Name: [FirstName] & ""
Criteria: "*" & [Forms]![frmFilterDialog]![FirstName] & "*"

You don't need the Is Null criteria, since the calculated field will never be
null. So if [Forms]![frmFilterDialog]![FirstName] is blank (or null) you will
get all records returned.

Another solution is to build your report query without a where clause and then
append one. Something likethe following code for a button on your form.

Private Sub PrintReport_Click()
Dim StrWhere as String

'For each control build a
IF Len([Forms]![frmFilterDialog]![FirstName] & "") 0 then
If Len(StrWhere)0 then
StrWhere = strWhere & " AND "
end If
StrWhere = strWhere & " [FirstName]=""" &
[Forms]![frmFilterDialog]![FirstName] & """"
End IF

IF Len([Forms]![frmFilterDialog]![LastName] & "") 0 then
If Len(StrWhere)0 then
StrWhere = strWhere & " AND "
end If
StrWhere = strWhere & " [LastName]=""" &
[Forms]![frmFilterDialog]![LastName] & """"
End IF

'repeat as necessary for your other fields, using the proper field delimiters.
Then call the report

DoCmd.OpenReport "YourReportName", acPreview, , strWhere

'The strWhere applies the criteria to query.

End Sub

Jeff J wrote:

Thank you John but the query DOS NOT work. I guess I might ask how would you create a report that could be created by a user "on the fly"? The way I have done it is an unbound form with several text boxes, corresponding to the fields in a table, a query as I discribed before, a report of a certain format, and a macro that is triggered by the "Ok" button of the unbound form. This macro basically opens the query which uses the variables from the form to filter a database then the report is opened in print preview, the query is closed, and the form is closed. This all worked the day I wrote it but the next day it did not and I thought it might be because of this "formatting" of the query as you call it. Can you think of a better way to do what I am trying here? Is there a better way to modify/create a query than the grid view?
Thanks

"John Spencer (MVP)" wrote:

Access reformats the query into this format. The only way to avoid the
reformatting is to never open the query in the grid view. IF the query works,
then don't worry about (until you need to modify it ;-0 ).



Jeff J wrote:

Using Access from Office XP I have written a query as part of a Query by Form operation. I have an unbound form with 7 text boxes and the query has 7 "columns" corresponding to those. The criteria on each column is in the form of two "rows" similar to the following:

First row
Like "*" & [Forms]![frmFilterDialog]![FirstName] & "*"

Second row
[Forms]![frmFilterDialog]![FirstName] Is Null

When I close and save the query in design view, then immediately re-open it in design view the criteria are "scattered" into dozens of rows of parts of the second row above. Even new columns are added with fragments of the second statement above as the field name. The first row above is not effected. I have tried creating a new database and a new query but the same thing happens. I have applied Office service pack 3. Any ideas?
Thanks