Storing the result of a field expression to a variable

[accessaspire219]

Hi,
I have a query that calculates a normalization factor based on the number of days in the current month. I need to use this factor in another query however these queries do not contain common fields that I can use to join them. The only way I guess I can do it is if I save the normalization factor to a variable and then use that variable in the other query, but I have no idea how to do this. Any help would be appreciated. I am also open to ideas pointing to other ways (without using a variable) to get to the same result.
Thanks!

 

[ajetrumpet]

is the factor the same for each record or different for each record? Is there anyway you can identify the records in the query you want to build? if so, use that identifier as a LOOKUP value to get from the first query, and get the factor from the same record in the old query that way.

 

[accessaspire219]

The first query calculates the normalization factor for the current period so it will always be unique.

Here is what I have so far, What I intend to do with the following code is to store the value in the column NORM_FACTOR in the query ForecastNormalization to the variable strNormFactor.

Public Sub NormalizeForecast()
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim strNormFactor As Variant
strNormFactor = DLookup("NORM_FACTOR", "ForecastNormalization")

End Sub

Is this syntax correct? Next I will need to use the variable strNormFactor in the expression of the second query. How do I do that?
Thanks.

 

[ajetrumpet]

The first query calculates the normalization factor for the current period so it will always be unique.

Here is what I have so far, What I intend to do with the following code is to store the value in the column NORM_FACTOR in the query ForecastNormalization to the variable strNormFactor.

Public Sub NormalizeForecast()
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim strNormFactor As Variant
strNormFactor = DLookup("NORM_FACTOR", "ForecastNormalization")

End Sub

Is this syntax correct? Next I will need to use the variable strNormFactor in the expression of the second query. How do I do that?
Thanks.

I would use a procedure call in the query SQL to get this done. Your procedure would be:

Function NormFactor(MyField as string, MyQuery as string) as variant

   NormFactor = DLOOKUP(Myfield, MyQuery)

Then, to get that value back into the query SQL, call the procedure with the objects you need to analyze:

SELECT NormFactor("NORM_FACTOR", "ForecastNormalization") as MyNewField

 

[accessaspire219]

ok, here's what I did for the first part,

Option Compare Database
Function NormFactor("NORM_FACTOR" As String "ForecastNormalization" As String)As Variant
NormFactor = DLookup("NORM_FACTOR", "ForecastNormalization")

but it highlights

Function NormFactor("NORM_FACTOR" As String "ForecastNormalization" As String)As Variant

the error message indentifier expected.

Also, the second part, of getting this value back in sql should be within the SQL code of the second query?

Thanks.

 

[ajetrumpet]

it's highlighting that because you are using quotes to signify ACTUAL strings that you are using as arguments. Arguments are arguments, they are NOT values. Think of them as storage devices. Therefore, you need to use words like MyField and MyQuery. Those are LITERAL, I didn't put those in there to tell you what to actually use. The actual VALUES are used in the query SQL, inside the function's () markings. For example, in my previous post, the MyField argument in the function would = "NORM_FACTOR". Do you understand how the values that you type in are being READ as arguments of the function?

 

[accessaspire219]

Acutally, no don't understand VBA at all and that's why I looked here for help
Anyways, I corrected it and it works fine! Thanks! Btw is there a way I can turn off the MyNewField from getting displayed in the second query result?