A have a report that is based on a query that works fine when the query returns records.
However if the query (legitimately) returns no records then the report fields (numeric) are blank.
How can i get the query or report to show either "0" or "no data" when no records are returned? I have tried the NZ function but that didn't work so i don't think that is the right approach here?
Suppose I have two tables with fields as follows:
Assigned Courses (Courses that employees should do)Employee number
Assigned Course Name
Fulfilled Course (Courses that employees have done)Employee number
Fulfilled Course
Now I need to do a query that tells me which employees haven't done. Is there anyway to do that that is not an exclude query?
I'm trying to write record set contents to excel. My query runs perfect in access query wizard, but recordset showing as null. My VBA code
Code:
Dim cnn As ADODB.Connection
Dim recordst As ADODB.Recordset
Dim strSQL As String
Dim strPath As String
Dim appXL As Excel.Application
Dim wb As Excel.Workbook
[Code] ....
I have a complex database app that has a form called from the main form. It requires two inputs: BeginningDate and EndingDate and I use a calendar picker for date selection. Using data assigned to a variable, I build the SQL query in VBA. The result is:
Code:
SELECT [1733_All Print Orders].[Application], Sum([1733_All Print Orders].[TotalImages]) AS SumOfCCPC
FROM [1733_All Print Orders]
WHERE [Application] = 'CCPC' AND [StatementDate] >= #9/3/2013# AND [StatementDate] <= #9/30/2013#
GROUP BY [1733_All Print Orders];[Application]
[1733_All Print Orders] is a defined query that combines 4 tables together and there are data that falls within the dates for CCPC. But the query returns no records.
I pasted the query to the query builder and using different combinations, I isolated that the [StatementDate] >= #9/3/2013# portion is what returns no records
To complicate matters even worse, prior to today, it worked. I made some adds and changes to another area of the application, but did not touch this code.
I am making a report off of a query. The report is returning values from a Option Group. 1=Yes, 2=No and 3=N/A. The report returns the numeric values and I want the value labels instead. So, I created a column in the query for exp1 which looks like the following:
=IIF([VSArrive]=1,"Yes",IIF([VSArrive]=2,"No","N/A"))
What the report returns is #Error.
SELECT tbl_Visits.vst_VisitDate, tbl_Visits.vst_Complaint, tbl_Visits.vst_Diagnosis
FROM tbl_Visits
WHERE (((tbl_Visits.vst_Complaint) Like "*asthma*")) OR (((tbl_Visits.vst_Diagnosis) Like "*asthma*"));
returns records with no occurrences of asthma. about 4 of 638 hits.
How does one handle function errors when running a query? Specifically:
(a) I have a user-defined function that lacks any error handling code except for an Exit Function that gets triggered if there is an unacceptable input parameter (e.g. typo in a flag value).
(b) This works fine when the function is simply part of VBA code or used in the Immediate window of the VBA editor. However,
(c) when the function is used in a SQL Select Query, I get bumped into the de-bugger, and Exit from there does not get me out of the Query. It just gets me out of the current record being processed and then hangs up again on the next.
It seems, therefore, that the function needs to have some instructions that abort the entire query when the function aborts the very first time.
Obvious neophyte issue here, sorry...
I have a table, PURCHASE_ITEMS with 3 fields: ID, TYPE, NAME
Another table, ITEM_TYPES with 2 fields: ID, TYPE
TYPE in both tables is a text field and there is a one-to-many relationship between them.
When I run a query on PURCHASE_ITEMS, I can see all 25 records. When I set a criteria for TYPE to one of the types, no records appear, even though there are 5 or 6 of that type.
Can anyone help me with this absurdly simple problem that I can't seem to get my brain around?
I hope this doesn't sound too simple for this forum, I'm only a newbie!
I have a select query that will display the recordset that meets the criteria (OK I know that's not a big deal) but...I want to know if, when there are no records that meet the criteria, can I open a form (dialog box maybe) that will say there are no records found instead of showing a blank recordset. Once again I hope this is not so blindingly obvious that I have to start watching my coffee intake
Thanks in advance
I import data through different files into a table, the data comes in different formats. I have a problem sometimes some records have double quotation marks and carriage returns. I know how to replace them as
Carriage Returns
Replace([FieldName],Chr(13) & Chr(10),""
Double Quotation Marks
Replace([FieldName],Chr(34),""
But if i want to use an iif statement because in the field there could be a Double Quotation Mark or Carriage return then it does not find the record
iif([FieldName] like Chr(34),Replace([FieldName],Chr(34),"",[FieldName])
I have a query that runs just fine and takes about 1 minute to run on average. I have vba code that uses Dlookup to determine if the query returns any records. The problem is that each time it runs it has to crunch all the data, all I care about is if there is any result at all. Is there a way to get the query to stop after it finds one record to speed things up greatly?
View 2 Replies View RelatedI have a front end that is connected to three back end files. The front end is on my local computer while the back end files are on a network drive.
There are a lot of calculations that go into the queries and intermediate queries. For a report, I have based it on a UNION query.
But when trying to design the report it takes about 45 seconds just to do any one thing, e.g.;
- Add Groupings
- Add Grouping Headers//Footer, sorting option
- Add bound textbox
:eek:
Needless to say this is very annoying.
:mad:
The union query itself runs fine (takes about 15 seconds to run) and returns about 12,000 Rows. The union query looks like this (I changed the field names to make it read easier, hopefully);
SELECT a1, a2, a3, a4, a5
FROM qry_A;
UNION SELECT ALL a1, b2 AS a2, b3 AS a3, a4, a5
FROM qry_B;
UNION SELECT ALL a1, c2 AS a2, c3 AS a3, a4, a5
FROM qry_C;
UNION SELECT ALL a1, d2 AS a2, a3, a4, a5
FROM qry_D;
UNION SELECT ALL a1, e2 AS a2, a3, a4, a5
FROM qry_E;
UNION SELECT ALL a1, f2AS a2, tblG.f3 AS a3, tblG.f4 AS a4, a5
FROM qry_F;
One solution I came across when searching the forums was to use an Append Query to append the query results to a table and base my report on that. This does indeed fix the problem.
But what I was wondering if it was is my query design that is causing it to be slow or is it just the fact that I am returning 12,000 rows?
:confused:
In case it matters, I wanted to mention that I can’t use the report wizard to create the report. When I select the union query, the fields will be showed for awhile then they just disappear. That in and of itself doesn’t cause any trouble since I am creating the report using the design view and not the wizard.
I have a tabbed navigation form. I have a form on one of the tabs, [tab1frm] that a query that consists of 6 fields that are each from a different query.as the record source. This shows a percentage field, an image field, and a count field for current month and then then the same fields for year-to-date.
The problem is that when there are no records for the current month, the whole form [tab1frm] is blank. I read that if no records are returned this can happen, but since there are always records returned for year-to-date, I don't know why its happening. Is there any way to make blank fields show up or to show the most recent records so the form is never blank?
[URL]
My problem is when i use this form as a sub form, it makes the main form close as well. How can I make the main form stay and only the sub form close.
Hello all,
I realise this issue is a common one and it is usually down to simple typos (I've looked at several similar posts) but none of the advice I've seen has solved my problem. I've designed the following Union Query:
SELECT [Email],[Title],[First_Name],[Last_Name],[Company],[Phone],[Country],[TEST_AND_MEASUR],[REC_AND_PROD],[LOG_AND_TRANSC]
FROM [Region 1]
UNION ALL SELECT [Email],[Title],[First_Name],[Last_Name],[Company],[Phone],[Country],[TEST_AND_MEASUR],[REC_AND_PROD],[LOG_AND_TRANSC]
FROM [Region 2]
UNION ALL SELECT [Email],[Title],[First_Name],[Last_Name],[Company],[Phone],[Country],[TEST_AND_MEASUR],[REC_AND_PROD],[LOG_AND_TRANSC]
FROM [Region 3];
It should be very simple and I've checked it over and over, but when I run the query an Enter Parameter dialog appears prompting me to enter Email.
If I just click OK I get all the records but with the email field blank.
Similarly if I type x@y.com it returns all records but with the email fields all containing x@y.com
Advice would be very much appreciated!! Thank you in advance.
Abi
i want to write a query that display records for only last date entered in a table and i want to show all the records from last date entries in a table.
View 6 Replies View RelatedI have a query, and when I execute it, the correct data set is returned. However one field shows "DELETE" as the value for all records.
when I ran it on the SQL server management studio as a test it ran fine. End user does not have access to the tables, so I created a small MS Access application. When I ran the query in MS Access, then connected table dbo_DocRef > Notes shows a value of DELETED.
What am I missing?
My problems is that when I use below setup without the "Notes" tabel, they query works fine, and returns the amount of spares and time used by a single WBS ID. (Customer). But when I add another tabel and link it together. Its returns load of lines with the same data - it looks like its copying its self, over and over again.
View 4 Replies View RelatedI have a grouped query that returns a list of all periods qryPeriod
Code:
SELECT [tblMaster].Year, [tblMaster].Week, ([Year] & " - " & Right(0 & [Week],2)) AS Period
FROM [tblMaster]
GROUP BY [tblMaster].Year, [tblMaster].Week, ([Year] & " - " & Right(0 & [Week],2));
Year | Week | Period
2013 | 48 | 2013 - 48
2013 | 49 | 2013 - 49
2013 | 50 | 2013 - 50
2013 | 51 | 2013 - 51
2013 | 52 | 2013 - 52
2014 | 1 | 2014 - 01
2014 | 2 | 2014 - 02
2014 | 3 | 2014 - 03
2014 | 4 | 2014 - 04
[code]...
these are then used as the start of a between lookup against [tblMaster] along with the last record in the qryPeriod (2014 - 04)...I will need 4 queries so If someone can sort out last period - 8 I can probably do the others.would DLookUp do this say return the last 4 values, Last 6 Values and Last 8 Values
I have Access 2010. If I run this query
Code:
SELECT Reference,
InStr(1,FunctionThatReturnsLongString(), ResponsiblePerson) As MyField
FROM MyTable
WHERE
(Reference ='ShouldBeOut'
OR
Reference = 'ShouldBeIn1'
OR
Reference = 'ShouldBeIn2')
It returns 3 rows, with values in MyField of 0, 23 and 355.
Now I add 1 more where clause to filter on MyField and the query looks like this:
Code:
SELECT Reference,
InStr(1,FunctionThatReturnsLongString(), ResponsiblePerson) As MyField
FROM MyTable
WHERE
(Reference ='ShouldBeOut'
OR
Reference = 'ShouldBeIn1'
OR
Reference = 'ShouldBeIn2')
AND
(
InStr(1,FunctionThatReturnsLongString(), ResponsiblePerson) > 0
)
So, you would think that it should return 2 values (ShouldBeIn1 and 2), but it doesn't. It only return the value where the Instr returns value of 23. The one with the value of 355 also disappears. Why would that be? Surely Instr does not return a byte?
I have a query in my database called "Open Date" which is a date formatted field when imported as DD/MM/YYYY. The criteria is set to:
>=#06/04/2014# And <=#05/04/2015#
The query works just fine. My customer has come back and asked for the way the date is displayed to be in the YYYY-MM-DD format (i know it's not ideal but thats what they want). When i've done that using the following
Format([Table].[Open Date],"yyyy-mm-dd")
The query then returns no data.
I have a database that has over 20 tables in it and am using Access 2000. Unfortunately I cannot change the structure as it performs specific functions, so I am stuck with it.
I have created a Union Query from these tables yaking data from 5 fields using the Serial Number entered by the user.
SELECT[Workstation].UnitPart,PropertyTag,UnitSerial,Date,Technician
FROM[Workstation]
WHERE((([Workstation].UnitSerial)=[Enter Serial No.];
UNION
SELECT[LAPTOP].UnitPart,PropertyTag,UnitSerial,Date,Technician
[Code] ....
I use a bar code scanner to scan the serial number, and it goes through the tables and returns the results along with the other specified fields.
I would like to scan up to 16 or more different serial numbers and have it return the results. Perhaps scan the first 16 serial numbers, then run the query? Is that possible. The serial numbers are unique and will return a combination of laptops, printers, monitors, etc...
I have created a report from the above union query and it works perfectly with only one serial number entered.
I have a table x where the field value is selected via a combo box in a form that is populated from another table z.
When I look in x, it appears to have correctly stored the text from z, not the ID number.
I then built a Query, qX, which looks in x and grabs the fields I want. That query shows the text correctly in each selected field.
Now when I build a form, frmQx and use a List control, it displays some of the fields as ID values from the original table z, not the text values.
How can I get round this? I've searched and searched for an answer, sigh. Maybe I'm just not quite certain how to phrase the search.
In Access 2010, I'm writing VBA code that assigns an ADO dataset as the recordsource for a form. The query itself varies and is constructed from a search term typed in a textbox by the user. The code below works perfectly until the dataset is empty, when no records satisfied the WHERE criteria. In that case, there is a very long pause (presumably a timeout?) and I eventually get the error message "ODBC call failed." I tried to get around this by testing the number of records in the dataset (see toward the end) before assigning it, but it makes no difference in the behavior, so I'm thinking the actual assignment isn't the issue ... something else is.
If it matters, the "FROM qry_beneficial_owners" in this case is predefined query local to my Access db. That query is based on a linked view from a SQL server.
Code:
Public Sub RunSearch(SearchTerm As String)
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sql As String
Dim cols As String
cols = "[acct], [acctname], [planid]"
[Code] ....
i'm creating a search form giving the end user a range of controls to use when filtering/searching data. See the image.But, i think my range search (using the textbox) to put in a lower and upper limit...is preventing this from working. In fact, when i put data into all the controls, no data pops up in my subform.
My query data source can also be seen...showing you how i've handled teh null entries. (i need to put in a null 'handler' for the two textboxes?)
Dear All:
I have created a form with students information, name, address, ID number, etc.
Is there a way to place a checkbox on this form that when it is checked, it displays text on a SPECIFIC AREA ON A REPORT?
I wish it to return this text on a report: "HONORS COLLEGE"
Many thanks in advance,
Dion