Drill Down

[mbrinser]

I have created a report that has a list of individual's names and have written code so that I can click on a person's last name and it will take you to their detailed info, but it brings up all the records who share the same last name so I added a field showing the last 4 of the social security number to make it more precise. However, when I try to use similar code it just links me to a blank form. I'm assuming the problem is that the form shows the whole SS# while the report just shows the last 4, how do i correct this? Below is the code I have written that gives me the blank form:

Private Sub Last_4_Click()
DoCmd.OpenForm "frmDataEntry", _
WhereCondition:="Social = '" & Me.Last_4 & "'"
End Sub

 

[georgedwilkinson]

I have no clue but I do have a thought. Would this work as your where condition:

WhereCondition:="Social LIKE '*" & Me.Last_4 & "'"

 

[mbrinser]

Thank you, that worked perfectly. As a follow up questions, is there any way I can have it display the SS# as follows: xxx-xx-1234 in my report, right now it just shows the last 4 digits without the placeholders.

 

[gemma-the-husky]

what this does show is that the "name" is inadequate for this purpose. What keys do you have

do you not have a unique individualid field you can use?

 

[georgedwilkinson]

I assume it is getting the 4 digits from the query. If so, modify the query with something like:

'xxx-xx'&Whatever.Last_4

in place of the the portion of the query that creates the last 4 digits field.

 

[mbrinser]

IT is getting the four digits from an unbound text box I created on the report, and the formula you gave me below got the format that I wanted, i.e. xxx-xx-1234, but now it messed up the WhereCondition, any suggestions on what needs to be fixed for hte wherecondition?

 

[georgedwilkinson]

Not really without seeing everything that creates the data for the report.

I've been taking a shot in the dark so far on everything.

 

[mbrinser]

I appreciate the help thus far. How do I create to where conditions? I have one where condition that searches for similar SS #'s, but in the rare instance where somebody has the same last 4 digits, how do i also include a second where condition to match last 4 and last name?

 

[georgedwilkinson]

Put the "AND" keyword between the conditions.

 

[mbrinser]

I swear I tried that before and it wouldn't let me...and now it did. Anyway, I am getting a runtime error '13' type mismatch when I try to combine the two conditions below, they worked independantly so what do I need to change now that I am trying to run them together?

WhereCondition:="Social LIKE '*" & Me.Last_4 & "'" And "LastName = '" & Me.LastName & "'"

 

[georgedwilkinson]

Too many quote marks. The "AND" (without quote marks) is part of the SQL query. Thus, when you build it, you cannot have "" around it.

WhereCondition:="Social LIKE '*" & Me.Last_4 & "' And LastName = '" & Me.LastName & "'"

Or something like that.