Dynamically declaring the length for the Left(str,len) function

[shacket]

I need to use the Left function (to get the left part of a string) but I need to tell it the length dynamically. VB help says that it merely needs to be an expression that produces a number. Here is what I have:

Public NumberLet As Variant 'I have tried 'Long' as well - this is declared at the top - for all Subs to use

NumberLet = Len(cboMyPowerGroup) 'This is in the BeforeUpdate procedure for the combo box 'cboMyPowerGroup'

rst.FindFirst "Left([PowerGroupID], NumberLet) = " & Prefix 'This is in the AfterUpdate event of the combo box. .rst and Prefix have been properly defined

I threw a msgbox in there to tell me what NumberLet was in the AfterUpdate event and it tells me just fine. However, when the code runs, the error message says:

"Run Time Error 3070
The Microsoft Jet Database engine does not recognize 'NumberLet' as a valid field name or expression."

Thanks for any help you can give me.

Dave

 

[Fornatian]

Dim Numberlet as an integer and see how you go.

 

[shacket]

I tried that. The same thing came up.

I am beginning to wonder if there is something in References that I have to change.

Any more help would be terrific!

Dave

 

[TiggR]

"Left([PowerGroupID], NumberLet) = " & Prefix
is a string so A2k will look for Numberlet as a field in the recordset
try
"Left([PowerGroupID], " & NumberLet & " ) = " & Prefix

I think this should work.

Doug

 

[shacket]

Thank you. That was the solution. (I am still trying to debug this code overall though - so I may post again!)