By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,573 Members | 903 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,573 IT Pros & Developers. It's quick & easy.

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

P: 36
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
Expand|Select|Wrap|Line Numbers
  1. Dim strCrt as String
  2. strCrt = "ID =" & "'" & Me.ID & "'"
  3. strCrt = strCrt & "And [dblPrint_Copies_Of_Labels]" is Not Null"
  4. 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
Jul 10 '10 #1

✓ answered by 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.

Share this Question
Share on Google+
7 Replies


nico5038
Expert 2.5K+
P: 3,072
Almost correct, try:
Expand|Select|Wrap|Line Numbers
  1. Dim strCrt as String
  2. strCrt = "ID =" & "'" & Me.ID & "'"
  3. strCrt = strCrt & " And [dblPrint_Copies_Of_Labels] is Not Null"
  4. DoCmd.OpenReport "LabelPrinting", acViewPreview, , strCrt, acDialog
  5.  
I've added a space in front of the AND and there's a " too much after the [dblPrint_Copies_Of_Labels].

Nic;o)
Jul 10 '10 #2

mseo
100+
P: 181
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
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdbutton_Click()
  2. On Error GoTo err_cmdbutton_click
  3. Dim stDocName As String
  4.     stDocName = "report name"
  5. If Len(Me.id& vbNullString) = 0 Then
  6.             MsgBox "no id to open report", vbokonly, "bytes"
  7.     Exit Sub
  8. Else
  9.         DoCmd.OpenReport stDocName, acPreview
  10. End If
  11. exit_cmdbutton_Click:
  12.     Exit Sub
  13. err_cmdbutton_click:
  14.     MsgBox "Sorry for inconvenience"
  15.     Resume exit_cmdbutton_Click
  16. End Sub
hope this helps
Jul 10 '10 #3

P: 36
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
Jul 10 '10 #4

nico5038
Expert 2.5K+
P: 3,072
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)
Jul 10 '10 #5

NeoPa
Expert Mod 15k+
P: 31,485
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.
Jul 11 '10 #6

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

Its working now
Jul 14 '10 #7

NeoPa
Expert Mod 15k+
P: 31,485
Ah good. Thanks for posting :)
Jul 14 '10 #8

Post your reply

Sign in to post your reply or Sign up for a free account.