Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 02-21-2007, 01:13 PM   #1
dkinnz
Registered User
 
Join Date: Jan 2007
Posts: 29
Thanks: 0
Thanked 0 Times in 0 Posts
dkinnz is on a distinguished road
Searching multiple columns for partial text

Hi,

I've been searching through the forum and I've found a few helpful posts, but I'm still having trouble pulling everything together.
Here's the situation:
In a user form, a value is selected from a combo box. I then want a search to be done on a query to look for that value and filter down to all the records that contain that value. The issue is the value that what I'm looking for is located in any one of three known columns (out of about 50 columns total), and the value that is being searched for is only a partial text in the field.

For example...
I select the value "Monk" from the combo box. I then want to search three columns in the query for it but the fields containing "Monk" actually might be something like "Monk-123" or "Monk ABC".
In summary, I want to filter out all records that don't contain the partial-text "Monk" which is located in any one of those three columns.

Thank you a ton for ANY help you can give!
dkinnz

dkinnz is offline   Reply With Quote
Old 02-21-2007, 01:56 PM   #2
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,296
Thanks: 13
Thanked 4,112 Times in 4,044 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Try

WHERE Field1 Like "*" & Forms!FormName.ComboName & "*" OR Field2 Like "*" & Forms!FormName.ComboName & "*" OR Field3 Like "*" & Forms!FormName.ComboName & "*"
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 02-22-2007, 01:47 AM   #3
Oldsoftboss
AWF VIP
 
Oldsoftboss's Avatar
 
Join Date: Oct 2001
Location: in the shed
Posts: 2,504
Thanks: 0
Thanked 57 Times in 49 Posts
Oldsoftboss will become famous soon enough
Download the sample I posted here.. http://www.access-programmers.co.uk/...d.php?t=123219

It has what pbaldy suggests above, but it is saved as a query. Then when a key is pressed, the list is simply requeried. Not much code, and easy to edit.

Dave

__________________
Apathy is on the increase, but who cares.

Imagine if there were no hypothetical questions

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Oldsoftboss is offline   Reply With Quote
Old 02-22-2007, 07:16 AM   #4
dkinnz
Registered User
 
Join Date: Jan 2007
Posts: 29
Thanks: 0
Thanked 0 Times in 0 Posts
dkinnz is on a distinguished road
Cool. Thanks for the replies. It's exactly what I was looking for.
dkinnz is offline   Reply With Quote
Old 02-22-2007, 12:20 PM   #5
dkinnz
Registered User
 
Join Date: Jan 2007
Posts: 29
Thanks: 0
Thanked 0 Times in 0 Posts
dkinnz is on a distinguished road
So, I ran into a problem and I've been trying to find an answer to it in this forum. It's related to the parital text search problem that I'm having but with a twist. I try and search the forum the best I can before I post...

I created a combo box whose values aren't in a query table, but each combo box value corresponds to data that I want to search for in the query.
For Instance,
User selects "Alpha" from the combo box.
"Alpha" actually corresponds to the partial text values of "A123", "B123" and "C123" which are in the query table. So, if the user selects Alpha, what would the code be to search through the column(s) to look for any or all of those values?

I'm guessing it would look something like this...
Code:
If cboGroup.Value = "Alpha" Then

'The following is definitely wrong...so what would go here?
Set SearchValue = "A123" Or "B123" Or "C123"  

'ElseIf cboGroup.Value = "Beta" Then
'etc.

End If
Any help is extremely appreciated!
dkinnz is offline   Reply With Quote
Old 02-23-2007, 07:06 AM   #6
dkinnz
Registered User
 
Join Date: Jan 2007
Posts: 29
Thanks: 0
Thanked 0 Times in 0 Posts
dkinnz is on a distinguished road
Looking over my last post, I'm not sure if it's very clear what I'm trying to do. I guess I'm just trying to figure out how to define a variable that is equal to multiple values...?
PLEASE help if you can!

Cheers,
dkinnz

dkinnz is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Summing multiple columns in the same query... ewomack Queries 9 01-14-2019 01:04 PM
Export Query Results to Multiple Text Files malissab General 0 08-25-2006 12:24 PM
Text file to MS Excel multiple file import application tokunbo General 3 07-22-2006 11:08 PM




All times are GMT -8. The time now is 04:11 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World