Why is this not working (Where Clause)?

[Ron]

I'm not sure why this is not working. It is returning all records instead
of just the desired criteria:

Report Source is:
SELECT InvoiceHeader.*, InvoiceDetail.*, Trim(InvoiceHeader!fname) & " " &
Trim(InvoiceHeader!lname) AS Printname FROM InvoiceHeader INNER JOIN
InvoiceDetail ON InvoiceHeader.Recno=InvoiceDetail.InvoiceRecno;

Here is the code from my form's button:
Private Sub btnPrintNow_Click()
Dim stReportname As String
Dim stCriteria As String

stReportname = "rptInvoice"

With Me!Recno
If Not IsNull(.Value) Then
strCriteria = "[InvoiceHeader.Recno] = " & .Value
End If
End With

MsgBox strCriteria
DoCmd.OpenReport stReportname, acViewPreview, Wherecondition:=stCriteria
End Sub

The msgbox is showing the criteria to be correct, but all the records are
still being selected.

 

[Rick Brandt]

I'm not sure why this is not working. It is returning all records
instead of just the desired criteria:

Report Source is:
SELECT InvoiceHeader.*, InvoiceDetail.*, Trim(InvoiceHeader!fname) &
" " & Trim(InvoiceHeader!lname) AS Printname FROM InvoiceHeader INNER
JOIN InvoiceDetail ON InvoiceHeader.Recno=InvoiceDetail.InvoiceRecno;

Here is the code from my form's button:
Private Sub btnPrintNow_Click()
Dim stReportname As String
Dim stCriteria As String

stReportname = "rptInvoice"

With Me!Recno
If Not IsNull(.Value) Then
strCriteria = "[InvoiceHeader.Recno] = " & .Value
End If
End With

MsgBox strCriteria
DoCmd.OpenReport stReportname, acViewPreview,
Wherecondition:=stCriteria End Sub

The msgbox is showing the criteria to be correct, but all the records
are still being selected.

Try just...

strCriteria = "Recno = " & .Value

 

[Ron]

No, still grabs all

I'm not sure why this is not working. It is returning all records
instead of just the desired criteria:

Report Source is:
SELECT InvoiceHeader.*, InvoiceDetail.*, Trim(InvoiceHeader!fname) &
" " & Trim(InvoiceHeader!lname) AS Printname FROM InvoiceHeader INNER
JOIN InvoiceDetail ON InvoiceHeader.Recno=InvoiceDetail.InvoiceRecno;

Here is the code from my form's button:
Private Sub btnPrintNow_Click()
Dim stReportname As String
Dim stCriteria As String

stReportname = "rptInvoice"

With Me!Recno
If Not IsNull(.Value) Then
strCriteria = "[InvoiceHeader.Recno] = " & .Value
End If
End With

MsgBox strCriteria
DoCmd.OpenReport stReportname, acViewPreview,
Wherecondition:=stCriteria End Sub

The msgbox is showing the criteria to be correct, but all the records
are still being selected.

Try just...

strCriteria = "Recno = " & .Value

 

[Ron]

I also tried "Invoice.recno" to no avail

No, still grabs all

I'm not sure why this is not working. It is returning all records
instead of just the desired criteria:

Report Source is:
SELECT InvoiceHeader.*, InvoiceDetail.*, Trim(InvoiceHeader!fname) &
" " & Trim(InvoiceHeader!lname) AS Printname FROM InvoiceHeader INNER
JOIN InvoiceDetail ON InvoiceHeader.Recno=InvoiceDetail.InvoiceRecno;

Here is the code from my form's button:
Private Sub btnPrintNow_Click()
Dim stReportname As String
Dim stCriteria As String

stReportname = "rptInvoice"

With Me!Recno
If Not IsNull(.Value) Then
strCriteria = "[InvoiceHeader.Recno] = " & .Value
End If
End With

MsgBox strCriteria
DoCmd.OpenReport stReportname, acViewPreview,
Wherecondition:=stCriteria End Sub

The msgbox is showing the criteria to be correct, but all the records
are still being selected.

Try just...

strCriteria = "Recno = " & .Value

 

[Rick Brandt]

I also tried "Invoice.recno" to no avail

Simplify and conquer. What happens if you use a literal that you know is in the
report's RecordSet?

Like...

strCriteria = "Recno = 123"

 

[Duane Hookom]

Try compile your code with Option Explicit. Then use code like:

Private Sub btnPrintNow_Click()
Dim stReportname As String
Dim stCriteria As String

stReportname = "rptInvoice"

With Me!Recno
If Not IsNull(.Value) Then
stCriteria = "[Recno] = " & .Value
End If
End With

MsgBox strCriteria
DoCmd.OpenReport stReportname, acViewPreview, , stCriteria
End Sub


--
Duane Hookom
MS Access MVP
--

No, still grabs all

I'm not sure why this is not working. It is returning all records
instead of just the desired criteria:

Report Source is:
SELECT InvoiceHeader.*, InvoiceDetail.*, Trim(InvoiceHeader!fname) &
" " & Trim(InvoiceHeader!lname) AS Printname FROM InvoiceHeader INNER
JOIN InvoiceDetail ON InvoiceHeader.Recno=InvoiceDetail.InvoiceRecno;

Here is the code from my form's button:
Private Sub btnPrintNow_Click()
Dim stReportname As String
Dim stCriteria As String

stReportname = "rptInvoice"

With Me!Recno
If Not IsNull(.Value) Then
strCriteria = "[InvoiceHeader.Recno] = " & .Value
End If
End With

MsgBox strCriteria
DoCmd.OpenReport stReportname, acViewPreview,
Wherecondition:=stCriteria End Sub

The msgbox is showing the criteria to be correct, but all the records
are still being selected.

Try just...

strCriteria = "Recno = " & .Value

 

[Ron]

Thanks Rick. I did put in a literal as you suggested and it worked like a
charm. So then I looked again and low and behold, the variable names are
different in my code (I am passing STcriteria but assigning a value to
STRcriteria). ARGH!

Thanks for the sanity!!

 

[Rick Brandt]

Thanks Rick. I did put in a literal as you suggested and it worked
like a charm. So then I looked again and low and behold, the
variable names are different in my code (I am passing STcriteria but
assigning a value to STRcriteria). ARGH!

Thanks for the sanity!!

Ah, that's why you always want OPTION EXPLICIT at the top of your modules.
If you spell a variable wrong it will catch it with Option Explicit, but
won't otherwise.