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

 

[MGFoster]

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!

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

It probably would be better if you stored the values and the text
description in a lookup table then JOIN that table to your query
table(s). Ex:

Table: NRSLevels
Fields: LevelID Integer
LevelDescription Text

You could do the same for the SS numbers - like this:

Table: SSNumbers
Fields: ss_start_nbr Integer
ss_end_nbr Integer
ss_description Text

Data would look like this:

ss_start_nbr ss_end_nbr ss_description
180 200 Beginning ESL
201 210 Intermediate ESL
.... etc. ...

Using NRSLevels table in a query:

SELECT L.NRSLevelDescription, <... other columns ...>
FROM table As T1 INNER JOIN NRSLevels As L
ON T1.NRSLevelID = L.NRSLevelID
WHERE ... etc.

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,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSqx2NIechKqOuFEgEQLFZQCg14f37kmoPU0xE8oG17sHptWnBwAAn1Fi
qmyn9XLzrLr/ioI18OQHHTiU
=i55X
-----END PGP SIGNATURE-----

 

[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

 

[MGFoster]

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.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

If you want the function to handle Long Integers you can change the
parameters' data types like this:

Public Function NRSLevelID(intID As Long, intSS As Long)
.... etc. ...

Since I didn't specify the function's return data type, it defaults to
Variant. That's just in case you don't get a match and have to return
NULL to the query. E.g.:

If ... Then
ElseIf ... Then
ElseIf ... Then
Else
' no match found
NRSLevelID = NULL
End If

If you want to use Text data types that hold Long Integer numeric data
you can call the function like this:

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


HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSq8ZVYechKqOuFEgEQJabgCbByLVPUXwu9Hq7zfxn2Q1XI93AE8An2Gr
Ng0wMgHlQjanqOBYsSkVGx1I
=zUU2
-----END PGP SIGNATURE-----

 

[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.