Query doesn't return all results

E

Evan McCutchen

Hello,

I have a query that when a partial company name is entered, it is supposed
to return all representatives of all companies that have that partial
company name (the companies and reps are stored in different tables - linked
by companyIDs). It works, unless a company doesn't have any reps. Does
anyone know how to fix this? I have the relation set to option 3 (accessXP).

thanks!
Evan M.
evan AT radiologyonesource DOT com
 
M

Michel Walsh

Hi,


Make an outer join rather than an inner join, keeping ALL the records form
the Company side. In your WHERE clause, handle the possibility that the
representation IS NULL:

.... WHERE (Rep LIKE whatIsEnter & "*") OR ( Rep IS NULL )

or just

.... WHERE Nz(Rep LIKE whatIsEnter & "*", True)


Hoping it may help,
Vanderghast, Access MVP
 
Top