Clive Beaton
asked on
Left function - 'Expected Array'
Can anyone help me with this compiler error? I use the Left function elsewhere in a different module which doesn't raise this error.
Thanks in advance
Capture.JPG
Thanks in advance
Capture.JPG
Try to use Left$ instead of Left
Left$ is equivalent to Left (but obsolete)
Your syntax for Left is fine.
You may want to Dim s1 and s2 on seperate lines of code. I've seen other posts around here saying that despite the 'As String', the second string will actually get defined as a Variant.
That in itself, however shouldnt cause this error. Your code looks good and works fine for me.
In the VBA editor, check tools --> references. If any are labeled as MISSING, uncheck them and replace them with the correct version.
Your syntax for Left is fine.
You may want to Dim s1 and s2 on seperate lines of code. I've seen other posts around here saying that despite the 'As String', the second string will actually get defined as a Variant.
Dim s1 As String
Dim s2 As String
s2 = "abc"
s1 = Left(s2, 1)
MsgBox s1
That in itself, however shouldnt cause this error. Your code looks good and works fine for me.
In the VBA editor, check tools --> references. If any are labeled as MISSING, uncheck them and replace them with the correct version.
ASKER
Done that. Different error.
Capture.JPG
Capture.JPG
ASKER
mbizup,
Did a Compact & Repair. Changed back to Left. Separated the Dim statements. Still got 'Expected array'
No missing references.
Tried it in an earlier version of the program and it worked fine. The references were identical as well.
Where from here, I wonder.
Did a Compact & Repair. Changed back to Left. Separated the Dim statements. Still got 'Expected array'
No missing references.
Tried it in an earlier version of the program and it worked fine. The references were identical as well.
Where from here, I wonder.
Is s2 defined anywhere else in this module?
Have you posted your full and complete code, or have you abbreviated it for the purpose of making a concise forum post? (We'd need to see the full code)
Have you posted your full and complete code, or have you abbreviated it for the purpose of making a concise forum post? (We'd need to see the full code)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The code compiled OK on my side. Please check the references in your project. If you have a wrong reference (look for "Missing" word on checked references) this is the behaviour
Try decompiling, and also give this a try:
Can you describe what you are trying to do? there may be a better approach.
Public Sub ConvertPostcodes()
Dim rs As Recordset
Dim i As Long
Dim s1 As String
Dim s2 As String
Dim Mystr As String
Set rs = CurrentDb.OpenRecordset("tblPostcodes", dbOpenDynaset)
s2 = ""
With rs
If Not .EOF Then
.MoveFirst
Do Until .EOF
s1 = !Locality
Do Until Len(s1) = 0
s2 = s2 & Left(s1, 1)
s1 = Mid(s1, 2)
i = InStr(s1, " ")
If i = 0 Then
s2 = s2 & s1
Else
s2 = s2 & Mid(s1, i - 1)
s1 = Mid(s1, i + 1)
End If
Loop
Debug.Print s2
' .Edit
' !Locality = s2
' .Update
.MoveNext
Loop
End If
End With
End Sub
Can you describe what you are trying to do? there may be a better approach.
ASKER
Tried different variable names.
Haven't used Left as a variable or global name.
Read Jim Dettmans article.
Decompiled.
Imported everything into a brand new database.
Created a new module, copied all the code into it (ctl/C Ctl/V), deleted the old module. and compiled.
Still get same error.
Interesting thing is, Left compiles and works in other modules, so I moved the function to another module and it compiled.
So at least I can move on. Your help has been worth the points. It put me on the right track.
I'm going it have to re-visit this because it worries me, so more questions on this will be coming soon..
Haven't used Left as a variable or global name.
Read Jim Dettmans article.
Decompiled.
Imported everything into a brand new database.
Created a new module, copied all the code into it (ctl/C Ctl/V), deleted the old module. and compiled.
Still get same error.
Interesting thing is, Left compiles and works in other modules, so I moved the function to another module and it compiled.
So at least I can move on. Your help has been worth the points. It put me on the right track.
I'm going it have to re-visit this because it worries me, so more questions on this will be coming soon..
ASKER
See my final comments.
ASKER
In answer to your question about what I want to do is take a name like
AUSTRALIAN NATIONAL UNIVERSITY
and convert it into
Australian National University.
AUSTRALIAN NATIONAL UNIVERSITY
and convert it into
Australian National University.
I ran into a similar problem recently.
Since this was posted under Access 2010, I am assuming that you are using the function in VBA.
When you have a lot of references... sometimes you inadvertently pull in another reference to "left" or "right" and your appliction can get confused. In this case if you fully qualify your function call... it will help your application understand which function it should use.
All of the following are equivalent... and one of the more fully qualified calls might work for you.
looking through VBA's object browser will help you determine what the "fully qualifed" names are that you should use for any given function.
Since this was posted under Access 2010, I am assuming that you are using the function in VBA.
When you have a lot of references... sometimes you inadvertently pull in another reference to "left" or "right" and your appliction can get confused. In this case if you fully qualify your function call... it will help your application understand which function it should use.
All of the following are equivalent... and one of the more fully qualified calls might work for you.
Sub test()
y = "Test"
x = Left(y, 1)
x = Strings.Left(y, 1)
x = VBA.Strings.Left(y, 1)
End Sub
looking through VBA's object browser will help you determine what the "fully qualifed" names are that you should use for any given function.
Your code could be simplified alot by converting to Proper case like this:
s = "AUSTRALIAN NATIONAL UNIVERSITY"
Newstring = strconv(s,vbProperCase)
s = "AUSTRALIAN NATIONAL UNIVERSITY"
Newstring = strconv(s,vbProperCase)
>> I'm going it have to re-visit this because it worries me
Agreed... If you can post a sample database (just scrub it of any sensitive data first), lets keep working at it.
Agreed... If you can post a sample database (just scrub it of any sensitive data first), lets keep working at it.
ASKER
mbizup,
You're worth your weight in gold. Thanks very much. I'll be back in touch.
crb
You're worth your weight in gold. Thanks very much. I'll be back in touch.
crb
If you haven't already, you should try the comment by buttersk at http:#a38007536, and put the vba prefix in front of Left.
However, since Left is a built in VBA function, unless something is slightly amiss in your database, left should work properly without the prefix (it should take priority over Left in other libraries).
Also check one more time for Left defined implicitly or explicitly as a function or variable anywhere within this module of code (or in your database).
Btw - if you use the Request Attention button, a Moderator can reopen this for you. Its best to just have solutions that work marked as the answers, since others in the same situation refer to these threads for solutions.
However, since Left is a built in VBA function, unless something is slightly amiss in your database, left should work properly without the prefix (it should take priority over Left in other libraries).
Also check one more time for Left defined implicitly or explicitly as a function or variable anywhere within this module of code (or in your database).
Btw - if you use the Request Attention button, a Moderator can reopen this for you. Its best to just have solutions that work marked as the answers, since others in the same situation refer to these threads for solutions.