Query Returns No Results, I need to see a 0

  • Thread starter Thread starter Lisa Schwaegerl
  • Start date Start date
L

Lisa Schwaegerl

I have a query which will often return no results which is ok, because there
are no matches, but I need to somehow get it to return a 0 so I can use the
number in a calculation.

Thanks,
Lisa
 
Please elaborate. What is the SQL of your query, and what do you expect to
see?

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
Hi Lisa,

Thanks for your post. According to your description, I am not quite clear
your accurate concern. Do you mean you want to return 0 when there is not
any records return form the query? Please try the following codes to see if
it meets your requirements.

//////////////////////////////////

Dim cnn1 As New ADODB.Connection
Dim cmd1 As ADODB.Command
Dim rst1 As ADODB.Recordset

'Create the connection to another database.
cnn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Program Files\Microsoft Office\Office10\" & _
"Samples\Northwind.mdb;"

'Define and execute command to select all ProductID field 'values from a
single table.
Set cmd1 = New ADODB.Command
With cmd1
.ActiveConnection = cnn1
.CommandText = "Select ProductID from [Order Details]"
.CommandType = adCmdText
.Execute
End With

'Assign the return set to a recordset.
Set rst1 = New ADODB.Recordset
rst1.CursorType = adOpenStatic
rst1.LockType = adLockReadOnly
rst1.Open cmd1
MsgBox rst1.RecordCount

////////////////////////////////////////////

Note: Because I am not sure the version of Access on your side. I assume
you are using Access 2002 and the sample database is under "C:\Program
Files\Microsoft Office\Office10\Samples\Northwind.mdb". Do not forget the
add the ADO reference when you complie your Access database.

In that case, if there is not any record returned, the 0 will be returned
in the message box. Then, you can use your code to check the return value
and perform the appropriate action.

Please feel free post in the newsgroup if it addresses your problem.

Regards,

Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
 
Michael-

Your example won't necessarily return all products, and it certainly doesn't
return a zero for "missing" products. A better example would be:

"Show me the sales for all products on May 1, 1998."

qrySales01May1998:
SELECT ProductID, Sum(CCur([Order
Details].UnitPrice*[Quantity]*(1-[Discount])/100)*100) As TotSales
FROM Orders INNER JOIN [Order Details]
ON Orders.OrderID = [Order Details].OrderID
WHERE Orders.OrderDate = #01 MAY 1998#
GROUP BY ProductID

qrySalesAllProducts01May1998:
SELECT Product.ProductID, Products.ProductName,
CCur(NZ(qrySales01May1998.TotSales, 0)) As Sales
FROM Products LEFT JOIN qrySales01May1998
ON Products.ProductID = qrySales01May1998.ProductID

You'll get a list of *all* products, with a zero displayed for products that
did not sell on that date.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
"Yuan Shao" said:
Hi Lisa,

Thanks for your post. According to your description, I am not quite clear
your accurate concern. Do you mean you want to return 0 when there is not
any records return form the query? Please try the following codes to see if
it meets your requirements.

//////////////////////////////////

Dim cnn1 As New ADODB.Connection
Dim cmd1 As ADODB.Command
Dim rst1 As ADODB.Recordset

'Create the connection to another database.
cnn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Program Files\Microsoft Office\Office10\" & _
"Samples\Northwind.mdb;"

'Define and execute command to select all ProductID field 'values from a
single table.
Set cmd1 = New ADODB.Command
With cmd1
.ActiveConnection = cnn1
.CommandText = "Select ProductID from [Order Details]"
.CommandType = adCmdText
.Execute
End With

'Assign the return set to a recordset.
Set rst1 = New ADODB.Recordset
rst1.CursorType = adOpenStatic
rst1.LockType = adLockReadOnly
rst1.Open cmd1
MsgBox rst1.RecordCount

////////////////////////////////////////////

Note: Because I am not sure the version of Access on your side. I assume
you are using Access 2002 and the sample database is under "C:\Program
Files\Microsoft Office\Office10\Samples\Northwind.mdb". Do not forget the
add the ADO reference when you complie your Access database.

In that case, if there is not any record returned, the 0 will be returned
in the message box. Then, you can use your code to check the return value
and perform the appropriate action.

Please feel free post in the newsgroup if it addresses your problem.

Regards,

Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Click to expand...
 
Hi Lisa,

How is the issue going on your side? Does our suggesioins make sense? Let
us know if you need further assistance on this issue.

Regards,

Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
 
Back
Top