checkbox as query criteria

Hi,
in an Access form i've put a combo box that has a query as data
source. On this form I'd like to add a check box that, when it is
selected, adds the "is not null" criteria on a query field.
There is a simple way to do this?
Thank you very much for help.

--

Remigio

www.sacraspina.it
www.amicitosondoro.it
www.icmonteodorisio.it
www.parrocchiacupello.it
www.cralnuovainiziativa.it
www.associazionehistonium.it
0
remigio
5/1/2010 12:55:14 PM
access 16762 articles. 2 followers. Follow

4 Replies
1039 Views

Similar Articles

[PageSpeed] 22

On Sat, 1 May 2010 05:55:14 -0700 (PDT), remigio <linoreale@gmail.com>
wrote:

Create two queries. Then switch between them based on your needs. For
example in the myCheckBox_AfterUpdate event you could write:
if me.myCheckbox.value = True then
  Me.myCombobox.RowSource = "query2"
else
  Me.myCombobox.RowSource = "query1"
end if
(of course you replace myObjectNames with yours)

-Tom.
Microsoft Access MVP


>Hi,
>in an Access form i've put a combo box that has a query as data
>source. On this form I'd like to add a check box that, when it is
>selected, adds the "is not null" criteria on a query field.
>There is a simple way to do this?
>Thank you very much for help.
0
Tom
5/1/2010 1:04:03 PM
On 1 Mag, 15:04, Tom van Stiphout <tom7744.no.s...@cox.net> wrote:
> On Sat, 1 May 2010 05:55:14 -0700 (PDT), remigio <linore...@gmail.com>
> wrote:
>
> Create two queries. Then switch between them based on your needs. For
> example in the myCheckBox_AfterUpdate event you could write:
> if me.myCheckbox.value =3D True then
> =A0 Me.myCombobox.RowSource =3D "query2"
> else
> =A0 Me.myCombobox.RowSource =3D "query1"
> end if
> (of course you replace myObjectNames with yours)
>
> -Tom.
> Microsoft Access MVP
>
> >Hi,
> >in an Access form i've put a combo box that has a query as data
> >source. On this form I'd like to add a check box that, when it is
> >selected, adds the "is not null" criteria on a query field.
> >There is a simple way to do this?
> >Thank you very much for help.

Thank you very much.

--

Remigio

www.sacraspina.it
www.amicitosondoro.it
www.icmonteodorisio.it
www.parrocchiacupello.it
www.cralnuovainiziativa.it
www.associazionehistonium.it
0
remigio
5/1/2010 2:31:12 PM
Remigio:

You can in fact do it with a single query as the RowSource of the combo box,
e.g.

SELECT SomeField
FROM SomeTable
WHERE Form!YourCheckBox = FALSE
OR (Form!YourCheckBox = TRUE 
AND SomeOtherField IS NOT NULL)
ORDER BY SomeField;

Note the use of the Form property to reference the current form rather than
referencing it as a member of the Forms collection.

This would return all rows from the table if the check box is not checked,
and only those where SomeOtherField contains a value if the check box is
checked.  Be sure that the check box's default value is False by putting:

Me.YourCheckBox = False

in the form's Open event procedure.  Otherwise it will be Null when the form
opens, until checked by the user.  In the checkbox's AfterUpdate event
procedure requery the combo box:

Me.YourComboBox.Requery

Ken Sheridan
Stafford, England

remigio wrote:
>Hi,
>in an Access form i've put a combo box that has a query as data
>source. On this form I'd like to add a check box that, when it is
>selected, adds the "is not null" criteria on a query field.
>There is a simple way to do this?
>Thank you very much for help.
>
>--
>
>Remigio
>
>www.sacraspina.it
>www.amicitosondoro.it
>www.icmonteodorisio.it
>www.parrocchiacupello.it
>www.cralnuovainiziativa.it
>www.associazionehistonium.it

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access/201005/1

0
KenSheridan
5/1/2010 4:39:59 PM
On 1 Mag, 18:39, "KenSheridan via AccessMonster.com" <u51882@uwe>
wrote:
> Remigio:
>
> You can in fact do it with a single query as the RowSource of the combo b=
ox,
> e.g.
>
> SELECT SomeField
> FROM SomeTable
> WHERE Form!YourCheckBox =3D FALSE
> OR (Form!YourCheckBox =3D TRUE
> AND SomeOtherField IS NOT NULL)
> ORDER BY SomeField;
>
> Note the use of the Form property to reference the current form rather th=
an
> referencing it as a member of the Forms collection.
>
> This would return all rows from the table if the check box is not checked=
,
> and only those where SomeOtherField contains a value if the check box is
> checked. =A0Be sure that the check box's default value is False by puttin=
g:
>
> Me.YourCheckBox =3D False
>
> in the form's Open event procedure. =A0Otherwise it will be Null when the=
 form
