Help with Query Code

[Ayo]

Private Sub cmdVendor_Reviewer_Report_Click()
Dim dbs As Database: Dim qdf As QueryDef: Dim strSQL As String

Set dbs = db1
strSQL = "SELECT * FROM [Invoice Tracker] WHERE ((([Invoice
Tracker].Vendor)=" & _
"Forms![Select Vendor and Reviewer]![cmbVendorName]" & ")AND ((" & _
"([Invoice Tracker].Market Reviewer)=" & "Forms![Select Vendor and
Reviewer]![cmbMarketReviewer]" & "));"
Set qdf = dbs.QueryDef("SecondQuarter", strSQL)
executeQueryDef qdf
End Sub

I need help with the above code. I am try to build a query using the above
code and build a report based on the query but, I am getting a Type mismatch
error message on " Set dbs = db1". And I don't know what is wrong.

Can any one please help?
Thanks
Ayo

 

[Maurice]

try replacing the set dbs=db1 with

set dbs=currentdb()

hth

 

[Douglas J. Steele]

What is db1 supposed to be? If it's supposed to be a file, you need

Set dbs = OpenDatabase("C:\Folder\db1.mdb")

What does executeQueryDef do? In general. there's little point in executing
Select queries: that's for Action queries (Update, Delete, Insert Into)

 

[Ayo]

So, how do I use this code to run the query and attach it to, and use it to
build, a report?

Thanks

What is db1 supposed to be? If it's supposed to be a file, you need

Set dbs = OpenDatabase("C:\Folder\db1.mdb")

What does executeQueryDef do? In general. there's little point in executing
Select queries: that's for Action queries (Update, Delete, Insert Into)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Private Sub cmdVendor_Reviewer_Report_Click()
Dim dbs As Database: Dim qdf As QueryDef: Dim strSQL As String

Set dbs = db1
strSQL = "SELECT * FROM [Invoice Tracker] WHERE ((([Invoice
Tracker].Vendor)=" & _
"Forms![Select Vendor and Reviewer]![cmbVendorName]" & ")AND ((" &
_
"([Invoice Tracker].Market Reviewer)=" & "Forms![Select Vendor and
Reviewer]![cmbMarketReviewer]" & "));"
Set qdf = dbs.QueryDef("SecondQuarter", strSQL)
executeQueryDef qdf
End Sub

I need help with the above code. I am try to build a query using the above
code and build a report based on the query but, I am getting a Type
mismatch
error message on " Set dbs = db1". And I don't know what is wrong.

Can any one please help?
Thanks
Ayo

 

[Douglas J. Steele]

Let's step back, since you still haven't explained by db1 is.

Are you trying to build a query that points to a table in some other
database?

If so, you can use

strSQL = "SELECT * FROM [;database=C:\Folder\db1.mdb].[Invoice Tracker] "
& _
"WHERE [Invoice Tracker].Vendor=" & _
"Forms![Select Vendor and Reviewer]![cmbVendorName] " & _
" AND [Invoice Tracker].Market Reviewer)=" & _
"Forms![Select Vendor and Reviewer]![cmbMarketReviewer]"

Then since that's now stored in query SecondQuarter, you'd simply set the
RecordSource for the report to SecondQuarter.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

So, how do I use this code to run the query and attach it to, and use it
to
build, a report?

Thanks

What is db1 supposed to be? If it's supposed to be a file, you need

Set dbs = OpenDatabase("C:\Folder\db1.mdb")

What does executeQueryDef do? In general. there's little point in
executing
Select queries: that's for Action queries (Update, Delete, Insert Into)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Private Sub cmdVendor_Reviewer_Report_Click()
Dim dbs As Database: Dim qdf As QueryDef: Dim strSQL As String

Set dbs = db1
strSQL = "SELECT * FROM [Invoice Tracker] WHERE ((([Invoice
Tracker].Vendor)=" & _
"Forms![Select Vendor and Reviewer]![cmbVendorName]" & ")AND
((" &
_
"([Invoice Tracker].Market Reviewer)=" & "Forms![Select Vendor
and
Reviewer]![cmbMarketReviewer]" & "));"
Set qdf = dbs.QueryDef("SecondQuarter", strSQL)
executeQueryDef qdf
End Sub

I need help with the above code. I am try to build a query using the
above
code and build a report based on the query but, I am getting a Type
mismatch
error message on " Set dbs = db1". And I don't know what is wrong.

Can any one please help?
Thanks
Ayo

 

[Ayo]

I figured out that I had to use CurrenDb. Now I am getting an error on
".CreateQueryDef" saying that there is"Wrong number of arguments or invalid
property assignement."
I know if I am able to create the query I can figure out the report paqrt of
things. IRight now I just can't get the code section below to work. What am I
doing wrong?

Thanks

Private Sub cmdVendor_Reviewer_Report_Click()
Dim dbs As Database: Dim qdf As QueryDef: Dim strSQL As String

Set dbs = CurrentDb
strSQL = "SELECT * FROM [Invoice Tracker] WHERE ((([Invoice
Tracker].Vendor)=" & _
"Forms![Select Vendor and Reviewer]![cmbVendorName]" & ")AND ((" & _
"([Invoice Tracker].Market Reviewer)=" & "Forms![Select Vendor and
Reviewer]![cmbMarketReviewer]" & "));"
Set qdf = dbs.CreateQueryDef("SecondQuarter", strSQL)
DoCmd.OpenQuery "SecondQuarter", , acReadOnly

