"Undefined function '--' in expression" Error message

[blackdogharry]

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_Email ed", "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_Email ed" 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_Email ed"
PG= 2
Case "Processed"
PG = 32
Case "Checked_in "
PG = 90
Case "Item_retur ned"
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!!!

[JConsulting]

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_Email ed", "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_Email ed" 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_Email ed"
PG= 2
Case "Processed"
PG = 32
Case "Checked_in "
PG = 90
Case "Item_retur ned"
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!!!

try this
Public Function PG(B_status As String) As String

[blackdogharry]

Thanks for the reply. But the change does not solve the problem.
I also tried As Integer instead of As String because the PG output is supposed to me numbers that I will need to do some further calculation later on.

I also looked up the tools-reference in the VB editor to see if any library appears missing but could find none...

Could anyone please give me some advice on how to proceed?
Thanksin advance.

[JConsulting]

Thanks for the reply. But the change does not solve the problem.
I also tried As Integer instead of As String because the PG output is supposed to me numbers that I will need to do some further calculation later on.

I also looked up the tools-reference in the VB editor to see if any library appears missing but could find none...

Could anyone please give me some advice on how to proceed?
Thanksin advance.

you said you're using this in a query, how are you using it?

select FunctionName([yourfield]) as something, [field2], [field3]
from sometable

??

[blackdogharry]

The SQL view of the query is as follows:
SELECT BATCH.B_id, PG([B_status]) AS Batch_Progress FROM BATCH;

This is the same as typing in "field" in the Design View I believe...
Batch_Progress: PG([B_status])

I tried modifying SQL to
SELECT PG([B_status]) AS Batch_Progress
FROM BATCH;

But no success.

I also found the following help in MS web page. It says it is a known problem. But I am not sure it applies to Access 2007 version. I don't see any missing reference in TOOLs>reference either...

http://office.microsoft.com/en-us/access/HP030844291033.aspx

Thanks so much for taking a look at this!!!

you said you're using this in a query, how are you using it?

select FunctionName([yourfield]) as something, [field2], [field3]
from sometable

??

[missinglinq]

I saved the function and the module both as PG.

If this statement is true, you've committed a classic error! You can't give a function and the module that holds it the same name! It confuses the Access gnomes! Just change the name of the module to something else and, barring other mistakes, you should be OK!

Welcome to TheScripts!

Linq ;0)>

[blackdogharry]

Oh, I did not know about this. I changed the module name to Module1 and left the function name as it is00 'PG'. Saved it and ran the function but still get the same error message. :-(

First time I get this error message. Next time when I click "!" on the ribbon to run the query, a line appear on the bottom saying "The action or event has been blocked by disabled mode."

I am utterly confused...

I even created a new db with just this function and one table.
This is a function with only one field as input. So no problem there.
Still it did not work... :-(

I also found this in Microsoft KnowledgeBase.
It this error message is a known bug that applies to ACCESS 2003 and 2007.

http://support.microsoft.com/kb/824277/#appliesto

I tried the suggested solution but no success.

Thanks so much for taking a look at this!!!

If this statement is true, you've committed a classic error! You can't give a function and the module that holds it the same name! It confuses the Access gnomes! Just change the name of the module to something else and, barring other mistakes, you should be OK!

Welcome to TheScripts!

Linq ;0)>