> opens, until checked by the user. =A0In the checkbox's AfterUpdate event
> procedure requery the combo box:
>
> Me.YourComboBox.Requery
>
> Ken Sheridan
> Stafford, England
>
>
>
> remigio wrote:
> >Hi,
> >in an Access form i've put a combo box that has a query as data
> >source. On this form I'd like to add a check box that, when it is
> >selected, adds the "is not null" criteria on a query field.
> >There is a simple way to do this?
> >Thank you very much for help.
>
> >--
>
> >Remigio
>
> >www.sacraspina.it
> >www.amicitosondoro.it
> >www.icmonteodorisio.it
> >www.parrocchiacupello.it
> >www.cralnuovainiziativa.it
> >www.associazionehistonium.it
>
> --
> Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Foru=
ms.aspx/access/201005/1

Thank you so much!
0
remigio
5/1/2010 7:36:05 PM
Reply:

Similar Artilces:

Find field query
I have inherited a Access 2000 database which up until now has allowed me to search for a record using the "Find" action searching by a field. Now if I add a new record and save it, I then go back to search for it using the above method and it says that Access can not find the record even though I know the record is there when I check on the original table. What has happened and how do I resolve it? Urgent assistance please!!!!!!!!!!!!! ...

load query results into ado recordset
I want to loop through query results in vba. This query is in the same access db as the vba module. I thought I wanted to load the query results into an ado recordset, but cannot find the syntax. I can re-create the query in vba, substituting the wildcard character "%" for "*", and that works, but is not what I want. I want to be able to manipulate the query in access, then process the results in vba, one row at a time. Any suggestions? ...

Why does my update query appear to do nothing?
Hi I am running the following code: Dim strsql As String strsql = "UPDATE TM_Boat SET TM_Boat.FK_Customer = " & [Forms]! [FM_ChangeBoatOwner]![Combo36] & _ " WHERE (((TM_Boat.PK_Boat)=" & [Forms]![FM_ChangeBoatOwner]! [TXT_BoatNum] & "));" Debug.Print strsql CurrentDb.Execute strsql, dbFailOnError MsgBox CurrentDb.RecordsAffected & " Records updated" It keeps telling me "0 Records Updated", although it IS updating the record! Any idea why? Stapes Probably because the database instance created wi...

RecordSource Query Help
Hello, I'm new to Access, and am learning it on the job. I'm working wit Access 2002. I recently took the forms from one database and paired them with th tables from another. It was mostly seamless, but when I open particular form, I get an error dialog box stating: "The Record Source '<SQL STATEMENT>' specified on this form or repor does not exist." The thing is, I can't find this SQL Statement anywhere. I've trie viewing the RecordSource field (in Properties) for every Form in m database, and it's not present in any of them. I also searc...

Creating a Group By query from more than one table
Is it possible when creating a report that includes the results of this query, to also create a pie chart or bar graph using the query information? And is it possible to tally results from more than one table list on the same Group By query? -- L. Edwards On Tue, 26 Jun 2007 16:46:00 -0700, L. Edwards <LEdwards@discussions.microsoft.com> wrote: >Is it possible when creating a report that includes the results of this >query, to also create a pie chart or bar graph using the query information? Sure. They'd be independent objects though. >And is it possible to tally...

