Can you use query fields as function variables?

[tdp]

Hi--Forgive my question--I have used Access quite a bit but VBA hardly at
all. I need to return a value based on two numerical values in a query. It
could be done with multiple IIf statements in a query, but it would then be
too complex (there are 12 different possible results, and I have read that
you can have a maximum of seven nested statements).

Can you use query field values in a VBA function? If so, how do you get the
"answer" back so you can use it in another query?

Here is an example of what I'm trying to do (I'm not writing the IIf syntax,
but writing it in English so maybe you can understand what types of
parameters I'm dealing with. If using VBA, the case fxn would probably be
easier):

If (qryNRS.LevelID is 2 and qryNRS.SS is between 181 and 200) then
NRSLevelID=2

There would be 11 more statements similar to this because there are 12 NRS
Levels.

The NRSLevelID would then need to be matched with its string value, for
example, NRSLevelID= 2 would be "Beginning ESL". This string value would need
to be in a report or query or form or whatever works, so I would need to be
able to get the function result back somehow to match it up.

If someone could explain to this VBA raw newbie how to go about this, it
would be much appreciated!

Thanks,
tdp

 

[KARL DEWEY]

Put the requirements in a translation Check table like this --
Check_Table
LevelID qryNRS_SS_Low qryNRS_SS_High NRSLevelID
2 181 200 2
1 0 180 9
.... through the 12

Then this query will work.
IIF((qryNRS.LevelID = Check_Table) AND (qryNRS.SS Between qryNRS_SS_Low
AND qryNRS_SS_High), NRSLevelID, "Unknown")

 

[KARL DEWEY]

Also if the items are subject to change then add a Yes/No field so as to
check if applicable in a forms before running the query and add criteria in
the query.

 

[tdp]

When I do this, I get an "Enter Parameter Value" box that says Check_Table.
(I changed ""Level ID" below to "ServiceTypeID" to match what I called the
field in qryNRSLevel).

tdp

 

[tdp]

You can create a VBA function in a standard module or a form/report
module. Ex (in a standard module):

Public Function NRSLevelID(intID As Integer, intSS As Integer)

' Returns a variant to the calling query

If intID = 2 And (intSS >= 181 And intSS <= 200) Then NRSLevelID=2
If intID = ... < etc. for all other parameters ...>

End Function

You call it in a query like this:

SELECT NRSLevelID(qryNRS.LevelID, qryNRS.SS) As NRSLevelID, ... etc. ...
FROM ...

You can use it in a WHERE clause also:

WHERE 18 = NRSLevelID(qryNRS.LevelID, qryNRS.SS)

HTH,

The value returned by the function would be an integer if the two variables
are integers, correct? I can get the function to run in a query fine, but
when I try to link it in another query to the table with the text values
corresponding to the NRSLevelID I get a "Type Mismatch Expression" error,
even though I went back to tblNRSLevel and made sure to change the field size
of the Number data type of NRSLevelID to Integer instead of Long Integer
(does the field size even matter?)
I am confused.

Thanks for your help,
tdp

 

[John Spencer]

If you don't include the Check_table in the query you can use DLookup to
retreive the value (or a sub-query).

DLookup("NRSLevelID","Check_Table",qryNRS.[SS] & " Between qryNrs_SS_Low and
qryNRS_SS_High AND LevelID =" & qryNRS.LevelID)

That should return null if there is no match and the value of NRSLevelID if
there is a match.

IF you want to include the translation table (Check_table in the query) then
you would need to use a non-equi join. That would require you to build the
SQL statement in the SQL window instead of using query design view.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[KARL DEWEY]

Post the SQL of your query by opening in design view, click on VIEW - SQL
View, highlight all, copy, and paste in a post.