Between .. And ..

Try ...
WHERE t1.dato BETWEEN t2.startdato AND t2.enddato

I

If you mean "SELECT t1.navn, t2.pris FROM Tabel1 t1 INNER JOIN Tabel2 t2 WHERE t1.dato BETWEEN t2.startdato AND t2.enddato" then we have a JOIN without ON which will not work.  I tried it anyway with the expected result, syntax error.

But what I had not thought about is the "oldfashioned" way "SELECT t1.navn, t2.pris FROM Tabel1 t1, Tabel t2 WHERE t1.dato BETWEEN t2.startdato AND t2.enddato" .  Was that what you have in mind?  By the way it works.

However, I am reluctant to believe that Access SQL is not designed to handle JOINs with BETWEEN .. AND.  I am reading Roger Jenning's 'Microsoft Office Access 2007 In Depth' where he says, for example:  "SELECT t1.navn, t2.pris
FROM Tabel1 t1 INNER JOIN Tabel2 t2 ON t1.dato >= t2.start AND t1.dato <= t2.end."  (And I tried an example with #2010-01-07# etc. with no success.)  This example does not specify JOIN, but Jennings advises against using the "old fashioned" way and advocates always using JOIN, and I am sure that he would have mentioned if we have to use the old fashioned way in this case.

I noticed that the errormessage is abbreviated, rather than saying "Du har brugt operatoren Between uden And. i forepsoergsels-expression t1.dato BETWEEN t2.start AND t2.end" it says: "Du har brugt operatoren Between uden And. i forepsoergsels-expression t1.dato BETWEEN t2.star."  This is not because of exceeding a limit for length of string in the messagebox because if I change the name from t2.start to t2.startdato the errormessage ends on "..t2.startdat." and whatever name I use the errormessage skips the last letter and replaces it with "."  So the errormessage does not quote the 'AND' and maybe the query for some reason stops before coming to the 'AND.'

Terry (or anybody else,) can you imagine a reason why I cannot use JOIN with BETWEEN .. AND?  Does JOIN with BETWEEN..AND work on your system (if I could convince you to try it out?)

Christian is it possible for you to send me an example of the database your using for the Jennings examples?

Also the queries you can or cant get to work?

ekspertenATsanthell.dk

It is underway (Access 2007.)  I created two simple tables, one with one date field and one with two date fields, and I tried to join them, as I can in mysql, on table1.dato BETWEEN table2.startdato AND table2.enddato, but then I get the errormessage that I have made a query with Between without And. in the expression Between table2.startdat.  The two fullstops are part of the message.  It looks like the system stops reading the query statement one character before the end of the first value.  If I change the name for example to start the errormessage will include star.

SELECT t1.navn, t2.pris, t1.dato, t2.startdato, t2.enddato FROM Tabel1 AS t1 INNER JOIN Tabel2 AS t2 ON (t1.dato BETWEEN t2.startdato AND t2.enddato)

Terry, you modify my query in two ways:  (1) you add the three dato fields used in the BETWEEN..AND criterium and (2) you place "t1.dato BETWEEN t2.startdato AND t2.enddato" in brackets.

I have tested your solution - modification (1) is not neccessary (thus no need to include the fields in order to use them as join criteria).  The key is the bracketing.

The million-dollar question is why I did not figure this out.  I believed that I tried all bracket combinations but apparantly not this one.  One study in itself is how Access sql differs from conventional sql that for example does not need this bracket.  I also noticed that Access for non-outer joins requires "INNER JOIN" where for example mysql is satisfied with "JOIN."

The good news and the bad news are that I am slowly making progress in Access.  The "making progress" bit is the good news, the "slowly" bit is the bad news.

Thanks again for the help.  You shall no doubt see more questions from me on the subject of Access.

I only added the from to dates so I could see that it was working OK, forgot to remove them.

"One study in itself is how Access sql differs from conventional sql"


There are many SQL dialects and they can differ in many ways. Access and SQL server (Microsoft) are in many ways similar.

MySQL and Oracle differ quite a lot.