I'm looking for someone that knows how to link check boxes on a form to the "show" check-box in a query. Basically, all I'm trying to do is have the end-user tell the query which fields to show in the output. If checkbox "A" is checked on a form prior to kicking off the query, I'd like field "A" to show in the query output...and so on.
Thanks, Paul
I have table.[reconciled] tat allows for a check box. i want to enter in the criteria filed in query design that i only want to see the unchecked or false entries. i have tried writing the criteria multiple ways and i cannot get it to filter out on my sub form query.
SELECT FuelmanImport_tbl.Reconciled, [Driver PIN].Division, [Driver PIN].[Transportation Supervisor],
FuelmanImport_tbl.[Transaction Date], FuelmanImport_tbl.[Card Number], [Driver PIN].Driver,
FuelmanImport_tbl.[Cardholder Name], FuelmanImport_tbl.[Prompted ID], FuelmanImport_tbl.[MCC Description],
FuelmanImport_tbl.[Merchant Name], FuelmanImport_tbl.[Merchant Address], FuelmanImport_tbl.[Merchant City],
[Code] ....
1. In a form that I have created, I have a checkbox that I want to have checked when the form (ticket) that was filled out has been completed by our company. I want the checkbox to only be accessed by us. How do I achieve this to only certain users have access to check that box?
2. I have a main screen that the form is able to be accessed by. In that home screen I have a query that shows what tickets have not been completed (checked). How do I get those tickets that have been checked, to not show up on that screen?
I am using the Multi Search from that's available in the template forum, and currently it's working perfectly. I am trying to add one checkbox (chkIncludeManifests) to the form to do some filtering.
When the box is checked, I should search all records. When the box is unchecked, I should search only records with no manifest number [manifestnum].
I've tried setting up an IIF statement to filter out the values that are empty but it's not working.
I have a query which is run from a form via a command button. On this form i have a couple control sources (not sure if this is the correct term, but i have text boxes and drop down lists which the query uses in its criteria).
Everything works fine except i can't figure out how to filter my checkbox field (aka MyDeliv). I'd like to have a drop down box with 3 options: "yes", "no", and "ignore". i would like "yes" to return only records with the MyDeliv box checked, "no" to return only records with the MyDeliv box unchecked, and "ignore" to return records regardless of the MyDeliv checkbox.
I have a form based on query. Each record has a checkbox.
I would like when activate checkbox to delete the specific record. from a button in the form.
Have not find anything around
I have a query that uses a checkbox from a form as a criteria. in the table the values are stored as 0,-1. if i run the query with the checkbox false, the query works and filters correctly, same if the box is null. But when I check the box true, no records load. It worked in an older version of my database so i am not sure why when i copied it over it doesn't work. I even tried a combo box with values of 0, -1 and got the same results.....
View 6 Replies View RelatedI have some VBA code that generates a query and saves it. The query is a list of people who will be sent a communication along with the formatting for the communication. After producing the query I want to look through it and decide if there is anyone I don't want to send the communication to. I can't delete records from the query without deleting them from the database or I would simply do this.
I thought I could add a checkbox in the query that I can tick to stop a communication being sent to that person. I only want the setting saved within the query and then when I have finished it be deleted along with the query.
I have some code that creates queries based on a value in a checkbox.
So, depending on that value, the queries may or may not exist.
I need to take those queries (if the exist) and create one union query.
This code creates the first query beautifully, but it won't union the second query.
Code:
Private Sub cmdSubmit_Click()
Dim blnQueryExists As Boolean
Dim cat As New ADOX.Catalog
Dim cmd As New ADODB.Command
Dim qry As ADOX.View
blnQueryExists = False
[Code] ....
I have a query that produces a report for charges to the client. On the form I have a checkbox that when the client is charged it is ticked. What would be better is that when I run the query to export to excel, it should check all those checkboxes and the ones that are blank, fill them in (tick the box). Then I need to add to the query that that field should contain only blanks so it will show me only the ones that where not charged and then after producing the report check those boxes so it keep track of all the clients charged (to avoid double charge).
View 3 Replies View RelatedI need to design a query that contains about a dozen fields for stationery items along with another dozen fields (checkboxes) to show whether these items were delivered (Yes, I know, not great db design).
I ran out of space with the the query criteria window.
Currently I have a query where the criteria is dependent on the combo boxes on my form. I would like to add checkboxes to my form which determines which fields are shown or hidden. For example if I had a checkbox for address, selecting it on my form will show the address column in my query results.
View 4 Replies View RelatedI have a SubForm "assignments" based on a Query, which has criteria to filter dates and also to filter 0 and 1 of the checkbox ...
The question is:
How do I put in that SubForm one or more Checkbox to "enable" and "disable", only the criteria of such query? So, toggle, for example, those jobs that are not completed (Checkbox of the query=0) and those that do ...
I have a parameter query that contains information on a list of people and contains 3 checkboxes: alumni, parent, business
In this query, I am trying to use parameters to filter the results based on these three fields i.e.
true, false, true would return all records where either alumni, business or both are true, and parent can be either true or false.
false, true, false would return all records where only parent is true, and the other fields do not matter.
I'm having an issue getting my query to omit records with a blank field - in fact, it omits all records.
What I'm trying to do is:
I have a list of customers, with phone and email addresses. I want to filter via query for only customers with their email address's entered.
Here is what I have:
IIf([Forms]![AdvancedReporting]![Check230]=-1,"*",Null)
I have developed a database which has required many checkbox fields to enable analysis. It requires to have the facility to input random/variable date ranges for statistical purposes.
I have built a query which obtains the counts of multiple fields using the following parameters in Query Builder in Access 2010. Although this comes up with the correct results for these multiple fields when I try introduce date range the results come up blank for all results.
An example of the parameters used for one of the checkbox fields in Query Builder is as follows:
Field: SumAnger: Sum([Anger]*-1)
Table: Default as only one table
Total: Expression
Show: Checked
This works fine.
My latest parameters for the date range are this:
Field: [cDate]
Table: Default as only one table
Total: WHERE Corrected! Whoops Copy & Paste Typo. Too early AM!
Show: Checked or Unchecked makes no difference
Criteria: Between [From Date:] And [To Date:]
This gives a statement in SQL view of:
SELECT Sum([Anger]*-1) AS SumAnger, Sum([Anxiety]*-1) AS SumAnxiety, Sum([Depression]*-1) AS SumDepression, Sum([Listening]*-1) AS SumListenig, Sum([Psychosis]*-1) AS SumPsychosis, Sum([Stress]*-1) AS SumStress, Sum([Other]*-1) AS SumOther, tblCommsLog.[cDate]
FROM tblCommsLog
WHERE (((tblCommsLog.[cDate]) Between [From Date:] And [To Date:]));
what I need to get this to work in Query Builder or failing that recommend some VBA script/code with embedded SQL to achieve the required report.
I have a query driving a report that fills in the on time or late based on comparing the release date to the production due date. The code works except for if we haven't released it yet and the release date is void. I need it to then check versus today's date and then populate the late/on time. But I need this to take a back seat to if both fields are populated. Here is the code I am currently using and I have attached a screen shot of the query with the 2 boxes highlighted in red.
On Time / Late Eng: IIf([Released to Prod]<=[Prod Due Date],"On Time",IIf([Released to Prod]>[Prod Due Date],"Late",""))
How do I use the Max() function to get only one number. And also what do I put in the parentheses?
View 14 Replies View RelatedI am using a function to set criteria in a an query. It reads the selected values from a combobox on a form and passes the appropriate value into the sql criteria.
E.g., the sql criteria is set to : like fnCountry()
And the function fnCountry() is something like;
If SelectedCountry = "All" Then
fnCountry = "*"
else fnCountry = SelectedCountry
end if
This works fine for a single selection (SelectedCountry = Africa) but doesn't work if I try to combine multiple selections into the criteria string.
E.g. SelectedCountry = Africa Or Italy
So the criteria would need to be
Like "Africa" Or like "Italy"
How else can i build this criteria with multiple values?
Hi
I have a table which has information the count of students in classrooms around the university and I need to summarize the table by Faculty. Therefore, all I want to do is a count of students for each faculty i.e. Art and Design, Business and Law etc.
The query i put together is: SELECT Count([tbl_Audited Classroom for Week 02].Faculty) AS CountOfFaculty
FROM [tbl_Audited Classroom for Week 02]
HAVING (((Count([tbl_Audited Classroom for Week 02].Faculty))="BAL"));
So when I run the query I get the error message 'Data Type mismatch in criteria expression'. The Faculty field is a text field, so I don't know if that would make a difference.
Can you please help?
thanks
I am trying to , create a field using the IIf function that will display a $250 bonus for agents every time they sell a car for at least $20,000. Otherwise, the function will return a 0. Name the field Bonus. I put in Bonus: IIf(SalePrice >= "20,000" Then + "250",True, 0) and it is saying that I have an improper syntax.
View 2 Replies View RelatedI have a table that holds the dates of various approvals for documents.
Approval DatesDocumentID
ApproverA_DateApproved
ApproverB_DateApproved
ApproverC_DateApproved
LastUpdated
In a query, I'd like there to be a column with the date of the most recent approval (max date) for each document. Using the following formula in the Expression Builder gives me the error below.
Code:
Name: Max( [Projects]![ApproverA_DateApproved],[Projects]![ApproverB_DateApproved],[Projects]![ApproverC_DateApproved])
"The expression you entered has a function containing the wrong number of arguments"
How can I get the most recent date a document was approved by any approver?
BTW - I initially thought I could just use the date the record was last updated. However, sometimes people remove their approval. This causes the LastUpdated date to be wrong (for this purpose), since it then reflects the date they removed their approval, not the most recent date that someone gave it.
How do I use the DateValue function with a query that pulls data between dates? Here is my code below. I'm getting an error that my expression is too complex or incorrect.
Code:
SELECT IIf([tblAUCodes].[BusinessLine] Like '*CMES*',"CMES",IIf([tblAUCodes].[BusinessLine] Like '*HOUS*',"CMES",IIf([tblAUCodes].[BusinessLine] Like 'CTO',"CTO",IIf([tblAUCodes].[BusinessLine] Like 'Shareowner*',"WFSS","SPS")))) AS LOB, Count(tblPACSTemplate.temID) AS CountOftemID, tblPACSTemplate.Type, DateValue([ApprovedDate]) AS ApproveDate
[Code] ....
I am currently designing a dashboard for my team which works on projects, I have a main table which has main fields which i use for my dashboard
Project Type,Project ID (Primary Key), Submitted by, Project Start date, Project end date, Status
The tricky part is that the raw data has projects which got started couple of years back some got closed in the previous fiscal year but some are still being worked upon..I was able to put in a where clause to only display projects whose close dates either have null value ( projects which are still being worked) and where the close dates are >= 1/1/2013. My Dashboard needs to have the below fields
Project Type, Count of Projects, Count of Closed Projects, Average days to complete, Count of Active Projects & Average Active days
I was able to get it correct except the last field Average Active days as it is displaying Average no's even against the project type which doesn't have any active projects or where the active project count is zero.
SELECT TblWO.[Operational* Categorization Tier 2], Count([TblWO]![Operational* Categorization Tier 2]) AS [Count], Avg([TblWO]![SLA in Days]) AS [Internal SLO in Days], Count([TblWO]![Closedate]) AS [No Closed Projects], Round(Avg([TblWO]![Closedate]-[TblWO]![Work Order Submit Date Time]),2) AS [Avg Close Days], [Count]-[No Closed Projects] AS [No Active Projects], Round(Avg(Date()-[TblWO]![Work Order Submit Date Time]),2) AS [Active Avg Days]
FROM TblWO
WHERE ((([TblWO]![Closedate]) Is Null Or ([TblWO]![Closedate])>=#10/1/2012#) AND (([TblWO]![Status])<>"Cancelled" And ([TblWO]![Status])<>"Rejected"))
GROUP BY TblWO.[Operational* Categorization Tier 2]
HAVING (((TblWO.[Operational* Categorization Tier 2])<>" "));
I have a query that I'm working on through Access 2010's design view. I'd like to add a criteria to the query where it only shows results with the employee name column matching a global variable I created that stores the name of the currently logged in employee.
Here's my vba code that declares the global variable and the public function i'm trying to pass as criteria in the query:
Global gbl_loginName As String
Public Function returnName() As String
If IsNull(gbl_loginName) Then
returnName = "test" ' dummy account created for development only
Else
returnName = gbl_loginName
End If
End Function
and here's the SQL code from Access's design view:
SELECT [Entry of Hours].WC, [Entry of Hours].[Employee Name],
[Entry of Hours].[Set Up Time], [Entry of Hours].[Run time],
[Entry of Hours].[Traveler Number], [Entry of Hours].[Entry Date],
[Entry of Hours].[Quantity Finished], [Entry of Hours].Notes, [Entry of Hours].WPS,
[Code] ....
when I try running the query, however, I get this error:
'returnName' is not a recognized built-in function name
Is there a problem with using public functions in Access' design view?
UMMonth1: Choose([Enter Qtr],[OperationsAuditData]![1],[OperationsAuditData]![4],[OperationsAuditData]![7],[OperationsAuditData]![10]).But I keep getting an error message "You tried to execute a query that does not include
"UMMonth1: Choose([Enter Qtr],[OperationsAuditData]![1],
[OperationsAuditData]![4],[OperationsAuditData]![7],[OperationsAuditData]![10])' as part of an aggregate function."