• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 249
  • Last Modified:

Return all records if none found?!?!

I have a query in my Access database. This query accepts a parameter and searchs records based on the input... More often than not, no records are found. in this case i want all the records to be returned... Can you tell me how to modify my query to accomplish this?!

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

Thanks in advance!

Nugs
0
Nugs
Asked:
Nugs
  • 7
  • 4
  • 4
  • +3
1 Solution
 
sinjinCommented:
I would think about using the OnNoData event of the report to change the record source to select * from tbl Agents.
best,
 - Sinjin
0
 
NugsAuthor Commented:
Can you explain further?
0
 
ArjiCommented:
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

0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
ArjiCommented:
Also, the above code depends on what programming interface you use(DAO or ADO).  It is currently for DAO
0
 
ArjiCommented:
Oops, a typo!

RS.RecordCount
0
 
sinjinCommented:
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
0
 
sinjinCommented:
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

0
 
sinjinCommented:
Hi Arji -
looks like great minds think alike ;-)
 - sinjin
0
 
ArjiCommented:
yeah, I noticed ;-)
0
 
jrb1Commented:
Here it is in a simple query:

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;
0
 
sujit_kumarCommented:
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;
0
 
JulianvaCommented:
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)
0
 
NugsAuthor Commented:
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
0
 
NugsAuthor Commented:
jrb1 your post above returns all records regardless of if the parameter passed exists or not...
0
 
NugsAuthor Commented:
sujit_kumar same for your example....
0
 
NugsAuthor Commented:
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...
0
 
jrb1Commented:
Did you use the parentheses around the "where" statement where you say all records are returned?  It's works fine for me (Access 2000).
0
 
NugsAuthor Commented:
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?
0
 
jrb1Commented:
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.
0
 
NugsAuthor Commented:
That worked after some testing... Thanks
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 4
  • 4
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now