Null value filtering syntax help

[melody.anne]

I want a code like this:

DoCmd.OpenReport "rptPrecintosAGDLLA", acViewReport, , "[Username]='" & Me.[Username] & "'"

But after the "Me.[Username]" part, I want it to also filter to only show me data that has null values on a column called "PN".

Alternatively, I also need a code that instead of only showing me data that has null values, I want it to show me data that excludes the data with null values on the column called "PN".

Can this be achieved with this simple code, or do I need something more complex? Can you show me a syntax example? Thanks!

 

[namliam]

either
"[Username]='" & Me.[Username] & "' and PN is null"
or
"[Username]='" & Me.[Username] & "' and PN is NOT null"
as answers to your two questions.

 

[melody.anne]

either
"[Username]='" & Me.[Username] & "' and PN is null"
or
"[Username]='" & Me.[Username] & "' and PN is NOT null"
as answers to your two questions.

Thanks for your input; however, what about in the case where I need three criteria instead of two? Like this one:

DoCmd.OpenReport "rptPrecintosMAYA", acViewReport, , "[Username]='" & Me.[Username] & "' AND [Order Type]='" & Me.[Order Type] & "' AND [PN] is not null & " '"

Got a runtime error '3075' saying there is a syntax error

Edit: Scratch that, it gives me the runtime error whether there's one, two, or three criteria to filter with.

 

[Gasman]

Try
DoCmd.OpenReport "rptPrecintosMAYA", acViewReport, , "[Username]='" & Me.[Username] & "' AND [Order Type]='" & Me.[Order Type] & " AND [PN] is not null"

If I was needing to vary the criteria, I'd probably build each part as a variable and then just use strCriteria1 & strCriteria2 & strCriteria3.

Normally makes the syntax easier.? I always get mixed up with the quotes single or double. as I do not do Access enough to do it off by heart. You can also inspect the string for errors in debug as well.

 

[melody.anne]

Try
DoCmd.OpenReport "rptPrecintosMAYA", acViewReport, , "[Username]='" & Me.[Username] & "' AND [Order Type]='" & Me.[Order Type] & " AND [PN] is not null"

If I was needing to vary the criteria, I'd probably build each part as a variable and then just use strCriteria1 & strCriteria2 & strCriteria3.

Normally makes the syntax easier.? I always get mixed up with the quotes single or double. as I do not do Access enough to do it off by heart. You can also inspect the string for errors in debug as well.

Had tried that already, no luck.

 

[namliam]

Make your code readable and you will find your problem

DoCmd.OpenReport "rptPrecintosMAYA", acViewReport, , "[Username]='" & Me.[Username] & "' " & _ 
                                                " AND [Order Type]='" & Me.[Order Type] & "' " & _  
                                                " AND [PN] is not null & " [b][u][COLOR="Red"]'[/COLOR][/b][/u]"

Note: I bolded, underlined and made it red for you as well

And yes you can extend or remove the number of restrictions you need, preferably you want to dynamicaly build it depending on input of the user ... or data entered on the form.

 

[melody.anne]

Make your code readable and you will find your problem

DoCmd.OpenReport "rptPrecintosMAYA", acViewReport, , "[Username]='" & Me.[Username] & "' " & _ 
                                                " AND [Order Type]='" & Me.[Order Type] & "' " & _  
                                                " AND [PN] is not null & " [B][U][COLOR=red]'[/COLOR][/U][/B]"

Note: I bolded, underlined and made it red for you as well

And yes you can extend or remove the number of restrictions you need, preferably you want to dynamicaly build it depending on input of the user ... or data entered on the form.

This is actually the first format I tried :/
Could it be that PN isn't on the report itself, but it's in the query the report feeds off of?

 

[fernando.rosales]

Why don't you just make a query and tie the report to that. You can also add a user input [] so when you open the report it will ask the value you need to filter by.

 

[melody.anne]

Why don't you just make a query and tie the report to that. You can also add a user input [] so when you open the report it will ask the value you need to filter by.

Because that means that for every report I have of this (5) I'd have to create 3 sub reports. One for all the data, one for null data, and one for all - null data, making it 15 different reports.

Edit: did the whole "OpenArgs" thing to use my already existing reports for this while using different queries.

Thanks everyone!