Queries :: Parameter Query Returning Incorrect Results Based On Checkbox
Jul 31, 2013
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.
View Replies
ADVERTISEMENT
Apr 26, 2013
I have a query (that gets it's data from several other queries) with a column called "max." The data in the column is correct, but when I call on the query in VBA, it shows me incorrect data.Here is where I call the query:
Code:
Dim db As DAO.Database, qdf As DAO.QueryDef, rs As DAO.Recordset
Dim strReport As String
Set db = CurrentDb()
Set qdf = db.QueryDefs("7-ErrorsReport")
qdf.Parameters(0) = Forms!frmmain!dt1.Value
qdf.Parameters(1) = Forms!frmmain!dt2.Value
qdf.Parameters(2) = Forms!frmmain!d2.Value
[code]....
The query looks at a table of employees and finds out if they have been issues a warning letter before, then prints out a corresponding report based on the "max" warning level they are at.The problem arises when an employee graduates from a 6 month probation period - all letters in that period should be ignored. As i said, they are ignored correctly when i run and view my query ("7-ErrorsReport") because they are filtered out at that point, but for some reason when this code runs, it somehow sees the previously issued letters which are stored in a table and likely in some of the other queries.
I am not sure if there is some issue with the query tree I have set up which is necessary to get the results I need, or if something is wacked with the was I am using it as a recordsource.
View 3 Replies
View Related
Mar 20, 2015
I suspect my design is flawed
Code:
SELECT tblData2.Prefix, tblData2.LineNum, tblData2.Year, tblComments.comment, tblComments.Address
FROM tblData2 LEFT JOIN tblComments ON tblData2.LineNum = (NumbersOnly([tblComments].[Address])
WHERE (((tblData2.MyYear)<1990))
ORDER BY tblData2.LineNum;
The NumbersOnly function returns a set of numbers from a custom formatted string. The above fails on the join (I think) but maybe there's another way of doing it?
View 4 Replies
View Related
May 7, 2014
I have two tables Contacts & Allowance. There is a relationship defined between the two based on Contact ID (primary field/contacts - foreign key/allowance). The Contacts table has Firstname + Surname. I have produced a query based on certain fields in both tables however the result is totally pulling incorrect info.
View 11 Replies
View Related
Oct 30, 2013
What is the best approach to returning calculated results in a query. I have been using nested IIf statements that include DateAdd but I think that I am at a point where there must be a better way.
I want my query to calculate a date based on:
1) A Type field from my table
2) A calculated date based on other query fields.
My query has the following fields:
[Type] [Action 1] [Projected Action 1] [Projected Action 1 Revised] [Action 2]
I want to create a calculated field for [Projected Action 2] that says:
If [Type] is "A" then
if [Action 2] Is NotNull, Null
if [Action 1] is NotNull, [Action 1] + 10 workdays
if [Projected Action 1 Revised] is NotNull, [Projected Action 1 Revised] + 10 workdays
if [Projected Action 1] is NotNull, [Projected Action 1] + 10 workdays
[Code] ....
I would like to keep using nested IIf but I keep running into problems and I thought that there is probably a better way.
View 7 Replies
View Related
Dec 30, 2014
I am building a prototype database and currently has two records in it (I know for certain they are not duplicates). Attached is the relationship and below is the query. The query keeps returning the correct results but twice. (Duplicate results.)
SELECT DISTINCT WeekdayName([Weekdaynumber],False,1) AS Weekday, [Master Data].Date,
[Master Data].Machine, [Master Data].Shift, [Master Data Query].[Master Data_Product Code],
[Master Data Query].[Cases/Pallet], [Master Data].[Expected Pallets to Produce],
[Master Data].[Actual Pallets Produced], [Master Data].[Damage (Units)],
[Code] .....
View 6 Replies
View Related
Dec 26, 2013
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.
View 4 Replies
View Related
Feb 25, 2008
Thought this thing was working great.Seems this is happening:If the sum of credits exceed the sum of charges, the query doubles the sum of charges. The query-SELECT TblCustInfo.CID, TblCustInfo.Name, TblCustInfo.SrvAddr, TblCustInfo.SrvAmt, TblCustInfo.BLCAT, Sum([TblCharges.Chargeamt]) AS SumOfCharges, Sum([TblPayments.Creditamt]) AS SumofCredits, nz([SumOfCharges],0)-nz([SumofCredits],0) AS RunBalance, TblCustInfo.HerbieFROM (TblCustInfo LEFT JOIN TblCharges ON TblCustInfo.CID=TblCharges.CID) LEFT JOIN TblPayments ON TblCustInfo.CID=TblPayments.CIDGROUP BY TblCustInfo.CID, TblCustInfo.Name, TblCustInfo.SrvAddr, TblCustInfo.SrvAmt, TblCustInfo.BLCAT, TblCustInfo.Cancel, TblCustInfo.HerbieHAVING (((TblCustInfo.Cancel)="n"));When I run the query, the SumofCharges calc is the culprit since it shows in that column. I can't dup it in the SumOfCredits, but I would suspect it will also do it somewhere down the line since they are virtually the same.HELP !!!!
View 14 Replies
View Related
Aug 16, 2013
I 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 ...
View 1 Replies
View Related
Jun 16, 2015
I am trying to sum a column on a subform named Cost of New if another column named Final = any of the values listed in my code. However, it is summing the Cost of New column regardless of the values of Final. It is summing for all values.What do I need to modify to make this correct or should I do it another way?
=IIf([Final]="RPR-RPR & RTN" Or [Final]="NFF-TST & NFF" Or [Final]="RTN-RETURN" Or [Final]="SCL-SCR LOCALLY" Or [Final]="SCR-SCR RETURN" Or [Final]="BER-BER RTN CST",Sum([Cost Of New]),Null)
View 3 Replies
View Related
Oct 6, 2014
I have a query which pulls up results based on the value of a text box. All works well except one field has a DOB (date) and if I search say for " /1976" I get no results, however search /76 and all the people with that year of birth show then show up. The date is stored in the dd/mm/yyyy format.
I'm using the like [forms]![clients]![searchfield] in the criteria of the query to bring up results.
View 10 Replies
View Related
Oct 25, 2013
I have a switchboard which runs a google style search query to find people based on their first name, surname, date of birth or NI number on clicking the search button. It uses the following criteria in the query Like "*" & [Forms]![SearchF]![Firstname] & "*" for each of the above fields.
This works perfectly until someone inputs a record that doesn't have anything in one of the fields. ie, full name and DoB, but no NI number. When a record is entered in this way, the table stores the record but the search query cannot find it.
View 3 Replies
View Related
Sep 11, 2014
Multi search wont return results: Obviously I have my table and search form. But have hit a problem that is probably simple to overcome, but for the life of me I cant see it.
The small peice of code I have used = forms]![searchF]![name1] I have also tried Like"*" & forms]![searchF]![name1] & "*" still not working. The qurey accompanying form (searchF) works to displaying searchF form unpopulated which I believe is correct. I should at this stage be able to fill out the one table heading (name1) where the query should return a result in the (searchf) form but it does not return.
The tables, forms and quries, have all been saved and closed and re opened many times
The form (searchF) propeties have been checked and double checked. I have even built a test table but the same things happen as my main table.
View 14 Replies
View Related
May 16, 2013
Within a query, I'd like to reference another query field based on a date specified as a parameter.
In my query, there are fields for each month: [January],[February], etc.
I have a field titled [Current Month], based on the parameter [As Of Date]. So if when running the query, the parameter pops up and I type 5/6/2013, it knows that the month is May. I know how to return May in the current month field (format([As Of Date],"MMMM"). But how to I return the value that is in the May column?
View 3 Replies
View Related
Jun 23, 2005
Hi,
I have a query with two table's in them. One is filled, the other is still empty.
Both table's are related to each other with the field "document number".
This was working fine in access 2000, now in 2003 it returns zero results.
I am starting to think it is because one table is empty it returns nothing.
How can i adapt the query criteria or something else that it shows the results from table one, while table two can still be empty.
One table holds the documents and all information about it, the other holds the information wich document is referenced on wich document. Now since i can have documens which don't reference to a document, i still want those in the list.
Anybody any idea?
Thanks
View 1 Replies
View Related
Jul 6, 2007
I have a table listing a number of cell phone products and their carrier along with their status' in the production process. I want the query to return the most recent status for each product for each carrier. However, out of the 20+ products that I'd like the most recent status for for each carrier, it's only returning about 12 products of the most recent status for one carrier.
Any help would be much appreciated! Leave a message if you need more clarification.
Thanks!
View 3 Replies
View Related
Apr 2, 2013
I run a physical therapy office and patients come in for treatment either 3, 4 or 5 times per week. My database is used to track these frequencies (among other things).
I have 3 queries which count how many patients come in 5, 4 and 3 times/week.
In my main table I have fields called "how many 5's", "how many 4's" and "how many 3's".
I have tried to design an update query which will update those fileds in my main table to reflect the counts in the 3 queries mentioned above.
(I'm not using SQL view, I'm using the query design view)
In the "update to:" row, I use the Build function and locate the count I'm looking for.
Problem: when I run the query I get the error: Operation must use an updateable query.
View 3 Replies
View Related
Jul 3, 2006
Hi everyone,
I'm having an issue with a query I created. When run the query requests an Artist Name. I enter this and it returns one result. However in actual table ther are two results for that artist. The only difference is that for the record that doesnt appear the field labelled "Gallery" has a zero value.
I have checked the table and the gallery field is not set to a required field so I dont understand why it wont show it as a result of the query.
Any ideas?
View 3 Replies
View Related
Mar 2, 2007
Hi all:
running access 2000. I have a query that I selected to return the Top 1 record sorted descending on the date so it would pick the most recent entry. I did it by selecting Top 5 from the drop down box in query design mode and then changed the 5 to a 1 using SQL View from qry design.
SELECT TOP 1 Class.CourseID, User.UserName, Class.TrainerID, Class.ClassLocationID, Class.ElapsedTime, Class.ClassDate, Class.Comments
FROM [User] INNER JOIN Class ON User.UserID = Class.UserID
ORDER BY Class.ClassDate DESC;
It still gives me as many records as are entered on the system. How can I get JUST the most recent record entry?
thanks:confused:
View 3 Replies
View Related
Sep 12, 2012
I have a db with 2 tables, one containing basic info FirstName, Surname, YearGroup, and another table with other data. I imported a list of names into the first table, firstname and surname only were imported, other field was left empty, other table was also left empty. I made a simple select query to pull all records from both tables and i get no results at all. None of the imported names show on the query results. what I am missing?
View 2 Replies
View Related
Oct 29, 2014
I have 2 form controls one a combobox and the other a text box. The text box select the site (txtLocation) where the user can enter part of the name of the site and all sites with those characters are returned. I've done this by adding:
Code:
Like [Forms]![frmSearchDB2]![txtLocation] & "*"
into the criteria on the Site field in the query design editor.I also want the combobox to have an affect on the query. I want it to query on user status. However if the combobox reads "All Users" I want it to return all status's and all null values. In the criteria field I put:
Code:
iif([Forms]![frmSearchDB2]![cbxUserStatus] = "All Users",like "*",[Forms]![frmSearchDB2]![cbxUserStatus])
It kind of works but no null values are pulled back. Should it be an expression?Do I need to do it in VBA?
View 1 Replies
View Related
Feb 23, 2014
When creating a query I have set up a parameter query as instructed (in this case searching for a name) but when I run the query it returns the error 'this expression is typed incorrectly etc.'
I have worked out that this must be due to the fact that the names are not typed in by a front-end user but need to be chosen from a dropdown list (which i have stored in a separate table) - So, the query seems to fail based on that.
View 10 Replies
View Related
May 22, 2006
I am using the following:
SELECT CPL_CLIENTS.CODE, CPL_CLIENTS.FULLNAME FROM CPL_CLIENTS
WHERE (((CPL_CLIENTS.FULLNAME) Like "*" & Forms!frmSafeKeep_AddClient_SearchDesig!txtSearchT ext & "*"));
As the control source of a listbox, so that the user can enter something in "txtSearchText", and the listbox will be narrowed to only those those clients that have a matching part of their name.
The problem is that the user enters, for example, "duncan", and the list returns nothing, when in actual fact there are 5 matching records. If the user enters "Duncan" (note the capital letter), they get all 5 records as they should.
If memory serves, access should only be case sensitive if you tell it to be - and this is working fine on other forms.
The only difference between them is the data source. Other forms are getting data from linked tables in the backend. This form is getting it from a linked Oracle table.
View 4 Replies
View Related
May 19, 2014
i have a query that pulls a read-only SQL table, i have check in that table and the date field is set up as a Date/Time type, however the db was set up with the date and time in the same field. (1/1/2014 9:00:00 AM)
I have set up the SQL code below to pull out just the Date and not the time. however when i enter my criteria on the form, only the correct month and days load, it pulls in all other years with that month/day.
my Access level - on the lower end of medium experienced.
SELECT IIf(InStr([dbo_Rides]![ApptDatetime]," "),Left([dbo_Rides]![ApptDatetime],InStr([dbo_Rides]![ApptDatetime]," "))) AS [Date], IIf(InStr([dbo_Rides]![ApptDatetime]," "),Mid([dbo_Rides]![ApptDatetime],InStr([dbo_Rides]![ApptDatetime]," ")+1)) AS [Time]
FROM dbo_Rides
WHERE (((IIf(InStr([dbo_Rides]![ApptDatetime]," "),Left([dbo_Rides]![ApptDatetime],InStr([dbo_Rides]![ApptDatetime]," ")))) Between [Forms]![frmMain]![subTCTools].[Form]![snavHistory].[Form]![subnDriver]![txtFromDate] And [Forms]![frmMain]![subTCTools].[Form]![snavHistory].[Form]![subnDriver]![txtToDate]));
View 2 Replies
View Related
Aug 12, 2013
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] ....
View 14 Replies
View Related
Jun 30, 2013
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] ....
View 4 Replies
View Related
