Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 05-01-2009, 02:57 PM   #1
bconner
Newly Registered User
 
Join Date: Dec 2008
Posts: 166
Thanks: 0
Thanked 0 Times in 0 Posts
bconner is on a distinguished road
How do you Declare a Variable as a Query

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

bconner is offline   Reply With Quote
Old 05-01-2009, 05:45 PM   #2
evanscamman
Newly Registered User
 
Join Date: Feb 2007
Posts: 274
Thanks: 0
Thanked 1 Time in 1 Post
evanscamman is on a distinguished road
Re: How do you Declare a Variable as a Query

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
evanscamman is offline   Reply With Quote
Old 05-01-2009, 05:48 PM   #3
KeithG
AWF VIP
 
KeithG's Avatar
 
Join Date: Mar 2006
Location: Illinois
Posts: 2,592
Thanks: 0
Thanked 4 Times in 4 Posts
KeithG will become famous soon enough KeithG will become famous soon enough
Re: How do you Declare a Variable as a Query

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

__________________
If my post has helped you solve your problems please add to my reputation. Click the scale in the top right side of this post.
KeithG is offline   Reply With Quote
Old 05-02-2009, 04:02 AM   #4
bconner
Newly Registered User
 
Join Date: Dec 2008
Posts: 166
Thanks: 0
Thanked 0 Times in 0 Posts
bconner is on a distinguished road
Re: How do you Declare a Variable as a Query

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.
bconner is offline   Reply With Quote
Old 05-02-2009, 01:29 PM   #5
evanscamman
Newly Registered User
 
Join Date: Feb 2007
Posts: 274
Thanks: 0
Thanked 1 Time in 1 Post
evanscamman is on a distinguished road
Re: How do you Declare a Variable as a Query

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
evanscamman is offline   Reply With Quote
Old 05-02-2009, 06:03 PM   #6
bconner
Newly Registered User
 
Join Date: Dec 2008
Posts: 166
Thanks: 0
Thanked 0 Times in 0 Posts
bconner is on a distinguished road
Re: How do you Declare a Variable as a Query

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

bconner is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Pass variable in Query Criteria fenhow Queries 15 12-23-2012 01:10 AM
How to call a Public variable in a query? latex88 Modules & VBA 18 08-17-2010 01:26 PM
Passing global variable to query Purdue2479 Modules & VBA 9 03-25-2008 01:10 PM
Experts- Need Knowledge & Advice modest Modules & VBA 11 02-16-2005 01:20 PM
Query public variable 2ShotLatte Queries 2 07-16-2003 01:52 PM




All times are GMT -8. The time now is 09:42 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World