Open Report with two criteria

  • Thread starter Thread starter el zorro
  • Start date Start date
E

el zorro

I have an adp linked to SQL 2000 back end. I have a from from which the user
selects a month by clicking on one of a group of radio buttons (Frame2) and a
year by selecting from a list (List470.) After making the selection, the user
clicks a control buttton with the following code (in part):
Dim stDocName As String
Dim stLinkCriteria As String
Dim stLinkCriteria2 As String

stLinkCriteria = "[BillMonth]=" & Me![Frame2]
stLinkCriteria2 = "[BillYear]=" & Me![List470]

stDocName = "VerifiedRgnR"
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria And
stLinkCriteria2

Each stLinkCriteria works individually, but I cant seem to get the syntax
right to combine them. I guess "And" isn't it. Probably obvious, but you help
would be greatly appreciated!
 
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria & " And " &
stLinkCriteria2

Although personally, I'd just use

Dim stDocName As String
Dim stLinkCriteria As String

stLinkCriteria = "[BillMonth]=" & Me![Frame2] & " And " & _
"[BillYear]=" & Me![List470]

stDocName = "VerifiedRgnR"
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria
 
On Fri, 14 Dec 2007 21:12:30 -0500, "Douglas J. Steele"

Doug is right.
The reason yours did not work was because And is a boolean operator.
Access treats "s1 And s2" as such, and therefore a Type Mismatch
occurs, because And only works with numeric arguments.

-Tom.
 
THanks Douglas-- That worked great! On a related note, I want to do the same
thing with the DoCmd.OpenQuery statement, and just run the query. THe
problem appears to be that DoCmd.OpenQuery does not allow for the insertion
of query criteria, as the OpenReport copmmand did. At least, that's how it
looks to me. Any suggestions? (I'm trying to keep the criteria out of the SQL
statement, since the month and year need to be taken from user input via the
form.)

Douglas J. Steele said:
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria & " And " &
stLinkCriteria2

Although personally, I'd just use

Dim stDocName As String
Dim stLinkCriteria As String

stLinkCriteria = "[BillMonth]=" & Me![Frame2] & " And " & _
"[BillYear]=" & Me![List470]

stDocName = "VerifiedRgnR"
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


el zorro said:
I have an adp linked to SQL 2000 back end. I have a from from which the
user
selects a month by clicking on one of a group of radio buttons (Frame2)
and a
year by selecting from a list (List470.) After making the selection, the
user
clicks a control buttton with the following code (in part):
Dim stDocName As String
Dim stLinkCriteria As String
Dim stLinkCriteria2 As String

stLinkCriteria = "[BillMonth]=" & Me![Frame2]
stLinkCriteria2 = "[BillYear]=" & Me![List470]

stDocName = "VerifiedRgnR"
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria And
stLinkCriteria2

Each stLinkCriteria works individually, but I cant seem to get the syntax
right to combine them. I guess "And" isn't it. Probably obvious, but you
help
would be greatly appreciated!
Click to expand...
Click to expand...
 
To be honest, I can't think of a legitimate use for OpenQuery in a
production application, so I've never played around with it.

What is it you're trying to do?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


el zorro said:
THanks Douglas-- That worked great! On a related note, I want to do the
same
thing with the DoCmd.OpenQuery statement, and just run the query. THe
problem appears to be that DoCmd.OpenQuery does not allow for the
insertion
of query criteria, as the OpenReport copmmand did. At least, that's how it
looks to me. Any suggestions? (I'm trying to keep the criteria out of the
SQL
statement, since the month and year need to be taken from user input via
the
form.)

Douglas J. Steele said:
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria & " And " &
stLinkCriteria2

Although personally, I'd just use

Dim stDocName As String
Dim stLinkCriteria As String

stLinkCriteria = "[BillMonth]=" & Me![Frame2] & " And " & _
"[BillYear]=" & Me![List470]

stDocName = "VerifiedRgnR"
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


el zorro said:
I have an adp linked to SQL 2000 back end. I have a from from which the
user
selects a month by clicking on one of a group of radio buttons (Frame2)
and a
year by selecting from a list (List470.) After making the selection,
the
user
clicks a control buttton with the following code (in part):
Dim stDocName As String
Dim stLinkCriteria As String
Dim stLinkCriteria2 As String

stLinkCriteria = "[BillMonth]=" & Me![Frame2]
stLinkCriteria2 = "[BillYear]=" & Me![List470]

stDocName = "VerifiedRgnR"
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria And
stLinkCriteria2

Each stLinkCriteria works individually, but I cant seem to get the
syntax
right to combine them. I guess "And" isn't it. Probably obvious, but
you
help
would be greatly appreciated!
Click to expand...
Click to expand...
Click to expand...
 
Maybe I should mention that I am migrating an ACCESS mdb application to an
adp file connecrted to SQL 2000, so my point of reference is still the Access
mdb environment. In Access, instead of the user running formatted reports
based on a query, sometimes it is sufficient just to let the user run the
query and view the results in datasheet view. It seems to be a more direct
approach, and speedier, that opening a report that is based on the query. So
when the user clicks a command buttion on a form, with a month and year
selected, I would like him to be able to just run the query for the selected
the month and year (rather than open a report based on the query). The way I
have done this in Access is to include the reference to the form with the
month and year in the SQL statement, for ex: WHERE
(((Appointment.BillMonth)=[Forms]![SelEomF]![Frame2]) AND
((Appointment.BillYear)=[Forms]![SelEomF]![List470]) AND ((Appointment.Money)
Is Not Null And (Appointment.Money)<>0)).

