SQL syntax from Filter by Form

[Guest]

I am trying to print a dynaset obtained from a Filter by Form. The
OpenReport fails if the resulting recordset is too big. The where clause is
being passed a comma separated list of the IDs of each record in the filtered
set thus exceeding the 32768 character limit. I would like to just pass it
the SQL of the filter that created the set.
Any ideas?

 

[Rob Oldfield]

Grab the me.filter property of the filtered form and use it as the where
clause on the end of the SQL with no filter... something like...

sql="select a, b, c from d where "&me.filter

 

[Dirk Goldgar]

Grab the me.filter property of the filtered form and use it as the
where clause on the end of the SQL with no filter... something like...

sql="select a, b, c from d where "&me.filter

There's one big complication to Rob's solution. If the form is filtered
on any combo or list boxes whose displayed columns are different from
their bound columns, then the filter will make reference to hidden
"lookup" subqueries that won't be available to your report. If the form
doesn't have any such controls, or isn't filtered on them, fine -- but
if it is, it's rather a complicated mess to sort out.

 

[Guest]

Thank you for your help. They are from combo boxes displaying column 2. I
used a debug.print me.filter and the filter is ((Lookup_myfield.column2 =
"Value")). Now when the report opens a "Enter Parameter Value" dialog box
pops asking for Lookup_myfield.column2

 

[Dirk Goldgar]

Thank you for your help. They are from combo boxes displaying column
2. I used a debug.print me.filter and the filter is
((Lookup_myfield.column2 = "Value")). Now when the report opens a
"Enter Parameter Value" dialog box pops asking for
Lookup_myfield.column2

That makes it tough. At one time, I started out to develop a solution
that would build a WHERE clause and incorporate subqueries to deal with
this problem, but I got bogged down and put it aside.

In the absence of such a solution, and faced with the limitations you
have, I can think of three other approaches besides the one you've
already tried.

Approach 1
------------
Have a boolean (yes/no) field in the table which you use solely for the
purpose of flagging the records you want to report. When you click the
button to open your report, you run an update query that clears all
these flags, and then run through the filtered recordset (via the form's
recordset clone) and set the flag field to True for every record in the
recordset. Your report would be based on a query that extracts only the
flagged records.

Disadvantage: you have to update the records just to report them.


Approach 2
------------
Have a work table in which you will store only the primary key of the
records you want to report. When you click the button to open your
report, you run a delete query that empties this table, and then run
through the form's filtered recordset and add the primary key of each
record to the work table. Your report is then based on a query that
inner joins the form's recordsource table to the work table on the
primary key, thus excluding all records that aren't represented in the
work table.

Disadvantage: constantly adding and deleting records from the work table
will encourage database bloat, forcing more frequent compaction. This
may or may not be a problem, depending on the size of the table, and
also could be eliminated by using a temporary, external database for the
work table. I have code for this, if you need it.


Approach 3
------------
Base your report on a query that calls a function for each record to see
if the record is in the form's recordset. In other words, your query
would be something like:

SELECT * FROM MyTable
WHERE fncIsInFormRecordset("YourForm", "ID", [ID])

Here's code for such a function I just threw together, along with a
helper function:

'----- start of code -----
'*** requires reference to the DAO object library ***

Function fncIsInFormRecordset( _
FormName As String, _
FieldName As String, _
FieldValue As Variant) _
As Boolean

With Forms(FormName).RecordsetClone
If .RecordCount = 0 Then Exit Function
.FindFirst "[" & FieldName & "] = " & _
fncSQLLiteral(FieldValue, .Fields(FieldName).Type)
fncIsInFormRecordset = Not .NoMatch
End With

End Function

Function fncSQLLiteral( _
ArgValue As Variant, ValType As Integer) _
As String

Select Case ValType

Case dbDate
If Len(ArgValue & vbNullString) = 0 Then
fncSQLLiteral = "Null"
Else
fncSQLLiteral = Format(ArgValue, "\#mm/dd/yyyy\#")
End If

