Database query. I need the query to count the records of a field and display a number for the records of the field. For instance, one field is [Genre] and the other is [Show]. The query needs to list the Genres along with the number of shows for each genre. I've been able to just use the query design and add the genre field and I can add the show field, use totals count which gives me the genre counts the number of shows. My problem is the null. Some genres don't have a show listed so the genre doesn't even show up in the result. If I could get the the genres that have null shows to result a 0 it would be perfect.
I have made a function returning True/False values. I used this function in a query and now it return value Error as well...Is there a way to set criteria to values received in that field (0/-1/#Error). I've tried putting Like 0, Like True with or without quotation mark.Also every workaround comes into play as long as it works.
Access 2010. I've had to learn it at work because our DBA was let go and I was the only one willing to give it a go. Read a book or two and picked up some stuff on the internet.
Here's my problem: I have a simple table -Employee with 4 fields. FirstName, LastName, Office and JobTitle. I have form called Form1 that has 3 control fields cboJobTitle (a combo box that is populated by a query that finds all the unique values of that field in the Employee table), cboOffice (same as above) and txtName (a text box to allow user input) that are used as the criteria for a multi-field query triggered by a button at the bottom of the form. The idea being that you could do a search using this form to find all the employees in one office or all the accountants in one office, or any other combination.
The main search query has the following criteria for each field - Like [forms]![Form1]![cboOffice] & "*" Like [forms]![Form1]![cboJobTitle] & "*" Like [forms]![Form1]![txtName] & "*"
It works great...until I enter a record where one of those fields may be null, such as if I leave the JobTitle blank. If I have two employees in an office in one city and then do a search for all the employees in that office, it only returns one record and ignores the one that has the null value in the JobTitle field.
Trying to run a query using criteria to populate the query by looking at information from a field on a form, if from is closed I need that criteria to look at the table and return all date in table.
I have a query in my database, and I have recently added new possible entries for area. My report tells me how many male chickens vs. how many female chickens I have. Now with the new entries for area, I want my breakdown to EXCLUDE any chickens that live in a specific area.
For instance:
Pen A has 15 males & 15 females. Pen B has 12 Males & 13 Females. Pen C has 50 males & 50 females.
Pen C is the recent addition...
Right now my report shows: 77 Males & 78 Females...
I WANT it to EXCLUDE Pen C, so it would report: 27 Males & 28 Females.
Can anyone tell me how best to do this? I figured I would add a custom field to the query that the criteria for is "Pen = C" and then on my report I would change the data source from: =Count([Chicken Query]![Male] --- and make it =Count([Chicken Query]![Male] - Count([Pen="C"]) --- or something like that.
Can anyone help me? And if so could you help with the proper code for the second part that I am adding new? I know the Count([Pen="C"]) isn't right, but until I know exactly how to add a custom field to the current query, with the critieria of Pen="C", I don't know how to write the -Count([NewField]) code, if that would even work.
I am trying to create a query to find duplicates and delete the duplicates. The result will eventually be used in another query (append query) to update a table.I have a table with 4 columns lets say for simplicity they are A, B, C, D
I want my query to find duplicates within B and deleting them. The catch is before deleting them I need to look into column A to ebsure they are actually duplicates. Example below
Example A B John Doe Tires John Doe Wipers Allison Doe Tires Allison Doe Tires
As you can see from the above Tires is a duplicate and need to be deleted.
A B John Doe Tires John Doe Wipers Allison Doe Tires
I have made a database for work and is fully functional, but theres one thing I want to add but cant get my head around how to do it.
I have created a Form called 'Filtered Search', on the form it has multiple combo boxes for 'Auditors' 'Area' 'Status' and 2 text boxes for date range.
I want to be able to set what filters I want, and for the query to ignore any fields with no information selected/inputted (i.e. I want to see all records raised by "Mr Smith" (Auditor) that are still 'Active' (Status) in all areas at any time).
Names of items;
Table = 'Incidents' Form = 'Filtered Search' Report = 'Filtered Report' Auditor = 'Combo7' Status = 'Combo156' Area = 'Combo5' Date Range From = 'Text161' Date Range To = 'Text163'
i am trying to make a query which will give my the result as if Sum of filed is not null.. Criteria 1) (Field Name)in Category "Asset ID Diff" And "Cusip Change" should select first then
2) Fund and Amt
if Fund Amt is not zero than data will comes in query
CategoryFundAmt Asset ID DiffADF01000 Asset ID DiffADF0-1000 Cusip ChangeADGH2000 Cusip ChangeADGH-2000 Asset ID DiffADF05000
in above data you can see ADF0 funds total is 5000 which is not null or 0.so query will give me the result as.Asset ID DiffADF05000
I have a query where data is first sorted by user input; first field's criteria: [fieldname], then by another field's criteria: Is Null.
I know there are records containing null values in the second field, as I have run a select query with the criteria: Like "*", to make sure they are null, and not zero-length-strings.
All. Using access 2010. I have a query that returns 92 records. When I put in the criteria for one field to leave out records with “approved” which totals to 9 records, the query only returns 10 records. It is not returning the records that are blank(not null) for that field. I want those records. Why is this happening and how can I get the blanks for this query?
I am currently working on an instrument datebase, I have a mainquery that takes care of user inputs from a form. The main fields that have been queried on are Type, System, and Manufacturer and they are all look-up fields that contain some null values.
On the same criteria row for these fields, I have
Like IIf([forms]![User Interface].[qtype2]="","*",[forms]![User Interface].[qtype2]) Like IIf([forms]![User Interface].[qsys2]="","*",[forms]![User Interface].[qsys2]) Like IIf([forms]![User Interface].[qman1]="","*",[forms]![User Interface].[qman1])
qtype, qsys and qman are the user inputs from the user interface that returns look-up table values.
This works fine when all 3 of these fields are all filled out for a certain instrument. The problem arise when some fields of the instrument are left blank or is null. The instrument won't show up in a query at all. What I wanted it to do is to show everything including the ones with null fields when the user input are null or "". When the user specifies certain requirement I only want to show the ones that are not null. I understand that putting them on the same row means AND, I have tried to OR them and did not have the result i wanted.
I have a form which a user can select upto 3 different options to search the main database.
The main table has: RVA Date Council Introducer PS NO Period Asset Description Current Cost SSAP21 Position
The three fields the user can search on is: Council PS No. Asset Description
I have created 3 individual queries to find the records for each of the above, as the other two choices could be left blank.
Not sure if one super query can be done to show the records based on the user input (as I say one or two choices could be left blank).
So far I have managed to get a main query (based on the 3 other queries) to work on all choices made by the user except Asset Description with the other two left blank.
I have made relationships between the three queries on the main query. Linking Council,PS No. & Asset Description to each other.
Almost there, just need the main query to work on the user selecting Asset Description only.....
I have a form that feeds information to a query which in turn sets up a report. For clarity i will list out in basic terms what I have
Input Form - Check Box to activate/De-activate a text field [chk-active] - Text field for a parameter [txt-Parameter]
the query has a column that has null values from the originating table. These values will be added a t a later date, but need to be queried and reported at some business intervals.
In the query criteria for this column, I have the following Criteria
Like IIF([Forms]![ReportGenerator]![chk-Active]=0, "*", [Forms]![ReportGenerator]![txt-Parameter])[/I][/I]
I have tried for the last 5 hours to figure out how to write the formula to be able to get the blank entries to show up as well but have had no luck.
I have created a form to select criteria for a table query (which ultimately will be used in a report). If a field in the form is left blank, how can I cause the query to not filter on that field?
For example, the form contains fields for Customer, Date Range, Item Number, and Sales Rep. If the user wants the report to include all sales reps how can the query criteria for this field be omitted or nullified when the query is run?
I have a form which users can toggle whether they want to only see entries that has data in a certain field. Previously I have set up a separate query with a hardcoded 'Is Not Null' in the criteria, and set the form to call the different queries based on the status of a toggle button. This time there is 22 queries that need to be modified so I'm hoping there's a better way.
I have a hidden textbox on my form that has value "Is Not Null" or Null based on the position of the toggle button.
In my query for the field criteria I have [Forms]![MainForm].[txtCriteria] where txtCriteria is the textbox previously mentioned.
My training database requires me to identify each training record in the tblEmpTrainHist table as either "Compliant" or "Delinquent". I thought a simple calculation in my query:
would do the trick. However, I did not consider the records where the employee has not yet completed the training and the field [DateReceived] is Null. There are two considerations: those employees who have no [DateReceived], but have not yet reached the DateDue (Compliant); and those employees who have exceeded the DateDue (Delinquent).
I have been working on an application where I am collecting survey data in a database. There are multiple survey tools available to the user, and it's possible to complete multiple survey tools in the survey.My problem is, it's possible for the surveyor to complete some tools on one day and other tools on another day. I am having problems with trying to figure out how to add a tool that has not been previously added and keep in the same survey which is all held under a single Survey Number.
The first step in the function is to set a Record Number temporary variable based on whether or not the tool has been used (it's possible to use multiple instances of a survey tool, so need to know if the Record Number is '1', or the next number in the sequence.I've been trying to do this by checking a query for a Null and setting the temporary variable using something like:
IF ISNull("RecordNumber","qryRecordHeader") Then '1' Else DLast("RecordNumber","qryRecordHeader") +1 End IF
The second half works just fine, so if there is a previous record, it will add. But if it's Null, it doesn't work.I'm trying to avoid opening a temporary form to run the query and checking a field. Is there a way around that?
I have a query that is search for fields in a table that are either
1 - High 2 - Medium 3 - Low
I have a query that counts these and then puts the results into pie charts on a report.
However, when there is no "1 - High" value in the table against a paricualr criteria, obviously the quiery has nothing to look for an does not show a 0 value against the criteria but simply omits it (correctly) form the results.
This does affect the pie charts though which I want to show red for High, yellow for Medium and green for Low.
Therefore I need the query to show all criteria search results include 0 values, or to understand how I can colour code the series rather than the segments on the report.
I have a question about errors on null value.I have made a small database for tryout, it has to be implemented in another one.And the small database is working.I have one table where there is one field called BatchInput.I scan a barcode into it and let two query's breaking it apart. I scan this batch into the table field
BatchInput: 20 MAY 2004H149-082-79 A4147011A05
Then I have my first query (Qrybreak1) extracting the date and deleting H14
And query (QryResult) even wont start, giving a popup with Invalid procedure call..How could I handle Null on the part where there is no space after the partnumber (missing Certnumber)?
I had an issue with writing LIKE statements in query criteria yesterday [URL]....
The answer they gave worked perfectly when I only used a single table in the query. But as soon as I did an INNER JOIN with two other tables, now I get parameter value prompts when I open frmSearch, and instead of seeing ALL my records when the controls are left null, I get only the first record in the table.
Here's the SQL of the query, can you point out what I messed up? NOTE that this SQL was 'written' by Access.. as I used the Query builder to set all the 'Like or Is Null' statements, then clicked SQL and sorta formatted the code so I can see what I'm looking at (instead of superthick wall-o-code):
Code: SELECT tblPeople.name, tblPeople.num FROM (tblPeople INNER JOIN tblAddresses ON tblPeople.name = tblAddresses.name) INNER JOIN tblPets ON tblPeople.name = tblPets.name
[code]....
Basically, this is a searchable database of participants in a pet-adoption program, along with the participants' pets history and address history (hence the linked tables as opposed to additional columns in one single table for pets and addresses... there are more than one in some cases). The frmSearch allows a person to run quick searches based upon ANY item in the database, such as name, pets, addresses, pet age, pet type, county of residence, etc. I need to be able to pick ANY field on frmSearch and type a value, and have the qrySearch return records for ANY record's related column wherein any part of it matches what I typed.
The statements as written worked PERFECTLY right up until I added the INNER JOIN. Now I get a set of parameter value prompts for every field on frmSearch that's referenced in the SQL for EACH table I linked to tblPeople, and if I leave everything null and click Search, I want to see EVERY person, but I'm only seeing the very FIRST person in tblPeople.
I am currently creating an Access 2007 database for calculating salesperson commissions. I have a table with 5 fields I'm working with: SalesRep, SOWRep, TerritoryRep, Period and Commission. I am trying to build a query that will calculate commission for a salesperson for each record where their name appears in one of the first three fields. Each time their name appears, they get the commission listed in the Commission field for the stated Period. I have managed to do this part successfully. My problem now is that I am creating a query that will sum all of their commissions by Period. I have run into a situation where sometimes a salesperson will not appear in one of the rep fields, resulting in NULL values in the previous queries. How can I create a final query that will sum correctly even if there are NULL values returned on the previous queries?
Here is what I have so far:
November: Avg((SELECT [SumOf1/3GM$s] FROM [BaxterActualMargin1] WHERE [Period] Like "November*")+(SELECT [SumOf1/3GM$s] FROM [BaxterActualMargin2] WHERE [Period] Like "November*")+(SELECT [SumOf1/3GM$s] FROM [BaxterActualMargin3] WHERE [Period] Like "November*"))
I need a date prompt and null records in the same line of criteria so I get all those within a certain date range under the field "CO_resp_rcvd" and those that didn't respond yet but need to -- is that possible to do both and if so how would you show me how?
This is what I have currently in my query
CO_resp_rcvd (date field)
Criteria: Between [Start Date] And [End Date]
(I need null values as well because there will be some if the CO has not responded yet but needs to)
Formula:
This formula gives me the number of bus days from the Review Date - CO_Resp_Rcvd Date and that works but if the CO-Resp-Rcvd date is null, I need it to calculate Review Date - Today's date to show the number of days outstanding for those that have not responded yet in the same formula?
Not sure how to combine it to work - the wrapper is a bus day function
This is what I have so far in the query
CO-Bus Days to Respond: Wrapper([Review Date],[CO_resp_recd]) but if CO_resp_recd is null then ([Review Date],Date())
