Export Paramatised Query to Excel

  • Thread starter Thread starter Andibevan
  • Start date Start date
A

Andibevan

I have got a paramaterized query that I want to export to excel. The
Transfertoexcel method will work fine for me but I can't get work out how to
pass a variable to the query. MyVariable is my variable (funnily
enough!!) - how would I call this query (called Query_1) from VBA and pass a
parameter?

SELECT [tbl_Sign-off_Approval].[Sign-off Group],
[tbl_Sign-off_groups].[Document Group], [tbl_Sign-off_Approval].[File Name],
[tbl_Sign-off_Approval].Version
FROM [tbl_Sign-off_groups] INNER JOIN [tbl_Sign-off_Approval] ON
[tbl_Sign-off_groups].[Group No] = [tbl_Sign-off_Approval].[Sign-off Group]
WHERE ((([tbl_Sign-off_Approval].[Sign-off Group]) Like MyVariable));

Can you pass a variable to a parameterized query?

TIA

Andi
 
There are a couple ways you could handle this. In your form you could put a
text or combo box to enter the parameter and then have your query have
WHERE ((([tbl_Sign-off_Approval].[Sign-off Group]) Like
Forms![FormName]![TextBoxName])); Then when you run the transfertoexcel it
will export the query based on the value entered. I would prefer this
because you can better control what is entered. Or you can do something like

Private Sub SomeButton_Click ()
Dim InputValue as string
Dim strSQL as string
Dim dbCurr As DAO.Database
Dim qdfCurr As DAO.QueryDef

InputValue = Inputbox("Please enter query parameter.")

strSQL = "SELECT [tbl_Sign-off_Approval].[Sign-off Group],
[tbl_Sign-off_groups].[Document Group], [tbl_Sign-off_Approval].[File Name],
[tbl_Sign-off_Approval].Version " _
& "FROM [tbl_Sign-off_groups] INNER JOIN [tbl_Sign-off_Approval] ON
[tbl_Sign-off_groups].[Group No] = [tbl_Sign-off_Approval].[Sign-off Group]
" _
& "WHERE ((([tbl_Sign-off_Approval].[Sign-off Group]) Like ('" & InputValue
& "')))"

Set dbCurr = CurrentDb
Set qdfCurr = dbCurr.QueryDefs("test_sqlchng")
qdfCurr.SQL = strSQL

'Perform transfer to excel
end sub

Beware of typos and strange line wrapping
 
Thanks for the fantastic suggestions - I just have one final question - How
would I modify

WHERE ((([tbl_Sign-off_Approval].[Sign-off Group]) Like
Forms![FormName]![TextBoxName])); so that it worked with a variable (say
MyVariable)?

TIA

Andi




schasteen said:
There are a couple ways you could handle this. In your form you could put a
text or combo box to enter the parameter and then have your query have
WHERE ((([tbl_Sign-off_Approval].[Sign-off Group]) Like
Forms![FormName]![TextBoxName])); Then when you run the transfertoexcel it
will export the query based on the value entered. I would prefer this
because you can better control what is entered. Or you can do something like

Private Sub SomeButton_Click ()
Dim InputValue as string
Dim strSQL as string
Dim dbCurr As DAO.Database
Dim qdfCurr As DAO.QueryDef

InputValue = Inputbox("Please enter query parameter.")

strSQL = "SELECT [tbl_Sign-off_Approval].[Sign-off Group],
[tbl_Sign-off_groups].[Document Group], [tbl_Sign-off_Approval].[File Name],
[tbl_Sign-off_Approval].Version " _
& "FROM [tbl_Sign-off_groups] INNER JOIN [tbl_Sign-off_Approval] ON
[tbl_Sign-off_groups].[Group No] = [tbl_Sign-off_Approval].[Sign-off Group]
" _
& "WHERE ((([tbl_Sign-off_Approval].[Sign-off Group]) Like ('" & InputValue
& "')))"