Case dbText, dbMemo
If IsNull(ArgValue) Then
fncSQLLiteral = "Null"
Else
fncSQLLiteral = _
Chr(34) & _
Replace( _
ArgValue, """", """""", , , vbBinaryCompare _
) & _
Chr(34)
End If

Case Else
If Len(ArgValue & vbNullString) = 0 Then
fncSQLLiteral = "Null"
Else
fncSQLLiteral = ArgValue
End If

End Select

End Function

'----- end of code -----

Disadvantages: This is bound to be relatively slow. Whether that makes
a difference in practice remains to be seen.

 

[Guest]

I attempted your first suggestion and am successful at flagging the desired
records although the update is a little slow. When I invoke the DoCmd to
open the report with Flag = True as the criteria I get all records, not just
the flagged records.

Thank you for your help. They are from combo boxes displaying column
2. I used a debug.print me.filter and the filter is
((Lookup_myfield.column2 = "Value")). Now when the report opens a
"Enter Parameter Value" dialog box pops asking for
Lookup_myfield.column2

That makes it tough. At one time, I started out to develop a solution
that would build a WHERE clause and incorporate subqueries to deal with
this problem, but I got bogged down and put it aside.

In the absence of such a solution, and faced with the limitations you
have, I can think of three other approaches besides the one you've
already tried.

Approach 1
------------
Have a boolean (yes/no) field in the table which you use solely for the
purpose of flagging the records you want to report. When you click the
button to open your report, you run an update query that clears all
these flags, and then run through the filtered recordset (via the form's
recordset clone) and set the flag field to True for every record in the
recordset. Your report would be based on a query that extracts only the
flagged records.

Disadvantage: you have to update the records just to report them.


Approach 2
------------
Have a work table in which you will store only the primary key of the
records you want to report. When you click the button to open your
report, you run a delete query that empties this table, and then run
through the form's filtered recordset and add the primary key of each
record to the work table. Your report is then based on a query that
inner joins the form's recordsource table to the work table on the
primary key, thus excluding all records that aren't represented in the
work table.

Disadvantage: constantly adding and deleting records from the work table
will encourage database bloat, forcing more frequent compaction. This
may or may not be a problem, depending on the size of the table, and
also could be eliminated by using a temporary, external database for the
work table. I have code for this, if you need it.


Approach 3
------------
Base your report on a query that calls a function for each record to see
if the record is in the form's recordset. In other words, your query
would be something like:

SELECT * FROM MyTable
WHERE fncIsInFormRecordset("YourForm", "ID", [ID])

Here's code for such a function I just threw together, along with a
helper function:

'----- start of code -----
'*** requires reference to the DAO object library ***

Function fncIsInFormRecordset( _
FormName As String, _
FieldName As String, _
FieldValue As Variant) _
As Boolean

With Forms(FormName).RecordsetClone
If .RecordCount = 0 Then Exit Function
.FindFirst "[" & FieldName & "] = " & _
fncSQLLiteral(FieldValue, .Fields(FieldName).Type)
fncIsInFormRecordset = Not .NoMatch
End With

End Function

Function fncSQLLiteral( _
ArgValue As Variant, ValType As Integer) _
As String

Select Case ValType

Case dbDate
If Len(ArgValue & vbNullString) = 0 Then
fncSQLLiteral = "Null"
Else
fncSQLLiteral = Format(ArgValue, "\#mm/dd/yyyy\#")
End If

Case dbText, dbMemo
If IsNull(ArgValue) Then
fncSQLLiteral = "Null"
Else
fncSQLLiteral = _
Chr(34) & _
Replace( _
ArgValue, """", """""", , , vbBinaryCompare _
) & _
Chr(34)
End If

Case Else
If Len(ArgValue & vbNullString) = 0 Then
fncSQLLiteral = "Null"
Else
fncSQLLiteral = ArgValue
End If

End Select

End Function

'----- end of code -----

Disadvantages: This is bound to be relatively slow. Whether that makes
a difference in practice remains to be seen.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

I attempted your first suggestion and am successful at flagging the
desired records although the update is a little slow. When I invoke
the DoCmd to open the report with Flag = True as the criteria I get
all records, not just the flagged records.

Obviously there's something wrong either with your flagging process
(such that all records end up being flagged) or with your filtering of
the report (such that it includes all records whether they are flagged
or not). Post your code for both processes and we'll try to spot the
problem.

 

[Guest]

The flagging process is working. Here is the print command:
DoCmd.OpenReport rept, acViewPreview , , [Flag] = True
rept is a report name variable, Flag is the field name

 

[Dirk Goldgar]

The flagging process is working. Here is the print command:
DoCmd.OpenReport rept, acViewPreview , , [Flag] = True
rept is a report name variable, Flag is the field name

If that's truly a quote of your code, the syntax is incorrect. The
WhereCondition argument must be a string that would be valid as the
WHERE clause of a SQL SELECT statement, but without the "WHERE" keyword.
Try this:

DoCmd.OpenReport rept, acViewPreview , , "[Flag] = True"

 

[Guest]

After all that I forget quotes. That worked. Thank you for all of your
assistance.

The flagging process is working. Here is the print command:
DoCmd.OpenReport rept, acViewPreview , , [Flag] = True
rept is a report name variable, Flag is the field name

If that's truly a quote of your code, the syntax is incorrect. The
WhereCondition argument must be a string that would be valid as the
WHERE clause of a SQL SELECT statement, but without the "WHERE" keyword.
Try this:

DoCmd.OpenReport rept, acViewPreview , , "[Flag] = True"

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Guest]

One more item. Is there a faster way to update the dynaset than looping
through each record? I would like to use something like
strSQL = "UPDATE Main SET Flag = " & True
CurrentDB.Execute strSQl
but I can't get CurrentDB to be the filtered dynaset.

After all that I forget quotes. That worked. Thank you for all of your
assistance.

The flagging process is working. Here is the print command:
DoCmd.OpenReport rept, acViewPreview , , [Flag] = True
rept is a report name variable, Flag is the field name

If that's truly a quote of your code, the syntax is incorrect. The
WhereCondition argument must be a string that would be valid as the
WHERE clause of a SQL SELECT statement, but without the "WHERE" keyword.
Try this:

DoCmd.OpenReport rept, acViewPreview , , "[Flag] = True"

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

One more item. Is there a faster way to update the dynaset than
looping through each record? I would like to use something like
strSQL = "UPDATE Main SET Flag = " & True
CurrentDB.Execute strSQl
but I can't get CurrentDB to be the filtered dynaset.

You *may* be able to do it by using the form's Filter property to make
an update query's WHERE clause, but that's not reliable when filtering
was performed from the Access user interface ("Filter For" or Filter by
Form), because the form's filter may use lookups for controls that have
values different from what is displayed -- combo boxes and such. The
most reliable method is to loop through the form's RecordsetClone.