Query On Other Queries Inc. IS NULL
Aug 16, 2007
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.....
View Replies
ADVERTISEMENT
Mar 24, 2013
I am trying to do the good 'ol sales report (query) to include customers with no sales.
I have a customers table, account number table, sales table & sales (line) detail table. (all linked in that order)
If I run a query to show customers (in the customer table) with account numbers, that works
An unmatched query to show customer without an account number works (but of course the unmatched account number field isn't shown).
How can I get the two two be shown together with the "unmatched" having a null or 0 for their account number?
I am guessing in principle, the resulting solution can be modified to show customers without sales alongside those with sales?
View 3 Replies
View Related
Jul 29, 2013
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.
View 5 Replies
View Related
Jun 16, 2015
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.
When I run the query no data is shown.
View 10 Replies
View Related
Oct 30, 2013
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.
View 5 Replies
View Related
Oct 2, 2014
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:
RecordStatus: IIF([DateReceived]<=[DateDue],"Compliant","Delinquent")
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).
View 5 Replies
View Related
May 30, 2014
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?
View 3 Replies
View Related
Jan 16, 2015
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.
View 1 Replies
View Related
Feb 7, 2015
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
Date: Left([BatchInput];11)
PartCertNr: Right([BatchInput];Len([BatchInput])-14)
The result is this:
Field date: 20 MAY 2004
Field PartCertNr: 9-082-79 A4147011A05
The second query (Qrybreak2) I look for the first space:
space: InStr([PartCertNr];" ")
Then with the result I cut it into two pieces
PartNumber: Trim(Left([PartCertNr];[space]-1))
CertNumber: Trim(Right([PartCertNr];[space]+2))
[code]...
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)?
View 7 Replies
View Related
Mar 29, 2015
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.
View 8 Replies
View Related
Apr 17, 2013
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*"))
View 14 Replies
View Related
Mar 5, 2014
I have two questions, both the same query.
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())
View 6 Replies
View Related
Dec 17, 2007
Hi all,Desperate for help on this.The query below ran like a charm for this years database which was full of entries, but when I did a quick test run for next year with limited entries the 'Temp' table fields were all blanked. All the individual queries, 1 through 9, work fine on their own and work fine in this linked form when there is enough data in the database to cover all the fields. Apparently if only one query is blank all the fields will be blank.For instance if there are multiple registered households, table ‘1’ would normally show a count of those households, but if none of those households have children, table '2' which counts children will be blank and in turn blank out all the rest of the fields including table ‘1’.I've tried the ‘is null, '=0" and ‘nz’ routines on the Count(Tablename.Fieldname)'s, but can't seem ‘to get them to output a value of 0 in the null fields. I must be doing something wrong. Any and all help would be very much appreciated.SELECT Year(Now()) AS ProjectYear, [1].CountOfClaimID AS Households, [2].CountOfPersonID AS Children, [3].CountOfPersonID AS Participants, [4].CountOfSponsorID AS SponsorsTotal, [5].CountOfHouseholdID AS Sponsored,[6].FamiliesWithChildren, [7].SponsoredFood, [8].SponsoredGifts, [9].SDSD INTO TempFROM (SELECT Count(Household.ClaimID) AS CountOfClaimID FROM Household GROUP BY Household.RegStatus HAVING (((Household.RegStatus)=-1))) AS 1, (SELECT Count(Members.PersonID) AS CountOfPersonID FROM Household INNER JOIN Members ON Household.HouseholdID = Members.HouseholdID GROUP BY Household.RegStatus, Members.Status HAVING (((Household.RegStatus)=-1) AND ((Members.Status) = "Child" ))) AS 2, (SELECT Count(Members.PersonID) AS CountOfPersonID FROM Household INNER JOIN Members ON Household.HouseholdID = Members.HouseholdID GROUP BY Household.RegStatus HAVING (((Household.RegStatus)=-1))) AS 3, (SELECT Count(Sponsors.SponsorID) AS CountOfSponsorID FROM Sponsors GROUP BY Sponsors.SponsorStatus HAVING (((Sponsors.SponsorStatus)=-1))) AS 4, (SELECT Count(Sponsorship.HouseholdID) AS CountOfHouseholdID FROM Sponsors INNER JOIN Sponsorship ON Sponsors.SponsorID = Sponsorship.SponsorID GROUP BY Sponsors.SponsorStatus, Sponsorship.SponsorshipYear HAVING (((Sponsors.SponsorStatus)=-1) AND ((Sponsorship.SponsorshipYear)=Year(Now())))) AS 5,(SELECT Count([Table0].HouseholdID) AS FamiliesWithChildrenFROM (SELECT Households_All.HouseholdID, IIf([Children]>0,1,2) AS FamChildrenFROM Households_AllGROUP BY Households_All.HouseholdID, Households_All.ChildrenHAVING (((IIf([Children]>0,1,2))=1))) as Table0) AS 6,(SELECT DISTINCT Count(Household.HouseholdID) AS SponsoredFoodFROM Household INNER JOIN Sponsorship ON Household.HouseholdID = Sponsorship.HouseholdIDGROUP BY Household.RegStatus, Sponsorship.SponsorshipYear, Sponsorship.FoodHAVING (((Household.RegStatus)=-1) AND ((Sponsorship.SponsorshipYear)=Year(Now())) AND ((Sponsorship.Food)=-1))) AS 7,(SELECT DISTINCT Count([Household].[HouseholdID]) AS SponsoredGiftsFROM (Household INNER JOIN Sponsorship ON [Household].[HouseholdID]=[Sponsorship].[HouseholdID]) INNER JOIN (SELECT Households_All.HouseholdID, IIf([Children]>0,1,2) AS FamChildrenFROM Households_AllGROUP BY Households_All.HouseholdID, Households_All.ChildrenHAVING (((IIf([Children]>0,1,2))=1))) as Table0 ON [Household].[HouseholdID]=[Table0].[HouseholdID]GROUP BY [Household].[RegStatus], [Sponsorship].[SponsorshipYear], [Sponsorship].[Gifts]HAVING (((Household.RegStatus)=-1) AND ((Sponsorship.SponsorshipYear)=Year(Now())) AND ((Sponsorship.Gifts)=-1))) as 8,(SELECT DISTINCT Count(Household.HouseholdID) AS SDSDFROM HouseholdGROUP BY Household.RegStatus, Household.SDSDHAVING (((Household.RegStatus)=-1) AND ((Household.SDSD)=-1))) as 9; Thanks much,aldo
View 2 Replies
View Related
May 22, 2013
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.
View 14 Replies
View Related
Apr 17, 2014
how to return all values in a query when a form critieria is left blank. I have made some progress, the combo box criteria queries were fairly simple, but i'm getting stuck with my date criteria. My query doesn't return null values when I want it to.
I want it to return all records (including null values) if the form OpenFrom and OpenTo dates are blank, and just the values between the selected dates (excluding null values) if the form is completed.
Code:
SELECT qryReportSelector2_Authority.*, qryReportSelector2_Authority.ApplicationDate AS ApplicationDateFilter
FROM qryReportSelector2_Authority
WHERE (((qryReportSelector2_Authority.ApplicationDate) Between Nz([Forms]![frmReportSelector]![OpenFromDate],DMin("[ApplicationDate]","[qryAllCases]")) And Nz([Forms]![frmReportSelector]![OpenToDate],DMax("[ApplicationDate]","[qryAllCases]"))));
View 8 Replies
View Related
Apr 18, 2006
Hello all,
A bit of a weird one, I've got a query and the criteria for showing records is that one particular field is null. However the query is showing records with the values in the field chosen for the Is Null.
Not sure why this is happening, has anyone come across this problem before?
Thanks.
View 4 Replies
View Related
Aug 8, 2014
What I am trying to do is merge data from several tables into one query, based on user defined options through multiple listboxes. I've figured out the VBA for the union all for the different selections, my problem is I can't determine how to make it dynamic and skip over a select statement if the list value is null. My code looks something like this so far.
Code:
Dim db As DAO.Database
Set db = CurrentDb
Dim qdf As DAO.QueryDef
Set qdf = db.QueryDefs("Flex2")
[code]...
View 5 Replies
View Related
Feb 15, 2008
I have 3 queries, one generates a calculation for a phone extension, the other generates a calculation for another field for the same phone extension, in the third query, I am using the data from the first 2 queries to show the data side by side, but when I run the third query, if any of the calculations have a null value, it leaves the whole phone extension out. Does anyone know a way around this?:eek:
View 4 Replies
View Related
Sep 12, 2007
i have checked the forums and i know they kind of answer this question, but im not really sure where i am ment to write it!
I have two queries, which a third query subtracts the 1st query and 2nd query values and gives the end value. If the 2nd value is null, i get a null value at the end, as i need to change this to zero instead. i know i need to use nz (as seen in microsoft access help, and other places on this forum) but im not sure exactly where to put this on my query, and in which query.
Basically:
(qryCountpartNo.CountPartCode)-(qryTotalquantityallocated.sumofquantity) = qryTotalFree
both countpartcode and sumofquantity may be null at any time
Any help would be greatly appreciated!
Thanks in advance,
Emily
View 14 Replies
View Related
Feb 9, 2015
I have a query feeding a form. It displays Active Orders. So the SQL is:
Code:
SELECT Orders.[Order ID], Orders.[Employee ID], Orders.[Customer ID], Orders.[Order Date], Orders.[Shipped Date], [Order Price Totals].[Price Total] AS [Sub Total], Orders.[Shipping Fee], Orders.Taxes, [Sub Total]+[Shipping Fee]+[Taxes] AS [Order Total], Orders.[Ship Name], Orders.[Ship Address], Orders.[Paid Date], [Orders Status].[status name] AS Status
FROM [Orders Status] RIGHT JOIN (Orders LEFT JOIN [Order Price Totals] ON Orders.[Order ID] = [Order Price Totals].OrderID) ON [Orders Status].[Status ID] = Orders.[Status ID]
ORDER BY Orders.[Order ID] DESC;
It has worked for the past year. However today is gives me an Invalid Use of Null error and will not load.
View 14 Replies
View Related
Sep 15, 2014
I am having a query, where a few date records are null because of no entry or record, what a want that the field with no date entry to be come up with zero "0" .
View 1 Replies
View Related
Aug 24, 2014
I need the following to display a zero if a null value is returned:
SELECT tdsIndivData.dsReportID, Count(tdsIndivData.StaffID) AS CountOfStaffID
FROM tdsReportData INNER JOIN tdsIndivData ON tdsReportData.dsReportID = tdsIndivData.dsReportID
WHERE (((tdsIndivData.Availability)="75% Availability"))
GROUP BY tdsIndivData.dsReportID;
View 5 Replies
View Related
Aug 30, 2013
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
View 4 Replies
View Related
Apr 24, 2006
Hi there, instead of blank fields in my crosstab query, and hence my report, i would like a standard comment, such as 'No Booking'.
My crosstab is something like this:
.........A...........B..............C............. D
1.......X
2.......X..........................X
3...................X............................. .X
(Ignore the dots, obviously)
Currently, when i try to open the report based on this query, it fails unless each column has data in it for at least one record.
I've tried using the Nz function but haven't been able to make it work. Thankyou in advance! :)
View 5 Replies
View Related
Jan 17, 2014
I am trying to alter this parameter to bring back all records if either beginningsalesrange or ending salesrange is left blank. I can't quite get it right.
Between [forms]![frmState]![BeginningSalesRange] And [Forms]![frmState]![EndingSalesRange]
View 13 Replies
View Related
Jun 20, 2015
I have a query which i need to get Zero as a result if NULL value. How Can i do this ? Present SQL code is as follows;
SELECT tbl_Impts_main.Bkg_number, Count(tbl_Impts_main.Bkg_number) AS CountOfBkg_number
FROM tbl_Impts_main INNER JOIN tbl_booking ON (tbl_Impts_main.Voyage=tbl_booking.Voyage) AND (tbl_Impts_main.Vessel=tbl_booking.Vessel) AND (tbl_Impts_main.Bkg_number=tbl_booking.Bkg_number)
GROUP BY tbl_Impts_main.Bkg_number
HAVING (((tbl_Impts_main.Bkg_number)=[forms]![frm_Export_data_entry]![bkg_number]));
View 7 Replies
View Related
