Query on a field with several values using a form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table with a field that has several values separated by a ; and a
space (the values are entered into the table through a milti-select box).
I know and I have been told that it is not advisable to have fields
containing several values, but in this case it seems to serve my purpose best.
My question is how can I use a form to place a parameter in a query that
will filter the multi-value field? For example on fields with only one value
the: forms![formname]![value] works fine. But it doesn't work on the
multi-value fields.
How is the exact syntax if I need to use LIKE and/or * or maybe something
else?
Thanks in advance.
Niels
 
NielsE said:
I have a table with a field that has several values separated by a ; and a
space (the values are entered into the table through a milti-select box).
I know and I have been told that it is not advisable to have fields
containing several values, but in this case it seems to serve my purpose
best.
My question is how can I use a form to place a parameter in a query that
will filter the multi-value field? For example on fields with only one
value
the: forms![formname]![value] works fine. But it doesn't work on the
multi-value fields.
How is the exact syntax if I need to use LIKE and/or * or maybe something
else?
Thanks in advance.
Niels
Click to expand...

My honest opinion is that you should reconsider your design before you go
too far down this road. I truely think you're setting yourself up for a lot
of pain... it might be easier initially this way, but a little more work
now, setting it up might pay you dividends in the future.

My suggestion, is if there are a few constant number of items, create one
column for each. If there are a lot or the quantity is very variable,
assuming each item in your table has a unique key, you could create a new
table that has a row for each value, linked back to the main table by the
key:

Your Way:
Id Item Values
1 XYZ this;that;the other
2 ABC one;two;three


My Way 1:
id Item Value1 Value2 Value3
1 XYZ this that the other
2 ABC one two three

My Way 2:

Id Item
1 XYZ
2 ABC


Id Value
1 this
1 that
1 the other
2 one
2 two
2 three

However, if you do think this really is the best way to go for your
particular situation, I guess you could try somthing like this:

Say your field contains: 'this;that;the other' and you want to match the
second item, you could use somthing like: LIKE '*;that'
or to match the third item: LIKE '*;*;the other'
 
I agree with Chris M. You really should consider redesigning your table
structure.

If you can't or won't, then try criteria like the following

Field: SearchThis: "; " & [Your Field] & ";"
Criteria: Like "*; " & [Find What] & ";*"

This will find records where the value you input is between two semicolons.
If the items don't have a semicolon and a space then remove the space after
the semicolon.

If you just want to match on partial occurences and don't care if you have
complete match then try
Field: [Your Field]
Criteria: Like "*" & [Find What] & "*"

Chris M said:
NielsE said:
I have a table with a field that has several values separated by a ; and a
space (the values are entered into the table through a milti-select box).
I know and I have been told that it is not advisable to have fields
containing several values, but in this case it seems to serve my purpose
best.
My question is how can I use a form to place a parameter in a query that
will filter the multi-value field? For example on fields with only one
value
the: forms![formname]![value] works fine. But it doesn't work on the
multi-value fields.
How is the exact syntax if I need to use LIKE and/or * or maybe something
else?
Thanks in advance.
Niels
Click to expand...

My honest opinion is that you should reconsider your design before you go
too far down this road. I truely think you're setting yourself up for a
lot of pain... it might be easier initially this way, but a little more
work now, setting it up might pay you dividends in the future.

My suggestion, is if there are a few constant number of items, create one
column for each. If there are a lot or the quantity is very variable,
assuming each item in your table has a unique key, you could create a new
table that has a row for each value, linked back to the main table by the
key:

Your Way:
Id Item Values
1 XYZ this;that;the other
2 ABC one;two;three


My Way 1:
id Item Value1 Value2 Value3
1 XYZ this that the other
2 ABC one two three

My Way 2:

Id Item
1 XYZ
2 ABC


Id Value
1 this
1 that
1 the other
2 one
2 two
2 three

However, if you do think this really is the best way to go for your
particular situation, I guess you could try somthing like this:

Say your field contains: 'this;that;the other' and you want to match the
second item, you could use somthing like: LIKE '*;that'
or to match the third item: LIKE '*;*;the other'
Click to expand...
 
Thanks Chris and John,
Your suggestion works fine John.
But you are probably both right that I should consider to re-construct the
tables to have single values only.
Niels

John Spencer said:
I agree with Chris M. You really should consider redesigning your table
structure.

If you can't or won't, then try criteria like the following

Field: SearchThis: "; " & [Your Field] & ";"
Criteria: Like "*; " & [Find What] & ";*"

This will find records where the value you input is between two semicolons.
If the items don't have a semicolon and a space then remove the space after
the semicolon.

If you just want to match on partial occurences and don't care if you have
complete match then try
Field: [Your Field]
Criteria: Like "*" & [Find What] & "*"

Chris M said:
NielsE said:
I have a table with a field that has several values separated by a ; and a
space (the values are entered into the table through a milti-select box).
I know and I have been told that it is not advisable to have fields
containing several values, but in this case it seems to serve my purpose
best.
My question is how can I use a form to place a parameter in a query that
will filter the multi-value field? For example on fields with only one
value
the: forms![formname]![value] works fine. But it doesn't work on the
multi-value fields.
How is the exact syntax if I need to use LIKE and/or * or maybe something
else?
Thanks in advance.
Niels
Click to expand...

My honest opinion is that you should reconsider your design before you go
too far down this road. I truely think you're setting yourself up for a
lot of pain... it might be easier initially this way, but a little more
work now, setting it up might pay you dividends in the future.

My suggestion, is if there are a few constant number of items, create one
column for each. If there are a lot or the quantity is very variable,
assuming each item in your table has a unique key, you could create a new
table that has a row for each value, linked back to the main table by the
key:

Your Way:
Id Item Values
1 XYZ this;that;the other
2 ABC one;two;three


My Way 1:
id Item Value1 Value2 Value3
1 XYZ this that the other
2 ABC one two three

My Way 2:

Id Item
1 XYZ
2 ABC


Id Value
1 this
1 that
1 the other
2 one
2 two
2 three

However, if you do think this really is the best way to go for your
particular situation, I guess you could try somthing like this:

Say your field contains: 'this;that;the other' and you want to match the
second item, you could use somthing like: LIKE '*;that'
or to match the third item: LIKE '*;*;the other'
Click to expand...
Click to expand...
 
Back
Top