Access does not seem to have FIND function. I need to capture Last
Name + Comma + First 4 Characters of First name. In the Mid function
since last names are variable length, I need to determine the location
of the comma within the field, then capture from 1 to Comma plus 5
characters.

This works in Excel, and I updated Access to read as follows:

=MID([Name],1,FIND(",",[Name])+5)

Access does not like FIND though

=Mid([Name],1,Instr(1,[Name],",")+5)

"Ben" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Access does not seem to have FIND function. I need to capture Last
> Name + Comma + First 4 Characters of First name. In the Mid function
> since last names are variable length, I need to determine the location
> of the comma within the field, then capture from 1 to Comma plus 5
> characters.
>
> This works in Excel, and I updated Access to read as follows:
>
> =MID([Name],1,FIND(",",[Name])+5)
>
> Access does not like FIND though
>

In Access it's Instr().

Debug.Print Left("Whittle, Jerry",Instr("Whittle, Jerry",",")+ 5)
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

"Ben" wrote:

> Access does not seem to have FIND function. I need to capture Last
> Name + Comma + First 4 Characters of First name. In the Mid function
> since last names are variable length, I need to determine the location
> of the comma within the field, then capture from 1 to Comma plus 5
> characters.
>
> This works in Excel, and I updated Access to read as follows:
>
> =MID([Name],1,FIND(",",[Name])+5)
>
> Access does not like FIND though
>
>

=left([Name], instr(1,[Name],",") + 5)

By the way it is bad practise to name a field or variable NAME
It is also bad practise to have a field that contains both lastname and
firstname

"Ben" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Access does not seem to have FIND function. I need to capture Last
> Name + Comma + First 4 Characters of First name. In the Mid function
> since last names are variable length, I need to determine the location
> of the comma within the field, then capture from 1 to Comma plus 5
> characters.
>
> This works in Excel, and I updated Access to read as follows:
>
> =MID([Name],1,FIND(",",[Name])+5)
>
> Access does not like FIND though
>