Query not returning a record it should

[robbeh]

I have two tables,

Entry
Exit

Both tables record data on a user when they first come in and when they leave. So same fields, just different data in both tables (minus the UniqueID).

UniqueID (PK) is set up as Text with an Input Mask of AB0000000 (AB is always AB but the 0000000 changes depending on user, example: AB0132344). Also Indexed (No Duplicates)



Issue:

I have a query that has a relationship between the two UniqueIDs, it works for every ID but this one AB01034219. I realized somehow the Exit table shows the ID was AB34219 (missing 010) not sure if someone went in there in changed it or how that happened.

I manually changed it back to: AB01034219 in the Exit table, the problem is, the Query won't pick up on it. The UniqueID matches in both tables but the query just doesn't want to return it.

Is it because I manually changed it? Is it the Input mask on the UniqueID or the fact it's been manually changed?

Thanks,
Robb

[ranman256]

No, manually changing wont affect it.

nOW, IF you can go to each table , and FILTER that # in both, to show they exist,
then it MUST work...IF you are joining on ID.

IF they are truly identical and you are NOT looking thru a mask that gives you the wrong info. (what you think youre seeing)

(check for Ohs instead of zeros)

[robbeh]

No, manually changing wont affect it.

nOW, IF you can go to each table , and FILTER that # in both, to show they exist,
then it MUST work...IF you are joining on ID.

IF they are truly identical and you are NOT looking thru a mask that gives you the wrong info. (what you think youre seeing)

(check for Ohs instead of zeros)

I can filter (it works), I have inspected the entry over and over. They are identical. Definitely joined on the UID for the query.

The only thing I can think of is the MASK. Initially when a record is added it's via VBA, when I go to manually enter I can only enter the numberic portion of the ID (the AB is static there).

How can I look at the fields without the Mask? I think this might be where the problem lies.

[ranman256]

copy a row and paste into excel, then inspect the field.

[robbeh]

Thanks! Yeah they match in excel. You can see them line up in the image and you can see the link in the query.

[robbeh]

This might help as well, I grabbed the query in SQL form:

SELECT <a bunch of fields> [Exit Table].Date, [Exit Table].Something etc..., [Entry Table].FirstName, [Entry Table].LastName
FROM [Exit Table]
INNER JOIN [Entry Table]
ON [Exit Table].txtUniqueNumber = [Entry Table].txtUniqueNumber

Keep in mind, with every other entry it works fine!! I just can't figure out why on earth it won't work for the record I changed manually? If someone deleted the record from the query, would it show again if I re-ran it? I just can't figure out why this one bugger won't show.

[IrogSinta]

I personally avoid using input masks as much as possible. Why not try copying your corrected number from the Exit table to the other one?

Ron

[robbeh]

I personally avoid using input masks as much as possible. Why not try copying your corrected number from the Exit table to the other one?

Ron

I initially tried that and it didn't work. One thing I have found. If I delete everything up to the AB (since I can't delete past AB) and instead of typing "AB"...######, I just type the number it works.

The only issue is, the number is displayed without the AB everywhere it's called in forms. To to clarify:

When I type in the table, I enter the field and delete everything and I am left with:

AB_______

If I type "AB" it will still read AB______, then if I type the number it's stored as AB#######. If I delete everything again, I am left with AB______, I type JUST the number and I am left with AB###### in the table but everywhere else it just reads #######.

So it's the AB/mask that's causing the issue, just not sure how to resolve because when I type AB, the query doesn't grab it.

[IrogSinta]

So you set up the mask in the table itself? Again, I prefer not using masks but if you wanted to, you should use them in a form on the format property of the control bound to this field.

Ron

[robbeh]

Oh believe me I couldn't agree more. I should have clarified I inherited this project. If everything is stored without the mask anyways I am going to try and remove it for good and see if that helps.

it's just the strangest thing how it works when I just put numbers but not when I put in two preceding letters.

[robbeh]

Ok so I figured out the issue.

Both tables had an input mask on the Text field for Unique ID - "AB"0000000

The database actually creates the record using VBA. Since the users didn't know the database Masked the field, they were putting in AB1234567 (these are unique file IDs so they are assigned manually but are unique).

The issue was the Entry database has the proper AB####### entered via code, so it looked fine - even when I copied it to Excel. However, no matter how I tried to correct the Exit field, I could never actually enter "OT". Therefore the it never matched. I removed the mask off both tables and sure enough one had AB in front of it, the other didn't.

I corrected it and voila it worked!

Thanks for everyone's help, even if your solution didn't work - it got us one step closer.