Multiple Optional Criteria to Generate Query

[HeatherD25]

Hi,

I'm trying to get around not being able to export a report to Excel in
Access 2007. So... I have a form (frm_Reports). On that form I have a
number of criteria that a user can enter data in (they can enter something in
all fields, some fields, or none) to create their report (which is actually a
query that I will export).

Some example fields:

[STATUS] (This is a combo selection box)
[LETTER_DATE_Begin] and [LETTER_DATE_End] (This has two boxes they can enter
a begin date and end date to pull data for [LETTER_DATE] field.
[FLAGGED] (This is a Yes/No field)

There are some other fields, but I'll just use the above as samples. I want
them to be able to run a query (qRpt_AdHoc) based on what's entered on the
form. So, if nothing is entered I want the query to return all of the
records. If two things are entered, I want the query to filter on both of
those things.

How do I do this? I had it working to pass the values through to a report,
but since I can't export a report that won't work. I can't figure out how to
get it to work with a query. I don't know what to put in the Criteria field.

Thanks for your help!
Heather

 

[Allen Browne]

See:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

The article explains how to build the WHERE clause from only the boxes where
the user entered something. This may be similar to what you were doing for
your report.

Once you have that working, you can use the WHERE clause as part of the SQL
statement for the export query. Just assign the SQL property of the QueryDef
before you export.

This kind of thing:

Dim strWhere As String
Dim strSql As String
strWhere = ... 'Build as in the article above
strSql = "SELECT * FROM Table1 WHERE " & strWhere & " ORDER BY ID;"
CurrentDb.QueryDefs("Query2").SQL = strSql
DoCmd.TransferSpreadsheet ...

 

[HeatherD25]

Thank you!! That was very helpful... I've almost got it working! One more
question...

I have a combo box (cbox_ReferredTo). I want to add something to the SQL
string that says if cbox_ReferredTo = [FIELD1] or cboxReferredTo = [FIELD2].
How do I string this together? I tried this and it didn't work:

If Not IsNull (Me.cbox_ReferredTo) Then
strWhere = strWhere & " AND [FIELD1] = '" & cbox_ReferredTo.Value & "' & "
OR [FIELD2] = '" & cbox_ReferredTo.Value & "'

Thanks!!

See:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

The article explains how to build the WHERE clause from only the boxes where
the user entered something. This may be similar to what you were doing for
your report.

Once you have that working, you can use the WHERE clause as part of the SQL
statement for the export query. Just assign the SQL property of the QueryDef
before you export.

This kind of thing:

Dim strWhere As String
Dim strSql As String
strWhere = ... 'Build as in the article above
strSql = "SELECT * FROM Table1 WHERE " & strWhere & " ORDER BY ID;"
CurrentDb.QueryDefs("Query2").SQL = strSql
DoCmd.TransferSpreadsheet ...

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I'm trying to get around not being able to export a report to Excel in
Access 2007. So... I have a form (frm_Reports). On that form I have a
number of criteria that a user can enter data in (they can enter something
in
all fields, some fields, or none) to create their report (which is
actually a
query that I will export).

Some example fields:

[STATUS] (This is a combo selection box)
[LETTER_DATE_Begin] and [LETTER_DATE_End] (This has two boxes they can
enter
a begin date and end date to pull data for [LETTER_DATE] field.
[FLAGGED] (This is a Yes/No field)

There are some other fields, but I'll just use the above as samples. I
want
them to be able to run a query (qRpt_AdHoc) based on what's entered on the
form. So, if nothing is entered I want the query to return all of the
records. If two things are entered, I want the query to filter on both of
those things.

How do I do this? I had it working to pass the values through to a
report,
but since I can't export a report that won't work. I can't figure out how
to
get it to work with a query. I don't know what to put in the Criteria
field.

Thanks for your help!
Heather

 

[HeatherD25]

I forgot to mention one thing. The [FIELD1] and [FIELD2] are name fields.
It is lastname, firstname. How do I deal with this comma in the SQL string?

Thank you!! That was very helpful... I've almost got it working! One more
question...

I have a combo box (cbox_ReferredTo). I want to add something to the SQL
string that says if cbox_ReferredTo = [FIELD1] or cboxReferredTo = [FIELD2].
How do I string this together? I tried this and it didn't work:

If Not IsNull (Me.cbox_ReferredTo) Then
strWhere = strWhere & " AND [FIELD1] = '" & cbox_ReferredTo.Value & "' & "
OR [FIELD2] = '" & cbox_ReferredTo.Value & "'

Thanks!!

See:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

The article explains how to build the WHERE clause from only the boxes where
the user entered something. This may be similar to what you were doing for
your report.

Once you have that working, you can use the WHERE clause as part of the SQL
statement for the export query. Just assign the SQL property of the QueryDef
before you export.

This kind of thing:

Dim strWhere As String
Dim strSql As String
strWhere = ... 'Build as in the article above
strSql = "SELECT * FROM Table1 WHERE " & strWhere & " ORDER BY ID;"
CurrentDb.QueryDefs("Query2").SQL = strSql
DoCmd.TransferSpreadsheet ...

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I'm trying to get around not being able to export a report to Excel in
Access 2007. So... I have a form (frm_Reports). On that form I have a
number of criteria that a user can enter data in (they can enter something
in
all fields, some fields, or none) to create their report (which is
actually a
query that I will export).

Some example fields:

[STATUS] (This is a combo selection box)
[LETTER_DATE_Begin] and [LETTER_DATE_End] (This has two boxes they can
enter
a begin date and end date to pull data for [LETTER_DATE] field.
[FLAGGED] (This is a Yes/No field)

There are some other fields, but I'll just use the above as samples. I
want
them to be able to run a query (qRpt_AdHoc) based on what's entered on the
form. So, if nothing is entered I want the query to return all of the
records. If two things are entered, I want the query to filter on both of
those things.

How do I do this? I had it working to pass the values through to a
report,
but since I can't export a report that won't work. I can't figure out how
to
get it to work with a query. I don't know what to put in the Criteria
field.

Thanks for your help!
Heather

 

[Allen Browne]

Suggestions:

1. Add brackets
This is important where you mix ANDs and ORs in a WHERE clause.
Example:
strWhere = strWhere & " AND (([FIELD1] = """ & _
Me.cbox_ReferredTo & """) OR ([FIELD2] = """ & _
cbox_ReferredTo.Value & """))"

2. Watch the bound column
Sometimes a combo is bound to a numeric field such as ClientID, but it shows
the client name not the number. You need to be clear about what data type
FIELD1 and FIELD2 are, and what the bound column of the combo is.

3. Make sure the combo is unbound.
You don't want to be actually changing a field of the current record when
you are trying to apply a filter.

4. Explicitly save
Worth adding a line such as:
If Me.Dirty Then Me.Dirty = False

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thank you!! That was very helpful... I've almost got it working! One
more
question...

I have a combo box (cbox_ReferredTo). I want to add something to the SQL
string that says if cbox_ReferredTo = [FIELD1] or cboxReferredTo =
[FIELD2].
How do I string this together? I tried this and it didn't work:

If Not IsNull (Me.cbox_ReferredTo) Then
strWhere = strWhere & " AND [FIELD1] = '" & cbox_ReferredTo.Value & "' & "
OR [FIELD2] = '" & cbox_ReferredTo.Value & "'

Thanks!!

See:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

The article explains how to build the WHERE clause from only the boxes
where
the user entered something. This may be similar to what you were doing
for
your report.

Once you have that working, you can use the WHERE clause as part of the
SQL
statement for the export query. Just assign the SQL property of the
QueryDef
before you export.

This kind of thing:

Dim strWhere As String
Dim strSql As String
strWhere = ... 'Build as in the article above
strSql = "SELECT * FROM Table1 WHERE " & strWhere & " ORDER BY ID;"
CurrentDb.QueryDefs("Query2").SQL = strSql
DoCmd.TransferSpreadsheet ...

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I'm trying to get around not being able to export a report to Excel in
Access 2007. So... I have a form (frm_Reports). On that form I have a
number of criteria that a user can enter data in (they can enter
something
in
all fields, some fields, or none) to create their report (which is
actually a
query that I will export).

Some example fields:

[STATUS] (This is a combo selection box)
[LETTER_DATE_Begin] and [LETTER_DATE_End] (This has two boxes they can
enter
a begin date and end date to pull data for [LETTER_DATE] field.
[FLAGGED] (This is a Yes/No field)

There are some other fields, but I'll just use the above as samples. I
want
them to be able to run a query (qRpt_AdHoc) based on what's entered on
the
form. So, if nothing is entered I want the query to return all of the
records. If two things are entered, I want the query to filter on both
of
those things.

How do I do this? I had it working to pass the values through to a
report,
but since I can't export a report that won't work. I can't figure out
how
to
get it to work with a query. I don't know what to put in the Criteria
field.

Thanks for your help!
Heather