is it possible to set a persistant variable from a query?

[njack217]

I am trying to set a specific value to a variable to be retrieved on
another line in the query where a specific value changes then the
value from the variable will be returned, but so far am having no
luck. here is the code for the module that contains the variable and
the sql that I am using any help would be greatly appreciated.

Thanks,
Nate

Option Compare Database
Global GBV As String
Public Function init_globals()
GBV = Null
End Function
Public Function globalvar(ivalue) As String
Function init_globals()
GBV = ivalue
End Function

SELECT prm5051.level, prm5051.part, prm5051.desc, prm5051.qty,
prm5051.uom, prm5051.mli, IIf([level] Like "0",[globalvar]
([part]),Null) AS Expr1, [gbv] AS base
FROM prm5051;

 

[Amy Blankenship]

I am trying to set a specific value to a variable to be retrieved on
another line in the query where a specific value changes then the
value from the variable will be returned, but so far am having no
luck. here is the code for the module that contains the variable and
the sql that I am using any help would be greatly appreciated.

Thanks,
Nate

Option Compare Database
Global GBV As String
Public Function init_globals()
GBV = Null
End Function
Public Function globalvar(ivalue) As String
Function init_globals()
GBV = ivalue
End Function

You need a function to return the value

Public Function getGVar() AS String
getGVar = GBV
End Funcion

SELECT prm5051.level, prm5051.part, prm5051.desc, prm5051.qty,
prm5051.uom, prm5051.mli, IIf([level] Like "0",[globalvar]
([part]),Null) AS Expr1, [gbv] AS base
FROM prm5051;

Try something like

SELECT prm5051.level, prm5051.part, prm5051.desc, prm5051.qty,
prm5051.uom, prm5051.mli, globalvar("foo") AS base,
IIF(prm5051.level = "0", getGVar(), prm5051.part) AS Expr1
FROM prm5051;

Also note that IIF only takes 3 arguments, so I removed the fourth one you
had in there, as I wasn't sure what you were doing there.

HTH;

Amy

 

[Larry Daugherty]

Yes, declare the function or the variables as static. Find "static"
in Help for guidance. They will retain their values for the session.

HTH

 

[njack217]

Ok, here is what I did with the suggestions you have given me. I'm
still not able to get this working it's a problem with my iif I'm sure
but I cant think of another way to run the function and still keep all
the records displayed in the query. Let me explain a little better
what exactly I'l trying to do also. I am trying to take the value of
[part] and store it to the variable when [level] = 0 and then return
the value as [base] for that part and all [part] while [level] is <>0
and reset itself to the new part number when [level]=0.

SELECT prm5051.level, prm5051.part, prm5051.desc, prm5051.qty,
prm5051.uom, prm5051.mli, IIf([prm5051].[level] Like
"0",getgvar([part]),[null]) AS Expr1, IIf([gbv]=[part],[part],[gbv])
AS base
FROM prm5051;

Option Compare Database
Static GBV As String
Public Function getgvar(ivalue) As String
GBV = ivalue
End Function

Thanks agian,
Nate

 

[Larry Daugherty]

What I had in mind in my response to your earlier post was creating a
Function procedure in a standard module. By declaring the function
itself as static or declaring the variables within it as static you
can establish persistent variables whose values you can manipulate
over the course of your session. Each time it is called the variables
have the values that resulted from the last call. I'd put the
intelligence into the function. Just call it from the query while
passing in the current value of a column in your query.

HTH
--
-Larry-
--

Ok, here is what I did with the suggestions you have given me. I'm
still not able to get this working it's a problem with my iif I'm sure
but I cant think of another way to run the function and still keep all
the records displayed in the query. Let me explain a little better
what exactly I'l trying to do also. I am trying to take the value of
[part] and store it to the variable when [level] = 0 and then return
the value as [base] for that part and all [part] while [level] is

and reset itself to the new part number when [level]=0.

