Access 2007 Form - Combo Box rowsource error

My form has a combo box which gets set with the following in the rowsource property

SELECT [ICNTable].[ICNNumber] FROM ICNTable WHERE (Left(ICNNumber,8)=CRNNumber) ORDER BY ICNNumber;

It works fine but now the CRNNumber is changing to a variable length.  So in the load event of the form I put in the following code

   If Len(CRNNumber) <> 8 Then
      targetLen = Len(CRNNumber)
      ICNNum.RowSource = "SELECT ICNTable.ICNNumber FROM ICNTable WHERE " & _
         "(Left (ICNNumber, 'targetLen') = CRNNumber) ORDER BY ICNNumber;"
   End If

and now I'm getting the following error message.

This expression is typed incorrectly, or it is too complex to be evaluated.  For example, a numberic expression may contain too many complicated elements.  Try simplifying the expression by assigning parts of the expression to variables.

I'm not sure where to go from here.

Thanks!
Helen
greenprgAsked:
Who is Participating?
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Connect With a Mentor Commented:
Well, you can't really reference a variable like that in a SQL stmt.

Create a hidden text box on the form    txtTargetLen

Then this:

Dim targetLen As Long

      If Len(CRNNumber) <> 8 Then
-->      Me.txtTargetLen = Len(CRNNumber)
           ICNNum.RowSource = "SELECT ICNTable.ICNNumber FROM ICNTable WHERE " & _
              "(Left (ICNNumber, Forms!YourFormName.txtTargetLen) = CRNNumber) ORDER BY ICNNumber;"
        End If
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Remove ' from around targetLen

   If Len(CRNNumber) <> 8 Then
      targetLen = Len(CRNNumber)
      ICNNum.RowSource = "SELECT ICNTable.ICNNumber FROM ICNTable WHERE " & _
         "(Left (ICNNumber, targetLen) = CRNNumber) ORDER BY ICNNumber;"
   End If

mx
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Also, where is this defined:

targetLen

?

mx
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
greenprgAuthor Commented:
Without the single quotes around targetLen, when I click on the combo box, it asks me for the value of targetLen like it was a parameter that it does not have the value for.

targetLen is defined right after the If statement

      If Len(CRNNumber) <> 8 Then
-->      targetLen = Len(CRNNumber)
           ICNNum.RowSource = "SELECT ICNTable.ICNNumber FROM ICNTable WHERE " & _
              "(Left (ICNNumber, targetLen) = CRNNumber) ORDER BY ICNNumber;"
        End If

0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
I meant as in

Dim targenLen As ?

0
 
greenprgAuthor Commented:
Oh....

Dim targetLen as Integer
0
 
greenprgAuthor Commented:
Perfect.  Works great!

Thanks!
Helen
0
 
greenprgAuthor Commented:
Thanks for the quick answers.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
You are welcome.
Thank you for using Experts Exchange.

mx
0
All Courses

From novice to tech pro — start learning today.