Please help with my formula

[Please Help]

Hello all,

I have the following formula for one of the fields in my query, and I am
getting an error message "Invalid Procedures Call".

Exp 1: Mid([Student]![Student Name],InStr(1,[Student]![Student Name]," ",0),6)

Does anyone know why? Please help.

Thanks.

 

[John Spencer]

Exp 1: Mid([Student]![Student Name],InStr(1,[Student]![Student Name],"
",0),6)

If there is no space in Student Name or Student name is null then you will
get errors.

Instr(1,Null," ",0) will return a null
Instr(1,"ABCDE"," ",0) will return zero

Both those values will generate an error when used as the second argument in
a call to the Mid function.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Please Help]

Hi John,

Thanks for your response.

Basically, I am trying to extract the first 6 characters of the student
middle name. Based on what you are saying, I may have some records with just
one name (John, instead of John Doe Smith).

Can you help me with the formula to handle the error and at the same, to
give me the result?

Thanks.

Exp 1: Mid([Student]![Student Name],InStr(1,[Student]![Student Name],"
",0),6)

If there is no space in Student Name or Student name is null then you will
get errors.

Instr(1,Null," ",0) will return a null
Instr(1,"ABCDE"," ",0) will return zero

Both those values will generate an error when used as the second argument in
a call to the Mid function.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hello all,

I have the following formula for one of the fields in my query, and I am
getting an error message "Invalid Procedures Call".

Exp 1: Mid([Student]![Student Name],InStr(1,[Student]![Student Name],"
",0),6)

Does anyone know why? Please help.

Thanks.

 

[Mika Oukka]

Try to:

*Open the database that has the problem
*Press ALT+F11 to switch to the Visual Basic Editor
*Choose Tools - References
*Look for check boxes that are marked as "MISSING:" to indicate a missing
reference
*Clear the check box for the library or object library
*Close VBE
*Close Database and open it again

Cheers,

Mika

 

[Please Help]

Hi Mika,

Thanks for your response. I just checked the references in VBE of the
database that I am having problem, and I didn't see any references with
"MISSING:".

Thanks.

Try to:

*Open the database that has the problem
*Press ALT+F11 to switch to the Visual Basic Editor
*Choose Tools - Reference
*Look for check boxes that are marked as "MISSING:" to indicate a missing
reference
*Clear the check box for the library or object library
*Close VBE
*Close Database and open it again

Cheers,

Mika

Hello all,

I have the following formula for one of the fields in my query, and I am
getting an error message "Invalid Procedures Call".

Exp 1: Mid([Student]![Student Name],InStr(1,[Student]![Student Name],"
",0),6)

Does anyone know why? Please help.

Thanks.

 

[Ofer Cohen]

You can create a function that return a part of the string by spacfying the
word location within the string

Something like:

Function MySplit(MyStr As String, MyLocation As Integer)
On Error GoTo MySplit_Err

' Use the Split function to retieve any string location
MySplit = Split(MyStr, " ")(MyLocation)

Exit Function
MySplit_Err:
MySplit = ""

End Function

And then, in the query use it as
NewName: MySplit([FieldName], 0) & " " & MySplit([FieldName], 2)


To retrieve any part of the text, the error handler will return empty space
if the string doesn't exist
--
Good Luck
BS"D

Hi John,

Thanks for your response.

Basically, I am trying to extract the first 6 characters of the student
middle name. Based on what you are saying, I may have some records with just
one name (John, instead of John Doe Smith).

Can you help me with the formula to handle the error and at the same, to
give me the result?

Thanks.

Exp 1: Mid([Student]![Student Name],InStr(1,[Student]![Student Name],"
",0),6)

If there is no space in Student Name or Student name is null then you will
get errors.

Instr(1,Null," ",0) will return a null
Instr(1,"ABCDE"," ",0) will return zero

Both those values will generate an error when used as the second argument in
a call to the Mid function.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hello all,

I have the following formula for one of the fields in my query, and I am
getting an error message "Invalid Procedures Call".

Exp 1: Mid([Student]![Student Name],InStr(1,[Student]![Student Name],"
",0),6)

Does anyone know why? Please help.

Thanks.

 

[John Spencer]

Lots of problems here.
-- You are assuming that the student name contains a middle name
-- You are assuming that the middle name is at least 6 characters long
-- You are assuming that the student name exists and is more than one word.
-- You are not going to handle names like Luke von Neumann correctly or like
Amy Sue Ann Retton or even Jim Bob Thornton (returns Bob Th)

I would recommend that you try to use the function posted by Ofer Cohen.
One problem there is that he didn't check for the existence of three "words"
in the name.

The following should return what you asked for. That does not mean it will
return what you want.
IIF([Student Name] Like "[a-z]* *[a-z] *", Mid([Student
Name],Instr(1,[Student Name], " ")+1,6),Null)

The IIF checks to see if there is a letter followed by a space followed
later by a letter and another space in Student name. If so it should
return the 6 characters following the first space.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hi John,

Thanks for your response.

Basically, I am trying to extract the first 6 characters of the student
middle name. Based on what you are saying, I may have some records with
just
one name (John, instead of John Doe Smith).

Can you help me with the formula to handle the error and at the same, to
give me the result?

Thanks.

Exp 1: Mid([Student]![Student Name],InStr(1,[Student]![Student Name],"
",0),6)

If there is no space in Student Name or Student name is null then you
will
get errors.

Instr(1,Null," ",0) will return a null
Instr(1,"ABCDE"," ",0) will return zero

Both those values will generate an error when used as the second argument
in
a call to the Mid function.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hello all,

I have the following formula for one of the fields in my query, and I
am
getting an error message "Invalid Procedures Call".

Exp 1: Mid([Student]![Student Name],InStr(1,[Student]![Student Name],"
",0),6)

Does anyone know why? Please help.

Thanks.

 

[John W. Vinson]

Basically, I am trying to extract the first 6 characters of the student
middle name. Based on what you are saying, I may have some records with just
one name (John, instead of John Doe Smith).

What purpose will this value serve?

What is the correct result for "John Doe Smith"? The first six letters of
"Doe", or "Doe Sm"? What will you do with a student whose first name is
"Billy Bob" or "Janet Lynn"?

I'm worried that you're problem space may not be quite what you intend!

John W. Vinson [MVP]

 

[Please Help]

Hi guys,

Thank you all very much for your helps.

As part of conversion process, I have to create a field for middle name by
extracting from a name field. What I didn't realize was the data are not
consistence. Unfortunately, I didn't key those names in. If I did, I would
have it structured (first, middle and last).

Thanks again for your helps. All you guys are great.

Have a nice and safe holiday season!