Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 280
  • Last Modified:

Filtering a Recordset

I have a process where I extract a number into a variable and I need to filter a recordset by that number.  I cant seem to find a method to make this work, the recordset is a linked table.

'Set rst2 = CurrentDb.OpenRecordset("dbo_Proposal", dbOpenForwardOnly)

Set rst2 = CurrentDb.OpenRecordset("select * from JobTest WHERE [Proposal Number] = '[txtJob]';")

            If rst2![Proposal Number] = null Then
                Stop
            Else
                Stop
            End If

any ideas?
0
mjelec
Asked:
mjelec
  • 5
  • 3
1 Solution
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
try this:

Set rst2 = CurrentDb.OpenRecordset("select * from JobTest WHERE [Proposal Number] = " & Chr(34) & Me.[txtJob] & Chr(34) )

           If IsNull(rst2![Proposal Number])  Then
                Stop
            Else
                Stop
            End If
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Typo .... use this:

Set rst2 = CurrentDb.OpenRecordset("select * from JobTest WHERE [Proposal Number] = " & Chr(34) & Me.txtJob & Chr(34) )

mx
0
 
mjelecAuthor Commented:
well that sorta works it seems.

It returns no current record if I try to pass a variable from the recordset but the isnull doesnt seem to register it correctly.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"if I try to pass a variable from the recordset "
Not quite following ...

How about testing for no records first:



    Set rst2 = CurrentDb.OpenRecordset("select * from JobTest WHERE [Proposal Number] = " & Chr(34) & Me.[txtJob] & Chr(34))
    If rst2.RecordCount = 0 Then
        ' No records =do whatever
        Exit Function '  ?
    End If
   
    If IsNull(rst2![Proposal Number]) Then
        Stop
    Else
        Stop
    End If
   

mx
0
 
mjelecAuthor Commented:
Thanks that works perfectly.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
cool. you are welcome ...

mx
0
 
mjelecAuthor Commented:
I also replaced the isnull with the check to see if it exists,

If rst2.RecordCount = 0 Then
                Stop
            Else
                Stop
            End If
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
ok
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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