Calling a function from a query 1

[SHAWTY721]

I created a function that holds new dept codes for ones that need to be updated once entered into the system. But when I go to call the function it isn't working. I have included some screen shots that show what I am talking about and explain what I am trying to do a little better.

Thanks in advance!

 

[SgtJarrow]

Shaw,

As I stated in your other post in another forum, you merely need to put the following in the field row of the column of the query where you want the result displayed...

ConvertedValue: myFunctionName([Expr1])

Replace myFunctionName with the name of your function. Replace Expr1 with the name of the field in the table that holds the "old value".

You should end up with a column with the header of ConvertedValue and the "updated values" as the rows.

Your function should have been created in a global module (modules tab) and should be of a public scope.

Public Function myFunctionName(strOldValue As string) As String
Dim strNewValue As String
Select Case strOldValue
Case "45H9": strNewValue = "4517"
' ....
End Select
myFunctionName = strNewValue
End Function

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[SHAWTY721]

Thanks for the help, on getting my function to work how I wanted it to work. To get that value to appear on a form, do I just need to set the control source of the property box to that query.

 

[SgtJarrow]

If the query only returns one record, then yes. If the query returns results as you originally described, you would need to work that a bit more. You could put the function as the controlsource for the field on the form, or you can include that on the query that the report is based on....Give a bit more info and maybe we can help you identify the best fix.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer