469,086 Members | 1,161 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,086 developers. It's quick & easy.

Creating a Filtered Form and Searching Values

121 100+
I have a table that I would like to be able for the users to search/filter.
I have multiple fields to search:
[DATA_OWNR]
[REC_ID]
[SERL_CD]
[DATE]

I built a form, and I would like them to be able to choose form a set of drop-downs which criteria to filter on. and then it show in the sub-form the records that match that criteria.

I initially created a select query on the table I'm searching. With Dlookups of the drop-down values for each of the relevant fields.

I can do it fine with all things being populated, but I Don't know how to make an unselected field return all values (i.e if they don't select a DATA_OWNER, it should not get filtered by that value).

any ideas on how to do this? Do I need ot do it a different way?
Apr 18 '07 #1
21 1859
Rabbit
12,516 Expert Mod 8TB
Have the field return an asterisk * if it's blank.

This does not work on NULL fields.

To get around that you'll have to change the query def.
Apr 18 '07 #2
JHNielson
121 100+
Have the field return an asterisk * if it's blank.

This does not work on NULL fields.

To get around that you'll have to change the query def.
Thanks for your help. I just have a couple of questions?

1) What do you mean by changing the query definition? To what?
2) To have it send an asterisk, do you mean, to simply change the default value of the drop-down to an asterisk?
Apr 18 '07 #3
Rabbit
12,516 Expert Mod 8TB
Thanks for your help. I just have a couple of questions?

1) What do you mean by changing the query definition? To what?
2) To have it send an asterisk, do you mean, to simply change the default value of the drop-down to an asterisk?
1)Every query has a query definition. Using code you can change the query definition's .SQL property.

2) That's one way of doing it.
Apr 18 '07 #4
JHNielson
121 100+
I set the Criteria to [Forms]![SEARCH]![ID]..... etc. for all the fields.
I set the default value to "*"
and when I run it, I get no records

When I type in a "*" in the fields, I still get no records.

As well, when I look at the query definition, there is nothing in it. What do I need to change this to? Or can you point me to some information on this?

Thanks for your help....
Apr 18 '07 #5
Rabbit
12,516 Expert Mod 8TB
The criteria has to be Like [Forms]![Form Name]![Control Name]
Apr 19 '07 #6
JHNielson
121 100+
The criteria has to be Like [Forms]![Form Name]![Control Name]

I really appreciate all your help, but I changed my criteria to what you said. Now it looks like this:
Expand|Select|Wrap|Line Numbers
  1. SELECT MAST_REC_LOG.REC_ID, MAST_REC_LOG.DATA_OWNR
  2. FROM MAST_REC_LOG
  3. WHERE (((MAST_REC_LOG.DATA_OWNR) Like [Forms]![SEARCH_TEST]![DATA]));
Now it is not filtering any records. Did I do something else wrong.


Thanks again
Apr 19 '07 #7
Rabbit
12,516 Expert Mod 8TB
Well, if DATA is = * then it won't filter anything. * returns all records.
Apr 19 '07 #8
JHNielson
121 100+
Well - I got the * issue to work fine. Thanks so much for your help.

However, I have discovered what you meant about the Null values issue- if there is any NULL values in the entire column, it stops the filter and I get no records. So how would I fix this?

You mentioned that I would need t change the definition of the query. Currently, there is nothing in the definition - can you tell me how to fix this? O r can you point me to something that can show me how?

Thanks again for all your great help!!
Apr 19 '07 #9
JHNielson
121 100+
Can anyone help with this?
Apr 19 '07 #10
Rabbit
12,516 Expert Mod 8TB
Changing the query definition requires code.
Expand|Select|Wrap|Line Numbers
  1. Dim qdf As QueryDef
  2. Set qdf = CurrentDb.QueryDefs("QueryName")
  3. qdf.SQL = "New SQL Statement"
  4. Set qdf = Nothing
  5.  
Apr 19 '07 #11
JHNielson
121 100+
Changing the query definition requires code.
Expand|Select|Wrap|Line Numbers
  1. Dim qdf As QueryDef
  2. Set qdf = CurrentDb.QueryDefs("QueryName")
  3. qdf.SQL = "New SQL Statement"
  4. Set qdf = Nothing
  5.  

Great. I had got to that point, but where I'm struggling is what the new SQL statements should look like to avoid the Null Problem.

