MS Access 2007 Query Returns No Results

Question

I am using the following query to find client who are from the medicine department:

SELECT client.clientID, client.firstname, client.lastname, client.organization,
       client.department, client.email, events.Date, events.title,
       eventAttendance.eventID, eventAttendance.clientID,
       eventAttendance.attended
FROM client INNER JOIN 
     (events INNER JOIN eventAttendance ON events.id = eventAttendance.ID) 
     ON client.clientID = eventAttendance.clientID
WHERE client.organization like '%medicine'

In the table, the possibilities are "School of Medicine" and "Department of Medicine;" however, no records are returned. What am I missing?

Thank you.

score 2 · Accepted Answer · answered Oct 04 '11 at 16:52

2

The wild card character in Access is a * not an %

change your sql like so:

like '*medicine'

answered Oct 04 '11 at 16:52

Aducci

26,101
8
63
67

This did return some more results. When I try WHERE client.organization = 'School of Medicine', nothing is returned. Why would that be? – aparker81 Oct 04 '11 at 17:24
@aparker81 - Not sure, but I would guess that there are some extra characters padded on the front or the end of the text. You can test this theory by trying this: client.organization like '*School of Medicine*' – Aducci Oct 04 '11 at 17:36
Thanks for the suggestion. It did not return the results. Perhaps I'll make another question out of it. – aparker81 Oct 04 '11 at 17:50
@aparker81- Sorry I left out the *, I meant: client.organization like '*School of Medicine\*' – Aducci Oct 04 '11 at 17:55
2

The wildcard character in Access can be either `*` or `%` depending on ANSI Query Mode or can be `%` regardless of ANSI Query Mode by using the `ALIKE` keyword. For more details, see [this question](http://stackoverflow.com/questions/719115/microsoft-jet-wildcards-asterisk-or-percentage-sign/720896#720896). – onedaywhen Oct 05 '11 at 09:06

score 0 · Answer 2 · answered Oct 04 '11 at 17:55

I think it is likely that you do not always have an event, so you need LEFT JOIN:

SELECT client.clientID, client.firstname, client.lastname, client.organization,
       client.department, client.email, events.Date, events.title,
       eventAttendance.eventID, eventAttendance.clientID,
       eventAttendance.attended
FROM client LEFT JOIN 
     (events LEFT JOIN eventAttendance ON events.id = eventAttendance.ID) 
     ON client.clientID = eventAttendance.clientID
WHERE client.organization like '*medicine'

MS Access 2007 Query Returns No Results

2 Answers2