Report based on criteria form

[danno]

I have a report that opens from a form that sets the reports criteria
(frmSelect). The row source for the report is in the form:
SELECT ....
FROM .....
INNER JOIN....
WHERE (tblTableName.FieldName = [Forms]![frmFormName]![Fieldname] Or
[Forms]![frmFormName]![Fieldname] Is Null) AND (tblTableName.FieldName2 =
[Forms]![frmFormName]![Fieldname2] Or [Forms]![frmFormName]![Fieldname] Is
Null) AND ....

There are about 8 fields used as criteria and the report returns the
criteria as entered into frmSelect (all records for field if left null)

The problems I have are these:
1. Access for some reason re-writes the above code in SQL listing every
single combination meaning it takes a long time to switch between the
rowsource design view and SQL - can this be stopped?

2. The report actually shows averages and standard deviations of data which
is what I want but I would also like to display how many records this
calculated data is based on - any ideas on how best to do this?

Thanks
DF

 

[Marshall Barton]

I have a report that opens from a form that sets the reports criteria
(frmSelect). The row source for the report is in the form:
SELECT ....
FROM .....
INNER JOIN....
WHERE (tblTableName.FieldName = [Forms]![frmFormName]![Fieldname] Or
[Forms]![frmFormName]![Fieldname] Is Null) AND (tblTableName.FieldName2 =
[Forms]![frmFormName]![Fieldname2] Or [Forms]![frmFormName]![Fieldname] Is
Null) AND ....

There are about 8 fields used as criteria and the report returns the
criteria as entered into frmSelect (all records for field if left null)

The problems I have are these:
1. Access for some reason re-writes the above code in SQL listing every
single combination meaning it takes a long time to switch between the
rowsource design view and SQL - can this be stopped?

2. The report actually shows averages and standard deviations of data which
is what I want but I would also like to display how many records this
calculated data is based on - any ideas on how best to do this?

The query's SQL is arranged in a manner that Access thinks
is appropriate. How it is presented in the SQL view window
or in the query designer window may be confusing to most
people, but, beyond that, it doesn't matter because the
query plan that ends up being run be the database engine is
optimized in many ways that may or may not make sense to
you.

in the case of opening a report (or form), you can avoid all
that by removing the criteria from the query and using the
OpenReport (or OpenForm) method's WhereCondition arguments
instead. That requires some VBA code in the procedure that
opens the report. The general idea is something like:

Dim stWhere As String

' Number field
If Not IsNull(Me.Fieldname) Then
stWhere = stWhere & " And FieldName = " & Me.Fieldname
End If

