07. marts 2010 - 18:50Der er
6 kommentarer og 1 løsning
Between .. And ..
Jeg har problemer med at faa BETWEEN .. AND .. til at virke. Jeg lavede Tabel1 med navn og dato og Tabel2 med pris, startdato og enddato. Jeg fyldte lidt data paa. Saa lavede jeg denne query: SELECT t1.navn, t2.pris FROM Tabel1 AS t1 INNER JOIN Tabel2 AS t2 ON t1.dato BETWEEN t2.startdato AND t2.enddato.
Det ville jeg mene skulle virke, og for at teste det lavede jeg i mysql tilsvarende tabeller og data og proevede den samme query som virkede.
Men i Access fik jeg denne besked (min oversaettelse fra hollandsk): "Du har brugt operatoren Between uden And. i forepsoergsels-expression t1.dato BETWEEN t2.star."
Jeg proevede saa i stedet med SELECT t1.navn, t2.pris FROM Tabel1 AS t1 INNER JOIN Tabel2 AS t2 ON t1.dato >= t2.startdato AND t1.dato <= t2.enddato. Det virkede fint.
Kan nogen hjaelpe mig at forstaa hvad der er galt med min BETWEEN ... AND.. expression? (Aabenbart er fejlmeldingen afkortet saa AND er udeladet, men jeg kan ikke tro at forespoergslen selv er afkortet. Jeg har lavet meget laengere forespoergsler.) Jeg har proevet alle kombinationer af paranteser uden held.
Teknologi, AI og forretning er i centrum på Computerworlds Cloud og AI Festival i København d. 18. og 19. september. Se hele programmet for den store konference om strategisk brug af Cloud og AI på: www.cloud-festival.dk
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?)
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.
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.
Synes godt om
Ny brugerNybegynder
Din løsning...
Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.