By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,483 Members | 1,939 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,483 IT Pros & Developers. It's quick & easy.

Better Apporach for filtering data from query

P: 24
I'm new to MS Access 2000 and have been working on a project that calls for retrieving filtered information. I have a form that had 3 checkboxes that would filter records based off them being checked or not. Initially I was able to determine the different combinations of the checkboxes to create queries based off each combination; however, as more criteria was requested to be added on to the form I ran a permutation which computed to nearly 70 different combinations which means if I continue with the inital technique (1 query per combination) there will be a lot of overhead. Is there a way I can make a master query where the form will have the checkboxes and those that are checked will be ran against the master query and those that are not checked are not displayed? Or is there a different approach that should be taken regarding the use of checkboxes?
Feb 28 '07 #1
Share this Question
Share on Google+
21 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
I'm new to MS Access 2000 and have been working on a project that calls for retrieving filtered information. I have a form that had 3 checkboxes that would filter records based off them being checked or not. Initially I was able to determine the different combinations of the checkboxes to create queries based off each combination; however, as more criteria was requested to be added on to the form I ran a permutation which computed to nearly 70 different combinations which means if I continue with the inital technique (1 query per combination) there will be a lot of overhead. Is there a way I can make a master query where the form will have the checkboxes and those that are checked will be ran against the master query and those that are not checked are not displayed? Or is there a different approach that should be taken regarding the use of checkboxes?
Can you post a sample SQL query that is being currently run off these checkboxes.

Mary
Feb 28 '07 #2

P: 24
Can you post a sample SQL query that is being currently run off these checkboxes.

Mary
What I have is 5 checkboxes and a command button that performs a search based off the criteria using VBA code. Here's an example of it:

If Me!chkActivity = 0 And Me!eval = 0 And Me!return = -1 And Me!relative = -1 And Me!eligible = 0 Then
DoCmd.OpenQuery "returnAndRelative", acNormal, acEdit

Then the "returnAndRelative" query who matches the criteria for fields:
'return': Is Not Null And [forms]![RetrieveForm]![return]
'relative': Is Not Null And [forms]![RetrieveForm]![relative]

Do you require anything more?
Feb 28 '07 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
I need the full SQL for the "returnAndRelative" query. If you're not sure how to get it. Open the query in design view and change the view to SQL then copy and paste it here.

Mary
Feb 28 '07 #4

P: 24
I need the full SQL for the "returnAndRelative" query. If you're not sure how to get it. Open the query in design view and change the view to SQL then copy and paste it here.

Mary
Here we go:

SELECT InternInformation.LMPeople, InternInformation.firstName, InternInformation.lastName, InternInformation.returningIntern, InternInformation.relative, GradList.gradDate
FROM InternInformation INNER JOIN GradList ON InternInformation.LMPeople = GradList.LMPeople
WHERE (((InternInformation.returningIntern) Is Not Null And (InternInformation.returningIntern)=[forms]![RetrieveForm]![return]) AND ((InternInformation.relative) Is Not Null And (InternInformation.relative)=[forms]![RetrieveForm]![relative]) AND ((GradList.gradDate) Is Not Null And (GradList.gradDate) Between [Forms]![RetrieveForm]![begin] And [Forms]![RetrieveForm]![end]));
Feb 28 '07 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
Expand|Select|Wrap|Line Numbers
  1. SELECT InternInformation.LMPeople, InternInformation.firstName, InternInformation.lastName, InternInformation.returningIntern, InternInformation.relative, GradList.gradDate
  2. FROM InternInformation INNER JOIN GradList 
  3. ON InternInformation.LMPeople = GradList.LMPeople
  4. WHERE (((InternInformation.returningIntern) Is Not Null 
  5. And (InternInformation.returningIntern)=[forms]![RetrieveForm]![return]) AND ((InternInformation.relative) Is Not Null 
  6. And (InternInformation.relative)=[forms]![RetrieveForm]![relative]) 
  7. AND ((GradList.gradDate) Is Not Null And (GradList.gradDate) Between [Forms]![RetrieveForm]![begin] 
  8. And [Forms]![RetrieveForm]![end]));
OK this query can be built in Code behind a command button based on the checkbox values. If you would like to take this approach I can go through it step by step.

Mary
Mar 1 '07 #6

