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

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 04-28-2018, 04:35 AM   #16
LeslyP
Newly Registered User
 
Join Date: Apr 2018
Posts: 34
Thanks: 16
Thanked 2 Times in 2 Posts
LeslyP is on a distinguished road
Re: Filter a database that contain a multivalued field

Quote:
Originally Posted by ridders View Post
Is Treatment the bound field in your combobox?
If not, you need to use the column number.
This is zero based so the second column is Me.cbo_treatment.Column(1) etc
Ridders Thank you ! It works One problem solved

LeslyP is offline   Reply With Quote
Old 04-28-2018, 04:44 AM   #17
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,017
Thanks: 114
Thanked 3,014 Times in 2,741 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Filter a database that contain a multivalued field

You're welcome. Can you explain your other problem again
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin (Mendip Data Systems)
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


"As we know, there are known knowns; there are things we know we know. We also know there are known unknowns; that is to say we know there are some things we do not know. But there are also unknown unknowns the ones we don't know we don't know. It is the latter category that tend to be the difficult ones" Donald Rumsfeld
isladogs is offline   Reply With Quote
Old 04-28-2018, 05:17 AM   #18
LeslyP
Newly Registered User
 
Join Date: Apr 2018
Posts: 34
Thanks: 16
Thanked 2 Times in 2 Posts
LeslyP is on a distinguished road
Re: Filter a database that contain a multivalued field

Ridders

I am actualy trying something, but here's the other problem:

When I am filtering with other field (example: Description), the records with two Treatments to appear twice (like in a Query). I would like them to appear only once with the Treatment(s) in one textbox.

I am trying to Concatenate the field Treatment with this code :
http://www.accessmvp.com/thedbguy/co...itle=simplecsv

I don't understand where to put the SQL statement, so I contacted the autor.

LeslyP is offline   Reply With Quote
Old 04-28-2018, 05:23 AM   #19
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,017
Thanks: 114
Thanked 3,014 Times in 2,741 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Filter a database that contain a multivalued field

Quote:
Originally Posted by LeslyP View Post
Ridders

I am actualy trying something, but here's the other problem:

When I am filtering with other field (example: Description), the records with two Treatments to appear twice (like in a Query). I would like them to appear only once with the Treatment(s) in one textbox.

I am trying to Concatenate the field Treatment with this code :
http://www.accessmvp.com/thedbguy/co...itle=simplecsv

I don't understand where to put the SQL statement, so I contacted the autor.
The DBGuy is usually very prompt at responding so I'll leave him to answer.
However, there are plenty of similar examples of concatenation both here and on other sites. For example http://allenbrowne.com/func-concat.html
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin (Mendip Data Systems)
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


"As we know, there are known knowns; there are things we know we know. We also know there are known unknowns; that is to say we know there are some things we do not know. But there are also unknown unknowns the ones we don't know we don't know. It is the latter category that tend to be the difficult ones" Donald Rumsfeld
isladogs is offline   Reply With Quote
The Following User Says Thank You to isladogs For This Useful Post:
LeslyP (04-28-2018)
Old 04-28-2018, 10:27 AM   #20
LeslyP
Newly Registered User
 
Join Date: Apr 2018
Posts: 34
Thanks: 16
Thanked 2 Times in 2 Posts
LeslyP is on a distinguished road
Re: Filter a database that contain a multivalued field

Whou ! Now everything works just fine ! It was not very complicated, I don't understand why there is not a clear article about that somewhere.

I am going to post my database later with full instructions.

Thanks again everyone

Last edited by LeslyP; 04-28-2018 at 04:23 PM.
LeslyP is offline   Reply With Quote
Old 04-29-2018, 07:14 AM   #21
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,253
Thanks: 15
Thanked 1,592 Times in 1,512 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Filter a database that contain a multivalued field

Quote:
For the 2 table level lookups, it seems fine for the moment
You won't see the problems until you start writing queries with criteria or VBA. Lookups belong on forms. Putting them on table fields simply masks the true underlying data and that is bad.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 04-29-2018, 11:51 AM   #22
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,570
Thanks: 92
Thanked 1,682 Times in 1,560 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Filter a database that contain a multivalued field

jdraw suggested not using a table with a lookup field, but rather doing a more "traditional" lookup. (My words, not his.) You asked what it means.

I don't know how familiar you are with database normalization, but the idea of normalizing is to reduce extra copies of stuff in tables. So to do a lookup from a list of known, approved values, you do not have a lookup field.

Instead, on forms you build a combo box do to a lookup of the key value from the table that contains the translation of that key value into text. So on a bound form, the key gets stored but the combo box can be "persuaded" to show you the translation thereof. There is a combo-box wizard that would build one of these for you on the form.

The other way this might work is to build a formal relationship between the field in the main table that would hold the lookup key and the key field (which must be the prime key) in the table that contains the translations. Then you can EASILY build a query that JOINS the main table with the translation table so that you can see both the key and its meaning in the query. This is what I commonly do for reports where the translation might be long but the key is just an INTEGER or LONG INTEGER.

The difference between a lookup field and a lookup table linked via a relationship is that with the lookup table, if you don't need the extra baggage of the lookup in a particular process, you don't need to carry along the translations. Then you have no extra implied records. That is where you can pick up extra records you weren't expecting.

For instance, let's say you have a lookup field called StatusCode. The code itself is really just a number from 1 to 8, let's say. If you tried to recover records with a specific value for some other field and tried to do a SELECT DISTINCT that recovered the status but didn't have a WHERE sub-clause for a specific status code, you might see as many as 8 records, one for each code, because the right code wasn't specified. This is why many of us avoid lookup fields.

__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
The Following User Says Thank You to The_Doc_Man For This Useful Post:
LeslyP (04-29-2018)
Old 04-29-2018, 12:33 PM   #23
LeslyP
Newly Registered User
 
Join Date: Apr 2018
Posts: 34
Thanks: 16
Thanked 2 Times in 2 Posts
LeslyP is on a distinguished road
Re: Filter a database that contain a multivalued field

Oh thank you The Doc Man !! I didn't know Access was doing this in my back :P
I though it was the same as creating myself the relationship. I will change all those lookup things.

So, Access is creating functionalities to help us.... but in the end, it's just crap. Like Multivalued fields xD
LeslyP is offline   Reply With Quote
Old 04-29-2018, 01:16 PM   #24
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,570
Thanks: 92
Thanked 1,682 Times in 1,560 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Filter a database that contain a multivalued field

Sometimes, pure merde. Other times, not really so bad.

For instance, the form wizards are really great in erecting a quick-and-dirty but functional scaffold for subsequent customization. I use them all of the time, including the event wizards.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 04-30-2018, 07:41 AM   #25
LeslyP
Newly Registered User
 
Join Date: Apr 2018
Posts: 34
Thanks: 16
Thanked 2 Times in 2 Posts
LeslyP is on a distinguished road
Re: Filter a database that contain a multivalued field

Hello you who is looking for a solution !
you can find it here : https://access-programmers.co.uk/for...d.php?t=299509

LeslyP is offline   Reply With Quote
Reply

Tags
filter , multivalued fields

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Using Filter Property with multivalued fields cmpd Queries 1 12-05-2013 06:09 AM
Hellllp: How do I filter a multivalued Listbox field - VBA alhabkk Forms 3 12-01-2013 03:25 PM
How do I filter a multivalued Listbox field - VBA alhabkk Modules & VBA 1 11-29-2013 10:20 AM
Use a ComboBox to Filter a MultiValued Field thensrud Forms 0 02-14-2011 01:07 PM




All times are GMT -8. The time now is 05:51 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