Refine listbox contents

[Allison]

Access 2003, Win XP, all service packs installed.

I used Allen Browne's SQL code to populate a list of report names for a
combo box on my main form (http://allenbrowne.com/ser-19.html). This works
fabulously!

Two things.

1. Can I somehow filter out the "rpt" at the front of these report names?

2. Can I somehow choose to display only those reports that contain a
certain word, such as "distribution"?

I was fiddling around with modifying the "WHERE" statement, but my knowledge
of SQL is almost null. I can read it, but I can't create it.

Any pointers? Or is this not possible?

Thanks for your help.

Allison

 

[KARL DEWEY]

Try these but only one at a time --
1- Using the actual field name for 'report names' --
Report_Names: Right([report names], Len([report names])-3)

2- Add to the WHERE statement --
AND [report names] Like "*distribution*"

 

[Allison]

Thanks Karl, that was perfect!

Try these but only one at a time --
1- Using the actual field name for 'report names' --
Report_Names: Right([report names], Len([report names])-3)

2- Add to the WHERE statement --
AND [report names] Like "*distribution*"

--
Build a little, test a little.

Access 2003, Win XP, all service packs installed.

I used Allen Browne's SQL code to populate a list of report names for a
combo box on my main form (http://allenbrowne.com/ser-19.html). This works
fabulously!

Two things.

1. Can I somehow filter out the "rpt" at the front of these report names?

2. Can I somehow choose to display only those reports that contain a
certain word, such as "distribution"?

I was fiddling around with modifying the "WHERE" statement, but my knowledge
of SQL is almost null. I can read it, but I can't create it.

Any pointers? Or is this not possible?

Thanks for your help.

Allison

 

[Douglas J. Steele]

SELECT IIf(Left([Name], 3) = "rpt", Mid([Name], 4), [Name]) FROM MsysObjects
WHERE (([Type] = -32764) AND ([Name] Not Like "~*") AND ([Name] Not Like
"MSys*")
AND ([NAME] LIKE "*distribution*"))
ORDER BY [Name];

 

[gael]

Access 2003, Win XP, all service packs installed.

I used Allen Browne's SQL code to populate a list of report names for a
combo box on my main form (http://allenbrowne.com/ser-19.html). This
works
fabulously!

Two things.

1. Can I somehow filter out the "rpt" at the front of these report
[email protected]

2. Can I somehow choose to display only those reports that contain a
certain word, such as "distribution"?

I was fiddling around with modifying the "WHERE" statement, but my
knowledge
of SQL is almost null. I can read it, but I can't create it.

Any pointers? Or is this not possible?

Thanks for your help.

Allison