Combo Filter not working for 'All'

[Tupacmoche]

I can't implement 'All' in a combo box control. I set up a field in my table called 'complete' its data type is bit and my database is mssql 2008 R2. I set the default value to zero (0). On the Access side, I made a table called tblComplete with three columns. The first is Description {All, Complete, Incomplete} values the second is Complete {'', 1, 0} Values. The code for the combo box AfterUpdate is :

Me.Filter = "(Complete) = " & Me.CboIsComplete
Me.FilterOn = True
Me.CboSigner.SetFocus

Finally, I have a qry that runs against the table tblComplete table above as follows:

SELECT
tblComplete.Description,
tblComplete.Complete
FROM tblComplete
UNION
Select "All" as Description,
'' as Complete
From tblComplete;
This gives me a table with a Description column 'All', 'Complete', 'Incomplete' and second column Complete '' (empty string), 1, 0
How can I get this to work?:banghead:

 

[CJ_London]

looks like your second column is mixing datatypes which will confuse access, either use

null as Complete

in your union query

or change your numbers to text "0","1"

but that will impact on your bit datatype

other things that could be causing a problem is you are using a reserved word - description which can have unexpected effects - see this link https://www.access-programmers.co.uk/forums/showthread.php?t=301681

finally suggest your vba code needs to be

if isnull(Me.CboIsComplete) then Me.Filter = "" else Me.Filter = "(Complete) = " & Me.CboIsComplete
Me.FilterOn = True

 

[Tupacmoche]

I'm changing to a char field as you suggested. I have changed description to EpistleDescription and EpistleStatus which should not be reserve words. And as they indicate show whether a letter is complete or not. So, EpistleDescription has two rows Complete, Incomplete. EpistleStatus has 1. This is where confusion sets in. Since, the EpistleStatus column is not populated on creation it is null. Only when a user completes a letter will a row be marked as complete "1" but everything else is NULL.

So, if you want to see complete things in the combo box select "1". But Null only shows rows that have not been processed and there is no "0" for incomplete since the default value for this column is Null. I guess Null could mean incomplete but what if you want to simple see all rows and have an 'All' selection in the combo box?
Please clarify.

 

[CJ_London]

not sure what you are asking

 

[Tupacmoche]

I have to resurrect this issue since it is not resolved on my end. I hope that I can clearly explain the matter. I have a column that I have added to the table that allows NULL so when, I start all the rows in this table of about 150 rows has all NULL's. The purpose of the column is to mark a record as complete. So, I have 150 NULL when I start. After completing 50 I have 50 equal to 1, and 100 equal to null.

So, I can filter for complete by selecting from a combo box for 1. While null can mean incomplete since nothing has been done to the row how would, I select ALL?

 

[CJ_London]

to clarify, your target table has a field (EpistleStatus) populated with null or 1. What I expect your combo rowsource (a value list) to be is something like

null;incomplete;1;complete

you really need to change your EpistleStatus values to 0 and 1 rather than null, it would make everything a lot easier

but sticking with what you have to get all you need another value, suggest 0, so your rowsource becomes

null;incomplete;1;complete;0;All

then your filter or criteria (not sure how you have it at the moment) would be something like


cboEpistleStatus=0 OR (EpistleStatus is null and cboEpistleStatus is null) OR (EpistleStatus=1 and cboEpistleStatus=1)

if you modified your EpistleStatus values to 0 and 1 then your rowsource would be

0;incomplete;1;complete;null;All

and your filter/criteria would be

cboEpistleStatus is null OR (EpistleStatus = cboEpistleStatus)

 

[JHB]

I would just turn the filter off.

  If Me.CboIsComplete.Column(1) = "All" Then
    Me.FilterOn = False
  Else
    Me.Filter = "(Complete) = " & Me.CboIsComplete
    Me.FilterOn = True
  End If
  Me.CboSigner.SetFocus

 

[CJ_London]

Me.Filter = "(Complete) = " & Me.CboIsComplete

don't think that will work when CboIsComplete is null

 

[JHB]

don't think that will work when CboIsComplete is null

Yes you're right, you made me read the whole thread again, (example is attached).

Private Sub CboIsComplete_AfterUpdate()
  If Me.CboIsComplete.Column(1) = "All" Then
    Me.FilterOn = False
  ElseIf Me.CboIsComplete.Column(1) = "InComplete" Then
    Me.Filter = "IsNull([ProjectStatus])"
    Me.FilterOn = True
  Else
    Me.Filter = "[ProjectStatus] = " & Me.CboIsComplete
    Me.FilterOn = True
  End If
 ' Me.CboSigner.SetFocus
End Sub

 

[CJ_London]

Would be easier of the OP changed the data to 0 and 1 rather than null and 1 because at the moment you have to test twice because the comparison method is different for nulls to numbers. Either something is complete or its not. Null would then imply 'don't know'