Hi,
I am a novice VBA user.
My access version is 2007 on Windows XP PC.
I need to create a query that will take pre-existing text values of the B_status field (such as "Received_Emailed", "Processed" etc.) and return the numeric value depending on the value of the B_status value.
So I would get 2 in the new field if the value of B_status is "Received_Emailed" and so on.
I opened Visual Basic Editor and created a function under Module 1 as follows:
Public Function PG(B_status As String)
Select Case B_status
Case "Received_Emailed"
PG= 2
Case "Processed"
PG = 32
Case "Checked_in"
PG = 90
Case "Item_returned"
PG=100
Case Else
PG =0
End Select
End Function
I saved the function and the module both as PG.
Then I went to Query mode and added B_status field and typed in the following to call the function I created.
Batch_Progress: PG([B_status])
But I keep getting the error message "Undefined function 'PG' in expression."
I compiled in Visual Editor to make sure there is no syntax error.
Still the query does not work...
Is there something wrong with my VBA function?
Or do I need to troubleshoot this error message?
I looked up Microsoft help page. It says something about missing reference but I cannot figure it out how to fix this problem...
Any help would be greatly appreciated!
Thanks in advance!!!