Combo-Box Lookup Finding Extraneous Values

[jmitchelldueck]

Hi,



I have a combo box on various forms that looks in tables to find the primary key associated with the name selected and bring up the associated data. However, I have run into an issue with this method. If I select my employee who has employee ID 9 (PK), it brings up that employee, but also all other employees who have an employee ID starting with that same digit (ie 91-99; 90 not included). This gives me the applicable records for more than one employee. How do I fix this?

Thanks,
jmd

[CJ_London]

depends on what you are doing at the moment - what is the sql to the rowsource to your combo?

[JamesDeckert]

Here's a video tutorial on how to make a combobox lookup and go into the query to see what's happening.
https://www.youtube.com/watch?v=EdH1aIWaD-0

[ssanfu]

If I select my employee who has employee ID 9 (PK), it brings up that employee, but also all other employees who have an employee ID starting with that same digit (ie 91-99; 90 not included)

By any chance is the ID field a text type?

I only use Autonumbers (Long Integers) for my PK fields and (of course) the FK field must also be a Long Integer.

[jmitchelldueck]

Steve: The PK field is an autonumber.
Ajax:

SELECT PhysicianT.physID, PhysicianT.firstName, PhysicianT.middleName, PhysicianT.lastName, PhysicianT.fullName, PhysicianT.department1, PhysicianT.department2, PhysicianT.department3, PhysicianT.program1, PhysicianT.program2, PhysicianT.program3, PhysicianT.section1, PhysicianT.section2, PhysicianT.section3, PhysicianT.officePhoneNumber, PhysicianT.faxNumber, PhysicianT.emailAddress, PhysicianT.officeAddress, PhysicianT.medCorpName, PhysicianT.pagerNumber, PhysicianT.cellPhoneNumber, PhysicianT.homePhoneNumber, PhysicianT.activeInactive, PhysicianT.inactiveReason, PhysicianT.uOfMRank, PhysicianT.dateOfBirth, PhysicianT.appointment, PhysicianT.homeAddress, PhysicianT.recentPerformanceReview, PhysicianT.performanceReviewer, PhysicianT.perfRevIncludedClinical, PhysicianT.umEmployeeID, PhysicianT.umFirstContract, PhysicianT.wrhaFirstContract, PhysicianT.ccmbFirstContract, PhysicianT.emergFirst, PhysicianT.emergLast, PhysicianT.emergPhone1, PhysicianT.emergPhone2, PhysicianT.emergDetails, PhysicianT.prctResearch, PhysicianT.prctClinical, PhysicianT.prctAdmin, PhysicianT.prctTeaching
FROM PhysicianT
WHERE (((PhysicianT.physID) Like [forms]![physiciannameselectf]![Combo0] & "*"))
ORDER BY PhysicianT.lastName;

I just did a search on the operators I am using after my 'Like'; it seems to me there is something about the '&' I don't quite understand. When I changed & to And in the code, it only returned one employee. Can I get an explanation for this?

Thanks,
jmd

[CJ_London]

that's a lot to bring through for a combobox

WHERE (((PhysicianT.physID) Like [forms]![physiciannameselectf]![Combo0] & "*"))

assuming physID is a number then you can't use the "*" which is for text

since you are only looking for those which are = to the number in combo0, try

WHERE (((PhysicianT.physID) = [forms]![physiciannameselectf]![Combo0]))

[jmitchelldueck]

OK. That works, thanks. Now, I do have a form where I have one record per page, and I want to be able to search for one individual, or scroll through my employees by clicking the 'next' button. The purpose of this is to allow my users to scroll manually through and do edits to records, or make changes to a specific record by selecting it with the combobox. If I use the code:

WHERE (((PhysicianT.physID)=[forms]![EPhysicianF]![Combo92]))

The form does not show any records at all, since no records have a PK of "" or Null (blank combobox - I'm not sure which value it is), but the search function (by selecting a name from the dropdown) opens up the appropriate record. To fix this, I was thinking of putting a conditional statement in, but I don't know how I can select all the PK's. It seems to me that the "*" had something to do with it finding all the records, but I don't understand why.

The code I was thinking was something along the lines of:

if [forms]![EPhysicianF]![Combo92] = Null //that is, if it is blank
//set combo92 to dummy variable that represents all records
//break if
WHERE (((PhysicianT.physID) = [forms]![EPhysicianF]![Combo92]))

Let me know what you think of this, or if there is a better way to do it. I also am not very familiar with SQL syntax, so please forgive my grammatical errors.

Thanks,
jmd

[jmitchelldueck]

WHERE (((PhysicianT.physID) Like [forms]![EPhysicianF]![Combo92] & "*"))

This gives me the individual scrolling option, but follows the same problem I described in my original post, that if PK 5 is selected, any other PK starting with a 5 is also searched for.

[June7]

As already stated in post 6, don't use LIKE with numeric data because of the issue you describe.

Cannot test for =Null, use IsNull(). Review
http://allenbrowne.com/casu-12.html
http://allenbrowne.com/QueryPerfIssue.html

Try: WHERE (((PhysicianT.physID) = [forms]![physiciannameselectf]![Combo0])) Or Is Null


I NEVER use dynamic parameterized queries. I prefer http://www.allenbrowne.com/ser-62.html

[jmitchelldueck]

The addition of the Is Null gives me a syntax error. I tried it with 'Or 0' and 'Or ""', neither of which work. I need some way to tell the query to find all primary keys.

[jmitchelldueck]

This may be the key. I know you all said not to use the "*" for numbers, but it works for me. No idea why:

WHERE ((PhysicianT.physID) = [forms]![EPhysicianF]![Combo92] and [forms]![EPhysicianF]![Combo92] is not null) OR (([forms]![EPhysicianF]![Combo92] is null and PhysicianT.physID like "*"))

Essentially an XOR statement, found the string here - Shawn Kovac's answer.

I don't think this will cause any issues with my database, so I think I'll close the thread down. Thanks for the help!