SELECT prm5051.level, prm5051.part, prm5051.desc, prm5051.qty,
prm5051.uom, prm5051.mli, IIf([prm5051].[level] Like
"0",getgvar([part]),[null]) AS Expr1, IIf([gbv]=[part],[part],[gbv])
AS base
FROM prm5051;

Option Compare Database
Static GBV As String
Public Function getgvar(ivalue) As String
GBV = ivalue
End Function

Thanks agian,
Nate

 

[njack217]

I am trying to set a specific value to a variable to be retrieved on
another line in the query where a specific value changes then the
value from the variable will be returned, but so far am having no
luck. here is the code for the module that contains the variable and
the sql that I am using any help would be greatly appreciated.

Thanks,
Nate

Option Compare Database
Global GBV As String
Public Function init_globals()
GBV = Null
End Function
Public Function globalvar(ivalue) As String
Function init_globals()
GBV = ivalue
End Function

SELECT prm5051.level, prm5051.part, prm5051.desc, prm5051.qty,
prm5051.uom, prm5051.mli, IIf([level] Like "0",[globalvar]
([part]),Null) AS Expr1, [gbv] AS base
FROM prm5051;

ya, I actually ended up needing to set it up so that it would review
the data several times, which is painfully slow (can anyone think of a
better way to deal with this?) because both the part and level change
and they may each be used more than once so I had to add a unique
field to the table so that I could keep the records processing in the
correct order otherwise the module returned the same value as [base]
for all records where [level] was >0 it's a hassle but I'm catching on
to some extent. still not working like i need it to but I think that I
am on the right track now.

Thanks for your help!

 

[Amy Blankenship]

Ok, here is what I did with the suggestions you have given me. I'm
still not able to get this working it's a problem with my iif I'm sure
but I cant think of another way to run the function and still keep all
the records displayed in the query. Let me explain a little better
what exactly I'l trying to do also. I am trying to take the value of
[part] and store it to the variable when [level] = 0 and then return
the value as [base] for that part and all [part] while [level] is <>0
and reset itself to the new part number when [level]=0.

SELECT prm5051.level, prm5051.part, prm5051.desc, prm5051.qty,
prm5051.uom, prm5051.mli, IIf([prm5051].[level] Like
"0",getgvar([part]),[null]) AS Expr1, IIf([gbv]=[part],[part],[gbv])
AS base
FROM prm5051;

Option Compare Database
Static GBV As String
Public Function getgvar(ivalue) As String
GBV = ivalue
End Function

First, I don't think that your function is doing what you think. Your
expression IIF(prm5051.level LIKE "0", getgvar([part]), [null]) suggests
that you are expecting it to return a value. Your function getgvar right
now is set up to set GBV, but not to return anything. You need to have a
set function and a get function. You can have a function that sets and gets
(so that the same value you pass it is still used in the expression. That
would look like this:

Public Function getgvar(ivalue) AS String
GBV = ivalue
getgvar = ivalue
End Function

Second, why are you using "Like" instead of "="? With no wildcards in the
pattern, Like is the same as =.

Third, you can't refer directly to gbv in SQL. You have to create a
function to retrieve it.

Public Function retrieveGBV() AS String
retrieveGBV = GBV
End Function

However, in the query above, there is no advantage in doing so, since you
know already that you've set GBV to [part] (assuming you get your logic all
pointed in the same direction and get the SQL working as it looks like
you've intended), and your IIF statement simply checks to see if they are
the same and then shows one or the other. Since they *are* the same, just
show [part].

HTH;

Amy

 

[James A. Fortune]

Yes, declare the function or the variables as static. Find "static"
in Help for guidance. They will retain their values for the session.

HTH

In:

http://groups.google.com/group/comp.databases.ms-access/msg/6d801e2c8e944015

I said:

One interesting result is that a UDF inside a query is called once prior
to it's first use within the query.

Beware of that gotcha when using static functions within queries.

James A. Fortune
(e-mail address removed)