Return all records if none found?!?!

I would think about using the OnNoData event of the report to change the record source to select * from tbl Agents.
best,
 - Sinjin

Can you explain further?

What you do when you figure out if there are records or not is dependent on what you are trying to accomplish. ie;Open a form, Open a report either based on the recordset determined by the if..then structure.  If [Num] is going to be text you will need to delimit it with apostrophes.

Dim MyDatabase as Database, RS as recordset
Dim st as string

st = "SELECT * FROM TBL_Agents WHERE Fld_Dest1=" & [Num] & " OR Fld_Dest2=" & [Num] & " OR Fld_Dest3=" & [Num]

Set MyDataBase = CurrentDb()
Set RS= MyDataBase.OpenRecordset(st)

If RS.RecordcCount = 0 then
     Open report, open form, etc with all records
else
    Open report, open form, etc with selected records
end if

Also, the above code depends on what programming interface you use(DAO or ADO).  It is currently for DAO

Oops, a typo!

RS.RecordCount

Hi -
First, I am assuming you wanted to print a report.... Is that OK?  Second... please ignore the use of OnNoData..... brain fade on my end.  

try something like this...
when your user enters the criteria and perhaps clicks a button to show the records call this function.  If it is true then do nothing and open the report using the criteria from the user.  If it is false you can can display a no data found message and then display all records.  I hope this makes sense :-)



public function isRecordSetEmpty(UserCritera1 as DATATYPE, UserCriteria2 as DATATYPE, ...) as boolean
dim rs as dao.recordset
dim db as dao.database


dim strSQL as string

strSQL = "SELECT * FROM TBL_Agents WHERE Fld_Dest1=[" & Usercriteria1 & " OR Fld_Dest2=[" & UserCriteria2 & "] OR Fld_Dest3=[" & UserCriteria3 & "] Order by BY Fld_FullName;"

set db = CurrentDb()
set rs = db.OpenrecordSet(strSQL)

if not rs.BOF and NOT rs.eor then
   'you know there is data so run with what the user criteria was
  isRecordSetEmpty = false
else
   'there is no data and you can now substitute SELECT * FROM TBL_Agents as the new recordsource
    isRecordSetEmpty = false
end if

End function

a typo in mine too....

rs.eof is correct not rs.eor

if not rs.BOF and NOT rs.eor then  should be if not rs.BOF and NOT rs.eo>f< then

Hi Arji -
looks like great minds think alike ;-)
 - sinjin

yeah, I noticed ;-)

also try,

Select * from(
SELECT *
FROM TBL_Agents A
WHERE  NOT EXISTS
(SELECT 'X' FROM TBL_Agents WHERE Fld_Dest1=[Num] OR Fld_Dest2=[Num] OR Fld_Dest3=[Num])
UNION
SELECT *
FROM TBL_Agents A
WHERE (Fld_Dest1=[Num] OR Fld_Dest2=[Num] OR Fld_Dest3=[Num]))
ORDER BY Fld_FullName;

If you want to pass a parameter then you need to create a
stored procedure

create procedure report
@num int

as

SELECT *
FROM TBL_Agents
WHERE Fld_Dest1=@Num OR Fld_Dest2=@Num OR Fld_Dest3=@Num
ORDER BY Fld_FullName

the above will create your stored proc

to execute the proc
execute report
@num = (your parameter)

Ok... well thank you guys for all the input... Let me explain a little further and hopefully answer everyones questions....

I'm building a website using .Net technology... ASP.NET, VB.NET and OleDb... My application calls the query that i posted above and passes a parameter (string), the query then returns records based on the parameter i passed. Now like i said more often than not no records are returned by my Access Query...

This is my page code that executes the Access Query... This code will actualy run one of two queries i have stored, although this has nothing to do witht he question.

----------------------------------------------------------------------------------------------------------------------------------------------
sub Page_Load()
      Dim FndQueryString as string
      Dim param as string
      
  If (Session("DestinationQuery") Is Nothing) then
   Response.Redirect("agents.aspx")
  Else
 
'Check Which Query to Run-----------------------------------------
        FndQueryString = Session("DestinationQuery")
    Select Case FndQueryString
          Case "QRY_Interests"
               param = Request.QueryString("ID")
          Case "QRY_Destinations"
               param = Request.QueryString("ID2")
          Case Else
              errorLabel.Text = "Wrong query string"
    End Select

'Then make connection to the DB----------------------------------------
      Dim con As OleDbConnection = New OleDbConnection(System.Configuration.ConfigurationSettings.AppSettings("MM_CONNECTION_STRING_TDGDBConn"))
      Dim cmd As OleDbCommand = New OleDbCommand(FndQueryString, con)
      
      cmd.CommandType = CommandType.StoredProcedure
      con.Open()

'Execute Query based on the above case statement and fill DS with results------
      Dim Parm As OleDbParameter = cmd.Parameters.Add("@Num", OleDbType.VarChar)
      Parm.Value = param
      
      Dim da As OleDbDataAdapter = New OleDbDataAdapter()
      da.SelectCommand = cmd
      
      Dim ds As New DataSet()
      da.Fill(ds, "FilteredRecords")
      DataGrid1.DataSource = ds.Tables("FilteredRecords")
      Datagrid1.DataBind()
      
      con.Close
  End If
 
End Sub
----------------------------------------------------------------------------------------------------------------------------------------------

I was hoping to simply edit the query in access to do this and not have to mess with my page code...

I will try some of your posts and post my comments shortly...

Nugs

jrb1 your post above returns all records regardless of if the parameter passed exists or not...

sujit_kumar same for your example....

I would really like to keep this inside Access like jrb1 and sujit_kumar examples but if anyone has any ideas how to edit my code above to do what i need to then feel free to let me know...

Did you use the parentheses around the "where" statement where you say all records are returned?  It's works fine for me (Access 2000).

Parentheses around the where statement?

This is what i have right now....

SELECT *
FROM TBL_Agents A
WHERE (Fld_Dest1=[Num] OR Fld_Dest2=[Num] OR Fld_Dest3=[Num])
OR NOT EXISTS
(SELECT 'X' FROM TBL_Agents WHERE Fld_Dest1=[Num] OR Fld_Dest2=[Num] OR Fld_Dest3=[Num])
ORDER BY Fld_FullName;

Is that wrong?

No, that looks fine.  Try it without the or not exists:

SELECT *
FROM TBL_Agents A
WHERE (Fld_Dest1=[Num] OR Fld_Dest2=[Num] OR Fld_Dest3=[Num])
ORDER BY Fld_FullName;

Does this ever bring back a matching entry?  If it does, adding back the "or not exists" should not change anything.

That worked after some testing... Thanks