However, for the migration to SQL 2000, I want to take the reference to the
form oput of the query, and let the VBA code that calls the query supply the
criteria. THis approach has worked with DoCmd.OpenReport (using the syntax
you suypplied, thanks), but not with DoCmd.OpenQuery.

Douglas J. Steele said:
To be honest, I can't think of a legitimate use for OpenQuery in a
production application, so I've never played around with it.

What is it you're trying to do?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


el zorro said:
THanks Douglas-- That worked great! On a related note, I want to do the
same
thing with the DoCmd.OpenQuery statement, and just run the query. THe
problem appears to be that DoCmd.OpenQuery does not allow for the
insertion
of query criteria, as the OpenReport copmmand did. At least, that's how it
looks to me. Any suggestions? (I'm trying to keep the criteria out of the
SQL
statement, since the month and year need to be taken from user input via
the
form.)

Douglas J. Steele said:
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria & " And " &
stLinkCriteria2

Although personally, I'd just use

Dim stDocName As String
Dim stLinkCriteria As String

stLinkCriteria = "[BillMonth]=" & Me![Frame2] & " And " & _
"[BillYear]=" & Me![List470]

stDocName = "VerifiedRgnR"
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I have an adp linked to SQL 2000 back end. I have a from from which the
user
selects a month by clicking on one of a group of radio buttons (Frame2)
and a
year by selecting from a list (List470.) After making the selection,
the
user
clicks a control buttton with the following code (in part):
Dim stDocName As String
Dim stLinkCriteria As String
Dim stLinkCriteria2 As String

stLinkCriteria = "[BillMonth]=" & Me![Frame2]
stLinkCriteria2 = "[BillYear]=" & Me![List470]

stDocName = "VerifiedRgnR"
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria And
stLinkCriteria2

Each stLinkCriteria works individually, but I cant seem to get the
syntax
right to combine them. I guess "And" isn't it. Probably obvious, but
you
help
would be greatly appreciated!
Click to expand...
Click to expand...
Click to expand...
 
I'd never present a query, though I might show the results as a datasheet in
a subform.

You can dynamically change the SQL for the query.

This works for MDBs: I believe it'll work for ADPs as well.

Dim qdfCurr As DAO.QueryDef
Dim strSQL As String

strSQL = "SELECT Field1, Field2, ...." & _
"FROM MyTable INNER JOIN MyOtherTable " & _
"ON MyTable.Field1 = MyOtherTable.Field2 " & _
"WHERE MyTable.Field1 = " & Forms!FormName!ControlName & " " & _
"AND MyTable.Field2 = '" & Forms!FormName!OtherControlName & "'"
Set qdfCurr = CurrentDb.QueryDefs("MyQuery")
qdfCurr.SQL = strSQL


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


el zorro said:
Maybe I should mention that I am migrating an ACCESS mdb application to an
adp file connecrted to SQL 2000, so my point of reference is still the
Access
mdb environment. In Access, instead of the user running formatted reports
based on a query, sometimes it is sufficient just to let the user run the
query and view the results in datasheet view. It seems to be a more direct
approach, and speedier, that opening a report that is based on the query.
So
when the user clicks a command buttion on a form, with a month and year
selected, I would like him to be able to just run the query for the
selected
the month and year (rather than open a report based on the query). The way
I
have done this in Access is to include the reference to the form with the
month and year in the SQL statement, for ex: WHERE
(((Appointment.BillMonth)=[Forms]![SelEomF]![Frame2]) AND
((Appointment.BillYear)=[Forms]![SelEomF]![List470]) AND
((Appointment.Money)
Is Not Null And (Appointment.Money)<>0)).

However, for the migration to SQL 2000, I want to take the reference to
the
form oput of the query, and let the VBA code that calls the query supply
the
criteria. THis approach has worked with DoCmd.OpenReport (using the syntax
you suypplied, thanks), but not with DoCmd.OpenQuery.

Douglas J. Steele said:
To be honest, I can't think of a legitimate use for OpenQuery in a
production application, so I've never played around with it.

What is it you're trying to do?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


el zorro said:
THanks Douglas-- That worked great! On a related note, I want to do the
same
thing with the DoCmd.OpenQuery statement, and just run the query. THe
problem appears to be that DoCmd.OpenQuery does not allow for the
insertion
of query criteria, as the OpenReport copmmand did. At least, that's how
it
looks to me. Any suggestions? (I'm trying to keep the criteria out of
the
SQL
statement, since the month and year need to be taken from user input
via
the
form.)

:

DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria & " And "
&
stLinkCriteria2

Although personally, I'd just use

Dim stDocName As String
Dim stLinkCriteria As String

stLinkCriteria = "[BillMonth]=" & Me![Frame2] & " And " & _
"[BillYear]=" & Me![List470]

stDocName = "VerifiedRgnR"
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I have an adp linked to SQL 2000 back end. I have a from from which
the
user
selects a month by clicking on one of a group of radio buttons
(Frame2)
and a
year by selecting from a list (List470.) After making the selection,
the
user
clicks a control buttton with the following code (in part):
Dim stDocName As String
Dim stLinkCriteria As String
Dim stLinkCriteria2 As String

stLinkCriteria = "[BillMonth]=" & Me![Frame2]
stLinkCriteria2 = "[BillYear]=" & Me![List470]

stDocName = "VerifiedRgnR"
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria And
stLinkCriteria2

Each stLinkCriteria works individually, but I cant seem to get the
syntax
right to combine them. I guess "And" isn't it. Probably obvious, but
you
help
would be greatly appreciated!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top