Set dbCurr = CurrentDb
Set qdfCurr = dbCurr.QueryDefs("test_sqlchng")
qdfCurr.SQL = strSQL

'Perform transfer to excel
end sub

Beware of typos and strange line wrapping


Andibevan said:
I have got a paramaterized query that I want to export to excel. The
Transfertoexcel method will work fine for me but I can't get work out how to
pass a variable to the query. MyVariable is my variable (funnily
enough!!) - how would I call this query (called Query_1) from VBA and pass a
parameter?

SELECT [tbl_Sign-off_Approval].[Sign-off Group],
[tbl_Sign-off_groups].[Document Group], [tbl_Sign-off_Approval].[File Name],
[tbl_Sign-off_Approval].Version
FROM [tbl_Sign-off_groups] INNER JOIN [tbl_Sign-off_Approval] ON
[tbl_Sign-off_groups].[Group No] = [tbl_Sign-off_Approval].[Sign-off Group]
WHERE ((([tbl_Sign-off_Approval].[Sign-off Group]) Like MyVariable));

Can you pass a variable to a parameterized query?

TIA

Andi
Click to expand...
Click to expand...
 
I do not understand what you want to do. You would put the variable in the
textbox on your form. I guess I do not have a full understanding of what you
are looking for.

Andibevan said:
Thanks for the fantastic suggestions - I just have one final question - How
would I modify

WHERE ((([tbl_Sign-off_Approval].[Sign-off Group]) Like
Forms![FormName]![TextBoxName])); so that it worked with a variable (say
MyVariable)?

TIA

Andi




schasteen said:
There are a couple ways you could handle this. In your form you could put a
text or combo box to enter the parameter and then have your query have
WHERE ((([tbl_Sign-off_Approval].[Sign-off Group]) Like
Forms![FormName]![TextBoxName])); Then when you run the transfertoexcel it
will export the query based on the value entered. I would prefer this
because you can better control what is entered. Or you can do something like

Private Sub SomeButton_Click ()
Dim InputValue as string
Dim strSQL as string
Dim dbCurr As DAO.Database
Dim qdfCurr As DAO.QueryDef

InputValue = Inputbox("Please enter query parameter.")

strSQL = "SELECT [tbl_Sign-off_Approval].[Sign-off Group],
[tbl_Sign-off_groups].[Document Group], [tbl_Sign-off_Approval].[File Name],
[tbl_Sign-off_Approval].Version " _
& "FROM [tbl_Sign-off_groups] INNER JOIN [tbl_Sign-off_Approval] ON
[tbl_Sign-off_groups].[Group No] = [tbl_Sign-off_Approval].[Sign-off Group]
" _
& "WHERE ((([tbl_Sign-off_Approval].[Sign-off Group]) Like ('" & InputValue
& "')))"

Set dbCurr = CurrentDb
Set qdfCurr = dbCurr.QueryDefs("test_sqlchng")
qdfCurr.SQL = strSQL

'Perform transfer to excel
end sub

Beware of typos and strange line wrapping


Andibevan said:
I have got a paramaterized query that I want to export to excel. The
Transfertoexcel method will work fine for me but I can't get work out how to
pass a variable to the query. MyVariable is my variable (funnily
enough!!) - how would I call this query (called Query_1) from VBA and pass a
parameter?

SELECT [tbl_Sign-off_Approval].[Sign-off Group],
[tbl_Sign-off_groups].[Document Group], [tbl_Sign-off_Approval].[File Name],
[tbl_Sign-off_Approval].Version
FROM [tbl_Sign-off_groups] INNER JOIN [tbl_Sign-off_Approval] ON
[tbl_Sign-off_groups].[Group No] = [tbl_Sign-off_Approval].[Sign-off Group]
WHERE ((([tbl_Sign-off_Approval].[Sign-off Group]) Like MyVariable));

Can you pass a variable to a parameterized query?

