Parameter query not working correct.

[Jim Moberg]

Hi,

I have a query that joins table A to Table B via the loan number. Table A
contains a record for each loan and an associated load ID that it was
uploaded into our system under. Table B contains other information I need to
pull along with the loan number and load ID.

When run the query prompts the user for an upload ID. For one of the ID's
(1599) no data is coming up at all. I have opened up table A and verified
there are records in the table under that ID. I then opened up table B and
successfully did a lookup for one of the loan numbers under load ID 1599.

I have been 100% successful at entering other load ID's at run time and
pulling up data. I can't for the life of me figure out what is so different
about these records. Table A was created from spreadsheet of loan numbers
and load ID's that I created manually.

Can anyone think of what might be the cause here?

 

[Allen Browne]

Open the table in design view.
What is the data type of the [loan number] and [load id] fields?

If Text, be aware that the matching may be affected by invisible formatting
characters, such as spaces, embedded carriage returns, or null characters.
Access also has problems matching fields that contain certain characters
such as dashes.

If Number, make sure you declared the parameter (Parameter on Query menu, in
query design) with the same data type as the field. (Do not declare the
parameter for Text fields, as that can create other problems.)

If the number is a Double or Single, it is also possible that the display
value is not the exact value, so the match does not occur.

It would also be worth compacting the database:
Tools | Database Utilities | Compact/Repair
just in case an index has corrupted.

If you are still stuck after that, post the SQL statement, indicate the data
type and size of these 2 fields, and the specific values that fail.