CODEWITHSUNDEEP
ms-access
Jim Vercoelen
Jim Vercoelen

Reputation: 1078

Query keeps giving error Access '13

I have this query and it isnt working correctly, but my knowledge says it should be!

ERD: enter image description here

Table 'Meetingen': enter image description here

Table 'Patienten': enter image description here

The query I have:

enter image description here

With the result by running: enter image description here

So, the strange thing here is; datum(date) BETWEEN 5/1/2015 AND 5/31/2015 should only give the persons from table 'meetingen' where the date is in May. But the result is, 'adres', 'postcode' and 'patientnr' from all 'Patienten'. Tryed everything... Does someone sees it?

EDIT Made one stupid mistake, m.patientnr = m.patientnr, already changed to p.patientnr = m.patientnr witch now gives the following error: enter image description here

Upvotes: 0

Views: 33

Answers (2)

Gustav
Gustav

Reputation: 55816

It is not date but datum:

Where ...
And datum Between #5/1/2015# And #5/31/2015#

Upvotes: 1

David
David

Reputation: 218837

BETWEEN 5/1/2015 AND 5/31/2015 should only give the persons from table 'meetingen' where the date is in May

That appears to be the case in the results. All of those dates are either 5/1/2015 or 5/30/2015, which is in the selected range.

But the result is, 'adres', 'postcode' and 'patientnr' from all 'Patienten'.

Yes, because those columns are on that table. Where else would you expect them to come from? If the columns existed on both tables then the query would result in an error indicating that it couldn't determine which column you were referring to. But since those columns are on only one table, the query can discern what to show and it shows you the results from that table.

Also, note:

WHERE m.patientNr = m.patientNr

This condition will always be true. Did you mean to do this instead?

WHERE m.patientNr = p.patientNr

You may want to investigate JOIN syntax to make table joins more explicit, rather than doing it in a WHERE clause. Separating the joins from the filters makes the intent more clear and errors easier to find.

Upvotes: 1

Related Questions