check value of a variable

[human_anomaly]

I am trying to check the value of a variable in a query. The problem is that the value is in the middle of a string. such as "Account# f2b5" I want to check the very first number of the account because that tells which branch it is from. I tried something like this: CASE WHEN ([AccountNum] = 0) THEN 0 ELSE CASE WHEN (mid([AccountNum] , 11 , 1) = 1) THEN 2 ELSE 3 END END
The query says that mid is not recognized. Is there any way to check this value in a query?

 

[wazz]

maybe this can be modified as needed:

Public Function FindAcctNum()

Dim stAcctNum As String
stAcctNum = "Account# f2b5"

    If mid(stAcctNum, 11, 1) = 0 Then
        stAcctNum = 0
    ElseIf mid(stAcctNum, 11, 1) = 1 Then
        stAcctNum = 2
    Else
        stAcctNum = 3
    End If
    
Debug.Print stAcctNum

End Function

w

 

[human_anomaly]

Let me explain this a bit better. I am using an ADP access database. I am trying to create a query. Just to make this simple lets say there are currently two varibles in the query, AccountNum and Date. I am trying to create a third variable called BranchNum, but its value will vary depending on what AccountNum contains. Lets say AccountNum = e25f
the second value in AccountNum is the branch number so in this case the account number is from branch 2. Now BranchNum needs to be set to this value. I have tried putting something like this into the column: Mid([AccountNum,2,1]) and the alias would be BranchNum. But when I go to save the query it gives me this error: ADO error: 'Mid' is not a recognized function name. Anyone know how I can grab a variable from the middle of a string in a query?

 

[RV]

I guess you've got a missing reference.
Open a (new) module.
In the menu bar, select Tools -> References
Check whether you've got a reference stating MISSING.
If so, untick the checkbox.

Check whether you've got a reference called "Microsoft DAO 3.x Object Library"
If not, select it from the list by ticking the checkbox.

RV

 

[human_anomaly]

None show up as missing. I added Microsoft DAO 3.51 object library and I still get the same error in my query.

 

[wazz]

I have tried putting something like this into the column: Mid([AccountNum,2,1]) and the alias would be BranchNum. But when I go to save the query it gives me this error:

move your bracket:

BranchNum: Mid([AccountNum],2,1)

w