How to open a report/form using 'where' condition(X field is not null AND ID = Me.ID)

[prashantdixit]

Hi,
I am beginner and i would like to open a report using where clause. Now i have to merge two condition in where clause
1. [ID] = Me!ID AND
2. [Fiel X in table] is Not Null

The code i have written is

Dim strCrt as String
strCrt = "ID =" & "'" & Me.ID & "'"
strCrt = strCrt & "And [dblPrint_Copies_Of_Labels]" is Not Null"
DoCmd.OpenReport "LabelPrinting", acViewPreview, , strCrt, acDialog

But somehow this is not working.
I dont know how to do this
Can someone help me on this?

Thanks
Prashant

[nico5038]

Almost correct, try:

Dim strCrt as String
strCrt = "ID =" & "'" & Me.ID & "'"
strCrt = strCrt & " And [dblPrint_Copies_Of_Labels] is Not Null"
DoCmd.OpenReport "LabelPrinting", acViewPreview, , strCrt, acDialog

I've added a space in front of the AND and there's a " too much after the [dblPrint_Copies _Of_Labels].

Nic;o)

[mseo]

hi
you can use this code on click command button event but after modifing the name the controls to fit the names you have in your form
then in the underlying SQL of your report, set the criteria of fieldX to be is not null

Private Sub cmdbutton_Click()
On Error GoTo err_cmdbutton_click
Dim stDocName As String
    stDocName = "report name"
If Len(Me.id& vbNullString) = 0 Then
            MsgBox "no id to open report", vbokonly, "bytes"
    Exit Sub
Else
        DoCmd.OpenReport stDocName, acPreview
End If
exit_cmdbutton_Click:
    Exit Sub
err_cmdbutton_click:
    MsgBox "Sorry for inconvenience"
    Resume exit_cmdbutton_Click
End Sub

hope this helps

[prashantdixit]

i think i have given the wrong information. What i want actually is:
1. When user clicks on ViewLabel or Print Button on Form A then only data corresponding to this Form A should open in Print Preview mode or print it respectively
2. When user clicks on Skip Label button then user should jump to next record and now when user clicks on View Label/Print button do the required task for this record.

Now as per your code or the one which i have written, its opening report/printing report for all the data against which [dblPrint_Copies _Of_Labels] is Not Null"

Can you help me guys in this requirement

[nico5038]

You lose me, as the "ID =" & "'" & Me.ID & "'" part will only get the current ID from the form....

Make sure you use the exact code !

Nic;o)

[NeoPa]

We're asuming, as you had it that way in your original code, that [ID] is a string field. If this is not the case then lose the single-quotes (') to indicate a numeric literal (See Quotes (') and Double-Quotes (") - Where and When to use them).

PS. If you continue to struggle, please post your latest code with an explanation of what result you get. That way we have a better idea of what's going on and can be more help to you.

[prashantdixit]

Thanks for all your help. [ID] is numeric field and i have removed the single quotes.

Its working now

[NeoPa]

Ah good. Thanks for posting :)