Filtering all but Fields with no DAte in a case statement

[eddyc]

Hi guys am trying to Filter all records that have been archived in a form. I know i can just build the form on a query excluding them but i want to be able to see and not see them. So i wrote this code -

Private Sub Check259_AfterUpdate()
Select Case Me!Check259.Value
Case True
Me.Filter = "[Archive Dates] = ** '" & NotIsNull & "'" **
Me.FilterOn = True
Case Else
Me.FilterOn = False
End Select

End Sub

What i want to be able to say where i have starred it is "where [Archive Dates] is has a date exclude it . But don't know how to any ideas? ed

 

[vbaInet]

Almost there:

Private Sub Check259_AfterUpdate()
    Select Case Me.Check259.Value
        Case Yes
            Me.Filter = "[Archive Dates] Not Is Null"
            Me.FilterOn = True
        Case Else
            Me.FilterOn = False
    End Select
End Sub

I would strongly advice you give your check box a meaningful name.

http://www.mvps.org/access/general/gen0012.htm

 

[eddyc]

Thankyou Very much i will try AND i will start to Give my Check boxes meaningful names. Think i have not so far because i expect them not to work !!! BTW where is the best place for looking up syntax, available functions etc on the web ... takes me time to find what the syntax is for the expressions and functions i am trying to use.

Thanks a million for your help.

I have tried the code but now it is saying that the search key is not found in any record and pulling up the Me.FilterOn = True for some reason? should i have single parentheses around the Not Is Null?

Cheers eddy

 

[DCrake]

If you need help on certain functions
Press Ctrl+G (immediate window)

Type in the function you want help with, eg:

DateDiff
Then press F1

 

[vbaInet]

This:

            Me.Filter = "Not [Archive Dates] Is Null"

The other variant (and this one) works directly in the SQL statement of a query but not in the Filter method.

Office online is another good resource too, but VBA help (not Access help) is sufficient.

 

[eddyc]

Brilliant thank you so much vbaInet Thats a great call it works great was it supposed or only in SQL done wonders for my form so thank you very much and thanks for all your tips on F1 and Ctrl G. i look forward to trying it out.

Ps... one simple thing that has been bugging me because i am sure i can work quicker is where can i find a list of the shortcut commands so i can flick between design and layout view quickly or save and close a form with a ctrl combination key ... i don't know the technical term to look for ...

Thank you,

Ed

 

[vbaInet]

Have a look at these links:

http://office.microsoft.com/en-us/access-help/keyboard-shortcuts-HP003089886.aspx

http://www.ddmcomputing.com/access/keys/ac_skeys.htm

 

[eddyc]

Is it possible to do this
Me.Filter = "[OPEN/CLOSED] = 'OPEN'" And "Not [Archive Dates] Is Null"
?

 

[vbaInet]

Sure is:

            Me.Filter = "[Open/Closed] = 'Open' AND Not [Archive Dates] Is Null"

 

[eddyc]

That is very kind - Thank You! I have it working now and it has transformed the form. I feel very encouraged by you and really appreciate your help. Thank you very much!

 

[vbaInet]

Glad we could help!