Solved

MS Access setTempVar

Posted on 2013-05-29
3
1,257 Views
Last Modified: 2013-05-30
I have a macro that retrieves a value from a table and sets it into a tempvar.

the value retrieved is "New Account " & [LocationID] & "-" & [ClientID]

so gnewacctformat = "New Account " & [LocationID] & "-" & [ClientID]

I am trying to insert this value into a query so that the query is executed using the variable information - in other words [LocationID] and [Clientid] exist in the table tblWorking_Locations.  So I wrote the query as follows;


SELECT [TempVars]![gnewacctformat] AS account
FROM tblWorking_Locations;

What is returned is "New Account " & [LocationID] & "-" & [ClientID]

What I want to do is have the query execute is

SELECT "New Account " & [LocationID] & "-" & [ClientID] AS account
FROM tblWorking_Locations;

and return  New Account 123-CSC   where 123 and CSC are values from the table tblWorking_Locations.

Is it possible to do this without creating and executing the query from VB?
0
Comment
Question by:keschuster
3 Comments
 
LVL 15

Expert Comment

by:Simon Ball
Comment Utility
are you just checking to see if an account 123-CSC exists?  and if so return the string "New Account 123-CSC"

there's no where clauses listed in your query examples... what are you using gnewacctformat for?

From your question it sounds like you are getting a value from a table into a variable then checking to see if a string exists in another table, but not saying how the where clause works....
0
 

Author Comment

by:keschuster
Comment Utility
I'm trying to manipulate the actual sql statement by passing a variable in the contains a piece of the statement.  What is retrieve does not matter here
0
 
LVL 26

Accepted Solution

by:
Nick67 earned 500 total points
Comment Utility
Typically, the query editor does NOT like to fetch global variables.
One method of attack is VBA
You create a little wrapper function

Public Function gnewacctformatValue() as string
    gnewacctformatValue = gnewacctformat
end function

You can then use gnewacctformatValue() in queries

Although this syntax confuses me
[TempVars]![gnewacctformat] as I have never seen anything like it

Try the sample for a straight up query, though
working-locations.mdb
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now