Solved

MS Access setTempVar

Posted on 2013-05-29
3
1,279 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
ID: 39206986
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
ID: 39207834
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
ID: 39208267
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

920 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