questiion about using <All> in a combobox
in [Forms]
|
Prev: Suming Data in a Calculeted Control
Next: programming
From: cinnie on 18 Mar 2010 22:14 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. -- cinnie -- cinnie
From: Mike Painter on 18 Mar 2010 22:46 cinnie wrote: > > 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.
From: Douglas J. Steele on 19 Mar 2010 08:12 "Mike Painter" <md.painter(a)sbcglobal.net> wrote in message news:VzBon.42563$sx5.27457(a)newsfe16.iad... > cinnie wrote: > > >> 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. Um, that's invalid SQL: you have a different number of fields in the two subqueries. -- Doug Steele, Microsoft Access MVP http://www.AccessMVP.com/DJSteele (no e-mails, please!)
From: Douglas J. Steele on 19 Mar 2010 08:14 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 = "*") -- Doug Steele, Microsoft Access MVP http://www.AccessMVP.com/DJSteele (no e-mails, please!) "cinnie" <cinnie(a)discussions.microsoft.com> wrote in message news:B67D6717-262D-4D98-A646-16ED1D5333AC(a)microsoft.com... > 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. > -- > cinnie > -- > cinnie
From: cinnie on 19 Mar 2010 08:25
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. -- cinnie "Mike Painter" wrote: > cinnie wrote: > > > > 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. > > > . > |