Solved

Excel VBA Error "Subquery returned more than 1 value"

Posted on 2010-08-29
6
862 Views
Last Modified: 2013-11-10
So I have a query in Excel VBA code that's usign a subquery which is returning more than 1 row, so I get this:

"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression"

How do I go about handling this error? I'd like to have a form popup (which Ive already designed" which prompts the user for a name. I'd then run another query specifying that name.

How do I check to see if more than 1 row was returned, or how do I check for this error so I can have the other form display? Thanks!
0
Comment
Question by:asphaltninja
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33553745
You must have a subquery somewhere.
To quick-fix it, add a TOP 1 in there, e.g. for a query like

select customername, (select contactname from contacts where contact.customerid=customer.customerid)
from customer
where customer.id=123

You can change it to

select customername, (select TOP 1 contactname from contacts where contact.customerid=customer.customerid)
from customer
where customer.id=123

The question is would it matter which record of the subquery (in this case contact) is showed?
0
 

Author Comment

by:asphaltninja
ID: 33553770
Hello and thanks for your reply. Yes it would matter; what I'm dealing with here are two separate records that have the same reference number, which is what the query is using in its where clause.

They aren't supposed to have the same reference number but someone (or some people) dropped the ball. I'll eventually have to fix it but for now I'd liek them to be able to then specify the name if there are two records returned to be more specific about the query.

Any suggestions?
0
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 500 total points
ID: 33553784
Using the same example, I would fire a query

select contactname from contacts where contact.customerid=123

And check record count.  If it is >1, loop through the recordset and extract the list of contactname to present to the user. Once a selection is made, add that back to the modified query

select customername, (select contactname from contacts where contact.customerid=customer.customerid and contactname='John')
from customer
where customer.id=123
0
 

Author Comment

by:asphaltninja
ID: 33553808
So you're saying I should use the built-in SQL rowcount function? Should I store the value in a variable and then check the value in my vb code, ie:

Run query, check rows affected
select  @variabletoShowNumberofRowsAffected
strRows = query.ExecSQL("Query statement here")
If Rows > 1 Then
'Display form asking for Name value
'Run new query with name value
End If
0
 
LVL 58

Assisted Solution

by:cyberkiwi
cyberkiwi earned 500 total points
ID: 33553816
>> Run query, check rows affected
That will give you the error.  Which is fine if you use error handling and detect some part of the error text like "Subquery returned more than 1 value"

Otherwise, run the subquery part only.
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question