extracting records from a query based on text input when the field has a string of text

[tstark02]

Hello. I am trying to build a query that will pull records based on a field that contains the following text. "graduate, undergraduate, vocational/occupational, highschool, master, postgraduate"

I toyed with the LIKE instruction in the criteria line using wild cards but I cannot seem to get it right. When I created an input parameter query and searched for undergraduate it would also return graduate and postgraduate.

Basically I would like the user to select their preferred text and then pull the records out this query that contain only the text they select.

Other possible text in this field could be:

"graduate, postgraduate"
"undergraduate, master, graduate"

. . .and many other combinations of degrees.

This is for a scholarship database. You can see that I am trying to isolate only certain types of degrees through this field. The field just lists all possible degree combinations separated by a comma.

Any input or advice would be very helpful.

Thank you in advance,

tstark02

 

[Golom]

Without seeing the SQL you are using I can't be sure but something like

WHERE Instr("," & DegreeField, "," & [Type Degree Name]) > 0

may work where
DegreeField is the field containing comma-separated degrees and
[Type Degree Name] is a user supplied parameter specifying which degree you want.

The prepended commas just allow you to find the first degree in the list even though it is not preceded by a comma.

I guess that LIKE could also be used in the same way

WHERE "," & [Type Degree Name] & "*" LIKE "," & [DegreeField]

 

[dhookom]

Golom has a workable solution however you might have issues with the Instr() since it looks like the field contains some spaces before or after the commas.

Have you considered normalizing your table so that you aren't storing multiple values in a single field? This is generally considered bad practice.

Duane
Hook'D on Access
MS Access MVP

 

[tstark02]

I had put some thought into what it might take to do this, but unfortunately I am not sure of how I could do this. I am not savvy enough with Access at this point. It would be ideal but for me I fear it might be a little over my knowledge base.

 

[dhookom]

Ok, so you might choose to not normalize but did you try Golom's suggestion?

Duane
Hook'D on Access
MS Access MVP

 

[tstark02]

I will be doing that once I get in my office this evening. . . .I will update this thread and let you know at that time if the solution was sufficient.

 

[Golom]

Duane does have a point (as he usually does). The solution that I posted depends critically on the format ,DegreeName1,DegreeName2, ... etc.. If the name of a degree does not immediately follow the comma then it won't work properly and you would need to resort to a user-defined function of some sort to adjust the formatting.

Normalizing data like this has been a frequent topic here at Tek-Tips and I'm sure that the gurus here can lead you through the process with minimal pain and discomfort.

 

[dhookom]

I think you could use the following to get rid of all the spaces and make sure there are leading and trailing commas.

WHERE Instr("," & Replace(DegreeField," ","") & ",", "," & [Type Degree Name]& ",") > 0

Also, IMO there are no good uses for parameter prompts in queries. All user interface should be through controls on forms.


Duane
Hook'D on Access
MS Access MVP

 

[MacroScope]

In a standard Select Query, in the DegreeField criteria line, enter the following.

Like [Enter desired degree]

Of course [Enter desired degree] doesn't exist as a field, so when you run the query a box will pop saying, "Enter desired degree", essentially asking what it is. You then enter postgraduate, as an example, and the expression then translates to Like "postgraduate".

The only caveat is that the user will either have to enter degrees exactly as recorded (high school is not the same as highschool, for example), or the user could put in post* or high* into the box.

 

[SkipVought]

@tstark02.

There are a number of problems with your approch. dhookom suggested that you normalize your data as a "Best and Accepted Practice."

There are reasons for this, one of which is to avoid the pain and suffering that you are currently experiencing as well as the time and expense that this delay is causing to YOUR PRODUCTIVITY and the productivity of your enterprise!

Not only that, but also, what happens when SOMEONE misspells and types a variant for ANY of the words that you expect to find in this list??? IT HAPPENS! And it is another reason, a BIG reason, why values like this are maintained and selected from a managed list of expected values as "Best and Accepted Practices".

Penny wise, but dollar foolish.

Either learn proper database design so you can do it yourself, hire a professional to do it for you, or continue to suffer nagging the results of ignorance and apathy.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]