Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Issue with Microsoft Access .FindFirst and Empty Strings

Posted on 2013-10-22
8
Medium Priority
?
1,021 Views
Last Modified: 2013-10-23
I am trying to use the VBA .FindFirst method in Microsoft Access 2010. In the code shown below, the first "findVendor" line finds a match. The second, commented out line, does not find a match.
     In my table, both FirstNm and MidNm are null. I've converted the values to empty strings in strFirstNm and strMidNm. What could be different about the fields such that one returns a match and the other doesn't? Ideally, I'd like to check for all 3 names in one query, but for now am just trying to get MidNm to match correctly.
     Is there something I'm missing about using an equality check for empty strings? is there a better way to do this?
     I do have a unique ID, but in this case I can't check for it because I don't know it at the time this code is running.
     Thanks for any suggestions!
                                 

Set rsVendor = CurrentDb.OpenRecordset("tblVendor", dbOpenDynaset)
findVendor = "[LastNm] = '" & strLastNm & "' And [FirstNm] = '" & strFirstNm & "'" 'findVendor = "[LastNm] = '" & strLastNm & "' And [MidNm] = '" & strMidNm & "'"
With rsVendor
       .FindFirst (findVendor)
        If .NoMatch Then
                     ….Do Stuff
        End If
End With
rsVendor.Close
0
Comment
Question by:oneDayAtaTime
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 49

Expert Comment

by:Dale Fye
ID: 39591957
A lot of people don't have middle names, and that field should be either NULL, or if you are certain that they have no middle name, then many systems use "NMN" to represent "No Middle Name".

Instead of setting rsVendor to reference the table, create a query (qryVendor) that converts [MidNm] using: NZ([MidNm], "NMN")

Then, in your find first, use something like:

'findVendor = "[LastNm] = '" & strLastNm & "' And [MidNm] = '" & NZ(strMidNm, "NMN") & "'"
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39591970
Keep it simple,
... does it work just for the First and Last name?
(I prefer the more segmented syntax)
   
    findVendor="[LastNm] =" & "'" & strLastNm & "'" & " And " & "[FirstNm] =" & "'" & strFirstNm & "'"

If not, then why bother adding them all into a bigger expression...
Make sense?

Perhaps another Expert can spot what I may be missing though


But again, I get something that works first, then build up from there.
This way you know exactly where the problem is...

JeffCoachman
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39591983
Sorry!

 Ignore my posts above  I thought that was one long expression
;-)
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39591992
I am sure fyed can get you sorted...
0
 
LVL 31

Expert Comment

by:hnasr
ID: 39592010
List few input records.
0
 

Author Comment

by:oneDayAtaTime
ID: 39592477
fyed,
     In the example I'm using (that's failing), the First Name and Middle Name are both null. I convert them to empty strings using the Nz function before I get to the .FindFirst. I did this because I know that nothing equals null, so I thought an empty string would be better.
     I'd rather not use a dummy value for middle name, because I grab these fields later and print them on a form as the person's name. If middle (or first) is blank, it doesn't print.
    I guess I'm just trying to figure out the difference - why a blank First Name works fine and finds a match, but a blank Middle Name does not. For example, say I have vendors named Smith (no first or last name), John Smith, and John H Smith. I want them all to resolve properly. Right now I don't seem to be able to use the middle name (if it's blank).
                      Joyce
0
 
LVL 49

Accepted Solution

by:
Dale Fye earned 2000 total points
ID: 39592547
Because you may actually have a blank value in the field.  You cannot actually achieve this by typing a blank in a field, but if you write an action query (Insert or Append) query you can actually write a zero length string to a field.

See the query in the attached file.  In the first query (qry_Name), I tested for IsNull and [LastNm] = "".  You will see that the last name is NULL in record 2, but a ZLS (zero length string) in #3

"Right now I don't seem to be able to use the middle name (if it's blank). "

Actually, if it is blank (zero length string), then it should work, but FindFirst is not going to work with a NULL in that field.
Nulls-and-ZLS.accdb
0
 

Author Comment

by:oneDayAtaTime
ID: 39593681
Thanks, fyed! That was it! I was so focused on my syntax that I neglected to think about the logic underneath! Sigh....
     I performed an update query to make all the possible blanks in First Name/Middle Name into empty strings. Now I can use .FindFirst and it works regardless.
     Thanks for the advice and for the troubleshooting tip! Next time I have issues, I will know to check the fields to see exactly what I have.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

783 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question