how to turn my expression in query to vba

[sspreyer]

hi , all

i have calulated expression in column in my query which filters my date field "date start" if i tick my checkbox "filter null" and click my command button on my form, it open my report and only show records with no date in "date start" field and if unticked only shows records that have a date in the "date start Field " some reason doesnt seem work all the time and would like to put it in to vba seem better option

heres my query expression

if([forms]![Form1]![filter null] = -1 AND isnull(Date Start), 1, iif([forms]![form1]![filter null] = 0 and not isnull(Date start), 1, 0))

my vba knowleage is very little hope someone could give me step 2 step guide on how to convert this to vba

thanks in advnce

shane

[June7]

If it doesn't always work in query will probably have issues with VBA, but maybe easier to debug.

Is [filter null] an unbound checkbox?

Advise no spaces or special characters/punctuation (underscore is exception) in naming convention. Better would be DateStart or Date_Start.

Name checkbox like cbxFilter

Something like:

DoCmd.OpenReport "reportname", , , IIf(Me.cbxFilter = False, "NOT ", "") & "[Date Start] Is Null"

[sspreyer]

Hi June7
Thanks yes filter null is unbound checkbox



shane

[June7]

This is resolved?

[sspreyer]

Thanks i will try later when I'm home later and will report back

Yet again thanks for your time

Shane

[sspreyer]

Hi June7
thank you ever so much worked Great adjusted slightly here it is

DoCmd.OpenReport "report2", acViewPreview, , IIf(Me.cbxFilter = False, "NOT ", "") & "[Date Start] Is Null"

just one more question how can add another filter to this

sort of like

 DoCmd.OpenReport "report2", acViewPreview, , IIf(Me.cbxFilter = False, "NOT ", "") & "[Date Start] Is Null" & IIf(Me.cbx2Filter = False, "NOT ", "") & "[Date completed] Is Null" but this doesn't work have I got wrong expression here?

any way JUNE7 you legend !!!

shane

[June7]

Try:

DoCmd.OpenReport "report2", acViewPreview, , IIf(Me.cbxFilter = False, "NOT ", "") & "[Date Start] Is Null" & " AND " & IIf(Me.cbx2Filter = False, "NOT ", "") & "[Date completed] Is Null"

[sspreyer]

Thanks!!!!!!! June7 worked yet again

thanks

shane