Query Parameter
Is there a way to use a query parameter with an expression. Or reference a textbox value within an expression. In the expression below I would like to replace the 9/1/2010 date with a reference to a textbox or parameter message box: MeasurementDays: IIf(DateDiff("d",qryActive_Agent![Hire Date],#9/1/2010#)<365,DateDiff("d",qryActive_Agent![Hire Date],#9/1/2010#)-nz([SumOfExcludedDays]),365-nz([SumOfExcludedDays])) Thanks for any help This should work in most queries. MeasurementDays: IIf(DateDiff("d",qryActive_Agent![Hire Date],CDate([Wha...

Form parameter Query
I'm hoping someone can help me with this . I'm trying to get my query to use a form to get the query parameters. When I run the query, it displays the text "[Forms]![frmEE]![cboEE]" as a prompt rather than displaying the form where the user can make a selection. I'm using the build wizard to construct the following query: SELECT Employees.* FROM Employees WHERE (((Employees.LastName)=[Forms]![frmEE]![cboEE])); -- Any and all contributions are greatly appreciated ... Best Regards Thanks Karl. You've been a big help. Have a good one. "KARL DEWEY"...

Printing Query
Is there a way of printing an area which printed landscape would spread over 2 pages as 1 page (not by fitting to page) but by printing what would appear on page 2 below that on page 1 as the data is not deep (area to be printed is P5..AK16) ...

SQL query for Smartlist
Is there any way to create a field in a SQL query that will appear in Smartlist Builder as available for choice in the Negative Values tab? Laura In SQL, you do not "create a field", you define columns, but more specifically in your case, you will be reading a column and creating a transformation for the row values in that column to reflect a negative value. You can accomplish this as follows: CREATE VIEW fooView AS SELECT col1, -col2, -col3,.., FROM fooTable WHERE fooExpression; GO Note that by inverting the sign of the numeric columns col2, and col3, I am abl...

What type of query?
Hi Everyone, I have records in my Access Database Version 9.0 with these basic fields, LName, FName, PtNumber, AdmitDate, DCDate. The Names and PtNumber are already entered and there are 2000 records. Each week I get a report that has all of our dicharged patients with many fields including PtNumber, AdmitDate and DCDate populated (about 100 records a week). So far we are manually entering these records. I think I read somewhere that info from excel can be imported to access so I am wondering how I can do this. I used a dataminer program (Monarch) and exported these fields AcctNum...

Vlookup query that checks 2 conditions
I've posted this before without too much luck so here it goes again... I'm trying to automate the creation of a vendor report that lists al vendor sales. Basically I need a formula that, based on a unique vendo number, will: - check colomn A for the vendor number match - then check column B to see if there is a buyer number (which mean that the item is sold) - then dumps then 'nth' occurance of the value / text from the column specify (8) I'm currrently using the formula: {=IF(Catalog!B2:B428 0,INDEX(VendorReportData,SMALL(IF(VendorReportData=$N$13,ROW(Catalog!$A$2:$...

Access 2002 queries
Why , when accessing a table in the database , you can have greater than 127 fields for an insert and retreive but are limited to 127 fields for an update. Guessing that you have something like UPDATE ... Set TableA.FieldA = TableB.FieldA, TableA.FieldB = TableB.Fieldb GUESSING (again) that is probably being counted as four fields in the query. So if you try to update more than 127 fields (2*127=254; 2*128 = 256) you are running into the maximum of 255 fields allowed in a query. If you really need to update more than 127 fields at one time then I suggest you will have to break the upda...

Making attribute a checkbox
How do you make an attribute a checkbox -- donn Hi, In Customization, open the entity. Than open click on Forms and Views and open the Form. Double click on bit type of field. In new window, the second tab will be formatting. You will have option in bottom, "Control Formatting" here you can choose the desrired format. -- PLEASE do click on Yes or No button if this post was helpful or not for our feedback. uMar Khan Email for direct contact: imumar at gmail dot com "Donald" wrote: > How do you make an attribute a checkbox > -- > donn Create a bit field an...

Query combining the tables
cons dcity dst dzip ocity ost ozip e f jk 7789 fg cd 989 c o lk 970 sf9 cdf 9890 cdd yf mh 979 hgg mkhi 7699 cons dcity dst dzip ocity ost ozip a d ak 560 b c 789 b e ck 869 de ef 970 c o lk 970 bh mk 976 the output qery should combine data from both tables, should include all rows from both tables and should have the following fields.------ the data should be combined from both tables across consignee, dcity, dstate and dzip fields cons dcity dst dzip table1.ocity table1.ost table1.ozip table2.ocity t...

nz function problem-query to form
I am using the nz function in my queries. The function works at that level. However, when I try to open the form it won't allow me to. If I take out the nz function everything works fine. Any suggestions Could be an issue with the data type. Try an IIf() expression instead. More details and example: http://allenbrowne.com/QueryPerfIssue.html#Nz -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "southbender" <southbender@disc...

Dialogsheet Checkbox to Select All Checkboxes
I am using a dialogsheet to get user input. The dialogsheet is created at runtime. The amount of checkboxes on the dialogsheet varies based on how many tabs are hidden. I would like to have a checkbox on the dialogsheet that allows users to "select all" checkboxes. Code Example: Dim SheetCount As Integer Dim CurrentSheet As Worksheet Dim PrintDlg As DialogSheet Dim cb As CheckBox Set CurrentSheet = ActiveSheet Set PrintDlg = ActiveWorkbook.DialogSheets.Add 'some code used to loop and build checkboxes not shown here If SheetCount <> 0 Then If ...

xpath query with more specifications
Hi group, I've written a program which uses this XPath query: //*[contains(translate(.,\"ABCDEFGHIJKLMNOPQRSTUVWXYZ\", \"abcdefghijklmnopqrstuvwxyz\"),"SomeString"] as you better know, it finds and locates "SomeString" under any path within an xml file. The thing is that I don't know how to make it ignore those nodes which have children and focus on nodes that have a text only. Like: <Parent> ignore <LastChild>SomeString</LastChild> OK </Parent> ignore You can use the text() function to evaluate only the text...

Select query help
I have tblLog. It's fields are: Call: text Freq: single Mode: text CID: text QSL_R: text Credited: T/F I need the SQL to return all records where 'Credited' is false, 'QSL_R' <> NULL or blank, and whose combination of 'Freq' and 'Mode' for a given 'CID' have 'Credited' = false, but not if that record has a 'mate' whose 'CID', 'Freq/Mode', 'QSL_R' all match, and 'Credited' is True. I'm not sure if my description is understandable, so here is an example. Call Freq Mode...

Business Portal deployment and queries #2
Hi, I would like to know how to transfer our customized queries in a BP deployement. When we deployed BP from our test environnement to production it worked smooltly for the sharepoint part but the problem is that the queries didn't follow... We don't want to recreate all customized queries on each deployement ! I pretty sure that the queries are in the Dynamics database. I tried to backup the test version of that DB and restore it in prod but after that BP refused to start-up... So, is the solution to export only certain Dynamics tables data ? If yes, which tables ? M...

database query select the "top record" only
good afternoon All, I would like to know the SQL to select only the "top" record of a query sorted by date/number or operation of numbers e.g. max of date, min of stock, etc so I only retrieve the first and most important value instead of a set of several values. here below an EG from a query I use every day: SELECT pordtl.pdtiid, pordtl.pdtvds, pordtl.pdtluc, pordtl.pdtvum, pordtl.pdtwhs, pordtl.pdtode, pordtl.pdtvdr FROM beth.pordtl pordtl GROUP BY pordtl.pdtiid, pordtl.pdtvds, pordtl.pdtluc, pordtl.pdtvum, pordtl.pdtwhs, pordtl.pdtode, pordtl.pdtvdr HAVING (pordtl.pdtiid I...

Access Report on Parameter Query
I have a report established identifying data based on a query for a single report month. Amounts are identifed by program and totaled. One of the categories are then segregated and the balance totaled. The report is built on the query but the formula used to segregate that category pulls from the table as follows: =DLookup(“[SAL & BEN]”,”CLIENT SUPPORTS”,”[MONTH REPORT]=#”&[MONTHREPORT] &”# AND [ADMIN]=’PA’ “) I need to segregate the same category for the parameter query which pulls multiple report months but have been unsuccessful thus far. Suggestions? If ...

checkbox with relative reference?
I have a checkbox in E5 controlling E5. If I copy it to K11, I want it to control K11. Now, how do I give it this kind of relative reference? Thanks, Sven Sven, I take it you're going to have to do this more than a few times. I suggest you create the checkbox objects in code rather than using the Excel Control Toolbox. Add the checkboxes in the workbook open event and then you can set the attributes of each using variables. Ross "Sven Berg" <wiesel69@gmx.de> wrote in message news:060fd6fc-4258-4b48-b67b-c6909c0f2c45@8g2000hsu.googlegroups.com... >I have a check...

Removing choices from combobox query but stilling showing for older entries
I have a form, frmMain that lists staff names in a combobox from a query into a lookup table. The table, lkpStaff has 3 fields, StaffID, Staff and ShowInViews. The RowSource Query for the combobox is: SELECT lkpStaff.ThingID, lkpStaff.Thing FROM lkpStaff WHERE (((lkpStaff.ShowInViews)=True)); The combobox ControlSource is the field Staff in tblMain which is populated from frmMain. By unchecking ShowInViews for staff that are no longer employed I can remove them from the combo's dropdown. However, of course, it also removes those staff names from older entries. I w...

Checkbox checked then show more checkboxes
I have added checkboxes from Control Toolbox and they seem to work fine. I just want to make one of them lets say checkbox3 to show more checkboxes when checkbox3 is marked. How do I do that? I'd add them all to exactly where I wanted them. But then have the "master" checkbox just unhide/hide the others. Option Explicit Private Sub CheckBox3_Click() Dim ShouldBeVisible As Boolean ShouldBeVisible = CBool(Me.CheckBox3.Value = True) Me.CheckBox1.Visible = ShouldBeVisible Me.CheckBox2.Visible = ShouldBeVisible Me.CheckBox4.Visible = ShouldBeVisible ...

Copying Checkbox Values into a different worksheet
Hi there, I'm trying to copy the value of several checkboxes to another worksheet. This works basically all fine by using the code below Sub EvaluateCB() ActiveWorkbook.Worksheets("Survey").Select Range("A1").Select d = Worksheets("Sheet1").CheckBox1.Value Selection = d End Sub But, as there are about 40 checkboxes I wonder whether there is a way to do this all automatically, i.e. by some for loop going through all the checkboxes. As I am a complete rookie in Excel I appreciate any hint / suggestions. Many thanks, Thiery You ...