' Text field
If Not IsNull(Me.Fieldname2) Then
stWhere = stWhere & " And FieldName2 = """ & Me.Fieldname2
& """"
End If

' Date field
If Not IsNull(Me.Fieldname3) Then
stWhere = stWhere & " And FieldName3 = " & _
Format(Me.Fieldname3, "\#yyyy\/m\/d\#")
End If

DoCmd.OpenReport "reportname", _
WhereCondition:= Mid(stWhere, 6)

 

[danno]

I have a report that opens from a form that sets the reports criteria
(frmSelect). The row source for the report is in the form:
SELECT ....
FROM .....
INNER JOIN....
WHERE (tblTableName.FieldName = [Forms]![frmFormName]![Fieldname] Or
[Forms]![frmFormName]![Fieldname] Is Null) AND (tblTableName.FieldName2 =
[Forms]![frmFormName]![Fieldname2] Or [Forms]![frmFormName]![Fieldname] Is
Null) AND ....

There are about 8 fields used as criteria and the report returns the
criteria as entered into frmSelect (all records for field if left null)

The problems I have are these:
1. Access for some reason re-writes the above code in SQL listing every
single combination meaning it takes a long time to switch between the
rowsource design view and SQL - can this be stopped?

2. The report actually shows averages and standard deviations of data which
is what I want but I would also like to display how many records this
calculated data is based on - any ideas on how best to do this?

The query's SQL is arranged in a manner that Access thinks
is appropriate. How it is presented in the SQL view window
or in the query designer window may be confusing to most
people, but, beyond that, it doesn't matter because the
query plan that ends up being run be the database engine is
optimized in many ways that may or may not make sense to
you.

in the case of opening a report (or form), you can avoid all
that by removing the criteria from the query and using the
OpenReport (or OpenForm) method's WhereCondition arguments
instead. That requires some VBA code in the procedure that
opens the report. The general idea is something like:

Dim stWhere As String

' Number field
If Not IsNull(Me.Fieldname) Then
stWhere = stWhere & " And FieldName = " & Me.Fieldname
End If

' Text field
If Not IsNull(Me.Fieldname2) Then
stWhere = stWhere & " And FieldName2 = """ & Me.Fieldname2
& """"
End If

' Date field
If Not IsNull(Me.Fieldname3) Then
stWhere = stWhere & " And FieldName3 = " & _
Format(Me.Fieldname3, "\#yyyy\/m\/d\#")
End If

DoCmd.OpenReport "reportname", _
WhereCondition:= Mid(stWhere, 6)

Thanks Marsh for the reply,
The problem for me is that the criteria fields are not included in the
SELECT statement as I don't want the data grouped on these fields.
It all runs fine but if I want to change something then there is a huge
hassle. I only mind that access re writes every single clause (design view
and/or SQL) meaning it is pages long and takes forever to load when I switch
views.
Just frustrated that I have to resort to cutting and pasting the SQL all the
time to get around the problem.
:-(

 

[Marshall Barton]

I have a report that opens from a form that sets the reports criteria
(frmSelect). The row source for the report is in the form:
SELECT ....
FROM .....
INNER JOIN....
WHERE (tblTableName.FieldName = [Forms]![frmFormName]![Fieldname] Or
[Forms]![frmFormName]![Fieldname] Is Null) AND (tblTableName.FieldName2 =
[Forms]![frmFormName]![Fieldname2] Or [Forms]![frmFormName]![Fieldname] Is
Null) AND ....

There are about 8 fields used as criteria and the report returns the
criteria as entered into frmSelect (all records for field if left null)

The problems I have are these:
1. Access for some reason re-writes the above code in SQL listing every
single combination meaning it takes a long time to switch between the
rowsource design view and SQL - can this be stopped?

2. The report actually shows averages and standard deviations of data which
is what I want but I would also like to display how many records this
calculated data is based on - any ideas on how best to do this?

The query's SQL is arranged in a manner that Access thinks
is appropriate. How it is presented in the SQL view window
or in the query designer window may be confusing to most
people, but, beyond that, it doesn't matter because the
query plan that ends up being run be the database engine is
optimized in many ways that may or may not make sense to
you.

in the case of opening a report (or form), you can avoid all
that by removing the criteria from the query and using the
OpenReport (or OpenForm) method's WhereCondition arguments
instead. That requires some VBA code in the procedure that
opens the report. The general idea is something like:

Dim stWhere As String

' Number field
If Not IsNull(Me.Fieldname) Then
stWhere = stWhere & " And FieldName = " & Me.Fieldname
End If

' Text field
If Not IsNull(Me.Fieldname2) Then
stWhere = stWhere & " And FieldName2 = """ & Me.Fieldname2
& """"
End If

' Date field
If Not IsNull(Me.Fieldname3) Then
stWhere = stWhere & " And FieldName3 = " & _
Format(Me.Fieldname3, "\#yyyy\/m\/d\#")
End If

DoCmd.OpenReport "reportname", _
WhereCondition:= Mid(stWhere, 6)

The problem for me is that the criteria fields are not included in the
SELECT statement as I don't want the data grouped on these fields.
It all runs fine but if I want to change something then there is a huge
hassle. I only mind that access re writes every single clause (design view
and/or SQL) meaning it is pages long and takes forever to load when I switch
views.
Just frustrated that I have to resort to cutting and pasting the SQL all the
time to get around the problem.

I did not see any grouping in the SQL you posted so I don't
know what issue you are talking about.

A different way would be to use the code I posted to
construct the entire SQL statement and get rid of the query
def:

. . .
strSQL = "SELECT ... " _
& "FROM ... " _
& "INNER JOIN ... " _
& "WHERE " & Mid(stWhere, 6)
DoCmd.OpenReport "reportname", _
OpenArgs:= strSQL

And in the report's Open event:

Me.RecordSource = Me.OpenArgs

 

[danno]

danno wrote:

I have a report that opens from a form that sets the reports criteria
(frmSelect). The row source for the report is in the form:
SELECT ....
FROM .....
INNER JOIN....
WHERE (tblTableName.FieldName = [Forms]![frmFormName]![Fieldname] Or
[Forms]![frmFormName]![Fieldname] Is Null) AND (tblTableName.FieldName2 =
[Forms]![frmFormName]![Fieldname2] Or [Forms]![frmFormName]![Fieldname] Is
Null) AND ....

There are about 8 fields used as criteria and the report returns the
criteria as entered into frmSelect (all records for field if left null)

The problems I have are these:
1. Access for some reason re-writes the above code in SQL listing every
single combination meaning it takes a long time to switch between the
rowsource design view and SQL - can this be stopped?

2. The report actually shows averages and standard deviations of data which
is what I want but I would also like to display how many records this
calculated data is based on - any ideas on how best to do this?


The query's SQL is arranged in a manner that Access thinks
is appropriate. How it is presented in the SQL view window
or in the query designer window may be confusing to most
people, but, beyond that, it doesn't matter because the
query plan that ends up being run be the database engine is
optimized in many ways that may or may not make sense to
you.

in the case of opening a report (or form), you can avoid all
that by removing the criteria from the query and using the
OpenReport (or OpenForm) method's WhereCondition arguments
instead. That requires some VBA code in the procedure that
opens the report. The general idea is something like:

Dim stWhere As String

' Number field
If Not IsNull(Me.Fieldname) Then
stWhere = stWhere & " And FieldName = " & Me.Fieldname
End If

' Text field
If Not IsNull(Me.Fieldname2) Then
stWhere = stWhere & " And FieldName2 = """ & Me.Fieldname2
& """"
End If

' Date field
If Not IsNull(Me.Fieldname3) Then
stWhere = stWhere & " And FieldName3 = " & _
Format(Me.Fieldname3, "\#yyyy\/m\/d\#")
End If

DoCmd.OpenReport "reportname", _
WhereCondition:= Mid(stWhere, 6)

The problem for me is that the criteria fields are not included in the
SELECT statement as I don't want the data grouped on these fields.
It all runs fine but if I want to change something then there is a huge
hassle. I only mind that access re writes every single clause (design view
and/or SQL) meaning it is pages long and takes forever to load when I switch
views.
Just frustrated that I have to resort to cutting and pasting the SQL all the
time to get around the problem.

I did not see any grouping in the SQL you posted so I don't
know what issue you are talking about.

A different way would be to use the code I posted to
construct the entire SQL statement and get rid of the query
def:

. . .
strSQL = "SELECT ... " _
& "FROM ... " _
& "INNER JOIN ... " _
& "WHERE " & Mid(stWhere, 6)
DoCmd.OpenReport "reportname", _
OpenArgs:= strSQL

And in the report's Open event:

Me.RecordSource = Me.OpenArgs

Thanks again for the tips. I understand I can construct the entire SQL
statement and not just the WHERE portion and get rid of the query def as you
suggest and its probably what I will do.
However, it really makes life hard when you really just want to "play
around" with the query def and see what results are returned.
With a complicated query def I usually just start small and gradually add
elements that I want. I don't want to have to run the report over and over
adding and deleting fields in it so I can see what is returned.
I understand this may not make sense to everyone (anyone?) and I think you
have answered my original question by directing me towards an alternative. ie
there is no way to stop access from doing it

Thanks again,
Danno

 

[Marshall Barton]

:
danno wrote:

I have a report that opens from a form that sets the reports criteria
(frmSelect). The row source for the report is in the form:
SELECT ....
FROM .....
INNER JOIN....
WHERE (tblTableName.FieldName = [Forms]![frmFormName]![Fieldname] Or
[Forms]![frmFormName]![Fieldname] Is Null) AND (tblTableName.FieldName2 =
[Forms]![frmFormName]![Fieldname2] Or [Forms]![frmFormName]![Fieldname] Is
Null) AND ....

There are about 8 fields used as criteria and the report returns the
criteria as entered into frmSelect (all records for field if left null)

The problems I have are these:
1. Access for some reason re-writes the above code in SQL listing every
single combination meaning it takes a long time to switch between the
rowsource design view and SQL - can this be stopped?

2. The report actually shows averages and standard deviations of data which
is what I want but I would also like to display how many records this
calculated data is based on - any ideas on how best to do this?


The query's SQL is arranged in a manner that Access thinks
is appropriate. How it is presented in the SQL view window
or in the query designer window may be confusing to most
people, but, beyond that, it doesn't matter because the
query plan that ends up being run be the database engine is
optimized in many ways that may or may not make sense to
you.

in the case of opening a report (or form), you can avoid all
that by removing the criteria from the query and using the
OpenReport (or OpenForm) method's WhereCondition arguments
instead. That requires some VBA code in the procedure that
opens the report. The general idea is something like:

Dim stWhere As String

' Number field
If Not IsNull(Me.Fieldname) Then
stWhere = stWhere & " And FieldName = " & Me.Fieldname
End If

' Text field
If Not IsNull(Me.Fieldname2) Then
stWhere = stWhere & " And FieldName2 = """ & Me.Fieldname2
& """"
End If

' Date field
If Not IsNull(Me.Fieldname3) Then
stWhere = stWhere & " And FieldName3 = " & _
Format(Me.Fieldname3, "\#yyyy\/m\/d\#")
End If

DoCmd.OpenReport "reportname", _
WhereCondition:= Mid(stWhere, 6)


The problem for me is that the criteria fields are not included in the
SELECT statement as I don't want the data grouped on these fields.
It all runs fine but if I want to change something then there is a huge
hassle. I only mind that access re writes every single clause (design view
and/or SQL) meaning it is pages long and takes forever to load when I switch
views.
Just frustrated that I have to resort to cutting and pasting the SQL all the
time to get around the problem.

I did not see any grouping in the SQL you posted so I don't
know what issue you are talking about.

A different way would be to use the code I posted to
construct the entire SQL statement and get rid of the query
def:

. . .
strSQL = "SELECT ... " _
& "FROM ... " _
& "INNER JOIN ... " _
& "WHERE " & Mid(stWhere, 6)
DoCmd.OpenReport "reportname", _
OpenArgs:= strSQL

And in the report's Open event:

Me.RecordSource = Me.OpenArgs

Thanks again for the tips. I understand I can construct the entire SQL
statement and not just the WHERE portion and get rid of the query def as you
suggest and its probably what I will do.
However, it really makes life hard when you really just want to "play
around" with the query def and see what results are returned.
With a complicated query def I usually just start small and gradually add
elements that I want. I don't want to have to run the report over and over
adding and deleting fields in it so I can see what is returned.
I understand this may not make sense to everyone (anyone?) and I think you
have answered my original question by directing me towards an alternative. ie
there is no way to stop access from doing it

I think everyone agrees that the way Access reconstructs
queries can be a serious pain. This is especially irksome
when you work to get it just the way you want it and the
next time you view the design or SQL, it is all rearranged
in some nearly incomprehensible way. There are cases where
this is such a problem that some folks have gone so far as
to keep the SQl in separate text files for editing and use
code or Copy/Paste to bring it back into Access.

If playing around with different views of a set of data is
really important, it might be worth the effort to create an
unbound form where you can specify the desired data. The
form's code can construct the query with the specified data
fields and the report's Open event procedure can manipulate
the report and its control's properties to display the
query's data as specified on the form.