Queries :: If Check Box Is Ticked Ignore 1 Criteria

Oct 15, 2014

I have 3 queries populating 3 subreports on a report, i have a checkbox on my main form i launch the report from - i reference it in a filter on the form to ignore certain error types - id like it do to the same in the queries that populate the reports

So the reports atm give - an over all count of error types, a count per person and then a breakdown on type per person

Thats all fine but id like the option to ignore one type of error (as we sometimes look at it and sometimes dont).

View Replies


ADVERTISEMENT

Queries :: Ignore Wildcard If Criteria Is Blank

Dec 16, 2014

In a database am building, I want to run a query with the criteria dependant on which field the user populates in a form.

The form has a number of fields that the user can select from including our reference number, the client's reference number and the site address.

I would like the user to be able to select the site address using a wildcard so that they can enter a part of the address such as "This Street" instead of "45 This Street" and the user be presented with all of the records matching "This Street".

I tried using the criteria:

Code:
Like "*" & [Forms]![SearchJobs]![SearchAddressLine1] & "*"

Which works perfectly as long as this field is populated. If this field is not populated, entering details in any other field bring up every record in the database.

Code:
IIf(IsNull([Forms]![SearchJobs]![SearchAddressLine1]),Null,[Forms]![SearchJobs]![SearchAddressLine1] & "*")

The full sql of my query is:

Code:
SELECT Jobs.JobsSalesEnquiryRecordNumber, Jobs.JobsJobStatus, Jobs.JobsEnquiryDate, Jobs.JobsTakenBy, ClientDetails.ClientDetailsURN, ClientDetails.ClientDetailsName, ClientDetails.ClientDetailsAddressLine1, ClientDetails.ClientDetailsAddressLine2, ClientDetails.ClientDetailsAddressLine3, ClientDetails.ClientClientPostCode, Jobs.JobsClientJobNumber, Jobs.JobsAlternativeClientJobNumber, Jobs.JobsClientContact,

[Code] ....

View 12 Replies View Related

Count Ticked Check Box

Apr 18, 2008

hi
i have table for attendance monitor and contaion following attribute:
session ID
Student Id
Attended session which is in yes/no format or in other work it give u option to tick it

date



and now what i want to do is that create a query and tell it to only count the number of attended session which has been ticked not count all the attended session for one student ,only count the ticked one
any idea please?
thanks

View 8 Replies View Related

If Check Box Is Ticked / Data Must Be Entered

Nov 29, 2012

working through validation rules etc at the minute on my database. Have come across this one is sort of stumping me. I have a tick box in the field "Medication to be repeated" the next field after it is "Date to be repeated on". I am looking a validation rule which makes it that if the checkbox is ticked then date to be repeated must contain a valid date value.

View 8 Replies View Related

Forms :: How To Hide Record If Check Box Ticked

May 17, 2014

I have a subform that populates from a query from a table. I also have a checkbox as part of the table. If the checkbox is ticked, I don't want that record to show in the subform

View 4 Replies View Related

Forms :: Check Box Within A Form Ticked Or Not Dependent On Another Value Within A Field?

May 10, 2013

I there a way to determine whether or not my checkbox within a form is ticked or not dependent on another value within a field?

I have a field called 'DaysRemaining' and another check box field called 'Expired'. I want the expired checkbox to be ticked if the value within 'DaysRemaining' is '<0' and unticked if '>=0'. Is there a way i can do this within the control source of my check box?

View 1 Replies View Related

Forms :: Lock Individual Record When Certain Check Box Ticked?

Dec 24, 2013

I want to lock an individual record when a certain check box is ticked so it can't be edited again. I've tried setting the form to read only when this check box is ticked on the form OnCurrent event, but this locked every record and I had to remove the code to untick the check box and be able to edit the record again.

View 2 Replies View Related

Modules & VBA :: Check If Active Field With Yes / No Datatype Is Ticked Or Not

Jan 29, 2015

I am writing the following code to check if 'Active' field in table TypeTable2 is ticked or not for records. If I write the following statement to select the records from Access table where Active is not ticked then it gives "data Type mismatch in criteria expression".

Code:
strsql = "SELECT distinct EnvelopeType FROM TypeTable2 where Active='Yes'

View 3 Replies View Related

Queries :: IIF Statement In Query Criteria Based On Check Box

Jun 19, 2014

I have a form with a check box. A query is run that looks at that check box and decides what the criteria are based on that. So, if the check box is checked, it should pull in all data in the field that is a Y. If it is not checked, i want it to pull all data (Y's and N's and blanks).

here is my criteria:

IIf([Forms]![frm_Query_Form]![CheckBox]<>0,"Y","*")

This does not seem to work. I have also tried:

IIf([Forms]![frm_Query_Form]![CheckBox]<>0,"Y")
IIf([Forms]![frm_Query_Form]![CheckBox]<>0,"Y",Like "*")
IIf([Forms]![frm_Query_Form]![CheckBox]<>0,"Y","like "*"")

