Results 1 to 4 of 4

Thread: Query Help

  1. #1
    Join Date
    Jan 2004
    Posts
    9

    Unanswered: Query Help

    How can I display a 'No Records Found' message as a query result if there are no matches for the query criteria? Basically I want it to return any data that matches the criteria or if no records match return the message 'No Records Found'.

    I have tried the following but nothing is returned.

    SELECT IIf(IsNull(tbl_Employees.[FIRST NAME]),'No Match Found',tbl_Employees.[FIRST NAME]) AS FirstName, IIf(IsNull(tbl_Employees.[LAST NAME]),'No Match Found',tbl_Employees.[LAST NAME]) AS LastName, IIf(IsNull(tbl_Employees.DOB),'No Match Found',tbl_Employees.DOB) AS DOB
    FROM tbl_Employees, tbl_NoFly
    WHERE (((tbl_Employees.[FIRST NAME])=tbl_NoFly.FIRSTNAME) And ((tbl_Employees.[LAST NAME])=tbl_NoFly.LASTNAME) And ((tbl_Employees.DOB)=tbl_NoFly.DOB));

    Any help would be appreciated!

    Thanks
    MEB

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    assuming matching rows ARE returned, what do you do with them?
    (list, combo, form or ????)

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Quote Originally Posted by mBlack
    How can I display a 'No Records Found' message as a query result if there are no matches for the query criteria? Basically I want it to return any data that matches the criteria or if no records match return the message 'No Records Found'.
    Instead of using a SELECT query, use a MakeTable query, and dump the results into a new temporary table that you can empty out after use. (Run the query in a way that you know you'll get results the first time.) Before displaying the results, check the .RecordCount property. If it's 0, display 'No Records Found' using MsgBox.

    After using the query the first time, delete all the records and change the query to an append query. That way you won't get error messages about the table not existing when you check the .RecordCount property.

    Sam
    Last edited by Sam Landy; 07-13-06 at 14:23.

  4. #4
    Join Date
    Jan 2004
    Posts
    9
    I am inserting the matching records into a results table. If no matches are found I want to insert the value "No Match Found" into the same table to be used later in a report. There is other information in the query that gets inserted into the results table as well. I removed them in the example for simplicity sake. The way it was currently working if no matches were found the other information was not being inserted which need to be there. Such as the name of the file that was loaded into the NoFly table and the scan date.
    Last edited by mBlack; 07-13-06 at 14:26.

Posting Permissions

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