Thanks again. You have been very generous in trying to help.
Apr 19 '07 #12
Rabbit
12,516 Expert Mod 8TB
Great. I had got to that point, but where I'm struggling is what the new SQL statements should look like to avoid the Null Problem.

Thanks again. You have been very generous in trying to help.
You don't have to do anything to avoid the Nulls. If the user wants to return all records, meaning he leaves the textbox/combobox blank, then the SQL query will select the field but won't put any criteria on it.
Apr 19 '07 #13
Rabbit
12,516 Expert Mod 8TB
One thing I forgot to mention is before assigning the new SQL you have to build the statement using code.
Apr 19 '07 #14
JHNielson
121 100+
One thing I forgot to mention is before assigning the new SQL you have to build the statement using code.

Okay great - I think I get what I need to do. I just am not sure where I put the code. Do i put it in the Query Definition of the actual query? or do I put it in the form somewhere?
Apr 21 '07 #15
Rabbit
12,516 Expert Mod 8TB
Okay great - I think I get what I need to do. I just am not sure where I put the code. Do i put it in the Query Definition of the actual query? or do I put it in the form somewhere?
What you do is build the SQL statement as a string and then store it in the existing query definition's .SQL property.
Apr 21 '07 #16
JHNielson
121 100+
GREAT! You have been a life saver! So now I have the code down below. I just don't know how to call on it. Do I put it as an AfterUpdate... on the drop down boxes? Do I need to put a EXECUTE button? Or does it go somewhere else in the DB?


THANKS AGAIN



