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

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

Hello everyone,

I've got a big problem, so I'll try to be crystal clear.

My Database
My database contain informations about soil. The Soil_T have different fields : ID_Soil (Auto#), Type (linked to another table), description (long text)... and Treatment (Multivalued field or many-to-many).
This tabe is linked to Analyse_T wich contain :ID_Analyse (Auto#), ID_Soil (number), Masse (number)...

1drv.ms/x/s!AqNow7kzXdyKhEM-p5ptOdSDrIC2

What I want to do

In antoher form, I have many field that allows me to filter the table Soil_T and Analyse_T. Basicly, this code -> allenbrowne.com/ser-62code.html ; allenbrowne.com/ser-62.html
So I am filtering the form with a filter with fields in the header.

It works number one when Treatement is a multivalued field except that I can't use the field Treatement to filter.

It seem to works when I am doing a many-to-many Relationship. When I am filteringwith the field treament, it works. But when I am filtering with another field, if a record has two Treaments, it's going to appear twice ! Not what I want.

Waht I want

I want to be able to filter with all the fields and I want every record to show only once.

The filter field (in the header) can be a combobox or a multivalued field. Multivalued field would be nice, but it may be to complicated.

Thanks a lot. I hope I am clear enough

PS: I looked a loooooooot on the internet and on the forum. I didn't find an answer :/

LeslyP


Last edited by LeslyP; 04-27-2018 at 01:21 PM.
LeslyP is offline   Reply With Quote
Old 04-27-2018, 10:26 AM   #2
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,242
Thanks: 94
Thanked 2,026 Times in 1,973 Posts
jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light
Re: Filter a database that contain a multivalued field

Most will tell you to avoid multivalued fields.
Tell us about the application and database. Plain English.
What stage of development are you at?
__________________

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.
jdraw is offline   Reply With Quote
Old 04-27-2018, 10:35 AM   #3
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

My data base is done (well, draft version) except for the Treatment field in the Search form.
In the header of the seach form, I can search with "Description", "Type",... it's going to work. In the result, the record is going to appear once ONLY IF the Treatment values are in ONE FIELD. Ontherwise, my record is going to appear twice.
PS: the result zone is a continous form.

I know multivalued field are not great, but I need my many-to-many relationship to show result in one field (one row in a query for exemple).

Need other informations ?

LeslyP is offline   Reply With Quote
Old 04-27-2018, 10:40 AM   #4
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,242
Thanks: 94
Thanked 2,026 Times in 1,973 Posts
jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light
Re: Filter a database that contain a multivalued field

Can you post a copy of your database? You can post an attachment to the forum if you have less than 10 posts if you use ZIP format.

A picture of your table/relationships would be helpful.

Do you have a clear description of the requirements of this database? That would be helpful to anyone offering assistance.

Good luck.
__________________

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.

Last edited by jdraw; 04-27-2018 at 10:45 AM.
jdraw is offline   Reply With Quote
Old 04-27-2018, 11:03 AM   #5
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,271
Thanks: 15
Thanked 1,595 Times in 1,515 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

There is nothing the Multi-Value field offers that you can't do in more traditional ways. When you use multi-value fields, you need to use specialized SQL syntax because Multi-Value fields are not supported by standard SQL syntax. I don't use them, ever so I am not familiar with the syntax but you should be able to locate it in help or Google. That also makes them not upsizable although that will probably not be a problem for you unless you expect to convert the BE to SQL Server or some other RDBMS. I've attached a sample m-m database so you can see how to implement m-m correctly. The MV field works only for a very restricted subset of possibilities. As soon as you need intersection data, you will need to abandon the MV field anyway so you might want to consider it now.
Attached Files
File Type: zip ManyToMany_A2016.zip (1.51 MB, 37 views)
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 04-27-2018, 11:33 AM   #6
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

Ok !
Pat Hartman:
Thank you for your exemple. However, I know how to do many-to-many Relationships. The problem is filtering. Records with many Treatment appear multiple times.

Jdraw:
This data base is use to store and find analyses of soil. I did a clean and English version of my data base. Ok !
Attached Files
File Type: zip Help_LeslyP.zip (1.45 MB, 41 views)
LeslyP is offline   Reply With Quote
Old 04-27-2018, 01:05 PM   #7
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

I got the version with the Treatment in the header as a filter too. But it doesn't work.
Attached Files
File Type: zip Help_LeslyP_bug.zip (1.48 MB, 40 views)

LeslyP is offline   Reply With Quote
Old 04-27-2018, 01:07 PM   #8
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,271
Thanks: 15
Thanked 1,595 Times in 1,515 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

Sorry, I can't help you with Multi-Value fields. I do not use them. the price for the "cool" control is too high. I prefer to do things the old fashioned way.

1. I don't see a multi-value field in any table.
2. I do see 2 table level lookups. This is another "feature" with too many problems to overcome so I also do not use it.
3. Behind the scenes, the MV relationship is implemented using a junction table. Consequently, the query will return the same record for each match in the junction table. You might be able to resolve this if you look up the correct SQL syntax. You might be able to get back one row with multiple values separated by commas but I can't say for certain.

To "fix" the problem in the current state, requires too much work.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 04-27-2018, 01:15 PM   #9
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

Pat Harman
Like I said in my original post, it's a MV OR a many-to-many. You said MV were to much trouble, so I change it for a many-to-many Relationship.
For the 2 table level lookups, it seems fine for the moment.
LeslyP is offline   Reply With Quote
Old 04-27-2018, 01:28 PM   #10
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,242
Thanks: 94
Thanked 2,026 Times in 1,973 Posts
jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light
Re: Filter a database that contain a multivalued field

Quote:
But it doesn't work.

What exactly does this mean? Error message?
Can you give instructions on how to test your form?

I would not use lookups at table field level. Would recommend using lookup table.
__________________

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.
jdraw is offline   Reply With Quote
Old 04-27-2018, 01:37 PM   #11
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

jdraw
Ok I was trying Something. When I do the Search with Treatment in a combobox, Nothing happen. But when I do the search with a textbox, there are results. (But I don't want to use the text box...)
However, there a still duplicates of the same record if it have more than one treatment.

Search with combobox
Code:
 If Not IsNull(Me.cbo_treatment) Then
    strWhere = strWhere & "([Treatment] like ""*" & Me.cbo_treatment & "*"") AND "
End If
Search with textbox
Code:
If Not IsNull(Me.txt_treatment) Then
    strWhere = strWhere & "([Treatment] like ""*" & Me.txt_treatment & "*"") AND "
End If
LeslyP is offline   Reply With Quote
Old 04-27-2018, 01:47 PM   #12
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,059
Thanks: 115
Thanked 3,021 Times in 2,748 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

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
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
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.


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.


I know that you know that we both know nothing
isladogs is offline   Reply With Quote
The Following User Says Thank You to isladogs For This Useful Post:
LeslyP (04-28-2018)
Old 04-27-2018, 02:25 PM   #13
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
Hummmm, I see. There is the Soil_T wich contain ID_Soil, the Treatment_T wich contain ID_traitement and Treament, and Treament_link_T wich contain ID_Soil and ID_Treatment.
I am going to try this tomorrow morning ! This might solve at least one problem. Thank you!

Still, I need to figure out how to fit the Treatment value in one field.
LeslyP is offline   Reply With Quote
Old 04-27-2018, 02:26 PM   #14
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 jdraw View Post
I would not use lookups at table field level. Would recommend using lookup table.
Excuse me, what do you mean ?
LeslyP is offline   Reply With Quote
Old 04-27-2018, 04:25 PM   #15
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

I just find a code that would allow me to stock all the Treatment in one row. I'll to try this tomorrow and see if it's works.

Code:
Option Compare Database
Option Explicit
Public Function SimpleCSV(strSQL As String, _
            Optional strDelim As String = "," ) As String
'Downloaded from theDBguy's site
'Returns a comma delimited string of all the records in the SELECT SQL statement
'Source: http://accessmvp.com/thedbguy
'v1.0 - 8/20/2013
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strCSV As String
Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
'Concatenate the first (and should be the only one) field from the SQL statement
With rs
    Do While Not .EOF
        strCSV = strCSV & strDelim & .Fields(0)
        .MoveNext
    Loop
    .Close
End With
'Remove the leading delimiter and return the result
SimpleCSV = Mid$(strCSV, Len(strDelim) + 1)
Set rs = Nothing
Set db = Nothing
End Function
Query
Code:
SELECT DISTINCT tblMyData.Order, SimpleCSV("SELECT [Stage] & ' ' & [Person] & ' ' & [StageDate] FROM tblMyData WHERE [Stage] is not null And [Order] = " & [Order]," " ) AS StagePersonDateSeries
FROM tblMyData;
https://forum.hardware.fr/hfr/Progra...t_121325_1.htm

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 06:40 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