Its not an answer to your question, but still is a suggestion. Your statement's readability
could
[u]reall</u>y benefit from formatting. It is far too long to fit on a single
screen-width, and breaks lines at unfortunate places:
Code:
selectStr = "SELECT tblAvsnitt.OppgaveNr, tblAvsnitt.AvsnittsNr, tblAvsnitt.AvsnittNavn, " & _
" tblOppgaver.OppgaveNr, tblOppgaver.Oppgavenavn, tblTimeregistrering_behandlet.Dato, " & _
" Sum(tblTimeregistrering_behandlet.Ma_Timer) AS 'Summer av Ma_Timer', " & _
" tblTimeregistrering_behandlet.Oppgavenr" & VBCrLf & _
"FROM `F:\Tidsbanken\tidsbank`.tblAvsnitt tblAvsnitt, " & _
" `F:\Tidsbanken\tidsbank`.tblOppgaver tblOppgaver, " & _
" `F:\Tidsbanken\tidsbank`.tblTimeregistrering_behandlet tblTimeregistrering_behandlet" & VBCrLf & " & _
"WHERE tblAvsnitt.OppgaveNr = tblOppgaver.OppgaveNr " & _
" AND tblAvsnitt.OppgaveNr = tblTimeregistrering_behandlet.Oppgavenr " & _
" AND tblOppgaver.OppgaveNr = tblTimeregistrering_behandlet.Oppgavenr " & _
" AND tblAvsnitt.AvsnittsNr = tblTimeregistrering_behandlet.Avsnitt" & VBCrLf & _
"GROUP BY tblAvsnitt.OppgaveNr, tblAvsnitt.AvsnittsNr, tblAvsnitt.AvsnittNavn, " & _
" tblOppgaver.OppgaveNr, tblOppgaver.Oppgavenavn, tblTimeregistrering_behandlet.Dato, " & _
" tblTimeregistrering_behandlet.Oppgavenr" & VBCrLf & _
"HAVING ( tblTimeregistrering_behandlet.Dato >= {ts '" & d1 & " 00:00:00'} " & _
" And tblTimeregistrering_behandlet.Dato <= {ts '" & d2 & " 00:00:00'}" & _
" ) " & _
" AND tblTimeregistrering_behandlet.Oppgavenr='" & pnr & "' " & _
"ORDER BY tblAvsnitt.AvsnittsNr"
For one, it is a monumentally bad idea to use line-continuation in a way that actually breaks
the beginning from the end of a word. That makes maintaining the code
[u]very</u> hard!
Next, dividing the statement at divisions ('SELECT,' 'FROM,' 'WHERE,' et al.) of clauses can be
an aid to analysis.
Next, I'm not 100% sure about this, but I don't think you can have both a WHERE and a HAVING clause
in the same SQL statement. If I were youâjust for peace of mindâI would try a really simple SQL
statement that uses both HAVING and WHERE, to see whether they can be used together like you are
trying to do.
Finally, Visual Basic has intrinsic constants VBCR (ASCII 13), VBLF (ASCII 10), VBCRLF (ASCII 13
followed by ASCII 10) and VBTab (ASCII 9). They make your code clearer, becaseu they read like
they function, rather than requiring memorizing what all the different control codes do. But such
characters are "white space," and SQL interpreters ignore them. There is no benefit to adding them,
therefor. So:
Code:
selectStr = "SELECT tblAvsnitt.OppgaveNr, tblAvsnitt.AvsnittsNr, tblAvsnitt.AvsnittNavn, " & _
" tblOppgaver.OppgaveNr, tblOppgaver.Oppgavenavn, tblTimeregistrering_behandlet.Dato, " & _
" Sum(tblTimeregistrering_behandlet.Ma_Timer) AS 'Summer av Ma_Timer', " & _
" tblTimeregistrering_behandlet.Oppgavenr" & _
"FROM `F:\Tidsbanken\tidsbank`.tblAvsnitt tblAvsnitt, " & _
" `F:\Tidsbanken\tidsbank`.tblOppgaver tblOppgaver, " & _
" `F:\Tidsbanken\tidsbank`.tblTimeregistrering_behandlet tblTimeregistrering_behandlet " & _
"WHERE tblAvsnitt.OppgaveNr = tblOppgaver.OppgaveNr " & _
" AND tblAvsnitt.OppgaveNr = tblTimeregistrering_behandlet.Oppgavenr " & _
" AND tblOppgaver.OppgaveNr = tblTimeregistrering_behandlet.Oppgavenr " & _
" AND tblAvsnitt.AvsnittsNr = tblTimeregistrering_behandlet.Avsnitt" & _
"GROUP BY tblAvsnitt.OppgaveNr, tblAvsnitt.AvsnittsNr, tblAvsnitt.AvsnittNavn, " & _
" tblOppgaver.OppgaveNr, tblOppgaver.Oppgavenavn, tblTimeregistrering_behandlet.Dato, " & _
" tblTimeregistrering_behandlet.Oppgavenr" & _
"HAVING ( tblTimeregistrering_behandlet.Dato >= {ts '" & d1 & " 00:00:00'} " & _
" And tblTimeregistrering_behandlet.Dato <= {ts '" & d2 & " 00:00:00'}" & _
" ) " & _
" AND tblTimeregistrering_behandlet.Oppgavenr='" & pnr & "' " & _
"ORDER BY tblAvsnitt.AvsnittsNr"
I seriously doubt that the expression is too long;
SQL interpreters are built to take really complex statements. I don't know if this helps, but it is
what I have.
Run-time error -2147217900 (80040e14)
