questiion about using <All> in a combobox

[cinnie]

hello

I have a form with a combobox having Row Source:

SELECT qryF.FID, qryF.FName FROM qryF
ORDER BY qryF.FID ASC;

'FID is autonumber, FName is text

When I click a command button, a Report opens. The Report is based on
qryAtt and has FILTER: FID = Forms!frmAtt!cboSelectF.

All of this works perfectly. Next I added <All> to the form's combobox by
changing the Row Source to:

SELECT qryF.FID, qryF.FName FROM qryF
UNION SELECT "*", "<All>" FROM qryF
ORDER BY qryF.FID ASC;

My problem is with how to get the Report's Filter to accept <All>.

a) Filter: FID = Forms!frmAtt!cboSelectF
gives Error 3071 (Reserved Error) when <All> is selected. The other choices
work fine.

b) Filter: FID Like Forms!frmAtt!cboSelectF
accepts the <All>, but for some reason it doesn't get all of the values,
just most of them. I'm not sure why.

I'd appreciate some tips on how to write the Report's Filter. Thanks.

 

[Mike Painter]

SELECT qryF.FID, qryF.FName FROM qryF
UNION SELECT "*", "<All>" FROM qryF
ORDER BY qryF.FID ASC;

My problem is with how to get the Report's Filter to accept <All>.

SELECT qryF.FID, qryF.FName FROM qryF
UNION SELECT "*" FROM qryF
ORDER BY qryF.FID ASC;

Will select all records.
"<all>" is meaningless in Access as far as I know.

 

[Douglas J. Steele]

SELECT qryF.FID, qryF.FName FROM qryF
UNION SELECT "*" FROM qryF
ORDER BY qryF.FID ASC;

Will select all records.
"<all>" is meaningless in Access as far as I know.

Um, that's invalid SQL: you have a different number of fields in the two
subqueries.

 

[Douglas J. Steele]

If you're not getting all the records with FID Like Forms!frmAtt!cboSelectF,
then you must have some records where FID is Null.

Try using

(FID = Forms!frmAtt!cboSelectF) OR (Forms!frmAtt!cboSelectF = "*")

 

[cinnie]

But the two SELECT statements that are being united must have the same number
of fields. My problem is not getting the <All> to work in the combobox, it
is in writing the FILTER in the Report filter section so that it processes
the <All> as expected.

 

[DrGUI]

Try the following:

Set your combobox rowsource to:

SELECT qryF.FID, qryF.FName FROM qryF
UNION
SELECT -1, "<All>" FROM qryF
ORDER BY 1 ASC;

Then on the command button's onClick event:

dim strWhere as string

strWhere =""
if Me.cboSelectF.value > 0 then
strWhere = "FID = " & Me.cboSelectF.value
endif

DoCmd.OpenReport "YourReportName", acViewPreview, , strWhere