TIA

Andi
Click to expand...
Click to expand...
Click to expand...
 
I wanted to be able to call the function from 2 different forms (using 2
different input boxes) and I was trying to make the code tidier by only
using one function.



schasteen said:
I do not understand what you want to do. You would put the variable in the
textbox on your form. I guess I do not have a full understanding of what you
are looking for.

Andibevan said:
Thanks for the fantastic suggestions - I just have one final question - How
would I modify

WHERE ((([tbl_Sign-off_Approval].[Sign-off Group]) Like
Forms![FormName]![TextBoxName])); so that it worked with a variable (say
MyVariable)?

TIA

Andi




schasteen said:
There are a couple ways you could handle this. In your form you could
Click to expand...
put
a
text or combo box to enter the parameter and then have your query have
WHERE ((([tbl_Sign-off_Approval].[Sign-off Group]) Like
Forms![FormName]![TextBoxName])); Then when you run the
Click to expand...
transfertoexcel
it
will export the query based on the value entered. I would prefer this
because you can better control what is entered. Or you can do
Click to expand...
something
like
Private Sub SomeButton_Click ()
Dim InputValue as string
Dim strSQL as string
Dim dbCurr As DAO.Database
Dim qdfCurr As DAO.QueryDef

InputValue = Inputbox("Please enter query parameter.")

strSQL = "SELECT [tbl_Sign-off_Approval].[Sign-off Group],
[tbl_Sign-off_groups].[Document Group], [tbl_Sign-off_Approval].[File Name],
[tbl_Sign-off_Approval].Version " _
& "FROM [tbl_Sign-off_groups] INNER JOIN [tbl_Sign-off_Approval] ON
[tbl_Sign-off_groups].[Group No] = [tbl_Sign-off_Approval].[Sign-off Group]
" _
& "WHERE ((([tbl_Sign-off_Approval].[Sign-off Group]) Like ('" & InputValue
& "')))"

Set dbCurr = CurrentDb
Set qdfCurr = dbCurr.QueryDefs("test_sqlchng")
qdfCurr.SQL = strSQL

'Perform transfer to excel
end sub

Beware of typos and strange line wrapping


:

I have got a paramaterized query that I want to export to excel. The
Transfertoexcel method will work fine for me but I can't get work
Click to expand...
out
how to
pass a variable to the query. MyVariable is my variable (funnily
enough!!) - how would I call this query (called Query_1) from VBA
Click to expand...
and
pass a
parameter?

SELECT [tbl_Sign-off_Approval].[Sign-off Group],
[tbl_Sign-off_groups].[Document Group],
Click to expand...
[tbl_Sign-off_Approval].[File
Name],
[tbl_Sign-off_Approval].Version
FROM [tbl_Sign-off_groups] INNER JOIN [tbl_Sign-off_Approval] ON
[tbl_Sign-off_groups].[Group No] = [tbl_Sign-off_Approval].[Sign-off Group]
WHERE ((([tbl_Sign-off_Approval].[Sign-off Group]) Like MyVariable));

Can you pass a variable to a parameterized query?

TIA

Andi
Click to expand...
Click to expand...
Click to expand...
 
Then I would probably combine the two suggestions I gave, and in the after
update event of each text box do:

Dim strSQL as string
Dim dbCurr As DAO.Database
Dim qdfCurr As DAO.QueryDef

If not isnull(Me![textboxname] then
strSQL = "SELECT [tbl_Sign-off_Approval].[Sign-off
Group],[tbl_Sign-off_groups].[Document Group],
[tbl_Sign-off_Approval].[FileName], [tbl_Sign-off_Approval].Version " _
& "FROM [tbl_Sign-off_groups] INNER JOIN [tbl_Sign-off_Approval] ON
[tbl_Sign-off_groups].[Group No] = [tbl_Sign-off_Approval].[Sign-off Group]" _
& "WHERE ((([tbl_Sign-off_Approval].[Sign-off Group]) Like ('" &
Me![textboxname] & "')))"