Expand|Select|Wrap|Line Numbers
  1. Function CREATE_SQL()
  2. Dim CURR_SQL As String
  3. Dim NEW_SQL As String
  4. Dim qdf As QueryDef
  5.  
  6.     Set qdf = CurrentDb.QueryDefs("SRCH - Master Records")
  7.  
  8.     CURR_SQL = "SELECT MAST_REC_LOG.* FROM MAST_REC_LOG WHERE (((MAST_REC_LOG.EVNT_DT)>[FORMS]![SEARCH - MASTER RECORDS]![EVNT_DT_MIN] And (MAST_REC_LOG.EVNT_DT)<[FORMS]![SEARCH - MASTER RECORDS]![EVNT_DT_MAX])"
  9.  
  10.         If [Forms]![SEARCH - MASTER RECORDS]![DATA_OWNR] <> "*" Then
  11.             NEW_SQL = CURR_SQL & " AND((MAST_CUST.DATA_OWNR) =" & """&[Forms]![SEARCH - MASTER RECORDS]![DATA_OWNR] & ""))"
  12.             CURR_SQL = NEW_SQL
  13.         End If
  14.  
  15.         If [Forms]![SEARCH - MASTER RECORDS]![REC_ID] <> "*" Then
  16.             NEW_SQL = CURR_SQL & " AND((MAST_CUST.REC_ID) =" & [Forms]![SEARCH - MASTER RECORDS]![REC_ID] & "))"
  17.             CURR_SQL = NEW_SQL
  18.         End If
  19.  
  20.         If [Forms]![SEARCH - MASTER RECORDS]![SRL_CD] <> "*" Then
  21.             NEW_SQL = CURR_SQL & " AND((MAST_CUST.SRL_CD) =" & [Forms]![SEARCH - MASTER RECORDS]![SRL_CD] & "))"
  22.             CURR_SQL = NEW_SQL
  23.         End If
  24.  
  25.         If [Forms]![SEARCH - MASTER RECORDS]![RCIPT_TYPE_ID] <> "*" Then
  26.             NEW_SQL = CURR_SQL & " AND((MAST_CUST.RCIPT_TYPE_ID) =" & [Forms]![SEARCH - MASTER RECORDS]![RCIPT_TYPE_ID] & "))"
  27.             CURR_SQL = NEW_SQL
  28.         End If
  29.  
  30.         If [Forms]![SEARCH - MASTER RECORDS]![CREA_BY] <> "*" Then
  31.             NEW_SQL = CURR_SQL & "AND((MAST_CUST.CREA_BY) =" & [Forms]![SEARCH - MASTER RECORDS]![CREA_BY] & "))"
  32.             CURR_SQL = NEW_SQL
  33.         End If
  34.  
  35.         If [Forms]![SEARCH - MASTER RECORDS]![CUST_ID] <> "*" Then
  36.             NEW_SQL = CURR_SQL & "AND((MAST_CUST.CUST_ID) =" & [Forms]![SEARCH - MASTER RECORDS]![CUST_ID] & "))"
  37.             CURR_SQL = NEW_SQL
  38.         End If
  39.  
  40.         If [Forms]![SEARCH - MASTER RECORDS]![EVNT_ID] <> "*" Then
  41.             NEW_SQL = CURR_SQL & "AND((MAST_CUST.EVNT_ID) =" & [Forms]![SEARCH - MASTER RECORDS]![EVNT_ID] & "))"
  42.             CURR_SQL = NEW_SQL
  43.         End If
  44.  
  45.         If [Forms]![SEARCH - MASTER RECORDS]![TXN_ID] <> "*" Then
  46.             NEW_SQL = CURR_SQL & " AND((MAST_CUST.TXN_ID) =" & [Forms]![SEARCH - MASTER RECORDS]![TXN_ID] & "))"
  47.             CURR_SQL = NEW_SQL
  48.         End If
  49.  
  50.         If [Forms]![SEARCH - MASTER RECORDS]![SPND_ATVY_CD] <> "*" Then
  51.             NEW_SQL = CURR_SQL & " AND((MAST_CUST.SPND_ATVY_CD) =" & [Forms]![SEARCH - MASTER RECORDS]![SPND_ATVY_CD] & "))"
  52.             CURR_SQL = NEW_SQL
  53.         End If
  54.  
  55.         If [Forms]![SEARCH - MASTER RECORDS]![BRD_NM_1_DS] <> "*" Then
  56.             NEW_SQL = CURR_SQL & " AND((MAST_CUST.BRD_NM_1_DS) =" & [Forms]![SEARCH - MASTER RECORDS]![BRD_NM_1_DS] & "))"
  57.             CURR_SQL = NEW_SQL
  58.         End If
  59.  
  60.         If [Forms]![SEARCH - MASTER RECORDS]![BRD_NM_2_DS] <> "*" Then
  61.             NEW_SQL = CURR_SQL & "AND((MAST_CUST.BRD_NM_2_DS) =" & [Forms]![SEARCH - MASTER RECORDS]![BRD_NM_2_DS] & "))"
  62.             CURR_SQL = NEW_SQL
  63.         End If
  64.  
  65.         If [Forms]![SEARCH - MASTER RECORDS]![RCIPT_FIRST_NM] <> "*" Then
  66.             NEW_SQL = CURR_SQL & "AND((MAST_CUST.RCIPT_FIRST_NM) =" & [Forms]![SEARCH - MASTER RECORDS]![RCIPT_FIRST_NM] & "))"
  67.             CURR_SQL = NEW_SQL
  68.         End If
  69.  
  70.         If [Forms]![SEARCH - MASTER RECORDS]![RCIPT_LAST_NM] <> "*" Then
  71.             NEW_SQL = CURR_SQL & "AND((MAST_CUST.RCIPT_LAST_NM) =" & [Forms]![SEARCH - MASTER RECORDS]![RCIPT_LAST_NM] & "))"
  72.             CURR_SQL = NEW_SQL
  73.         End If
  74.  
  75.         If [Forms]![SEARCH - MASTER RECORDS]![RCIPT_ADDR_LN_1_TXT] <> "*" Then
  76.             NEW_SQL = CURR_SQL & " AND((MAST_CUST.RCIPT_ADDR_LN_1_TXT) =" & [Forms]![SEARCH - MASTER RECORDS]![RCIPT_ADDR_LN_1_TXT] & "))"
  77.             CURR_SQL = NEW_SQL
  78.         End If
  79.  
  80.         If [Forms]![SEARCH - MASTER RECORDS]![RCIPT_CITY_NM] <> "*" Then
  81.             NEW_SQL = CURR_SQL & " AND((MAST_CUST.RCIPT_CITY_NM) =" & [Forms]![SEARCH - MASTER RECORDS]![RCIPT_CITY_NM] & "))"
  82.             CURR_SQL = NEW_SQL
  83.         End If
  84.  
  85.         If [Forms]![SEARCH - MASTER RECORDS]![RCIPT_ST_NM] <> "*" Then
  86.             NEW_SQL = CURR_SQL & " AND((MAST_CUST.RCIPT_ST_NM) =" & [Forms]![SEARCH - MASTER RECORDS]![RCIPT_ST_NM] & "))"
  87.             CURR_SQL = NEW_SQL
  88.         End If
  89.  
  90.         If [Forms]![SEARCH - MASTER RECORDS]![INSN_NM] <> "*" Then
  91.             NEW_SQL = CURR_SQL & " AND((MAST_CUST.INSN_NM) =" & [Forms]![SEARCH - MASTER RECORDS]![INSN_NM] & "))"
  92.             CURR_SQL = NEW_SQL
  93.         End If
  94.  
  95.         If [Forms]![SEARCH - MASTER RECORDS]![INSN_SUB_TYPE] <> "*" Then
  96.             NEW_SQL = CURR_SQL & " AND((MAST_CUST.INSN_SUB_TYPE) =" & [Forms]![SEARCH - MASTER RECORDS]![INSN_SUB_TYPE] & "))"
  97.             CURR_SQL = NEW_SQL
  98.         End If
  99.  
  100.         If [Forms]![SEARCH - MASTER RECORDS]![INSN_ADDR_LN_1_TXT] <> "*" Then
  101.             NEW_SQL = CURR_SQL & " AND((MAST_CUST.INSN_ADDR_LN_1_TXT) =" & [Forms]![SEARCH - MASTER RECORDS]![INSN_ADDR_LN_1_TXT] & "))"
  102.             CURR_SQL = NEW_SQL
  103.         End If
  104.  
  105.         If [Forms]![SEARCH - MASTER RECORDS]![INSN_CITY_NM] <> "*" Then
  106.             NEW_SQL = CURR_SQL & " AND((MAST_CUST.INSN_CITY_NM) =" & [Forms]![SEARCH - MASTER RECORDS]![INSN_CITY_NM] & "))"
  107.             CURR_SQL = NEW_SQL
  108.         End If
  109.  
  110.         If [Forms]![SEARCH - MASTER RECORDS]![INSN_ST_CD] <> "*" Then
  111.             NEW_SQL = CURR_SQL & " AND((MAST_CUST.INSN_ST_CD) =" & [Forms]![SEARCH - MASTER RECORDS]![INSN_ST_CD] & "))"
  112.             CURR_SQL = NEW_SQL
  113.         End If
  114.  
  115.         If [Forms]![SEARCH - MASTER RECORDS]![INSN_PSTZ_CD] <> "*" Then
  116.             NEW_SQL = CURR_SQL & " AND((MAST_CUST.INSN_PSTZ_CD) =" & [Forms]![SEARCH - MASTER RECORDS]![INSN_PSTZ_CD] & "))"
  117.             CURR_SQL = NEW_SQL
  118.         End If
  119.  
  120.         If [Forms]![SEARCH - MASTER RECORDS]![ORGZN_NM] <> "*" Then
  121.             NEW_SQL = CURR_SQL & " AND((MAST_CUST.ORGZN_NM) =" & [Forms]![SEARCH - MASTER RECORDS]![ORGZN_NM] & "))"
  122.             CURR_SQL = NEW_SQL
  123.         End If
  124.  
  125.         If [Forms]![SEARCH - MASTER RECORDS]![ORGZN_SUB_TYPE] <> "*" Then
  126.             NEW_SQL = CURR_SQL & " AND((MAST_CUST.ORGZN_SUB_TYPE) =" & [Forms]![SEARCH - MASTER RECORDS]![ORGZN_SUB_TYPE] & "))"
  127.             CURR_SQL = NEW_SQL
  128.         End If
  129.  
  130.         If [Forms]![SEARCH - MASTER RECORDS]![ORGZN_ADDR_LN_1_TXT] <> "*" Then
  131.             NEW_SQL = CURR_SQL & " AND((MAST_CUST.ORGZN_ADDR_LN_1_TXT) =" & [Forms]![SEARCH - MASTER RECORDS]![ORGZN_ADDR_LN_1_TXT] & "))"
  132.             CURR_SQL = NEW_SQL
  133.         End If
  134.  
  135.         If [Forms]![SEARCH - MASTER RECORDS]![ORGZN_CITY_NM] <> "*" Then
  136.             NEW_SQL = CURR_SQL & " AND((MAST_CUST.ORGZN_CITY_NM) =" & [Forms]![SEARCH - MASTER RECORDS]![ORGZN_CITY_NM] & "))"
  137.             CURR_SQL = NEW_SQL
  138.         End If
  139.  
  140.         If [Forms]![SEARCH - MASTER RECORDS]![ORGZN_ST_CD] <> "*" Then
  141.             NEW_SQL = CURR_SQL & " AND((MAST_CUST.ORGZN_ST_CD) =" & [Forms]![SEARCH - MASTER RECORDS]![ORGZN_ST_CD] & "))"
  142.             CURR_SQL = NEW_SQL
  143.         End If
  144.  
  145.         If [Forms]![SEARCH - MASTER RECORDS]![ORGZN_PSTZ_CD] <> "*" Then
  146.             NEW_SQL = CURR_SQL & " AND((MAST_CUST.ORGZN_PSTZ_CD) =" & [Forms]![SEARCH - MASTER RECORDS]![ORGZN_PSTZ_CD] & "))"
  147.             CURR_SQL = NEW_SQL
  148.         End If
  149.  
  150.     qdf.SQL = CURR_SQL
  151.     Set qdf = Nothing
  152.     MsgBox NEW_SQL
  153.  
  154. End Function
Apr 21 '07 #17
JHNielson
121 100+
Okay - so I think I got there on my own. I made a search button that runs the query. When I open the query that it changes, it looks correct.

But the subform I have that shows the filtered values, on the form where they choose the search criteria, doesn't update.

I added Me.requery, but that still doesn't get me updated records. I don't know if I need to get the requery to point ot the subtable or to the underlying query...or what.

I can't find the exact syntax for that, I've tred a few things but just keep getting errors.

Is this what I need to do?
If so, what is the syntax to direct what to requery.
If not, what should I do?


Thanks again
Apr 22 '07 #18
Rabbit
12,516 Expert Mod 8TB
Okay - so I think I got there on my own. I made a search button that runs the query. When I open the query that it changes, it looks correct.

But the subform I have that shows the filtered values, on the form where they choose the search criteria, doesn't update.

I added Me.requery, but that still doesn't get me updated records. I don't know if I need to get the requery to point ot the subtable or to the underlying query...or what.

I can't find the exact syntax for that, I've tred a few things but just keep getting errors.

Is this what I need to do?
If so, what is the syntax to direct what to requery.
If not, what should I do?


Thanks again
I can't remember exactly but I believe it's either:
Expand|Select|Wrap|Line Numbers
  1. Me.SubformControl.Requery
  2. or
  3. Me.SubformControl.Form.Requery
If neither works, you might have to set the record source of the subform to the query again.
Apr 22 '07 #19
JHNielson
121 100+
Thanks for everyone's help.

I have tried adding all the following to the on_click event that updates my search criteria.

Expand|Select|Wrap|Line Numbers
  1. Me.SRCH___Master_Records_subform.Requery
  2. 'Forms![Search - Master Records].Form.Requery
  3. 'Me!SRCH___Master_Records_subform.Form.Requery
  4. 'Me!SRCH___Master_Records_subform.Requery
Currently it looks like:
Expand|Select|Wrap|Line Numbers
  1. Call CREATE_SQL
  2. Me.SRCH___Master_Records_subform.Requery

The odd thing is that when I open the query, that the subform's record source is assigned to, the search is right.

Also, If I close the form, and then reopen it, then try to search based on a criteria, the records it returns are the ones from the previous search

example: If I choose for DATA_OWNER as TEST and click the searhc button, I won't get any records, or I will get wrong records. Then if i maually open the query - It will be only the TEST records.

Then If i close the form and reopen the form, then choose DATA_OWNER as GENIE, I get the TEST records from the previous search. I don't know why. It seems like the refresh isn't actually happening.

I don't know if I also have to requery the actual source query, or if there is a form property I need to fix, or my code is in the wrong order, or what. I'm out of ideas.

Please help. My deadline is tomorrow by noon! EST! I thought I had it working but it seems to have stopped.

Thanks for anyones help!!
Apr 25 '07 #20
JHNielson
121 100+
I hate to bump this so soon, but I'm on a tight deadline and really hoping someone can help.....
Apr 25 '07 #21
Rabbit
12,516 Expert Mod 8TB
What's the row source of the subform?
Apr 25 '07 #22

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by Thelma Roslyn Lubkin | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by kglaser89 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.