Public Function

[Marcie]

I am trying to create a public function in a query
I have a table that lists all of the city and zips within district (cityzip)
EX: La Crosse 54601

In function I am passing city state and zip returning InDistrict,

Not all City states and zips are populated so it could just contain 54601
and I want to be able to know it is in district because it is in my cityzip
table.

If the value is not in my cityzip table I want to go on to evaluate state
and if it is in WI give value of in state but not district

I then want to go on to evaluate those left (that are not blank) for out of
state and give value of such.

I think I have all the code to evaluate all except getting the values from
the cityzip table into function so I can evaluate those first.

 

[Dale Fye]

Marcie,

Functions and queries are two different animals, and you cannot "create a
public function in a query". You can, however, use a public function in a
query.

Create a public function in the modules window. Call it fnInSomething. It
might look something like:

Public Function fnInSomething(City as string, _
State as String, _
Zip as String) as String

Dim strCriteria as string

strCriteria = "[Zip] = " & chr$(34) & Zip & chr$(34)
if NOT ISNULL("ID", "tbl_CityZip", strCriteria) Then
fnInSomething = "InDistrict"
exit function
end if

strCriteria = "[State] = " & chr$(34) & State & chr$(34)
if NOT ISNULL("ID", "tbl_State", strCriteria") THEN
fnInSomething = "InState"
exit function
endif

'you get the idea
'test for outside of state here

'If none of the other critieria is met
fnInSomething = "Outta this world"

End Function

Then, in your query, go to a new column and enter something like:

InSomething: fnInSomething([City], [State], [Zip])

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.