How do you Declare a Variable as a Query (1 Viewer)

bconner

Registered User.
Local time
Today, 10:58
Joined
Dec 22, 2008
Messages
183
I have a couple queries that for some reason someone started the names out with an *, well the TransferSpreadsheet function doesn't like the * in the Query name. How do I Declare a variable as a Query?


Thanks
 

evanscamman

Registered User.
Local time
Today, 08:58
Joined
Feb 25, 2007
Messages
274
I don't really understand your question, but it is common to use an asterisk in queries as a wildcard.

Here are two common uses:

1. "Select tblItem.*" will add all fields from tblItem to the query.
2. "Where tblItem.ItemName Like "*" & .... " - the asterisk is used as a wildcard in the Criteria.

Evan
 

KeithG

AWF VIP
Local time
Today, 08:58
Joined
Mar 23, 2006
Messages
2,592
What error are you getting? Maybe try putting the query name in brackets [query]
 

bconner

Registered User.
Local time
Today, 10:58
Joined
Dec 22, 2008
Messages
183
I have a query named "*90JJ AR by Rej" when I run the DoCmd.TransferSpreadsheet function it will transfer the query to Excel however when I open the Excel workbook it opens with an Error saying something like some information is unreadable, so it opens as a recovered workbook. Well it turns out that the DoCmd.TransferSpreadsheet doesn't like it when the name of a query contains an asterisk because I renamed the query without and it worked. Basically I want to know what the sytnax is for declaring a variable as a query example below:

Dim NewQueryName as String ?? Is this correct
NewQueryName = "*90JJ AR by Rej" setting the variable = to the Query name is this correct??? Basically instead of changing the name of all of the queries I was going to change the name by assigning them to variables and passing the variable to the DoCmd.TrasnferSpreadsheet function.
 

evanscamman

Registered User.
Local time
Today, 08:58
Joined
Feb 25, 2007
Messages
274
Here are two different ways you could do this:

Code:
Dim strQueryName as String, qryDef as QueryDef
 
strQueryName = "MyQueryName"
Set qryDef as CurrentDb.QueryDefs(strQueryName)

Or

Code:
Dim qryDef as QueryDef
For Each qryDef in CurrentDb.QueryDefs
    Docmd.TransferQuery qryDef   .... or however this command goes    
Next

Hope this helps,
Evan
 

bconner

Registered User.
Local time
Today, 10:58
Joined
Dec 22, 2008
Messages
183
Thank you so much that worked.......
 

Users who are viewing this thread

Top Bottom