| Author |
 Topic  |
|
|
withanhdammit
Junior Member
USA
236 Posts |
 Posted - 30 September 2005 : 15:10:00
|
In my Access 2003 db, I have a StatusID field that I want to filter on. I am using check boxes so I can pick multiple filters, but it only works when one of the check boxes is selected. If I select multiple check boxes, the filter returns no data. Could someone please look at my filter code and tell me what I'm missing.
'Select Store Status Filter
If Me.chkCompleted = True Then
If Not IsNull(strFilter) Then strFilter = strFilter & " And "
strFilter = strFilter & "tblStoreData.StatusID = 1"
End If
If Me.chkScheduled = True Then
Me.tglUpcomingStores.Enabled = True
If Not IsNull(strFilter) Then strFilter = strFilter & " And "
strFilter = strFilter & "tblStoreData.StatusID = 2"
End If
If Me.chkPending = True Then
If Not IsNull(strFilter) Then strFilter = strFilter & " And "
strFilter = strFilter & "tblStoreData.StatusID = 3"
End If
'End Select Store Status Filter
If I select the first check box, then the strFilter returns "tblStoreData.StatusID = 1"
If I select the first and second check box, the strFilter returns "tblStoreData.StatusID = 1 And tblStoreData.StatusID = 2"
If I select all three check boxes, the strFilter returns
"tblStoreData.StatusID = 1 And tblStoreData.StatusID = 2 And
tblStoreData.StatusID = 3"
As far as I can tell, the filter is correct, but something's not quite
right.
Thanks for any help!
Darhl
|
I reject your reality and substitute my own. |
|
|
pdrg
Support Moderator
United Kingdom
2897 Posts |
Posted - 03 October 2005 : 06:15:09
|
easy win on this one!
tblStoreData.StatusID = 1 And tblStoreData.StatusID = 2 And tblStoreData.StatusID = 3
this is where your problem lies - imagine if it said 'show me all cars where the car is red and the car is green and the car is blue' - you'd get no cars which were red, green AND blue at the same time!!
change your AND's above to OR's, should work just fine (show me all cars where the car is red OR the car is green OR the car is blue!!) |
 |
|
|
withanhdammit
Junior Member
USA
236 Posts |
Posted - 03 October 2005 : 07:14:06
|
Thanks pdrq! That was the deal. I knew it had to be something simple, I just couldn't see it.
Much obliged!
h |
I reject your reality and substitute my own. |
|
|
|
pdrg
Support Moderator
United Kingdom
2897 Posts |
Posted - 03 October 2005 : 08:34:49
|
you're totally welcome  |
|
|
|
withanhdammit
Junior Member
USA
236 Posts |
Posted - 03 October 2005 : 11:14:53
|
OK,now that I've got that straightened out, I've added some more filter criteria, in an attempt to make my db app more functional. I'll list the new filter code below, can you take a look at it and let me know if there's a more efficient/streamlined way to do it? It does work correctly now, even with the extra filter stuff, but it's a bunch of lines that I don't know if I really need or not. Of course, if it ain't broke, don't fix it, right? I just want it to be as tight of code as I can get.
Thanks a ton!
h
Private Sub Apply_Filter()
strFilter = Null
SetParen = False
SetParen2 = False
setOwner = False
setStatus = False
setType = False
Me.tglNonTraditional.Enabled = False
Me.tglUpcomingStores.Enabled = False
Me.ogReports = 1
If Me.cboOwner <> "" Or Not IsNull(Me.cboOwner) Then
strFilter = "tblStoreData.OwnerID = " & Me.cboOwner
setOwner = True
End If
'Select Store Status Filter
If Me.chkCompleted = True Then
If setOwner = True Then
strFilter = strFilter & " And ("
SetParen = True
End If
strFilter = strFilter & "tblStoreData.StatusID = 1"
setStatus = True
End If
If Me.chkScheduled = True Then
Me.tglUpcomingStores.Enabled = True
If setOwner = True And setStatus = False Then
strFilter = strFilter & " And ("
SetParen = True
Else
If setStatus = True Then strFilter = strFilter & " Or "
End If
strFilter = strFilter & "tblStoreData.StatusID = 2"
setStatus = True
End If
If Me.chkPending = True Then
If setOwner = True And setStatus = False Then
strFilter = strFilter & " And ("
SetParen = True
Else
If setStatus = True Then strFilter = strFilter & " Or "
End If
strFilter = strFilter & "tblStoreData.StatusID = 3"
setStatus = True
End If
If SetParen = True Then strFilter = strFilter & ")"
'End Select Store Status Filter
'Select Store Type Filter
If Me.chkTraditional = True Then
If setOwner = True Or setStatus = True Then
strFilter = strFilter & " And ("
SetParen2 = True
End If
strFilter = strFilter & "tblStoreData.TypeID = 1"
setType = True
End If
If Me.chkNonTraditional = True Then
If setType = True Then
strFilter = strFilter & " Or "
Else
If setOwner = True Or setStatus = True Then
strFilter = strFilter & " And ("
SetParen2 = True
End If
End If
Me.tglNonTraditional.Enabled = True
strFilter = strFilter & "tblStoreData.TypeID <> 1"
setType = True
End If
If SetParen2 = True Then strFilter = strFilter & ")"
'End Select Store Type Filter
If IsNull(strFilter) Then
Me.FilterOn = False
Else
Me.Filter = strFilter
Me.FilterOn = True
End If
If IsNull(Me.cboOwner) Then
Me.cmdClear.Enabled = False
Else
Me.cmdClear.Enabled = (Me.cboOwner <> "")
End If
End Sub |
I reject your reality and substitute my own. |
|
|
|
pdrg
Support Moderator
United Kingdom
2897 Posts |
Posted - 03 October 2005 : 12:00:41
|
Hmmm - I'm probably not best placed to give you an ASP code review, but wrt database stuff I can give you a big hint for the future, which is that filters blow!
Here's how it all works (roughly, as a rule) - the SQL bit (SELECT blah FROM blah WHERE blah ORDER BY blah) returns a dataset, then the filter goes through that dataset, reducing it further - so if you can just get the right dataset in the first place, you've saved memory and clock cycles :)
So, as a rule, if you *really* want to optimise your code and working, try learning good SQL, and so create your SELECT statements with all the filters you're using in the WHERE clause of the SQL instead - the dbengine is heavily optimised to filter and order records for you - way better than practically any other code you can run - it's a core competency!
Well you *did* ask... ;-) But I'd go with your 'if it aint broke, don't fix it' for now, until you're ready to upgrade your whole way of working to make the most of the dbengine :)
hth! |
|
|
|
withanhdammit
Junior Member
USA
236 Posts |
Posted - 05 October 2005 : 14:34:35
|
Thanks pdrq, it does help. I'll have to take a look at that some day when I'm feeling a little froggy  I'll leave it for now, your suggestions sound like something to do when I'm a little more saavy with the whole SQL stuff.
Thanks again!
h |
I reject your reality and substitute my own. |
|
|
|
pdrg
Support Moderator
United Kingdom
2897 Posts |
Posted - 06 October 2005 : 05:28:58
|
You're welcome - it's worth coming to terms with SQL as a language, it will be incredibly useful to you in the future if you do anything with data ever...and almost identical code (SQL) will run on Oracle, Access, MS SQL Server, Ingres, MySQL etc!
|
|
|
|
AnonJr
Moderator
United States
5768 Posts |
Posted - 06 October 2005 : 06:40:19
|
quote:
Originally posted by pdrg
You're welcome - it's worth coming to terms with SQL as a language, it will be incredibly useful to you in the future if you do anything with data ever...and almost identical code (SQL) will run on Oracle, Access, MS SQL Server, Ingres, MySQL etc!
I will say this, since I've started working with the forum I have learnd tons about SQL; mostly about how to work with INSERT and UPDATE. |
|
|
|
pdrg
Support Moderator
United Kingdom
2897 Posts |
Posted - 06 October 2005 : 07:14:40
|
the big 4 are SELECT, INSERT, UPDATE aand DELETE - the rest is just frills ;-)
The better db engines support other things (stored procedures, triggers, cursors, user-defined functions, blah), but they're really ways of packaging up those big 4 statements, the ones that throw data around ;-) |
|
|
| |
Topic |
|
Topic Locked
