MS Access ListBox Help

I have a list box and i am trying to get it to select reports, but not all of the reports say i have 20 reports i want it to select 4 i tried to use this and sows nothing


SELECT msysobjects.Name
FROM msysobjects
WHERE (((msysobjects.Name)="qry_drivers_log(Main)" & "rptCreditCard(Main)" & "pay" & "rptDispatchMain") AND ((msysobjects.Type)=-32764));

this works for showing all reports


SELECT msysobjects.Name
FROM msysobjects
WHERE (((msysobjects.Type)=-32764));

and this works for selecting one report

SELECT msysobjects.Name
FROM msysobjects
WHERE (((msysobjects.Name)="rptDispatchMain") AND ((msysobjects.Type)=-32764));

Also is there a way to rename the report just for listbox purpose for the customer to view it as like Dispatch Report instead of rptDispatchMain, and also need these to all be Me.lstRptName.ItemData(0), what i mean is i dont want to have more then one column for the name of the report i want them to all show in one column
Blueice13085Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
try this

SELECT msysobjects.Name
FROM msysobjects
WHERE msysobjects.Name In("rptDispatchMain","report2","report3","report"4) AND msysobjects.Type=-32764
Rey Obrero (Capricorn1)Commented:
oops


try this

SELECT msysobjects.Name
FROM msysobjects
WHERE msysobjects.Name In("rptDispatchMain","report2","report3","report4") AND msysobjects.Type=-32764

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Blueice13085Author Commented:
alright that works, but can i make the name of a report change only in the list box but act report name stays same?
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Rey Obrero (Capricorn1)Commented:
what?
Blueice13085Author Commented:
what i mean is in the list box i want say "rptDispatchMain" to show AS "Dispatch"
als315Commented:
There are two possible ways:
1. If your reports have similar names and you can create function, which will trim left and right part of report name, try to do it.
for rptDispatchMain: mid(msysobjects.Name,4, len(msysobjects.Name)-7)
2. Create table from query above, Add field for name and use it in list box. In this case every time, when you will add report, you will need update this query and add nemes for them.
Jeffrey CoachmanMIS LiasonCommented:
One issue at a time please...

1. Your question text:
"I have a list box and i am trying to get it to select reports,"
The SQL you are posting will LIST reports in the listbox, not SELECT  them
Can you please clarify if you want to DISPLAY certain reports or SELECT them?

Also in your text you state:

this works for showing all reports
SELECT msysobjects.Name
FROM msysobjects
WHERE (((msysobjects.Type)=-32764));

and this works for selecting one report
SELECT msysobjects.Name
FROM msysobjects
WHERE (((msysobjects.Name)="rptDispatchMain") AND ((msysobjects.Type)=-32764));

Again, here the SQL will list the reports not select them.
Displaying the report names and selecting certain reports from a listbox are two separate operations.
Please clarify which you are trying to do please...

If you want to "Select" an item from a listbox you can use code like this:
    ' "Selects" the Item
    Me.lstReports.Selected(3) = True
    ' Makes the Value of the Listbox the Value of the Selected Item
    Me.lstReports.Value = Me.lstReports.ItemData(3)
See here too:
http://allenbrowne.com/func-12.html

3. The issue of "Renaming" reports in the listbox is a separate issue and should be dealt with in a new question.
This avoids problems with deciding who answered what parts of which question...

As far as I can tell capricorn1 has answered the "first" Main question, and als315 has addressed the second question.

Please remember that anytime you have the word "Also" in your question, consider making this a separate question if it is not directly related to the main question.
This avoids confusion/conflicts.
;-)

JeffCoachman
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.