dont print report if field1=null

[Xiphiaz]

Hi all,

Im printing a report from a form. using a filter:

Dim stDocName As String
stLinkCriteria = "[RMA nr]=" & "'" & Me![RMA nr] & "'"
stDocName = "Repair"
DoCmd.OpenReport stDocName,acViewNormal, ,stLinkCriteria

But I want to put an extra filter in it and I dont know how to do that.

If field1 doesnt have data, then theres no need to print the form.

So what I need is a filter like: [RMA nr]=123 AND [field1]<>null

Can someone tell me how to do that??

Thanx,
Xiphias

 

[cghoga]

Hi. Try this...

Dim stDocName As String
stLinkCriteria = &quot;[RMA nr]=&quot; &amp; &quot;'&quot; &amp; Me![RMA nr] &amp; &quot;'&quot;
stDocName = &quot;Repair&quot;

If IsNull(Field1) and Me.[RMA nr] = 123 Then

Else

DoCmd.OpenReport stDocName,acViewNormal, ,stLinkCriteria

End If

 

[Xiphiaz]

Hi Cghoga

Thank you for your reaction, but this isnt what I need.

Because when I use the filter on the report( &quot;[RMA nr]=&quot; &amp; &quot;'&quot; &amp; Me![RMA nr] &amp; &quot;'&quot
Then I got e.g. 10 records that are going to be printed, but a few of them have a blank&quot;field1&quot;.
Those records dont have to be printed.

So I want to put a &quot;double&quot; Filter on the record.

e.g. DoCmd.OpenReport stDocName,acViewNormal, ,stLinkCriteria AND [field1]&lt;&gt;Null
Or something like that...

Hope you can help me.

Greetings,
Xiphias

 

[EmptyMind]

silly question, have you tried:

stLinkCriteria = &quot;[RMA nr]=&quot; &amp; &quot;'&quot; &amp; Me![RMA nr] &amp; &quot;' AND '&quot; &amp; Me![field1] &amp; &quot;' &lt;&gt; Null&quot;

otherwise couldn't you base your report on a query that evaluates this from the form for you?

 

[RoyVidar]

If you alredy have assigned something to the stLinkCriteria, just add this criteria using text concatination. You'll need the SQL &quot;version&quot; of testing for null, something like this:

[tt]stLinkCriteria = stLinkCriteria &amp; &quot; AND Not [field1] Is Null&quot;
DoCmd.OpenReport stDocName,acViewPreview, ,stLinkCriteria[/tt]

If this produces no data, you could use the on no data event of the report, to cancel the opening:

[tt]msgbox &quot;No data&quot;
cancel=true[/tt]

This would again create the need to trap for the &quot;canceling report error&quot; in the calling routine. I'm assuming you have some ordinary error trapping, then include/alter the statement providing a message box:

[tt]if err.number &lt;&gt; 2501 then
msgbox err.description
end if[/tt]

Roy-Vidar

 

[Xiphiaz]

Yes its working!!

This is going to save me loads of paper!

I used the :
stLinkCriteria = stLinkCriteria &amp; &quot; AND Not [field1] Is Null&quot;

I didnt checked the other solutions, but thanx anyway..

Have a very nice day!