P: 24
Expand|Select|Wrap|Line Numbers
  1. SELECT InternInformation.LMPeople, InternInformation.firstName, InternInformation.lastName, InternInformation.returningIntern, InternInformation.relative, GradList.gradDate
  2. FROM InternInformation INNER JOIN GradList 
  3. ON InternInformation.LMPeople = GradList.LMPeople
  4. WHERE (((InternInformation.returningIntern) Is Not Null 
  5. And (InternInformation.returningIntern)=[forms]![RetrieveForm]![return]) AND ((InternInformation.relative) Is Not Null 
  6. And (InternInformation.relative)=[forms]![RetrieveForm]![relative]) 
  7. AND ((GradList.gradDate) Is Not Null And (GradList.gradDate) Between [Forms]![RetrieveForm]![begin] 
  8. And [Forms]![RetrieveForm]![end]));
OK this query can be built in Code behind a command button based on the checkbox values. If you would like to take this approach I can go through it step by step.

Mary
Yes I would very much appreciate that!
Mar 1 '07 #7

MMcCarthy
Expert Mod 10K+
P: 14,534
Yes I would very much appreciate that!
  1. Create a query. It doesn't matter what is in the query as we will be replacing the contents using VBA code. For convenience call the query qryDummy.
  2. Now put a command button on the form which we will call cmdBuildQuery.
  3. For the purposes of this example I am using 4 checkboxes called CheckA, CheckB, CheckC and CheckD.
  4. In the VBA editor go to Tools - References and check that there is a Microsoft DAO library ticked. If not then scroll down the list until you find one (3.6 if you have it) and tick it.
  5. Now put the following code behind the command button.
Expand|Select|Wrap|Line Numbers
  1. Private sub cmdBuildQuery_Click()
  2. Dim db As DAO.Database
  3. Dim qdf As DAO.QueryDef
  4. Dim strSQL As String
  5.  
  6.    strSQL = "SELECT InternInformation.LMPeople, InternInformation.firstName, " & _
  7.        "InternInformation.lastName, InternInformation.returningIntern, InternInformation.relative, GradList.gradDate" & _
  8.        "FROM InternInformation INNER JOIN GradList " & _
  9.        "ON InternInformation.LMPeople = GradList.LMPeople " & _
  10.      "WHERE (GradList.gradDate Between #" & Me.begin & "# And #" Me.end] & "#) " & _
  11.        "AND (GradList.gradDate Is Not Null) "
  12.  
  13.    If CheckA Then ' if checkbox CheckA is ticked
  14.       strSQL = strSQL & "AND (InternInformation.FieldA Is Not Null " & _
  15.            "AND InternInformation.FieldA=" & Me.FieldA & ") "
  16.    End If
  17.  
  18.    If CheckB Then ' if checkbox CheckB is ticked 
  19.       strSQL = strSQL & "AND (InternInformation.FieldB Is Not Null " & _
  20.          "AND InternInformation.FieldB=" & Me.FieldB & ") "
  21.    End If
  22.  
  23.    If CheckC Then ' if checkbox CheckC is ticked 
  24.       strSQL = strSQL & "AND (InternInformation.FieldC Is Not Null " & _
  25.            "AND InternInformation.FieldC=" & Me.FieldC & ") "
  26.    End If
  27.  
  28.    If CheckD Then ' if checkbox CheckD is ticked 
  29.       strSQL = strSQL & "AND (InternInformation.FieldD Is Not Null " & _
  30.            "AND InternInformation.FieldD=" & Me.FieldD & ") "
  31.    End If
  32.  
  33.    Set db = CurrentDB()
  34.    Set qry = db.QueryDefs("qryDummy")
  35.  
  36.    qdf.SQL = strSQL 
  37.  
  38.    Set qdf = Nothing
  39.    Set db = Nothing
  40.  
  41.    DoCmd.OpenQuery "qryDummy"
  42.  
  43. End Sub
  44.  
Let me know if there is anything you don't understand

Mary
Mar 1 '07 #8

P: 24
  1. Create a query. It doesn't matter what is in the query as we will be replacing the contents using VBA code. For convenience call the query qryDummy.
  2. Now put a command button on the form which we will call cmdBuildQuery.
  3. For the purposes of this example I am using 4 checkboxes called CheckA, CheckB, CheckC and CheckD.
  4. In the VBA editor go to Tools - References and check that there is a Microsoft DAO library ticked. If not then scroll down the list until you find one (3.6 if you have it) and tick it.
  5. Now put the following code behind the command button.
Expand|Select|Wrap|Line Numbers
  1. Private sub cmdBuildQuery_Click()
  2. Dim db As DAO.Database
  3. Dim qdf As DAO.QueryDef
  4. Dim strSQL As String
  5.  
  6.    strSQL = "SELECT InternInformation.LMPeople, InternInformation.firstName, " & _
  7.        "InternInformation.lastName, InternInformation.returningIntern, InternInformation.relative, GradList.gradDate" & _
  8.        "FROM InternInformation INNER JOIN GradList " & _
  9.        "ON InternInformation.LMPeople = GradList.LMPeople " & _
  10.      "WHERE (GradList.gradDate Between #" & Me.begin & "# And #" Me.end] & "#) " & _
  11.        "AND (GradList.gradDate Is Not Null) "
  12.  
  13.    If CheckA Then ' if checkbox CheckA is ticked
  14.       strSQL = strSQL & "AND (InternInformation.FieldA Is Not Null " & _
  15.            "AND InternInformation.FieldA=" & Me.FieldA & ") "
  16.    End If
  17.  
  18.    If CheckB Then ' if checkbox CheckB is ticked 
  19.       strSQL = strSQL & "AND (InternInformation.FieldB Is Not Null " & _
  20.          "AND InternInformation.FieldB=" & Me.FieldB & ") "
  21.    End If
  22.  
  23.    If CheckC Then ' if checkbox CheckC is ticked 
  24.       strSQL = strSQL & "AND (InternInformation.FieldC Is Not Null " & _
  25.            "AND InternInformation.FieldC=" & Me.FieldC & ") "
  26.    End If
  27.  
  28.    If CheckD Then ' if checkbox CheckD is ticked 
  29.       strSQL = strSQL & "AND (InternInformation.FieldD Is Not Null " & _
  30.            "AND InternInformation.FieldD=" & Me.FieldD & ") "
  31.    End If
  32.  
  33.    Set db = CurrentDB()
  34.    Set qry = db.QueryDefs("qryDummy")
  35.  
  36.    qdf.SQL = strSQL 
  37.  
  38.    Set qdf = Nothing
  39.    Set db = Nothing
  40.  
  41.    DoCmd.OpenQuery "qryDummy"
  42.  
  43. End Sub
  44.  
Let me know if there is anything you don't understand

Mary

Hi! I tried out the code and got an error with the strSQL, the compiler pointed to this line of code:

"AND (GradList.gradDate Is Not Null) "

and gave the Run-time error '2465'; MS Access can't find the field '|' referred in your expression.

I modified the code to match the field names, but the error points to the strSQL script.
Expand|Select|Wrap|Line Numbers
  1. Dim db As DAO.Database
  2.     Dim qdf As DAO.QueryDef
  3.     Dim strSQL As String
  4.  
  5.    strSQL = "SELECT InternInformation.LMPeople, InternInformation.firstName, " & _
  6.        "InternInformation.lastName, InternInformation.returningIntern, InternInformation.relative, GradList.gradDate" & _
  7.        "FROM InternInformation INNER JOIN GradList " & _
  8.        "ON InternInformation.LMPeople = GradList.LMPeople " & _
  9.      "WHERE (GradList.gradDate Between #" & [Me.begin & "# And #" Me.end] & "#) " & _
  10.        "AND (GradList.gradDate Is Not Null) "
  11.  
  12.    If CheckA Then ' if checkbox CheckA is ticked
  13.       strSQL = strSQL & "AND (InternInformation.resume Is Not Null " & _
  14.            "AND InternInformation.resume=" & Me.CheckA & ") "
  15.    End If
  16.  
  17.    If CheckB Then ' if checkbox CheckB is ticked
  18.       strSQL = strSQL & "AND (InternInformation.evaluation Is Not Null " & _
  19.         "AND InternInformation.evaluation=" & Me.CheckB & ") "
  20.    End If
  21.  
  22.    If CheckC Then ' if checkbox CheckC is ticked
  23.       strSQL = strSQL & "AND (InternInformation.returningIntern Is Not Null " & _
  24.           "AND InternInformation.returningIntern=" & Me.CheckC & ") "
  25.    End If
  26.  
  27.    If CheckD Then ' if checkbox CheckD is ticked
  28.       strSQL = strSQL & "AND (InternInformation.relative Is Not Null " & _
  29.           "AND InternInformation.relative=" & Me.CheckD & ") "
  30.    End If
  31.  
  32.    Set db = CurrentDb()
  33.    Set qry = db.QueryDefs("qryDummy")
  34.  
  35.    qdf.SQL = strSQL
  36.  
  37.    Set qdf = Nothing
  38.    Set db = Nothing
  39.  
  40.    DoCmd.OpenQuery "qryDummy"
  41.  
  42.  
I created the query, Is it simply a syntax error?
Mar 1 '07 #9

