How do you Declare a Variable as a Query

[bconner]

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]

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]

What error are you getting? Maybe try putting the query name in brackets [query]

 

[bconner]

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]

Here are two different ways you could do this:

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

Or

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

Hope this helps,
Evan

 

[bconner]

Thank you so much that worked.......