I am running a report based on a query. When the query returns zero rows, the count function in the report gives an error. Probably a simple solution but I've searched and can't find an answer. What do I do to stop the report when there is no data?
View RepliesI am using this function
Expr2: Mid$([ItemInventoryAssemblyRefFullName],(InStrRev([ItemInventoryAssemblyRefFullName],"-")+1),(InStr([ItemInventoryAssemblyRefFullName],":"))-(InStrRev([ItemInventoryAssemblyRefFullName],"-"))-1)
to parse this string
4150-ATH:GRP_54X8/BL
into this
ATH
It works great until I get to a string that looks like this
4110-SPECIAL:OLY:LONG-TRACK:REPAIRS
Which I need to return the word SPECIAL but it gives me #Error in the Query.
It would seem that when there are multiple : it is giving the error but when there is only one it works fine. Is there anyther way to atchieve what I am doing?
I am doing this in an update query to get data from Quickbooks into a table in Access.
I 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've been toying with this for a few hours but I always end up with the wrong result. Hopefully an expert here can provide some insight. I have a table called colors with two columns PNAME and COLOR. There are various products and only three possible colors. E.g:
Tablename: COLORS
PNAME COLOR
-----------------------
tshirt blue
pants blue
jacket blue
jacket green
shoes green
vest black
Now what I'm trying to do is create a single query that would provide me with the count of colors in three different columns.
E.g
Blue Green Black
-----------------------
3 2 1
I can get these results easily by creating several queries and then getting the results into one query from these, but I'm trying to learn how to put together nested queries. Any help would be greatly appreciated.
I am trying to count rows in the result of one of the queries and I am having a bit of trouble getting it going.
The current code - this is executed as on-click event when clicked on List Box feed with query below.
What I want to add is simple if that when number of rows produced by the querry is 1 it will enable a picture item in the different part of the form, however it does not want to count the rows for me.
Code:
Private Sub search_items_Click()
Me.OBSFullFilledOrdersHolder.Enabled = True
mysql = "SELECT orders.[order id] , STUDENTS.[first name]& ' ' & students.[surname] AS Name, students.[contact name] AS ContactName , ORDERS.[Online Bookshelf order] AS OBS , STUDENTS.[Delivery Address 1], STUDENTS.[Delivery Address 2], STUDENTS.[Delivery Address 3], STUDENTS.[Delivery Address 4]"
[Code] ....
The query itself works when tested but when used in code with DCount function will return error: Run-Time 2471 the expression you entered as query parameter prouced this error
'[Forms]![FULLFILL ORDERS]![search items].[Column(0)]'.
I have a count column in this query, and i would like for it to return a zero instead of null if it doesnt find anything to count. Here's the SQL for the query.
Code:
SELECT Documents.Status, Count(Documents.Document) AS CountOfDocument
FROM [Request Details] INNER JOIN Documents ON [Request Details].Request_ID = Documents.Request_ID
GROUP BY Documents.Status, [Request Details].Contract, [Request Details].CDRL, [Request Details].Change_Cycle
HAVING (((Documents.Status)="No Record") AND (([Request Details].Contract)=[Forms]![Report Runner]![Contract]) AND (([Request Details].CDRL)=[Forms]![Report Runner]![CDRL]) AND (([Request Details].Change_Cycle)=[Forms]![Report Runner]![ChangeCycle]));
I need to do a count of the total number of rows in a table or query. Say I have a table with 7 records, I want to be able to get total number of rows instead of the sum of the row and save it to some other table.
View 5 Replies View RelatedApologies if this is a basic question. Here's my situation:
I'm trying to update rows in a table based on a count of items in the same table. The table in question contains order line items. On each line item, I'd like to store the total number of items attached to that order (because it affects how the individual line items are processed).
I have a query that seems like it should do the trick, but Access doesn't like it:
UPDATE sales AS S1 SET S1.EXPC = (select count(*) from sales S2 where S2.order_id = S1.order_id AND S2.product_code = "EXPC");
Here's the error I get:
Operation must use an updateable query.
Thoughts?
I need to write a code that checks if every 6 lines in a table contains a string. If it contains the string, then carry on looping, but if it does not, it will delete the current line and the previous 6 lines before it. The program should then continue looping through the entire table.
For example,
Code:
Apple
a
b
c
d
e
Apple
f
g
h
i
j
Apple
l
m
n
o
p
q
Apple
.
.
.
I need to find the string "apple" in every 6 lines. So, that means that the first 2 Apples are fine, but for the third one needs to be deleted as it contains 7 lines instead of 6.
I have an access forum that I need to have a textbox count how many rows have dates in my "Ship_To_Date_HP_" field
View 6 Replies View RelatedI've got a number of different append and delete queries running on command on one of my forms, in which it makes a copy of all of the data on that form and included subforms, copies them into another table, and then deletes all of the data from that record. When this runs, I get an error saying "record deleted" and then another error message. Now it is deleting the records and it is moving them, however when i go onto one of the subforms, all the fields in the subform show "Deleted#" until i go back to the previous record or forward to the next, then they clear, but everytime it brings up the other error message it says "end or Debug" which I don't want it doing.
Is this the query? Or the coding?
Thanks
Hi all,
Resolved the date range issue, thanks, now I have a problem with the query results.
I have a query based upon several other queries that have the StaffID and the linking factor. When I combine some of the queries it works fine, but with others, seemingly those that have no valves or data in the tables, the query brings back no results what so ever.
The queries are running a simple count of records so they have, StaffID input, date range and it counts the records for that staff member within the date range. The second query gives a sum of the count of the first query.
But when I try to combine the queries so that I can produce a report for multiple work areas for audit, I hit problems, is there another, easy way for me to do this, maybe creating a report from invididual query results and only combining them for the report itself.
HELP! Can not attach actual db because of sensitive nature of the information contained, but would be willing to create a like db as sample.
Thanks for any assistance!
The following query never returns, even though there is an index on the name field in the Patient table:
SELECT p.Name FROM sheet4 AS s INNER JOIN Patient AS p ON p.NAME LIKE s.LASTNAME + ', ' + s.FIRSTNAME + ' %'
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 am creating a database for a hyperthetical car hire company. A customer hires a car from and until a certain date. If a new customer decides to hire a car i want to generate a list of cars that he can choose on depending on the other dates from which other cars are hire from and until. I have created a query that generates all of the cars that the new customer can not use.
I have also generated a list of all of the cars, in the database.
I have created a new query takes the numberplates of all the cars and subtracts the numberplates of the cars that are being used.
This is where i hit a problem. For some reason the query is generating the list of number plates twice and then subtracting the numberplates that are being used. This leaves me with with 2 values for every numberplate that can be used and 1value for every numberplate that cannot be used. Can you help me?
Please post your email address so i can send you the zip file as the file is too large to upload. The query that i am having problems with has the title:SEARCH FOR AVAILIABLE CARS.
Thank You
Hi all,
I think this could be another one of my stupid moments. Here is my query expression -
IIf([WHAT TEAM?]=1,[strCurrent_Team]="FINANCE",[strCurrent_Team]<>"FINANCE")
When run, the user is prompted to enter a team number. If they enter 1, then by my reckoning the query should return all records where [strCurrent_Team] = "Finance".
However, no records are returned.
strCurrent_Team is formatted as text.
Can anyone tell me what I am doing wrong here?
Cheers,
Rob
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
Hi, i'm hoping someone here might be able to help me. I have come to a bit of a dead end with a database application i am working on.
The database is a delivery newspaper management system for a newsagents. Basically i need it to link customers to the paper they wish to receive, organise them into delivery rounds, and produce a bill for each customer based on the newspaper they get delivered.
The problem I am currently having is when it comes to the billing.
I have:
a table that stores customer details
a table that stores newspaper details (inc. price)
a table that stores the customerID and then the NewspaperID for each day of the week. (since not all customers get a paper everyday of the week)
If a customer gets two papers then two entries are made in the requirements table under there CustomerID)
Now to fetch the price of the newspaper the customer is down to receive each day and then add these all together is where i have been having trouble.
After many different attempts the solution i am currently using is this. I have a query for each day of the week. Each query take the customerID from the requirements table, then the newspaper price from the table storing newspaper details.
The problem here is that the query returns the CustomerID next to every newspaper in the database. I found the solution was this. I added the newspaperId from the newspaper details table and in the criteria stated "[tableRequirements].[NewspaperID]" And this worked perfectly.
Untill i added a customer who gets two papers. As far as i can tell the query is returning all the possible combinations of which newspaper the customer should get.
Any help with this would be greatly appreciated - thanks in advance.
I am trying to find an Access solution to handling the MAX 2/3rds (I.E. 2from3 or 8from12 etc) returns from a series of "Scores" of a league table which will have many players, playing in many matches. I have created a crosstab query which delivers the total scores for each player from each game and played around with a make table query and a report output which I have used in excel to deliver the best 2/3rds of the scores. As others will be using the completed data base I would like to keep the solution within Access but I cannot work out how or if this is possible. Below is a limited output from the crosstab query showing the Total of all games played which I wish to turn into best 2/3rds.
View 5 Replies View RelatedHi,
I'm trying to create a database project for college and have run into a problem with a query. I am trying to find all bookings that have taken place in the last month, but with added details from other tables.
I have taken the job details and date (with validation for the last month only) from my Jobs table, and this works perfectly. However, when i try to match customer IDs to their names (stored in a seperate table), Access returns the same job multiple times with every customer name possible.
Any help you can give me to return just the one result needed would be greatly appreciated.
Mompo
This is at least the 3rd time I've come across something that is, to me, an extremely serious bug in Access. Anyone else seen this, and anything I can do to avoid it??
In a query, I ask for the values from Field A.
The query returns the values from Field B, but still calls it Field A.
This is in a fairly complex query. If I delete one particular field from the query, the bug disappears. If I put that field back, the bug returns.
Here is my current query:
SELECT ByPN_1.PartNumber, ByPN_1.LostPerSetup, ByPN_1.ActualMachine, ByPN_1.PermQty, ByPN_1.FirstOfFeederSize, ByPN_1.Leaf1, ByPN_1.FirstOfType, ByPN_1.NonPerm, IIf([nonperm]*[concurrentsetups]<1,1,CLng([nonperm]*[concurrentsetups])) AS QtyIfDed, [nonperm]*[setupsperday] AS MaxLoadsSavedPerDay, [qtyifded]*[setupsperday]/[concurrentsetups] AS DedLoadsSavedPerDay, IIf([maxloadssavedperday]<[dedloadssavedperday],[maxloadssavedperday],[dedloadssavedperday]) AS LoadsSavedPerDay, [loadssavedperday]*[laborrate]*[loadunloadperfeeder]*250/60 AS Labor_Annual, master_attr.STD_COST, [loadssavedperday]*[lostpersetup]*[std_cost]*250 AS Parts_Annual
FROM (ByPN_1 LEFT JOIN FeederCost1 ON (ByPN_1.ActualMachine = FeederCost1.Machine) AND (ByPN_1.FirstOfFeederSize = FeederCost1.Size) AND (ByPN_1.FirstOfType = FeederCost1.Type)) LEFT JOIN master_attr ON ByPN_1.PartNumber = master_attr.ITEM
WHERE (((ByPN_1.PartNumber)=107573));
HEre's the pertinent part of the record from BYPN_1:
PartNumberLostPerSetupFirstOfFeederSize
107573 3 12X4
But here's what the query returns:
PartNumberLostPerSetupFirstOfFeederSize
107573 12X4 12X4
Now, if I just delete the "parts_annual" field from the query:
SELECT ByPN_1.PartNumber, ByPN_1.LostPerSetup, ByPN_1.FirstOfFeederSize, ByPN_1.ActualMachine, ByPN_1.PermQty, ByPN_1.Leaf1, ByPN_1.FirstOfType, ByPN_1.NonPerm, IIf([nonperm]*[concurrentsetups]<1,1,CLng([nonperm]*[concurrentsetups])) AS QtyIfDed, [nonperm]*[setupsperday] AS MaxLoadsSavedPerDay, [qtyifded]*[setupsperday]/[concurrentsetups] AS DedLoadsSavedPerDay, IIf([maxloadssavedperday]<[dedloadssavedperday],[maxloadssavedperday],[dedloadssavedperday]) AS LoadsSavedPerDay, [loadssavedperday]*[laborrate]*[loadunloadperfeeder]*250/60 AS Labor_Annual, master_attr.STD_COST
FROM (ByPN_1 LEFT JOIN FeederCost1 ON (ByPN_1.ActualMachine = FeederCost1.Machine) AND (ByPN_1.FirstOfFeederSize = FeederCost1.Size) AND (ByPN_1.FirstOfType = FeederCost1.Type)) LEFT JOIN master_attr ON ByPN_1.PartNumber = master_attr.ITEM
WHERE (((ByPN_1.PartNumber)=107573));
Now the query returns what I expect:
PartNumberLostPerSetupFirstOfFeederSize
107573 3 12X4
I'm trying to assign the result of an SQL query to a variable using VBA in Access. The query returns a value but the variable which it is assigned to has a value of Nothing. Here is the code snippet:
Dim queryReturnID As String
queryReturnID = "select dbo_tbl_SupplierReturn.ReturnID from dbo_tbl_SupplierReturn" & _
" where SupplierID = " & lstPOHdr.Column(1)
Debug.Print queryReturnID
Dim RecordSet1 As DAO.RecordSet
Set RecordSet1 = CurrentDb.OpenRecordset(queryReturnID)
Can someone help me please???:rolleyes: I have to query for the avg balance from customer table, custbal field from a certain state... I've tried everything and cannot seem to get the expression correct. How do I do this???? any help is appricated :D
View 2 Replies View RelatedI have a table that stores the criteria that the query is supposed to pull from so that when a user logs into my database, it reads that the person is part of a sepcific unit and then pulls that specific criteria and places it in a text box on a form. When they click a button on that form, it then opens the form that is connected to the query. The data type for the field I need criteria for is a number, so the criteria that shows up in the text box is this "1 or 3 or 5" so that it shows those specific numbers. If i put that directly into the query, it works just fine, but if I try to connect it from the text box in the form, it gives me a "data mismatch error" or an error explaining that criteria is too vast for access to pull the information. How do I make it read the information in there so that I dont have to create 1000 forms for each different unit.
Example:
Field: Unit
Table: Master Log Data
Criteria: 1 or 2 or 3
Works fine!
Field: Unit
Table: Master Log Data
Criteria: [Forms]![Selector]![Criteria]
The text box that is referenced in the criteria in the query says 1 or 2 or 3 and then I get an error... If the information is the same, why does it work when I place it in the query specifically, but not when its in the text box?
I am trying to assign teams to players. I have an import table with all of the players information listed. What I want to do is determine the count of players in a given city. For every 9 players I want to add a new record to the Team table and assign the team number (auto incremented for each team created). Then I want to add the players to the Players table with the Team Number that was created.
View 8 Replies View RelatedI looked thru the forums and didn't seems to get what I needed;
I want a count query to give me all fields in "group by" column, and assign 0 to where there are no count for certain fields.
I tried to set Outputallfield to "yes" but got an error message saying that there is a data type mismatch. The query ran just fine when outputallfield was set "no" and gave right results.
Can anyone help, please?