MMcCarthy
Expert Mod 10K+
P: 14,534
Remove the square brackets around the dates Me.begin and Me.end.

Expand|Select|Wrap|Line Numbers
  1. Dim db As DAO.Database
  2.     Dim qdf As DAO.QueryDef
  3.     Dim strSQL As String
  4.  
  5.    strSQL = "SELECT InternInformation.LMPeople, InternInformation.firstName, " & _
  6.        "InternInformation.lastName, InternInformation.returningIntern, InternInformation.relative, GradList.gradDate" & _
  7.        "FROM InternInformation INNER JOIN GradList " & _
  8.        "ON InternInformation.LMPeople = GradList.LMPeople " & _
  9.      "WHERE (GradList.gradDate Between #" & Me.begin & "# And #" Me.end & "# " & _
  10.        "AND GradList.gradDate Is Not Null) "
  11.  
  12.    If CheckA Then ' if checkbox CheckA is ticked
  13.       strSQL = strSQL & "AND (InternInformation.resume Is Not Null " & _
  14.            "AND InternInformation.resume=" & Me.CheckA & ") "
  15.    End If
  16.  
  17.    If CheckB Then ' if checkbox CheckB is ticked
  18.       strSQL = strSQL & "AND (InternInformation.evaluation Is Not Null " & _
  19.         "AND InternInformation.evaluation=" & Me.CheckB & ") "
  20.    End If
  21.  
  22.    If CheckC Then ' if checkbox CheckC is ticked
  23.       strSQL = strSQL & "AND (InternInformation.returningIntern Is Not Null " & _
  24.           "AND InternInformation.returningIntern=" & Me.CheckC & ") "
  25.    End If
  26.  
  27.    If CheckD Then ' if checkbox CheckD is ticked
  28.       strSQL = strSQL & "AND (InternInformation.relative Is Not Null " & _
  29.           "AND InternInformation.relative=" & Me.CheckD & ") "
  30.    End If
  31.  
  32.    Set db = CurrentDb()
  33.    Set qry = db.QueryDefs("qryDummy")
  34.  
  35.    qdf.SQL = strSQL
  36.  
  37.    Set qdf = Nothing
  38.    Set db = Nothing
  39.  
  40.    DoCmd.OpenQuery "qryDummy"
  41.  
  42.  
Mary
Mar 2 '07 #10

P: 24
Remove the square brackets around the dates Me.begin and Me.end.

Expand|Select|Wrap|Line Numbers
  1. Dim db As DAO.Database
  2.     Dim qdf As DAO.QueryDef
  3.     Dim strSQL As String
  4.  
  5.    strSQL = "SELECT InternInformation.LMPeople, InternInformation.firstName, " & _
  6.        "InternInformation.lastName, InternInformation.returningIntern, InternInformation.relative, GradList.gradDate" & _
  7.        "FROM InternInformation INNER JOIN GradList " & _
  8.        "ON InternInformation.LMPeople = GradList.LMPeople " & _
  9.      "WHERE (GradList.gradDate Between #" & Me.begin & "# And #" Me.end & "# " & _
  10.        "AND GradList.gradDate Is Not Null) "
  11.  
  12.    If CheckA Then ' if checkbox CheckA is ticked
  13.       strSQL = strSQL & "AND (InternInformation.resume Is Not Null " & _
  14.            "AND InternInformation.resume=" & Me.CheckA & ") "
  15.    End If
  16.  
  17.    If CheckB Then ' if checkbox CheckB is ticked
  18.       strSQL = strSQL & "AND (InternInformation.evaluation Is Not Null " & _
  19.         "AND InternInformation.evaluation=" & Me.CheckB & ") "
  20.    End If
  21.  
  22.    If CheckC Then ' if checkbox CheckC is ticked
  23.       strSQL = strSQL & "AND (InternInformation.returningIntern Is Not Null " & _
  24.           "AND InternInformation.returningIntern=" & Me.CheckC & ") "
  25.    End If
  26.  
  27.    If CheckD Then ' if checkbox CheckD is ticked
  28.       strSQL = strSQL & "AND (InternInformation.relative Is Not Null " & _
  29.           "AND InternInformation.relative=" & Me.CheckD & ") "
  30.    End If
  31.  
  32.    Set db = CurrentDb()
  33.    Set qry = db.QueryDefs("qryDummy")
  34.  
  35.    qdf.SQL = strSQL
  36.  
  37.    Set qdf = Nothing
  38.    Set db = Nothing
  39.  
  40.    DoCmd.OpenQuery "qryDummy"
  41.  
  42.  
Mary

Hmmm....removing the aquare brackets seemed to have created another compile error, it says the syntax is wrong. Is it expecting the end of the statement or something?
Mar 2 '07 #11

MMcCarthy
Expert Mod 10K+
P: 14,534
Hmmm....removing the aquare brackets seemed to have created another compile error, it says the syntax is wrong. Is it expecting the end of the statement or something?
try this ...

Expand|Select|Wrap|Line Numbers
  1.  
  2. strSQL = "SELECT InternInformation.LMPeople, InternInformation.firstName, " & _
  3.        "InternInformation.lastName, InternInformation.returningIntern, InternInformation.relative, GradList.gradDate" & _
  4.        "FROM InternInformation INNER JOIN GradList " & _
  5.        "ON InternInformation.LMPeople = GradList.LMPeople " & _
  6.      "WHERE (GradList.gradDate Between #" & Me.begin & "# And #" Me.end & "#) " & _
  7.        "AND (GradList.gradDate Is Not Null) "
  8.  
Mar 2 '07 #12

P: 24
try this ...

Expand|Select|Wrap|Line Numbers
  1.  
  2. strSQL = "SELECT InternInformation.LMPeople, InternInformation.firstName, " & _
  3.        "InternInformation.lastName, InternInformation.returningIntern, InternInformation.relative, GradList.gradDate" & _
  4.        "FROM InternInformation INNER JOIN GradList " & _
  5.        "ON InternInformation.LMPeople = GradList.LMPeople " & _
  6.      "WHERE (GradList.gradDate Between #" & Me.begin & "# And #" Me.end & "#) " & _
  7.        "AND (GradList.gradDate Is Not Null) "
  8.  
I'm still getting a compile error. Maybe a string concat was offset but I wouldn't know how to identify it...
Mar 2 '07 #13

MMcCarthy
Expert Mod 10K+
P: 14,534
I'm still getting a compile error. Maybe a string concat was offset but I wouldn't know how to identify it...
Put

Expand|Select|Wrap|Line Numbers
  1. Debug.Print strSQL 
after the initial statement and open the immediate pane. When the code runs the statement should be printed in the immediate pane. Copy and paste it here if you can't figure out what the problem is.

Mary
Mar 2 '07 #14

P: 24
Put

Expand|Select|Wrap|Line Numbers
  1. Debug.Print strSQL 
after the initial statement and open the immediate pane. When the code runs the statement should be printed in the immediate pane. Copy and paste it here if you can't figure out what the problem is.

Mary

I placed the Debug.strSQL but there was no statement outputted. I toyed around with the code some more, I'll toy with it some more. The rest of the VBA, is that generating Dynamic Criteria like: checkA And checkC or checkB and checkD; does it support all the combinations?
Mar 2 '07 #15

MMcCarthy
Expert Mod 10K+
P: 14,534
I placed the Debug.strSQL but there was no statement outputted. I toyed around with the code some more, I'll toy with it some more. The rest of the VBA, is that generating Dynamic Criteria like: checkA And checkC or checkB and checkD; does it support all the combinations?
Yes it does.

If you can'tget the Debug.Print strSQL to work then try using a msgbox

Msgbox strSQL

Mary
Mar 2 '07 #16

NeoPa
Expert Mod 15k+
P: 31,186
Debug.Print is better for using here as you can Copy/Paste the results into a post.
From the VBA window (Alt-F11 from Access window) use Ctrl-G to open and switch to the Immediate pane where the results are shown.
Mar 3 '07 #17

NeoPa
Expert Mod 15k+
P: 31,186
I placed the Debug.strSQL but there was no statement output.
What you need is :
Expand|Select|Wrap|Line Numbers
  1. Debug.Print strSQL
rather than :
Expand|Select|Wrap|Line Numbers
  1. Debug.strSQL
Mar 3 '07 #18

P: 24
Thank you all for your help. I found it better to use a multi-selection list box instead of checkboxes which would generate an SQL string to be passed into a parameter query.
Mar 14 '07 #19

NeoPa
Expert Mod 15k+
P: 31,186
...But you've managed to do what you needed right?
Mar 14 '07 #20

P: 24
...But you've managed to do what you needed right?
Yes. I could not have finished it with out your examples of VBA and SQL though; they supplemented my understanding. Thank you all again!
Mar 14 '07 #21

NeoPa
Expert Mod 15k+
P: 31,186
You're Welcome :)
That's great news!
Fixing problems is good - but helping people to learn is better :)
Mar 14 '07 #22

Post your reply

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