Results 1 to 4 of 4
  1. #1
    JamesP
    Guest

    How to test what is returned by query?

    HI
    I am testing attempt to test if a specific record exists within a table in Form Module.
    SQLstr = "SELECT tblInspectionFootages.FootTaken" _
    & " FROM tblInspectionFootages" _
    & " WHERE tblInspectionFootages.Eid=" & [Me].[EqipmentID] _
    & " AND tblInspectionFootages.FootTaken)=" & [Me].[Footage] & ";"

    Using this query. Is there a way to test if the query returns nothing?
    <font color=red>Thanks!</font color=red>

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    The Netherlands
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to test what is returned by query?

    Hi,

    Use the dCount function, it will return the number of records that complies with your WHERE clause.

    It should look something like this:

    lngNumberOfRec = dCount("FootTaken", "tblInspectionFootages", "Eid=" & [Me].[EqipmentID]& " AND FootTaken=" & [Me].[Footage])

  3. #3
    Star Lounger
    Join Date
    Feb 2001
    Location
    Wirral, Merseyside, Merseyside, England
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to test what is returned by query?

    dim rst as recordset

    SQLstr = "SELECT tblInspectionFootages.FootTaken" _
    & " FROM tblInspectionFootages" _
    & " WHERE tblInspectionFootages.Eid=" & [Me].[EqipmentID] _
    & " AND tblInspectionFootages.FootTaken)=" & [Me].[Footage] & ";"


    'open the recordset
    Set rst = db.OpenRecordset(SQLstr)

    'check for 0 records returned
    If rst.Recordcount = 0 Then
    'this does nothing if no records found
    Else
    rst.MoveFirst
    Do Until rst.EOF = True
    'carry on here

  4. #4
    JamesP
    Guest

    Re: How to test what is returned by query?

    Thanks Caz and Bart!
    Just what I was looking for!
    [img]/w3timages/icons/cool.gif[/img]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •