Expected: expression - Access 2K Run Time Error

[kirk Wilson]

Listed below is a public function that is supposed to fetch a text
string based upon an index value. The function compiles without a
problem. When I tested it in the immediate window I get the following
run time error:

Compile error:
Expected: expression

I have no idea what the problem is. Any advice will be apprecisted.

Public Function LookupValue(strTableName As String, strKeyField As _
String, strLookupField As String, bytKeyValue As Byte) As String

Dim rstRecordset As ADODB.Recordset
Dim strCriteria As String

Set rstRecordset = New ADODB.Recordset
strCriteria = strKeyField & "=" & bytKeyValue
With rstRecordset
Set .ActiveConnection = CurrentProject.Connection
.Source = strTableName
.CursorLocation = adUseServer
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.Open
.Find strCriteria
LookupValue = .Fields("Type")
End With
rstRecordset.Close
Set rstRecordset = Nothing

End Function
--------------------------------------------------------------------
tblPhoneType
Recid# = autonumber / primary key / indexed no duplicates
Type = Text * 20

Recid# Type
1 Primary Phone
2 Work Phone
3 Work Fax
4 Pager
5 Cell

----------------------------------------------------------------------
Immediate Window
?lookupvalue(tblphonetype,recid#,type,4)

Run time error

Compile error:
Expected: expression

 

[Brendan Reynolds]

The first three arguments are strings, so you need quotes around them. Try
....

? lookupvalue("tblphonetype", "recid#", "type", 4)

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[kirk Wilson]

Hi Brendan,

Thanks form the help. Everything works fine.

Why do I have to enclose the parameters in quotes? Is there some way
to pass the parameters without using quotes? It seems to me that
Access should be able to handle this without quotes. Everything is
comma deleminted & declaired in the function. Access is able to handle
the 4 parameter without problems.

Kirk

 

[Brendan Reynolds]

The quotes are required around literal strings because a string could be
either a literal value, or the name of a variable that contains the value.
The quotes indicate that it is a literal value. For example ...

Dim strWhatever As String

strWhatever = "1234567"

'Quotes because we're passing a literal value ...
Debug.Print Left$("1234567", 4)

'No quotes, because we're passing a variable ...
Debug.Print Left$(strWhatever, 4)

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[kirk Wilson]

What was confusing me was that the arguments were inclosed in brackets
(tblPhoneType,...). I thought that would make it a literal and not a
variable.

Excellent example. Thanks for the help.

Kirk