What Access function would = the folllowing from Excel: =MID(H31,1,FIND(",",H31)+5)

B

Ben

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
 
J

Jerry Whittle

In Access it's Instr().

Debug.Print Left("Whittle, Jerry",Instr("Whittle, Jerry",",")+ 5)
 
B

Bill Edwards

=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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

MID function fails in SQL 3
Two Different Characters in String 1
Syntax to combine MID() and FIND("LastPost:") to extract the LastPost Date ? 3
Separate first and last name in two columns 3
Parsing (again) 1
Query - Mid Function & Mail Merge 4
Two Access questions 4
Finding the height of a row in a word table from Excel 0

Top