HI RICK, Did you get this resolved? I would caution you not to use the filed name "name". This is a reserved word and can cause confusion when used in code like me.name. This would give you the name of the object such as the form name or the report name. Using something like "FullName" or "SearchName" will eliminate some confusion.
Doing parsing like this can also be problematic when data integrity is not maintained. What I mean by this is; if the person has a middle initial are some followed by a period? Are some first names only entered as an initial? If data integrity is maintained you are only dealing with the last two or three characters in the name. In the criteria of the query under name or fullname : InStr(Right([fullname],2)," ")>"0" Glenn P. Jones IHS -----Original Message----- From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Rick Sent: Thursday, 22 September, 2005 8:53 AM To: [email protected] Subject: [AccessDevelopers] Problem testing for imbedded 'blank' in string Hi, Using Access 2000, I'm working with a table which was created from an imported text file. The table contains a name field where the names are entered like "DOE, JOHN A". I want to create a query to find all the records that have a middle initial at the end of the string by searching for a blank space at the next to last character. I'm using "Mid([name],Len([name])-1,1)" as the expression which works fine, but for the criteria I've tried comparing it to a blank (" "), a zero length string (""), or a Null value, and I get a data type mismatch error. I've done some research and have tried using MidB and LenB, but that hasn't worked. I also read about byte storage, Unicode and ANSI storage, and checking for a dll file, but nothing that has been helpful to me. Does anyone have any suggestions what's wrong and how to test for this condition? Thanks very much, Rick Please zip all files prior to uploading to Files section. Yahoo! Groups Links ------------------------ Yahoo! Groups Sponsor --------------------~--> Get Bzzzy! (real tools to help you find a job). Welcome to the Sweet Life. http://us.click.yahoo.com/A77XvD/vlQLAA/TtwFAA/q7folB/TM --------------------------------------------------------------------~-> Please zip all files prior to uploading to Files section. Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/AccessDevelopers/ <*> To unsubscribe from this group, send an email to: [EMAIL PROTECTED] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
