Filter in Header retrieves wrong record

[alansidman]

I have a form with a search combo box in the header that when the contact is selected retrieves the record for that contact. This works without issue except for one contact record. The name of the combo box is mySelector. The SQL statement behind the combo box which is unbound is

SELECT tContacts.ID, tContacts.Last, tContacts.First
FROM tContacts
WHERE (((tContacts.ID) Like [forms]![fContacts].[myselector] & "*"))
ORDER BY tContacts.Last;

Once the combo box is selected there is an AfterUpdate event

me.requery

The Correct ID for contact I am searching is 34. The record that comes up has an ID of 3476.

I have used this procedure in several other DB without issue. This one has me dumbfounded.

Advise if you need more information or a knockdown version of the DB

Regards
Alan

As I indicated earlier, all works well for every search except the one noted.

[pbaldy]

It doesn't sound like you want the wildcard. Try:

WHERE (((tContacts.ID) = [forms]![fContacts].[myselector]))

[orange]

Alan,

Maybe I'm not following, but something seems strange. The LIKE operator works with Text fields so it seems that your ID must be a text datatype???

[pbaldy]

Like will "work" with numeric and date/time fields, it just doesn't make sense to use it in my view. I'd suspect that the correct record of 34 is returned in this case, it just isn't the only record returned, and 3476 is the one that shows first. Other times the desired record is probably first, but again not the only record returned.

[alansidman]

Paul, when I replace & "*" with WHERE (((tContacts.ID) = [forms]![fContacts].[myselector])) I get a syntax error. Maybe I'm misunderstanding how you are you suggesting I write the SQL statement. You are correct in your statement that it is returning all the records beginning with 34. I had not noticed that before. I am guessing that I never experienced this before due to the limited number of records. Could you give me the entire SQL statement that you think will work? I'd like that to work before I think about reassigning new Contact IDs. From a development standpoint, during the building of the DB, I had Contact IDs up to 307. Then I did something that caused the Automatic Contact IDs to jump to 3476 as the next ID. I don't recall what I did, but felt it really did not make a difference as Contact IDs are just unique numbers to reference a record.

[pbaldy]

What I posted was the whole criteria, not just a replacement for the wildcard. On a iPad, but just replace the WHERE line in yours with mine.

[alansidman]

Thanks Paul, but that gives me an error message Undefined function WHERE in expression. I think I need to do a little bit of reassigning Contact IDs and Foreign IDs in the related tables. Ugh!! I am going to try one more thing and instead of using the ID, I will use the last name as the lookup value in the combo box. Appreciate your input and help on this matter.

[ssanfu]

Maybe try concatenating the comb box???

WHERE tContacts.ID = " & [forms]![fContacts].[myselector]

[Micron]

what if you have multiples (smith,john; smith, albert)?
Maybe post the new problem version? First, I'd check in the immediate window what the combo value is at runtime based on the sql that was working; maybe it's not what you think. If it looks OK, you could dump the sql that the form is based on into a new test query to see the resulting record(s) based on that combo value. Don't forget to factor in the LIKE operator. The form recordsource might be conducive to returning a value(s) other than what you think it should.

[alansidman]

Steve, Got an invalid syntax error. Tried adding a second quotation mark after the & and then tried at the end of the expression. Neither worked. Gave invalid syntax error in one case and in the other opened a parameter query box.

Micon, when I changed the combo box to look up names, it gave the same results as if I was looking up the names using the ID as the bound field. As I indicated earlier to Paul, it does return the expected value, but it also returns 28 other records all having IDs beginning with 34.

Still sounds like a revamping of Primary ID and Foreign ID

[pbaldy]

What is your SQL now? If it worked with like but returned too many records, the revision should pull the one record.

[orange]

Alan,
Can you post a copy of the database?
I agree with Paul --normally with an ID you would want

WHERE tContacts.ID = " & [forms]![fContacts].[myselector]

Your use of Like in this situation is a little different.

[alansidman]

Jack
I will post a cut down version of the DB later today. There is a lot of confidential data that needs to sanitized first; and the mountains are calling and I need to get my skis on this morning and make some turns.

Alan

[orange]

I understand --nature calls!
It called me to go golfing this afternoon.
At least we have our priorities under control...

[alansidman]

Right!

Here is the knocked down version. I am getting a parameter notice when I open the knocked down file. Am not receiving this in the full file. Just click cancel for now and it should not make a difference in what we are looking at. Look at the properties for the unbound combo box "myselector" and see if you can find an alternative to setting this up so that I only get the one record I want and not all the records that start with 34 as part of the ID. Thanks for looking at this. I hope you had lots of Birdies out there. It was great ski day. We had a dusting of 4 inches of fresh powder on top of 60 inch base. We have had over 200 inches of snow so far this year and they are predicting 18-24 inches over the next couple of days. WooHoo!!!