Queries :: Return Result When No Records Are Found?
May 2, 2013
Any way to build something into a sub-query that says 'if no records are found, return '0' or some other string'?
Otherwise is there a way to make a master query ignore sub-queries if they return no records?
Allow me to explain in more detail: I have a series of sub-queries, most of these take the sum of several fields from a number of different tables, and I have a main query which combines all of these, to be used as the basis of a summary report.
These queries aren't a problem, but I have a few other essential queries which take the modal (most common....) entry for fields which aren't numerical. So I can't use the sum function.
Now, if all the sub-queries are working then so does the main query, however if one of them fails to find a result, then none of them show up in the main query. I don't know why.
The issue is that depending on the date range selected, some of the tables targeted by the sub-queries don't have any records at all, so when they are run they return nothing. The sum queries can handle this since they just return 0, but those searching for modal records just find nothing (not 0's and not null fields, just blank across all rows).
Here's an example of my sql statement for the modal queries.
Code:
SELECT TOP 1 Trends.Trend AS ModeTboxTalk, "1" AS [Key]
FROM Trends INNER JOIN [Toolbox Talks] ON Trends.TrendID = [Toolbox Talks].TrendID
GROUP BY Trends.Trend, [Toolbox Talks].TrendID, [Toolbox Talks].TalkDate
HAVING ((([Toolbox Talks].TalkDate)<=[Forms]![WeeklyReportSelect]![WeekBox] And ([Toolbox Talks].TalkDate)>[Forms]![WeeklyReportSelect]![WeekBox]-7) AND (([Toolbox Talks].SiteID)=[Forms]![WeeklyReportSelect]![SiteBox]))
ORDER BY Count([Toolbox Talks].TrendID) DESC;
- FYI the weekly select form is where users select the week and site they want to report against. So it would be really peachy if I could tell the above to say something like 'no trend this week' if indeed there were no records.
DateLimiter: (SELECT Date_Retro_Fees_Rate FROM tblRetroFees AS Alias WHERE Date_Retro_Fees_Rate = (SELECT FIRST(Date_Retro_Fees_Rate) FROM tblRetroFees AS Alias2 WHERE Alias2.Date_Retro_Fees_Rate > tblRetroFees.Date_Retro_Fees_Rate AND Alias2.Id_Product = tblRetroFees.Id_Product) AND Alias.Id_Product = tblRetroFees.Id_Product)
All using access 2010. Here's the situation. I built a searchform according to datapigtechnologies video. Used a query with criterias on fields i want to search on the form: ex.
Code: Like [frm]![frmMyform]![MyField]
It worked fine i thought until i ran the same query outside the form without the criteria field. I filtered the query for the same result and the one used on the search form did not return all data records for that particular result set. Ex. on the searchform; I select the fields I wanted to filter then ran the query. I come up with only 9 records when it should be 18 I get with running the exact same query without the criteria and manually filter the results.
I am working with Access 2010, on vista. What I have is a query made up of two tables, one product the other inventory. (see below) query.jpg
In the product table i have a field called "minimum reorder level". In the inventory table i have two fields one called "number in stock" and "number on order". What i want to happen is "number on order" to be filtered by the result, if the "number in stock", is less than "minimum reorder level", if it is, have the result placed in the "number on order" field. EG. if the "number in stock" = 2 and the "minimum reorder level" = 5 then 3 would be placed in the field "number on order" and only the second record from the query would be visible (see below) Query result.jpg The result of this would mean that the field "number on order" would be populated with the result and the and query would also use this to filter the record.
I cannot get my query to return result when I enter the month of "June" as search criteria. June is listed in the table and query, every other month is returned except June.
I have several queries that perform an audit on data. I want to create a form that has two buttons, multiple labels and textboxes. When the first button is pressed, it runs all queries and returns a count of the records within each query into the corresponding textbox. Then I want a a button that will clear all textboxes. I don't want to see the queries, I just want them to run in the background and the count results to be populated in the textboxes.
I have a query that returns several calculated fields. One of them is simply derived by simple summation of the others. If this calculated field returns a negative number, I need it to show as a zero.
The only way I know how to do this is by an IIF statement :
Code: SELECT [fld1], [fld2], [fl3], ..... IIF(([fld1]-[fld2]-fld[3])<0,0,([fld1]-[fld2]-fld[3])) AS fld4 FROM...
(The above doesn't suggest that [fld1], [fld2] etc are calculated fields - I just wrote it like that for succintness - they calculate fine, there's no issue with them...)
Is there a more efficient way of doing this? I find IIF's a bit tardy, possibly because they evaluate for both True & False eventualities, regardless of the condition, and this query is going to run against a fairly large dataset so any performance lag is going to be exacerbated.
Code: SELECT Table1.[Material Key], Table1.[Material Name], Table1.[Info] FROM Table1 INNER JOIN search_MaterialKeys ON [Material Key] = search_MaterialKeys.[MaterialKeySearch];
So at the moment, users paste material numbers in the search_materialKeys table and the query is created with the necessary information. If the number they paste into the search_materialKeys table is not found in Table1 then the query doesnt return anything. I need the query to list all the entered values in the temptable in the query so that users know which of their inputs were not found. Is there any way to do this?
I am setting up a database to hold staff details, and would like a query to show each member of staff's total hours and FTE.
Staff name etc is in tblStaff Staff shift details are in tblShifts linked via staffID
tblShifts will contain details of the shift worked on each day of the week, but the majority of our staff work a standard shift - e.g 8-4, 9-5. Therefore what I wanted to do was in tblStaff set a field named shiftPattern to 1 2 3 or 4. 1 indicating a custom shift, and any other number indicating a set shift defined in a separate table.
The problem I have is that my query only returns people who have details in tblShifts - regardless of their shiftPattern value. If I enter a blank record in tblShifts it will do the above as intended.
When I run the following sql (in a query) I enter information once for [enter cty id]. and it all works fine.
What I would like to do now, is write the vba code so that the sql runs multiple times looping through a list of items.
For instance instead of the popup window asking "enter cty id", I'd like to automatically have the code loop through a list. For example a list of 1 through 10.
Then, in the loop vba code:
after item 1 is run, do an export of the results. after item 2, do an export. etc.
Code:
select a.south_index_id, c.cnty_cd, a.southwest_name_source_cd, sp.south_ssn, sp.estimated_birth_dt, [a.LAST_NAME] & ', ' & [a.FIRST_NAME) PERSONNAME], a.BIRTH_DT, a.GENDER_CD, sp.ms_pmi, cnty_person_id FROM SW_alias_name AS a, SW_south_person AS sp, SW_county_pid AS c WHERE EXISTS (select * from SW_alias_name a2 where ucase(a2.last_name) = ucase(a.last_name)
I am wandering if it possible to check programatically whether a query returns a NULL result?
I am writing a program(VB) that involves an IFF statement where if there are records in the query it does a specific calculation & if there are no records returned in the query it does something else. Is this possible to achieve?
My issue surrounds retrieving the last (based on most recent date) set of records based on the most recent date. I have query, containing 2 tables as the sources for the query results. Currently, the query yields:
Field A Field B Field C 123456 AAAA 1/8/13 123456 BBBBI 1/8/13 123456 CCCC 1/8/13 123456 DDDD 1/8/13 123456 EEEEEE 3/10/13 123456 FFFFFF 3/10/13 123456 GGGG 3/10/13 123456 HHHH 3/28/13 123456 IIII 3/28/13 123456 JJJJ 3/28/13
The desired results would be to return all records with the last/max date, so yield:
I'm trying to create a query to show me records for a given year. The issue I'm having is that each record has (4) dates fields and each record can contain null values.Is it possible to do this in a query with data like the example below?
Field 1 ID (1), Field 2 Date (12/22/2012), Field 3 Date (2/06/2013), Field 3 Date (Null), Field 4 Date (Null)
In this example I would want 2013 data but would need to be able to search any year. I could also have dates in all (4) fields
I've got a database of club members with names, addresses, contact info etc.
I need to send letters by post to those members that I don't have email addresses for. How to create a query to return a list of people whom I don't have email addresses for.
I know how to mail merge the info with the letter I've written in word, how to group people who's email address cell is empty.
I understand that a query can accept wildcards in the search criteria but this is different.
I have a form on which a user selects a [product] part number in an unbound combo e.g. "12345-V111".
I then need a subform on the form to list records where the relevant field matches the left most part of this part number but is not necessarily complete. e.g. records with "12345-V1" would be returned, records with "12345-V2" wouldn't. The number of characters recorded in this field will vary, but if the characters that are entered match, then the record should be listed.
In other words, the record entries themselves need to be treated like wildcard entries.
I have two functions both should have the same results but they do not.
Public Function DefaultGreeting() As String On Error Resume Next DefaultGreeting = "Dear " & [Forms]![frm_contacts]![Dear] & ":" End Function
Public Function DefaultBodyText() As String On Error Resume Next
DefaultBodyText = [Forms]![frm_e_mailing]![mess_text] / this equals this just pulled from a table "Dear " & [Forms]![frm_contacts]![Dear] & ":" End Function
I am trying to create a query that returns records whether a field has data or not...
There are three fields in question, SSN, DOB (this is a date field), POB (this is a foreign key representing a state in the query shows the actual state). Now unless the criterion is different then I just need the answer for one I can reproduce.
I would like to do this in the criteria box in the query.
The query pulls from one table, some of the employees in this table have the three fields populated some don't. I would like the query to return all employees...
I have an existing query, created using the query wizard which works just fine - however, I would like to modify it to return only instances where there are 3 or more records appearing.
Essentially, its an employee history report for a particular action done by those employees, which returns all records between two dates as specified by the user. What I would like to do is only show those employees who have had more than three instances of this action in the given date period.
I am thinking along the lines of DCount? but how it would be phrased in the query?
I have a table that has 3 columns: Unique number, Date, and Results
I want the user to enter the unique number and date into the form. Then the "results" column/field will autopopulate a 0 or 1. I want it to populate a 0 85% of the time and a 1 15% of the time. This should be cumulative (meaning not every entry has a 15% chance of being 1). Is this possible?
I display some buttons on my Form. I should do an SQL request to know how many and what the button should display. It works fine with a RecordSet. Now I want to move the code that do the stuff to a function. I read that I can't return a pointer to a RecordSet and I should used a variant with a getrows.
So I try this :
*here is my function
Function get_nom_operation(ByVal cnn As ADODB.connection) As Variant Dim requetteSQL As String Dim rst As New ADODB.Recordset
Set res = get_nom_operation(conn) Dim i As Integer 'i = 1
For i = LBound(res) To UBound(res)
Set Obj = Me.Controls.Add("forms.CommandButton.1") With Obj .Name = "monButton" & i .Object.Caption = res(0,i) .Left = 14 .Top = 25 * i .Width = 60 .Height = 20 End With
'ajout de l'objet dans la classe Set Ge = New gere_event Set Ge.CButton = Obj Collect.Add Ge i = i + 1
Next
But it doesn' work, and I don't know why...
The error doesn't show where the code is stoped, but only show the call to display this form.
someone could show me some way to find the solution please ?
I have a table with duplicate records (which is ok) and I want to return distinct data for each client.
It works fine when there is only two fields returned however, when I add a third field to the query it no longer returns only the Distinct records - I am getting Duplicates returned.
I.E
SELECT DISTINCT tblClient.ClientNo, tblClient.Name FROM tblClient
Works fine with only the Distinct records for each client returned
However
SELECT DISTINCT tblClient.ClientNo, tblClient.Name, tblClient.Address, tblClient.OrderValue FROM tblClient
Now returns Duplicates!
Is there a limit to the number of fields to be returned using DISTINCT or what else could be the problem? Should I be doing this some other way?