End Sub

Let's step back, since you still haven't explained by db1 is.

Are you trying to build a query that points to a table in some other
database?

If so, you can use

strSQL = "SELECT * FROM [;database=C:\Folder\db1.mdb].[Invoice Tracker] "
& _
"WHERE [Invoice Tracker].Vendor=" & _
"Forms![Select Vendor and Reviewer]![cmbVendorName] " & _
" AND [Invoice Tracker].Market Reviewer)=" & _
"Forms![Select Vendor and Reviewer]![cmbMarketReviewer]"

Then since that's now stored in query SecondQuarter, you'd simply set the
RecordSource for the report to SecondQuarter.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

So, how do I use this code to run the query and attach it to, and use it
to
build, a report?

Thanks

What is db1 supposed to be? If it's supposed to be a file, you need

Set dbs = OpenDatabase("C:\Folder\db1.mdb")

What does executeQueryDef do? In general. there's little point in
executing
Select queries: that's for Action queries (Update, Delete, Insert Into)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Private Sub cmdVendor_Reviewer_Report_Click()
Dim dbs As Database: Dim qdf As QueryDef: Dim strSQL As String

Set dbs = db1
strSQL = "SELECT * FROM [Invoice Tracker] WHERE ((([Invoice
Tracker].Vendor)=" & _
"Forms![Select Vendor and Reviewer]![cmbVendorName]" & ")AND
((" &
_
"([Invoice Tracker].Market Reviewer)=" & "Forms![Select Vendor
and
Reviewer]![cmbMarketReviewer]" & "));"
Set qdf = dbs.QueryDef("SecondQuarter", strSQL)
executeQueryDef qdf
End Sub

I need help with the above code. I am try to build a query using the
above
code and build a report based on the query but, I am getting a Type
mismatch
error message on " Set dbs = db1". And I don't know what is wrong.

Can any one please help?
Thanks
Ayo

 

[Douglas J. Steele]

Your SQL would appear to be incorrect.

I count six opening parentheses, and only five closing ones.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I figured out that I had to use CurrenDb. Now I am getting an error on
".CreateQueryDef" saying that there is"Wrong number of arguments or
invalid
property assignement."
I know if I am able to create the query I can figure out the report paqrt
of
things. IRight now I just can't get the code section below to work. What
am I
doing wrong?

Thanks

Private Sub cmdVendor_Reviewer_Report_Click()
Dim dbs As Database: Dim qdf As QueryDef: Dim strSQL As String

Set dbs = CurrentDb
strSQL = "SELECT * FROM [Invoice Tracker] WHERE ((([Invoice
Tracker].Vendor)=" & _
"Forms![Select Vendor and Reviewer]![cmbVendorName]" & ")AND ((" &
_
"([Invoice Tracker].Market Reviewer)=" & "Forms![Select Vendor and
Reviewer]![cmbMarketReviewer]" & "));"
Set qdf = dbs.CreateQueryDef("SecondQuarter", strSQL)
DoCmd.OpenQuery "SecondQuarter", , acReadOnly

End Sub

Let's step back, since you still haven't explained by db1 is.

Are you trying to build a query that points to a table in some other
database?

If so, you can use

strSQL = "SELECT * FROM [;database=C:\Folder\db1.mdb].[Invoice Tracker]
"
& _
"WHERE [Invoice Tracker].Vendor=" & _
"Forms![Select Vendor and Reviewer]![cmbVendorName] " & _
" AND [Invoice Tracker].Market Reviewer)=" & _
"Forms![Select Vendor and Reviewer]![cmbMarketReviewer]"

Then since that's now stored in query SecondQuarter, you'd simply set the
RecordSource for the report to SecondQuarter.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

So, how do I use this code to run the query and attach it to, and use
it
to
build, a report?

Thanks

:

What is db1 supposed to be? If it's supposed to be a file, you need

Set dbs = OpenDatabase("C:\Folder\db1.mdb")

What does executeQueryDef do? In general. there's little point in
executing
Select queries: that's for Action queries (Update, Delete, Insert
Into)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Private Sub cmdVendor_Reviewer_Report_Click()
Dim dbs As Database: Dim qdf As QueryDef: Dim strSQL As String

Set dbs = db1
strSQL = "SELECT * FROM [Invoice Tracker] WHERE ((([Invoice
Tracker].Vendor)=" & _
"Forms![Select Vendor and Reviewer]![cmbVendorName]" & ")AND
((" &
_
"([Invoice Tracker].Market Reviewer)=" & "Forms![Select
Vendor
and
Reviewer]![cmbMarketReviewer]" & "));"
Set qdf = dbs.QueryDef("SecondQuarter", strSQL)
executeQueryDef qdf
End Sub

I need help with the above code. I am try to build a query using the
above
code and build a report based on the query but, I am getting a Type
mismatch
error message on " Set dbs = db1". And I don't know what is wrong.

Can any one please help?
Thanks
Ayo