A problem with a simple query MS Access 10

I have a simple query that I use to populate a listbox with transactions, selecting the required account by preloading a property.  It will select one account but not the other.

I have attached a small simple test database to demonstrate the problem.

This is driving me mad and I would greatly appreciate any help I can get.

Thanks in advance,

Clive
Clive BeatonAccess DeveloperAsked:
Who is Participating?
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
This works ... when all combo values are in the Import Table.
Database1MX01.accdb
0
 
Clive BeatonAccess DeveloperAuthor Commented:
DB attached.
Database1.accdb
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
There is something wrong with the data in the 2/3 values in the combo

see image - note Spaces or what ever they aredata
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Clive BeatonAccess DeveloperAuthor Commented:
Thanks, Joe.

Can you suggest any way of discovering what the leading characters are?  Standard Access things I've tried, don't throw any light.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
more data
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Well ... not sure that is the problem.  Notice the combo  box account selected above is not in Import table.  So that is the actual problem
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
The spaces are WEIRD.  In the Immediate Window, Trim removes the Spaces ... so they must be spaces.
Not sure what is going on
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Two out of three of the numbers in the combo are not in the Import table.
So ... it's actually working correctly ...
0
 
Clive BeatonAccess DeveloperAuthor Commented:
Thanks, Joe.

I changed the query on the account field to read the accounts table, and one of the account numbers was corrupted.  Fixed that and now all is good.

Clive
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Cool.  Don't forget to close the question :-)
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
OP confirmed that  my solution worked
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.