We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Test for BOF in a recordset

CatDaddy2003
CatDaddy2003 asked
on
Medium Priority
283 Views
Last Modified: 2006-11-17
Here is the SQL statement I currently have:

SELECT tblEqpt.EqptID, NZ([Amount],"0") AS CalcAmount, NZ([COAmount],"0") AS CalcCOAmount, NZ([InvoiceAmount],"0") AS CalcInvoiceAmount, (NZ([Amount])+NZ([COAmount])-NZ([InvoiceAmount])) AS [Amount Remaining]
FROM tblReq INNER JOIN ((tblEqpt INNER JOIN tblChangeOrder ON tblEqpt.EqptID = tblChangeOrder.EqptID) INNER JOIN tblInvoice ON tblEqpt.EqptID = tblInvoice.EqptID) ON tblReq.ReqID = tblEqpt.ReqID
WHERE (((tblEqpt.EqptID)=[Forms]![frmRequisition]![txtLink]));

2 possibilities will exist
1. tblChangeOrder may not have any rows
2. tblInvoice may not have any rows

If this is the case, I need the calculation to be performed just as if [COAmount] were 0 and [InvoiceAmount] were 0.

I thank you in advance for your assistance.
Comment
Watch Question

Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
CERTIFIED EXPERT
Top Expert 2016

Commented:
Save your query as Query1

Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim rs As Recordset
    Set db = CurrentDB()
    Set rst = db.OpenRecordset("Query1", dbOpenDynaset)
     
  If rst.BOF = True Then  'No records

 'your codes for calculation

    End If
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.