We help IT Professionals succeed at work.

Pull only some queries with SysObjects select statement

Kevin Smith
Kevin Smith asked
on
336 Views
Last Modified: 2008-02-01
I have the following statement to pull a list of queries (Access adp, SQL Server backend):

SELECT name FROM dbo.SysObjects WHERE (type = 'V') or (type = 'P')

How do I get it to pull ONLY queries with a certain name (example: if I have a bunch of queries, some are named User Query Stage 1, User Query Stage 2, qry_buster, qry_sample, and I want the box to pull only the queries that begin with "User Query")?

Kevin
Comment
Watch Question

Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Leigh PurvisDatabase Developer
CERTIFIED EXPERT

Commented:
SELECT name FROM dbo.SysObjects S WHERE ((S.type = 'V') or (S.type = 'P')) and S.name Like 'User Query%'
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Commented:
woow
CERTIFIED EXPERT
Top Expert 2016

Commented:
try
where left([name], 10)='User Query' And ((type = 'V') or (type = 'P'))

Commented:
woow?
Leigh PurvisDatabase Developer
CERTIFIED EXPERT

Commented:
Wey hey - it's a pile in!!!!

Author

Commented:
What's the type for reports?
CERTIFIED EXPERT
Top Expert 2016

Commented:
report type is  -32764      
Leigh PurvisDatabase Developer
CERTIFIED EXPERT

Commented:
Not in an ADP.
CERTIFIED EXPERT
Top Expert 2016

Commented:
sorry,
 not sure about ADP
in mdb  report type is  -32764  

Author

Commented:
ah, adp...anyway to do it in ADP?

Author

Commented:
I'm actually gonna assign points on this and start a new question...will post link in a sec.

Commented:
you could use Where ParentID = dlookup("Id","dbo.SysObjects","Name = 'Reports'")....

Author

Commented:
https://www.experts-exchange.com/Databases/MS_Access/Q_22131671.html

(new question, related to the questions I was gonna start asking here)

------

I'm splitting points between Jillyn and flavo...I combined their answers to get the code working:

SELECT name FROM dbo.SysObjects WHERE ((type = 'V') or (type = 'P')) AND (Name Like 'User Query%');

Thanks!
Kevin
Leigh PurvisDatabase Developer
CERTIFIED EXPERT

Commented:
If you want a list of reports from an ADP you'll need to iterate the collection in code.
Assigned to a list control...

    Dim objAcc As AccessObject
    Dim strList As String
   
    For Each objAcc In Application.CurrentProject.AllReports
        strList = strList & ";" & objAcc.Name
    Next
    Me.lstReports.RowSourceType = "Value List"
    Me.lstReports.Rowsource = Mid(strList, 2)

Commented:
Thanks for the split! :)

Author

Commented:
I know this is PAQ'd, but LPurvis, how would I apply a filter to the report name (to show only reports with a certain name in the title)?

Kevin
Leigh PurvisDatabase Developer
CERTIFIED EXPERT

Commented:
Do you mean a part of its name?
For example only Reports who's name begins with rptOrder

    Dim objAcc As AccessObject
    Dim strList As String
    Const cName as String = "rptOrder"
   
    For Each objAcc In Application.CurrentProject.AllReports
        If Left(objAcc.Name, Len(cName)) = cName Then
            strList = strList & ";" & objAcc.Name
        End If
    Next
    Me.lstReports.RowSourceType = "Value List"
    Me.lstReports.Rowsource = Mid(strList, 2)

Author

Commented:
Perfect!  Thanks Leigh...I put this under a new question so I could give you some points :)

https://www.experts-exchange.com/Databases/MS_Access/Q_22133172.html

Kevin
Leigh PurvisDatabase Developer
CERTIFIED EXPERT

Commented:
Hi - this Q is already at 500 so you'll not be able to create another based on its content.
No worries though.  As long as you're sorted.

Author

Commented:
sorted...and thanks!
Leigh PurvisDatabase Developer
CERTIFIED EXPERT

Commented:
Welcome.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.