By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,257 Members | 2,812 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,257 IT Pros & Developers. It's quick & easy.

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

P: 4
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!!!
Oct 5 '07 #1
Share this Question
Share on Google+
6 Replies


JConsulting
Expert 100+
P: 603
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!!!
try this
Public Function PG(B_status As String) As String
Oct 5 '07 #2

P: 4
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.
Oct 5 '07 #3

JConsulting
Expert 100+
P: 603
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

??
Oct 5 '07 #4

P: 4
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/ac...844291033.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

??
Oct 5 '07 #5

missinglinq
Expert 2.5K+
P: 3,532
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)>
Oct 5 '07 #6

P: 4
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)>
Oct 5 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.