Set dbCurr = CurrentDb
Set qdfCurr = dbCurr.QueryDefs("test_sqlchng")
qdfCurr.SQL = strSQL

end if

You can include the transfer to excel either with the above or with a button.

Andibevan said:
I wanted to be able to call the function from 2 different forms (using 2
different input boxes) and I was trying to make the code tidier by only
using one function.



schasteen said:
I do not understand what you want to do. You would put the variable in the
textbox on your form. I guess I do not have a full understanding of what you
are looking for.

Andibevan said:
Thanks for the fantastic suggestions - I just have one final question - How
would I modify

WHERE ((([tbl_Sign-off_Approval].[Sign-off Group]) Like
Forms![FormName]![TextBoxName])); so that it worked with a variable (say
MyVariable)?

TIA

Andi




There are a couple ways you could handle this. In your form you could put
a
text or combo box to enter the parameter and then have your query have
WHERE ((([tbl_Sign-off_Approval].[Sign-off Group]) Like
Forms![FormName]![TextBoxName])); Then when you run the transfertoexcel
it
will export the query based on the value entered. I would prefer this
because you can better control what is entered. Or you can do something
like

Private Sub SomeButton_Click ()
Dim InputValue as string
Dim strSQL as string
Dim dbCurr As DAO.Database
Dim qdfCurr As DAO.QueryDef

InputValue = Inputbox("Please enter query parameter.")

strSQL = "SELECT [tbl_Sign-off_Approval].[Sign-off Group],
[tbl_Sign-off_groups].[Document Group], [tbl_Sign-off_Approval].[File
Name],
[tbl_Sign-off_Approval].Version " _
& "FROM [tbl_Sign-off_groups] INNER JOIN [tbl_Sign-off_Approval] ON
[tbl_Sign-off_groups].[Group No] = [tbl_Sign-off_Approval].[Sign-off
Group]
" _
& "WHERE ((([tbl_Sign-off_Approval].[Sign-off Group]) Like ('" &
InputValue
& "')))"

Set dbCurr = CurrentDb
Set qdfCurr = dbCurr.QueryDefs("test_sqlchng")
qdfCurr.SQL = strSQL

'Perform transfer to excel
end sub

Beware of typos and strange line wrapping


:

I have got a paramaterized query that I want to export to excel. The
Transfertoexcel method will work fine for me but I can't get work out
how to
pass a variable to the query. MyVariable is my variable (funnily
enough!!) - how would I call this query (called Query_1) from VBA and
pass a
parameter?

SELECT [tbl_Sign-off_Approval].[Sign-off Group],
[tbl_Sign-off_groups].[Document Group], [tbl_Sign-off_Approval].[File
Name],
[tbl_Sign-off_Approval].Version
FROM [tbl_Sign-off_groups] INNER JOIN [tbl_Sign-off_Approval] ON
[tbl_Sign-off_groups].[Group No] = [tbl_Sign-off_Approval].[Sign-off
Group]
WHERE ((([tbl_Sign-off_Approval].[Sign-off Group]) Like MyVariable));

Can you pass a variable to a parameterized query?

TIA

Andi
Click to expand...
Click to expand...
Click to expand...
 
thanks schasteen - your suggestion have been very helpful and got me a lot
further.

Ta

Andi

schasteen said:
Then I would probably combine the two suggestions I gave, and in the after
update event of each text box do:

Dim strSQL as string
Dim dbCurr As DAO.Database
Dim qdfCurr As DAO.QueryDef

If not isnull(Me![textboxname] then
strSQL = "SELECT [tbl_Sign-off_Approval].[Sign-off
Group],[tbl_Sign-off_groups].[Document Group],
[tbl_Sign-off_Approval].[FileName], [tbl_Sign-off_Approval].Version " _
& "FROM [tbl_Sign-off_groups] INNER JOIN [tbl_Sign-off_Approval] ON
[tbl_Sign-off_groups].[Group No] = [tbl_Sign-off_Approval].[Sign-off Group]" _
& "WHERE ((([tbl_Sign-off_Approval].[Sign-off Group]) Like ('" &
Me![textboxname] & "')))"

Set dbCurr = CurrentDb
Set qdfCurr = dbCurr.QueryDefs("test_sqlchng")
qdfCurr.SQL = strSQL

end if

You can include the transfer to excel either with the above or with a button.

Andibevan said:
I wanted to be able to call the function from 2 different forms (using 2
different input boxes) and I was trying to make the code tidier by only
using one function.



schasteen said:
I do not understand what you want to do. You would put the variable
Click to expand...
in
the
textbox on your form. I guess I do not have a full understanding of
Click to expand...
what
you
are looking for.

:

Thanks for the fantastic suggestions - I just have one final
Click to expand...
question -
How
would I modify

WHERE ((([tbl_Sign-off_Approval].[Sign-off Group]) Like
Forms![FormName]![TextBoxName])); so that it worked with a variable (say
MyVariable)?

TIA

Andi




There are a couple ways you could handle this. In your form you
Click to expand...
could
put
a
text or combo box to enter the parameter and then have your query have
WHERE ((([tbl_Sign-off_Approval].[Sign-off Group]) Like
Forms![FormName]![TextBoxName])); Then when you run the transfertoexcel
it
will export the query based on the value entered. I would prefer this
because you can better control what is entered. Or you can do something
like

Private Sub SomeButton_Click ()
Dim InputValue as string
Dim strSQL as string
Dim dbCurr As DAO.Database
Dim qdfCurr As DAO.QueryDef

InputValue = Inputbox("Please enter query parameter.")

strSQL = "SELECT [tbl_Sign-off_Approval].[Sign-off Group],
[tbl_Sign-off_groups].[Document Group], [tbl_Sign-off_Approval].[File
Name],
[tbl_Sign-off_Approval].Version " _
& "FROM [tbl_Sign-off_groups] INNER JOIN [tbl_Sign-off_Approval] ON
[tbl_Sign-off_groups].[Group No] = [tbl_Sign-off_Approval].[Sign-off
Group]
" _
& "WHERE ((([tbl_Sign-off_Approval].[Sign-off Group]) Like ('" &
InputValue
& "')))"

Set dbCurr = CurrentDb
Set qdfCurr = dbCurr.QueryDefs("test_sqlchng")
qdfCurr.SQL = strSQL

'Perform transfer to excel
end sub

Beware of typos and strange line wrapping


:

I have got a paramaterized query that I want to export to excel. The
Transfertoexcel method will work fine for me but I can't get
Click to expand...
work
out
how to
pass a variable to the query. MyVariable is my variable (funnily
enough!!) - how would I call this query (called Query_1) from
Click to expand...
VBA
and
pass a
parameter?

SELECT [tbl_Sign-off_Approval].[Sign-off Group],
[tbl_Sign-off_groups].[Document Group], [tbl_Sign-off_Approval].[File
Name],
[tbl_Sign-off_Approval].Version
FROM [tbl_Sign-off_groups] INNER JOIN [tbl_Sign-off_Approval] ON
[tbl_Sign-off_groups].[Group No] = [tbl_Sign-off_Approval].[Sign-off
Group]
WHERE ((([tbl_Sign-off_Approval].[Sign-off Group]) Like MyVariable));

Can you pass a variable to a parameterized query?

TIA

Andi
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

"Enter parameter value" error when exporting to Excel 0
Export query to Excel fails 6
Export To Excel 3
Operation must use an Updatable Query 2
Exporting Query from Access 2007 to Excel 2007 4
Passing a parameter to a Query 2
Export query into Excel Template 1
Use Parameter Form to export a query 3

Back
Top