Query doesn't return all results

  • Thread starter Thread starter Evan McCutchen
  • Start date Start date
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
 
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
 
Back
Top