Multiple chose - dynamic criterias (1 Viewer)

E

Erik_seb

New member
Local time
Today, 09:00
Joined
Feb 24, 2018
Messages
9
Hi,

i have a table that holds a set of criterias, named tlblCriteria. This table consits of a number of colums that equals each criteria. The rows in the table are unique ControlType.

By a user Interface this allow the user to choose one ore moore of the criterias to define a set of criterieas that represent a ControlType.

I have then a Query that combines each criteria from TblCriteria by the "&" so a "key" is calclulated. In a separate Query based on TblReference i generate a simular key based on the corresponding criterias in that table.

The keys is then compared to eachother (key from tbl Criteria and key from tblReference, and if a match the column "ControlType" in Reference table are updated to the ControlType defined in TblCriteria.

This work as a charme in those cases the user has used ALL the colums. But not in the cases the user only choses one or moore (not all) of the possible criterias.

The problem is that when generating the key from TblReference i dont know exactly which Fields the user has chosen in TblCriteria (or if the user later changes the choises).

Is there any way to build an Expression in the Query that checks what Fields (criterias) the user has chosen in tblCriteria and only generate the key from TblReferance from those exact Fields? This would make the Whole thing a lot moore dynamic.

TblCritaria

ControlType: S1
Criteria_A - YES
Criteria_B - NO
Criteria_C - left blank by user
Criteria_D - left blank by user

Query: Criteria_A & Criteria_B & Criteria_C & Criteria_D =YESNO (Key)

TblReference

Data1
Data2
etc.
Criteria_A - YES (included in data export from "mother system"
Criteria_B - NO (included in data export from "mother system"
Criteria_C - NO (included in data export from "mother system"
Criteria_D - NO (included in data export from "mother system"

Query2; Criteria_A & Criteria_B & Criteria_C & Criteria_D =YESNONONO (Key)


Combined Query to update Controltype (that now only Works if all columns are chosesn in tblCrieria):

If Key1 = Key2 -->Update ControlType to Controltype = TblCritria

If Query2 could be modifided to first check what Fields user has chosen in tblCriteria, and then generate key only based on those columns i would very happy.

I have tried some Version of SQL to loop through each textbox in the user input form, but With no Luck..

Any ideas of what i should try ?
 
Ranman256

Ranman256

Well-known member
Local time
Today, 12:00
Joined
Apr 9, 2015
Messages
4,339
build the 'where' clause by cycling thru all the controls....
it executes after a find button CLICK event
if null, ignore.
if not, apply.

Code: 
'----------------
sub btnFilter_click()
'----------------
dim sWhere as string 

sWhere = "1=1"
if not IsNUll(cboST) then sWhere = sWhere & " and [State]='" & cboST & "'"
if not IsNUll(cboCity) then sWhere = sWhere & " and [city]='" & cboCity & "'"
if not IsNUll(cboZip) then sWhere = sWhere & " and [ZipCode]='" & cboZip & "'"

If sWhere = "1=1" Then
  Me.FilterOn = False
Else
  Me.Filter = sWhere
  Me.FilterOn = True
End If
end sub

instead of filter, you can also use the where to make a query:
set qdf = currentdb.querydefs("qsMyQry")
qdf.sql = "Select * from table " & swhere
qdf.close
docmd.openquery qdf.name
 
E

Erik_seb

New member
Local time
Today, 09:00
Joined
Feb 24, 2018
Messages
9
Thank You very much for the pointer.. :)

I am not sure i understand fully how to loop through all the boxses (ex: text boxses) by firing the Query off from a on click event. And further - how to use that to use as criterias in my Query (se above )..

I wil study Your response in moore detail later, maybe i "see the light" ;.)
 
bastanu

bastanu

AWF VIP
Local time
Today, 09:00
Joined
Apr 13, 2010
Messages
1,402
Hi there,

I believe you posted this question somewhere else and you were also mentioning a "priority" field. I am attaching a sample database that should help you to achieve what you're after.

Cheers,
Vlad
 

Attachments

  • Database2.accdb
    456 KB · Views: 112
bastanu

bastanu

AWF VIP
Local time
Today, 09:00
Joined
Apr 13, 2010
Messages
1,402
Forgot to mention, have a look at the tables first then review the 3 queries (query 3 is the one you want o use for your updating).

Cheers,
Vlad
 
MarkK

MarkK

bit cruncher
Local time
Today, 09:00
Joined
Mar 17, 2004
Messages
8,179
Hey ranman, you can just test the length of sWhere too, and you need to remove the leading ' and ' from sWhere, like...
Code: 
sub btnFilter_click()
   dim sWhere as string 

   if not IsNUll(cboST) then sWhere = sWhere & " and [State]='" & cboST & "'"
   if not IsNUll(cboCity) then sWhere = sWhere & " and [city]='" & cboCity & "'"
   if not IsNUll(cboZip) then sWhere = sWhere & " and [ZipCode]='" & cboZip & "'"

   If Len(sWhere) Then           [COLOR="green"]'length of zero is a boolean False[/COLOR]
      me.filter = mid(sWhere, 5) [COLOR="Green"]'drop the leading " and "[/COLOR]
      Me.FilterOn = true
   Else
      Me.FilterOn = false
   End If
End Sub
Cheers,
Mark
 
E

Erik_seb

New member
Local time
Today, 09:00
Joined
Feb 24, 2018
Messages
9
bastanu said:
Forgot to mention, have a look at the tables first then review the 3 queries (query 3 is the one you want o use for your updating).

Cheers,
Vlad
Click to expand...

Hi Vlad,

I tested Your sugestion very fast - it seems like it work Perfect ! Many thanks ! keep testing moore tomorrow !!!

And, i also want to say thanks to all that use their valuable time to help others.. u are all great !
 
E

Erik_seb

New member
Local time
Today, 09:00
Joined
Feb 24, 2018
Messages
9
MarkK said:
Hey ranman, you can just test the length of sWhere too, and you need to remove the leading ' and ' from sWhere, like...
Code: 
sub btnFilter_click()
   dim sWhere as string 

   if not IsNUll(cboST) then sWhere = sWhere & " and [State]='" & cboST & "'"
   if not IsNUll(cboCity) then sWhere = sWhere & " and [city]='" & cboCity & "'"
   if not IsNUll(cboZip) then sWhere = sWhere & " and [ZipCode]='" & cboZip & "'"

   If Len(sWhere) Then           [COLOR=green]'length of zero is a boolean False[/COLOR]
      me.filter = mid(sWhere, 5) [COLOR=green]'drop the leading " and "[/COLOR]
      Me.FilterOn = true
   Else
      Me.FilterOn = false
   End If
End Sub
Cheers,
Mark
Click to expand...

Hi,

i havent had time to test this solution yet, but i will do. Thank u very much for Your kind answer !!!
 
M

Mark_

Longboard on the internet
Local time
Today, 09:00
Joined
Sep 12, 2017
Messages
2,111
Much easier way would be to make a little function that knows if it needs to add the "and" or not.
Code: 
Function AddStr(pvAdd As Variant, pvStr As Variant) As String
   If Nz(pvAdd,"") = "" THEN ' If nothing to add,
      AddStr = pvStr ' Return the original string
   ELSE
      If Nz(pvStr, "") = "" Then 'If no origal string
          AddStr = pvAdd 'Return what we were given
      Else 'Otherwise add what we were given to the original string.
          AddStr = pvStr & " AND " & pvAdd
      End If
   End If
End Function

This would make your code

Code: 
sub btnFilter_click()
   dim sWhere as string 

   if not IsNUll(cboST) then sWhere = AddStr(  [State]='" & cboST & "'", sWhere)
   if not IsNUll(cboCity) then sWhere = AddStr(  "[city]='" & cboCity & "'", sWhere)
   if not IsNUll(cboZip) then sWhere = AddStr( "[ZipCode]='" & cboZip & "'", sWhere)

   If Len(sWhere) Then           'length of zero is a boolean False
      me.filter = sWhere
      Me.FilterOn = true
   Else
      Me.FilterOn = false
   End If
End Sub

This should avoid having to rework the logic on each line to see if you add "and" or not.
 
MarkK

MarkK

bit cruncher
Local time
Today, 09:00
Joined
Mar 17, 2004
Messages
8,179
The is how I do this kind of thing...
Code: 
Property Get StateFilter
[COLOR="Green"]'  this property returns null if the control's value is null[/COLOR]
   StateFilter = "AND State = '" + Me.cboState + "' "
End Property

Property Get CityFilter as String
   CityFilter = "AND City = '" + Me.cboCity + "' "
End Property

Property Get ZipFilter as String
   ZipFilter = "AND ZipCode = '" + Me.cboZip + "' "
End Property

Property Get WholeFilter as String
[COLOR="green"]'  this property assembles the filter[/COLOR]
   Dim tmp as string
   tmp = Me.StateFilter & Me.CityFilter & Me.ZipFilter [COLOR="green"]'null members simply don't show up[/COLOR]
   If len(tmp) Then WholeFilter = Mid(tmp, 5)
End Property

sub btnFilter_click()
   me.filter = Me.WholeFilter
   me.filteron = len(me.filter)
end sub
...which is a sort of OOP approach, and breaks the problem down into very small chunks. Also note, each chunk's solution is clearly named, and dead simple, so when you come back to this code six months later to add a CountryFilter, you will be a happy developer.
hth
Mark
 
You must log in or register to reply here.

Similar threads

M
Query for multiple "not like" criteria on long text fields operating as "or", not "and".
Replies
9
Views
347
mike60smart
mike60smart
Y
Key violations in append query
Replies
9
Views
359
Pat Hartman
P
C
Solved Key Violation Error Trapping During TransferText Operation
2 3
Replies
58
Views
1,521
Pat Hartman
P
E
JOIN versus WHERE
Replies
14
Views
665
sonic8
S
N
Can't get query criteria to read a form text field
Replies
9
Views
428
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom