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?
21 2067
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
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?
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
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]));
-
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]));
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
-
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]));
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!
Yes I would very much appreciate that!
- 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.
- Now put a command button on the form which we will call cmdBuildQuery.
- For the purposes of this example I am using 4 checkboxes called CheckA, CheckB, CheckC and CheckD.
- 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.
- Now put the following code behind the command button.
-
Private sub cmdBuildQuery_Click()
-
Dim db As DAO.Database
-
Dim qdf As DAO.QueryDef
-
Dim strSQL As String
-
-
strSQL = "SELECT InternInformation.LMPeople, InternInformation.firstName, " & _
-
"InternInformation.lastName, InternInformation.returningIntern, InternInformation.relative, GradList.gradDate" & _
-
"FROM InternInformation INNER JOIN GradList " & _
-
"ON InternInformation.LMPeople = GradList.LMPeople " & _
-
"WHERE (GradList.gradDate Between #" & Me.begin & "# And #" Me.end] & "#) " & _
-
"AND (GradList.gradDate Is Not Null) "
-
-
If CheckA Then ' if checkbox CheckA is ticked
-
strSQL = strSQL & "AND (InternInformation.FieldA Is Not Null " & _
-
"AND InternInformation.FieldA=" & Me.FieldA & ") "
-
End If
-
-
If CheckB Then ' if checkbox CheckB is ticked
-
strSQL = strSQL & "AND (InternInformation.FieldB Is Not Null " & _
-
"AND InternInformation.FieldB=" & Me.FieldB & ") "
-
End If
-
-
If CheckC Then ' if checkbox CheckC is ticked
-
strSQL = strSQL & "AND (InternInformation.FieldC Is Not Null " & _
-
"AND InternInformation.FieldC=" & Me.FieldC & ") "
-
End If
-
-
If CheckD Then ' if checkbox CheckD is ticked
-
strSQL = strSQL & "AND (InternInformation.FieldD Is Not Null " & _
-
"AND InternInformation.FieldD=" & Me.FieldD & ") "
-
End If
-
-
Set db = CurrentDB()
-
Set qry = db.QueryDefs("qryDummy")
-
-
qdf.SQL = strSQL
-
-
Set qdf = Nothing
-
Set db = Nothing
-
-
DoCmd.OpenQuery "qryDummy"
-
-
End Sub
-
Let me know if there is anything you don't understand
Mary
- 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.
- Now put a command button on the form which we will call cmdBuildQuery.
- For the purposes of this example I am using 4 checkboxes called CheckA, CheckB, CheckC and CheckD.
- 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.
- Now put the following code behind the command button.
-
Private sub cmdBuildQuery_Click()
-
Dim db As DAO.Database
-
Dim qdf As DAO.QueryDef
-
Dim strSQL As String
-
-
strSQL = "SELECT InternInformation.LMPeople, InternInformation.firstName, " & _
-
"InternInformation.lastName, InternInformation.returningIntern, InternInformation.relative, GradList.gradDate" & _
-
"FROM InternInformation INNER JOIN GradList " & _
-
"ON InternInformation.LMPeople = GradList.LMPeople " & _
-
"WHERE (GradList.gradDate Between #" & Me.begin & "# And #" Me.end] & "#) " & _
-
"AND (GradList.gradDate Is Not Null) "
-
-
If CheckA Then ' if checkbox CheckA is ticked
-
strSQL = strSQL & "AND (InternInformation.FieldA Is Not Null " & _
-
"AND InternInformation.FieldA=" & Me.FieldA & ") "
-
End If
-
-
If CheckB Then ' if checkbox CheckB is ticked
-
strSQL = strSQL & "AND (InternInformation.FieldB Is Not Null " & _
-
"AND InternInformation.FieldB=" & Me.FieldB & ") "
-
End If
-
-
If CheckC Then ' if checkbox CheckC is ticked
-
strSQL = strSQL & "AND (InternInformation.FieldC Is Not Null " & _
-
"AND InternInformation.FieldC=" & Me.FieldC & ") "
-
End If
-
-
If CheckD Then ' if checkbox CheckD is ticked
-
strSQL = strSQL & "AND (InternInformation.FieldD Is Not Null " & _
-
"AND InternInformation.FieldD=" & Me.FieldD & ") "
-
End If
-
-
Set db = CurrentDB()
-
Set qry = db.QueryDefs("qryDummy")
-
-
qdf.SQL = strSQL
-
-
Set qdf = Nothing
-
Set db = Nothing
-
-
DoCmd.OpenQuery "qryDummy"
-
-
End Sub
-
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. -
Dim db As DAO.Database
-
Dim qdf As DAO.QueryDef
-
Dim strSQL As String
-
-
strSQL = "SELECT InternInformation.LMPeople, InternInformation.firstName, " & _
-
"InternInformation.lastName, InternInformation.returningIntern, InternInformation.relative, GradList.gradDate" & _
-
"FROM InternInformation INNER JOIN GradList " & _
-
"ON InternInformation.LMPeople = GradList.LMPeople " & _
-
"WHERE (GradList.gradDate Between #" & [Me.begin & "# And #" Me.end] & "#) " & _
-
"AND (GradList.gradDate Is Not Null) "
-
-
If CheckA Then ' if checkbox CheckA is ticked
-
strSQL = strSQL & "AND (InternInformation.resume Is Not Null " & _
-
"AND InternInformation.resume=" & Me.CheckA & ") "
-
End If
-
-
If CheckB Then ' if checkbox CheckB is ticked
-
strSQL = strSQL & "AND (InternInformation.evaluation Is Not Null " & _
-
"AND InternInformation.evaluation=" & Me.CheckB & ") "
-
End If
-
-
If CheckC Then ' if checkbox CheckC is ticked
-
strSQL = strSQL & "AND (InternInformation.returningIntern Is Not Null " & _
-
"AND InternInformation.returningIntern=" & Me.CheckC & ") "
-
End If
-
-
If CheckD Then ' if checkbox CheckD is ticked
-
strSQL = strSQL & "AND (InternInformation.relative Is Not Null " & _
-
"AND InternInformation.relative=" & Me.CheckD & ") "
-
End If
-
-
Set db = CurrentDb()
-
Set qry = db.QueryDefs("qryDummy")
-
-
qdf.SQL = strSQL
-
-
Set qdf = Nothing
-
Set db = Nothing
-
-
DoCmd.OpenQuery "qryDummy"
-
-
I created the query, Is it simply a syntax error?
Remove the square brackets around the dates Me.begin and Me.end. -
Dim db As DAO.Database
-
Dim qdf As DAO.QueryDef
-
Dim strSQL As String
-
-
strSQL = "SELECT InternInformation.LMPeople, InternInformation.firstName, " & _
-
"InternInformation.lastName, InternInformation.returningIntern, InternInformation.relative, GradList.gradDate" & _
-
"FROM InternInformation INNER JOIN GradList " & _
-
"ON InternInformation.LMPeople = GradList.LMPeople " & _
-
"WHERE (GradList.gradDate Between #" & Me.begin & "# And #" Me.end & "# " & _
-
"AND GradList.gradDate Is Not Null) "
-
-
If CheckA Then ' if checkbox CheckA is ticked
-
strSQL = strSQL & "AND (InternInformation.resume Is Not Null " & _
-
"AND InternInformation.resume=" & Me.CheckA & ") "
-
End If
-
-
If CheckB Then ' if checkbox CheckB is ticked
-
strSQL = strSQL & "AND (InternInformation.evaluation Is Not Null " & _
-
"AND InternInformation.evaluation=" & Me.CheckB & ") "
-
End If
-
-
If CheckC Then ' if checkbox CheckC is ticked
-
strSQL = strSQL & "AND (InternInformation.returningIntern Is Not Null " & _
-
"AND InternInformation.returningIntern=" & Me.CheckC & ") "
-
End If
-
-
If CheckD Then ' if checkbox CheckD is ticked
-
strSQL = strSQL & "AND (InternInformation.relative Is Not Null " & _
-
"AND InternInformation.relative=" & Me.CheckD & ") "
-
End If
-
-
Set db = CurrentDb()
-
Set qry = db.QueryDefs("qryDummy")
-
-
qdf.SQL = strSQL
-
-
Set qdf = Nothing
-
Set db = Nothing
-
-
DoCmd.OpenQuery "qryDummy"
-
-
Mary
Remove the square brackets around the dates Me.begin and Me.end. -
Dim db As DAO.Database
-
Dim qdf As DAO.QueryDef
-
Dim strSQL As String
-
-
strSQL = "SELECT InternInformation.LMPeople, InternInformation.firstName, " & _
-
"InternInformation.lastName, InternInformation.returningIntern, InternInformation.relative, GradList.gradDate" & _
-
"FROM InternInformation INNER JOIN GradList " & _
-
"ON InternInformation.LMPeople = GradList.LMPeople " & _
-
"WHERE (GradList.gradDate Between #" & Me.begin & "# And #" Me.end & "# " & _
-
"AND GradList.gradDate Is Not Null) "
-
-
If CheckA Then ' if checkbox CheckA is ticked
-
strSQL = strSQL & "AND (InternInformation.resume Is Not Null " & _
-
"AND InternInformation.resume=" & Me.CheckA & ") "
-
End If
-
-
If CheckB Then ' if checkbox CheckB is ticked
-
strSQL = strSQL & "AND (InternInformation.evaluation Is Not Null " & _
-
"AND InternInformation.evaluation=" & Me.CheckB & ") "
-
End If
-
-
If CheckC Then ' if checkbox CheckC is ticked
-
strSQL = strSQL & "AND (InternInformation.returningIntern Is Not Null " & _
-
"AND InternInformation.returningIntern=" & Me.CheckC & ") "
-
End If
-
-
If CheckD Then ' if checkbox CheckD is ticked
-
strSQL = strSQL & "AND (InternInformation.relative Is Not Null " & _
-
"AND InternInformation.relative=" & Me.CheckD & ") "
-
End If
-
-
Set db = CurrentDb()
-
Set qry = db.QueryDefs("qryDummy")
-
-
qdf.SQL = strSQL
-
-
Set qdf = Nothing
-
Set db = Nothing
-
-
DoCmd.OpenQuery "qryDummy"
-
-
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?
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 ... -
-
strSQL = "SELECT InternInformation.LMPeople, InternInformation.firstName, " & _
-
"InternInformation.lastName, InternInformation.returningIntern, InternInformation.relative, GradList.gradDate" & _
-
"FROM InternInformation INNER JOIN GradList " & _
-
"ON InternInformation.LMPeople = GradList.LMPeople " & _
-
"WHERE (GradList.gradDate Between #" & Me.begin & "# And #" Me.end & "#) " & _
-
"AND (GradList.gradDate Is Not Null) "
-
try this ... -
-
strSQL = "SELECT InternInformation.LMPeople, InternInformation.firstName, " & _
-
"InternInformation.lastName, InternInformation.returningIntern, InternInformation.relative, GradList.gradDate" & _
-
"FROM InternInformation INNER JOIN GradList " & _
-
"ON InternInformation.LMPeople = GradList.LMPeople " & _
-
"WHERE (GradList.gradDate Between #" & Me.begin & "# And #" Me.end & "#) " & _
-
"AND (GradList.gradDate Is Not Null) "
-
I'm still getting a compile error. Maybe a string concat was offset but I wouldn't know how to identify it...
I'm still getting a compile error. Maybe a string concat was offset but I wouldn't know how to identify it...
Put
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
Put
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?
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
NeoPa 32,556
Expert Mod 16PB
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.
NeoPa 32,556
Expert Mod 16PB
I placed the Debug.strSQL but there was no statement output.
What you need is :
rather than :
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.
NeoPa 32,556
Expert Mod 16PB
...But you've managed to do what you needed right?
...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!
NeoPa 32,556
Expert Mod 16PB
You're Welcome :)
That's great news!
Fixing problems is good - but helping people to learn is better :)
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Alex Satrapa |
last post by:
I have a table from which I'm trying to extract certain information. For
historical reasons, we archive every action on a particular thing
('thing' is identified, funnily enough, by 'id'). So the...
|
by: Jason |
last post by:
I am trying to filter records in a primary form based on records in
related tables. The data in the related tables is being displayed in
the primary form
through subforms. To be more specific, I...
|
by: Elias Farah |
last post by:
Hello All,
I hope someone can give me (and other keen access enthusiasts) some
helpful information to explain how to most efficiently filter Queries
& subqueies.
Consider this common simple...
|
by: Sean |
last post by:
Greetings all,
I am attempting to make a form that will filter through several tables
that
(I believe) have refretial integrity. I am pulling data from several
tables into the form and i would...
|
by: Richard Hollenbeck |
last post by:
I tried to ask this question before on the 14th of January but I never got a
reply. I'm still struggling with the problem. I'll try to rephrase the
question:
I have a crosstab query with rows...
|
by: |
last post by:
Hello,
Does anyone have an idea on how I can filter the data in the gridview
control that was returned by an sql query?
I have a gridview that works fine when I populate it with data. Now I...
|
by: JUAN ERNESTO FLORES BELTRAN |
last post by:
Hi you all,
I am developping a python application which connects to a database
(postresql) and displays the query results on a treeview. In adittion to
displaying the info i do need to implement...
|
by: Shawn Ramirez |
last post by:
As with most web applications speed is a huge deal to me in my applications.
My customers don't really care if my app is a true 3 tier application or not,
they just want it to be faster then it was...
|
by: Mary Pegg |
last post by:
There's got to be a better way:
if (isset($c)) echo $c."<br>";
if (isset($c)) echo $c."<br>";
if (isset($c)) echo $c."<br>";
if (isset($c)) echo $c."<br>";
if (isset($c)) echo $c."<br>";
but...
|
by: DolphinDB |
last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation.
Take...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
| |