Query doesn't return all results

[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

 

[ack]

For the companies that don't have reps, add a rep
called "No Reps."

 

[Evan]

Is there any other way around it????

 

[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