View 14 Replies View Related

Queries :: Left Function - NOT Ignore Leading Zeros

Aug 3, 2015

In my table I have a numerical field for case numbers. Our case numbers are in the format of YYMM000000 (YY = 2 digit year, MM = 2 digit month, remaining 0's are consecutive case numbers). In order to properly list the case numbers in descending order, I have

caseyr: Left([CaseNo],2) (which pulls the first 2 digits being the year)
officeno: Right([CaseNo],3) (which pulls the last 3 for our part of the case number)

This was working fantastic until I had to enter cases from 2009, ex: 0911000587

I have set the format for the CaseNo field to 000000000 everywhere it is. It displays the 09 cases perfectly, retaining the zero, however, my left function ignores it. In the case number of 0911000587, it pulls 91 as the first 2 digits. So, in the form that I list my cases in order, the 2009 cases are showing up first (because they're pulling as 91 instead of 09).

Is there anything I can do to force it to NOT ignore the leading zero?

View 3 Replies View Related

Problem With Criteria Using Check Box

May 27, 2005

I want to run query by using a criteria on a checkbox. The query will output the list of records if user has selected the check box. How can I write my criteria? :confused:

Thanks a bunch for any help advance...

View 2 Replies View Related

Check Box For Criteria In Make Table Query

Jul 29, 2005

having used a query to generate data from a set of records where i can select the criteria (they are two letter abrv and are in a single field in the table but there may be multiple criteria). As I would like others to do it without using the query my first thought was a form with a series of check boxes tick for inclusion in the query or not. As i have built the query am I looking at changing that or taking it all into VB and using some code to pull the relavant 'ticks' across............ and how the hell would i do that......


many thanks in anticipation......

View 1 Replies View Related

Check Box For Criteria In Make Table Query

Jul 29, 2005

having used a query to generate data from a set of records where i can select the criteria (they are two letter abrv and are in a single field in the table but there may be multiple criteria). As I would like others to do it without using the query my first thought was a form with a series of check boxes tick for inclusion in the query or not. As i have built the query am I looking at changing that or taking it all into VB and using some code to pull the relevant 'ticks' across............ and how the hell would i do that......


many thanks in anticipation......

View 2 Replies View Related

Query Based On Multiple Criteria With Check Box's

Nov 18, 2004

I have a table(Product Change) with these fields:
Tracking Number
Approved (a check box)
Engineering (text box that represents department)
Purchasing (text box that represents department)
Quality (text box that represents department)
Production (text box that represents department)
Customer Service (text box that represents department)

I want my query results to show all records that have the "Approved" check box...checked, and then only the records that have one of the Department fields with a null value.
So I'm looking to see only records that are "Approved" and out of those....only the records with at least one department field empty(Null).

Any help doing this is SQL view would be great...or even design view.

View 2 Replies View Related

Forms :: Passing Check Box Value To A Query As Criteria

Jan 30, 2014

I need to pass values of my two check boxes on my Form

Check box US and Check Box Canada
if both checked the criteria would be "UD", "ud',"b","B","us","US","CD","cd"
if Canada check box checked criteria would be "cd","CD"
if US only checked criteria would be UD", "ud',"b","B","us","US"

how to write the criteria statement on the query?this probably would be a complicated iff statement?

View 2 Replies View Related

Modules & VBA :: Find First Criteria - Check Field?

Feb 6, 2014

What is wrong with this criteria...

RecSet.FindFirst ("[SomeField] = '" & mMESTO & "'") and ([CheckField] = True)

View 5 Replies View Related

Modules & VBA :: DLookup - Check 2 Criteria In Two Different Columns In Same Table

Dec 2, 2013

I would like to make a DLookup that check 2 criteria in two different columns in the same table. The reason to use a DLookup is that I would like to check if two parts already have been linked together. The user therefore selects two values in to different comboboxes (lstLinkPart and lstLinkToPart). In this case the value of the first combobox is column: ComponentPN in tblProductLinkComponent. For the second (lstLinkToPart) column ProductPN, in the same table, has to be checked.

If both values from the comboboxes match the values in both (and only) columns I don't want to continue and made a code to be executed.

I found multiple examples on the internet for using multiple criteria but something similar to what I require.

View 6 Replies View Related

Ignore Duplicates

Aug 15, 2006

I have 2 tbl's one is class and the other is professor... in my logic i create a new class for each student however, I am trying to make label for the professor and since a professor can teach more than one class i need only one label. How do i do this

View 1 Replies View Related

How To Ignore Parameter?

Oct 18, 2007

Hi there

I've been looking for this one for a while. In my query I'm using a date field to extract dates using the between And function. I'm refrencing to txt boxes within a dialog form within the query.

For example:
Between [Forms]![frmViewByCatagory]![TxtStartDate] And [Forms]![frmViewByCatagory]![txtEndDate]

The field actually only lists years rather whole dates .
I want to find a way to add an expression so that I can leave the txt boxes blank and access will ingnore the txt boxes and give me all the records.

Does anyone have an idea what I can do here. I thought the IIF function along with Null might work but I don't know the syntax.

Thanks

View 3 Replies View Related

Enable Combo When Ticked.

Mar 17, 2006

Hi.
I have sort of found an answer to my problem by this thread (Thread (http://www.access-programmers.co.uk/forums/showthread.php?t=20358&highlight=show+field+ticked))
but it doesn't seem to be working.

I have (amongst others) a tickbox and a combo.

Basically, I would like it so if the tickbox is ticked, the combo is enabled, and no tickbox and it is disabled.

I have been putting this code:-

If Me.Helpdesk_Call = -1 Then
Me.Combo23.Enabled = False
Else
Me.Combo23.Enabled = True
End If

on the forms onCurrent event, and then changing the onUpdate event of the combo, but is doesn't seem to work. I have tried numerous amounts of variations, but nothing seems to be working.

Can someone help?

Thanks.

Frank.

View 3 Replies View Related

Enabling Comments When A Box Is Ticked

Aug 22, 2006

Hey,

In the form I am creating, I have checkboxes. Some of these checkboxes, when ticked, need additional comments entered into a comments box.

Only when the box is ticked, the comments box should appear/become enabled.

Any pointers in the general direction I should help would be greatly appreciated.

Thanks.

View 6 Replies View Related

Ignore Form_close Command

Aug 16, 2006

Hi guys, is there any code u can write that ignores this hidden command that closes the form when you press the X button in the top right corner of the form.

Many thanks, Ben

View 4 Replies View Related

Hide Form When Checkbox Ticked

Dec 20, 2005

Hi All.
What I would like to do, is have a form popup when the main form is opened in my db, which will detail all the changes I have made to it since the last version (this bit is OK) and also, I would like a checkbox on the popup form, that the user can tick once they have read the details so it will not show up everytime they open the main form. (Just like the "hidestartupform" property within the Northwind Database)

I create the form and all that, but do not know how to do the checkbox so once it is checked, the form will not appear.

Hope this makes sense and someone can enlighten me and point me in the right direction?

Many Thanks.

Frank.

View 4 Replies View Related

Insert Comment If Checkbox Is Ticked

Jul 10, 2015

I am working on a database which will basically serve to be a massive Auditing Checklist. Lots of "Is this done" type yes/no questions to record, some with a comment.

Brief Description: I have many child-forms on a tab-control, each with a different heading/category of things to check for. When someone visits a store, I would like them to record their findings in this form.

In the above example, if the Floors are clean, no input is necessary. If the floors are not clean then the user ticks the checkbox and the text "Floors were not clean" should be automatically entered into the field: Cleanliness 1. The reason for this is that I wish to link the value from this field to a Microsoft Word document using mail merge for reporting purposes, and in the report it needs to state any problems in full sentences.

I have tried using this code in order to update the comment field when the checkbox is ticked, however, the problem with this is that I would have to insert code manually for every single checkbox in my forms, and I have about 150 checkboxes in total.

Code:
Private Sub Cleanliness_A1_AfterUpdate() If Cleanliness_A1.Value = -1 Then 'If checkbox is checked
Cleanliness_C1.Value = Cleanliness_C1.DefaultValue
Cleanliness_C1.Enabled = True
Else
Cleanliness_C1.Value = ""
Cleanliness_C1.Enabled = False
End If
End Sub

View 14 Replies View Related

Ignore Blank Parametres In Query

Jan 22, 2008

Hey

I have a form where the user can choose from a drop down menu, values like Name= James.90
Date=1/10/07.


Then it would return the record with James.90 And That date in it

But my problem is you have to choose a value for both boxes. Is there a way to ignore it if the field is left blank.

My current code is


SELECT timesheet.WeekEnding, timesheet.FName, timesheet.Company, timesheet.Project, timesheet.CTR, timesheet.Comments, timesheet.Total, timesheet.Project_Title
FROM timesheet
WHERE (((timesheet.WeekEnding)=[Forms]![FormBeta]![Week]) AND ((timesheet.Company)=[Forms]![FormBeta]![Company]) AND ((timesheet.Project)=[Forms]![FormBeta]![Project]));

View 14 Replies View Related

Modules & VBA :: Ignore Certain Events In A Form?

Apr 9, 2014

In my database I have module that checks if tick box is True and then closes all the forms and kicks the user out of the database. However in most of my forms, there is a custom Save command in the On Click of cmdSave button and the Form_BeforeUpdate. The save requires certain things to be true so naturally if a user has made a change to a record, the form won't allow you to close it if you haven't saved your changes or undone them.

Is there anyway I can get my module to bypass certain events in the form?

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved