How filter date is null using check box

S

sspreyer

Registered User.
Local time
Today, 05:11
Joined
Nov 18, 2013
Messages
251
Hi all

I have form that user can filter the records and generate a report but I have difficult trying filter null date.

If I have check box called filter null if it has a tick in I would like it only show records that have no value (is null) in field "date start" but if unticked I would like it to only show records with a date in field "date start"

Hope you understand what I trying to do not very good at using expression builder

Thanks in advance you legends

Shane
 
Hi thanks but that thread being build with in vba hoping I could do it with expression builder

Shane
 
Can be done with expession builder, but solutions with Nulls and lookups mixed over potentially multiple fields gets complicated fast. If you can master the VBA way, you can keep things nice and clean.
 
Hi thanks I feel to build it in vba seem a little long and my vba knowledge is very little as only have 2 fields to filter client and date start so I feel it would be easier to build in expressions builder just need help in creating the expression so if check box filter null is ticked show all records with no date in field date start and if filter null is untick only show records with a date in date start field

Thanks
Shane
 
Hi Shane,
Doing it in VBA should actually be very quick. Assuming the date start control is named dtDateStart and the check box named ckDateFilter, you would put the following code in the AfterUpdate event of that control:
Code: 
Private Sub ckDateFilter_AfterUpdate()
Me.FilterOn = False
If Me.ckDateFilter = True Then
Me.Filter = "dtDateStart Is Null"
Else
Me.Filter = "Not dtDateStart Is Null"
End If
Me.FilterOn = True
End Sub

That seems a lot simpler to me than your proposed approach. Code tested and working in 2007.
 
Last edited:
Hi, Marla
Thanks for your help so i have field named dtdatestart in my table and add checkbox named ckDateFilter to my form and have add the code on after update in event control but it does seem to be filtering anything it as the same if i have it ticked or unticked when i open my report i cant see what im doing wrong have you got sample of this working so i can see what im doing wrong

thank you ever so much

shane
 
Shane,
The code I gave will work for filtering form records, not for filtering a report run from the form. What is the code you are currently using to open your report? We'll need to adjust it to add a Where condition based on the ckDateFilter control.
 
Hi Maria
Code I'm using at the moment is calulated column in query here my
Code: if([forms]![Form1]![filter null] = -1 AND isnull(Date Start), 1, iif([forms]![form1]![filter null] = 0 and not isnull(Date start), 1, 0))

I just put one in the date start field criteria

But would like to do this in vba

Thank again shane
 
Shawn,
I repeat, what is the code you are using to open your report that you want filtered?

Marla
 
Sorry Maria

It's
Code: 
DoCmd.OpenReport "report1", acViewPreview


Thanks
Shane
 
Try the following instead:
Code: 
Dim strWhere As String

If Me.ckDateFilter = True Then
	strWhere = "dtDateStart Is Null"
Else
	strWhere = "Not dtDateStart Is Null"
End If

DoCmd.OpenReport "report1", acViewPreview, , strWhere

It's untested air code but will probably work.
 
Thanks Marla worked great! I just put code behind command button one more question if say I would like to put filter on another field call dbDateFinish and check box name ckDateFinishFilter how can add this and still apply the original filter ckDateFilter

Thanks for you help

Shane
 
Shane, are you checking for dtDateFinish to be null as well, or are you checking it against a date value or date range?


P.S. It'd be great if you'd click the Thanks button on the post that resolved your question. :)
 
Hi Marla thanks for the help
Yes dtDateFinish just checking for null same as dtDateStart

Thank again Marla for your help

Shane
 
Shane,
This ought to do it then:
Code: 
Dim strWhere As String

If Me.ckDateFilter = True AND ckDateFinishFilter= True Then
	strWhere = "dtDateStart Is Null AND dtDateFinish Is Null"
ElseIf Me.ckDateFilter = True AND ckDateFinishFilter= False Then
	strWhere = "dtDateStart Is Null AND Not dtDateFinish Is Null"
ElseIf Me.ckDateFilter = False AND ckDateFinishFilter= True Then
	strWhere = "Not dtDateStart Is Null AND dtDateFinish Is Null"
Else
	strWhere = "Not dtDateStart Is Null AND Not dtDateFinish Is Null"
End If

DoCmd.OpenReport "report1", acViewPreview, , strWhere

Thanks for clicking the Thanks button! :)
 
Last edited:
Thank you!!!! Marla Yet again!!!!! work great I have tried to have ago myself add another one I need to add cbknoinvoice last one!!!!check for null like the other 2 here your code I have try to adapt I don't think it right though can you check it over for me

sorry for being a pain in neck very grateful for you time and help here the code


Code: 
 Dim strWhere As String
 If Me.Ckbnodate = True And CkbNodate2 = True And cbknoinvoice = True Then
    strWhere = "[Date Work Started] Is Null AND [Date Work Completed] Is Null and [Invoice Date] is Null"
ElseIf Me.Ckbnodate = True And CkbNodate2 = False And cbknoinvoice = False Then
    strWhere = "[Date Work Started] Is Null AND Not [Date Work Completed] Is Null and Not [Invoice Date] is Null"
ElseIf Me.Ckbnodate = True And CkbNodate2 = False And cbknoinvoice = True Then
    strWhere = "[Date Work Started] Is Null AND Not [Date Work Completed] Is Null and [Invoice Date] is Null"
ElseIf Me.Ckbnodate = True And CkbNodate2 = True And cbknoinvoice = False Then
    strWhere = "[Date Work Started] Is Null AND [Date Work Completed] Is Null and not [Invoice Date] is Null"
ElseIf Me.Ckbnodate = False And CkbNodate2 = True And cbknoinvoice = False Then
    strWhere = "Not[Date Work Started] Is Null AND [Date Work Completed] Is Null and not [Invoice Date] is Null"
    ElseIf Me.Ckbnodate = False And CkbNodate2 = False And cbknoinvoice = True Then
    strWhere = "[Date Work Started] Is Null AND not[Date Work Completed] Is Null and [Invoice Date] is Null"
 ElseIf Me.Ckbnodate = False And CkbNodate2 = True Then
    strWhere = "Not [Date Work Started] Is Null AND [Date Work Completed] Is Null"
Else
    strWhere = " Not [Date Work Started] Is Null AND Not [Date Work Completed] Is Null"
End If
 DoCmd.OpenReport "rptgen", acViewPreview, , strWhere
 End Sub
 
Shane, good job extrapolating from what we'd done already. :) You had a missing space and a missing statement, but this ought to get it:
Code: 
Dim strWhere As String
If Me.Ckbnodate = True And CkbNodate2 = True And cbknoinvoice = True Then
    strWhere = "[Date Work Started] Is Null AND [Date Work Completed] Is Null and [Invoice Date] is Null"
ElseIf Me.Ckbnodate = True And CkbNodate2 = False And cbknoinvoice = False Then
    strWhere = "[Date Work Started] Is Null AND Not [Date Work Completed] Is Null and Not [Invoice Date] is Null"
ElseIf Me.Ckbnodate = True And CkbNodate2 = False And cbknoinvoice = True Then
    strWhere = "[Date Work Started] Is Null AND Not [Date Work Completed] Is Null and [Invoice Date] is Null"
ElseIf Me.Ckbnodate = True And CkbNodate2 = True And cbknoinvoice = False Then
    strWhere = "[Date Work Started] Is Null AND [Date Work Completed] Is Null and not [Invoice Date] is Null"
ElseIf Me.Ckbnodate = False And CkbNodate2 = True And cbknoinvoice = False Then
    strWhere = "Not [Date Work Started] Is Null AND [Date Work Completed] Is Null and not [Invoice Date] is Null"
ElseIf Me.Ckbnodate = False And CkbNodate2 = False And cbknoinvoice = True Then
    strWhere = "[Date Work Started] Is Null AND not[Date Work Completed] Is Null and [Invoice Date] is Null"
 ElseIf Me.Ckbnodate = False And CkbNodate2 = True And cbknoinvoice = True Then
    strWhere = "Not [Date Work Started] Is Null AND [Date Work Completed] Is Null and [Invoice Date] is Null"
Else
    strWhere = "Not [Date Work Started] Is Null AND Not [Date Work Completed] Is Null"
End If
DoCmd.OpenReport "rptgen", acViewPreview, , strWhere
 
Thank you!!!!!! Marla for your help work Great Thread Closed
Legend!
 
Hi
Marla I have open this thread again!!! due to being asked to add another field call Date 1 and checkbox for this is called ckDateRaised check for null again like the others I have adapted the code but would like you check over it see if I miss any statements which I'm pretty confident I haven't but would like your expertise to look over it
here it is

Code: 
 Dim strWhere As String
If Me.Ckbnodate = True And CkbNodate2 = True And cbknoinvoice = True And ckDateRaised = True Then
    strWhere = "[Date Work Started] Is Null AND [Date Work Completed] Is Null and [Invoice Date] is Null and [Date 1] is Null"
 
 ElseIf Me.Ckbnodate = True And CkbNodate2 = False And cbknoinvoice = False And ckDateRaised = False Then
    strWhere = "[Date Work Started] Is Null AND Not [Date Work Completed] Is Null and Not [Invoice Date] is Null and Not [Date 1] is Null"

  ElseIf Me.Ckbnodate = True And CkbNodate2 = False And cbknoinvoice = True And ckDateRaised = False Then
    strWhere = "[Date Work Started] Is Null AND Not [Date Work Completed] Is Null and [Invoice Date] is Null and Not [Date 1] is Null"

  ElseIf Me.Ckbnodate = True And CkbNodate2 = True And cbknoinvoice = False And ckDateRaised = False Then
    strWhere = "[Date Work Started] Is Null AND [Date Work Completed] Is Null and not [Invoice Date] is Null and Not [Date 1] is Null"

  ElseIf Me.Ckbnodate = False And CkbNodate2 = True And cbknoinvoice = False And ckDateRaised = False Then
    strWhere = "Not [Date Work Started] Is Null AND [Date Work Completed] Is Null and not [Invoice Date] is Null and Not [Date 1] is Null"

  ElseIf Me.Ckbnodate = False And CkbNodate2 = False And cbknoinvoice = True And ckDateRaised = False Then
    strWhere = "[Date Work Started] Is Null AND not[Date Work Completed] Is Null and [Invoice Date] is Null and Not [Date 1] is Null"
 
 ElseIf Me.Ckbnodate = False And CkbNodate2 = True And cbknoinvoice = True And ckDateRaised = False Then
    strWhere = "Not [Date Work Started] Is Null AND [Date Work Completed] Is Null and [Invoice Date] is Null and Not [Date 1] is Null"

  ElseIf Me.Ckbnodate = True And CkbNodate2 = False And cbknoinvoice = False And ckDateRaised = True Then
        strWhere = "[Date Work Started] Is Null AND Not [Date Work Completed] Is Null and Not [Invoice Date] is Null and [Date 1] is Null"

  ElseIf Me.Ckbnodate = True And CkbNodate2 = False And cbknoinvoice = True And ckDateRaised = True Then
    strWhere = "[Date Work Started] Is Null AND Not [Date Work Completed] Is Null and [Invoice Date] is Null and [Date 1] is Null"

  ElseIf Me.Ckbnodate = True And CkbNodate2 = True And cbknoinvoice = False And ckDateRaised = True Then
    strWhere = "[Date Work Started] Is Null AND [Date Work Completed] Is Null and not [Invoice Date] is Null and [Date 1] is Null"

  ElseIf Me.Ckbnodate = False And CkbNodate2 = True And cbknoinvoice = False And ckDateRaised = True Then
    strWhere = "Not [Date Work Started] Is Null AND [Date Work Completed] Is Null and not [Invoice Date] is Null and [Date 1] is Null"

  ElseIf Me.Ckbnodate = False And CkbNodate2 = False And cbknoinvoice = True And ckDateRaised = True Then
    strWhere = "[Date Work Started] Is Null AND not[Date Work Completed] Is Null and [Invoice Date] is Null and [Date 1] is Null"
 
 ElseIf Me.Ckbnodate = False And CkbNodate2 = True And cbknoinvoice = True And ckDateRaised = True Then
    strWhere = "Not [Date Work Started] Is Null AND [Date Work Completed] Is Null and [Invoice Date] is Null and  [Date 1] is Null"

  Else
    strWhere = "Not [Date Work Started] Is Null AND Not [Date Work Completed] Is Null"
End If
DoCmd.OpenReport "rptgen", acViewPreview, , strWhere
 End Sub
thanks for your time and help

Shane
 
You must log in or register to reply here.

Similar threads

opopanax666
insert literal date when null
Replies
6
Views
423
Pat Hartman
P
Q
Solved Filtering range of records
Replies
19
Views
515
quest
Q
C
Invalid use of Me.
Replies
8
Views
455
cheekybuddha
cheekybuddha
T
Solved It takes too long to download data from server
Replies
8
Views
310
GPGeorge
GPGeorge
C
Date Filter not working
Replies
3
Views
537
Gasman
Gasman

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom