multi query

[Guest]

Hi,

im using a form with comboboxes to build a sql query, i have three choises (see code below) which depens on a inner join. Is this possible, i get error when i chose more than one of these (se below)..

If Valg1 = True The
sFrom = sFrom & " Inner Join Selskap i " & " ON s.[organisasjonsnummer] = i.[organisasjonsnummer]
sWhere = " And i.[Registrert MVA] = " & CboMV
End I
If Valg2 = True The
sFrom = sFrom & " Inner Join Selskap i " & " ON s.[organisasjonsnummer] = i.[organisasjonsnummer]
sWhere = " And i.[Levert selvangivelse og NO] = " & CboSAN
End I

If Valg5 = True The
sFrom = sFrom & " Inner Join Selskap i " & " ON s.[organisasjonsnummer] = i.[organisasjonsnummer]
sWhere = " And i.[Betydelige restanser] = " & CboRestanse
End I

Erik

 

[John Vinson]

Hi,

im using a form with comboboxes to build a sql query, i have three choises (see code below) which depens on a inner join. Is this possible, i get error when i chose more than one of these (se below)..

If Valg1 = True Then
sFrom = sFrom & " Inner Join Selskap i " & " ON s.[organisasjonsnummer] = i.[organisasjonsnummer]"
sWhere = " And i.[Registrert MVA] = " & CboMVA
End If
If Valg2 = True Then
sFrom = sFrom & " Inner Join Selskap i " & " ON s.[organisasjonsnummer] = i.[organisasjonsnummer]"
sWhere = " And i.[Levert selvangivelse og NO] = " & CboSANO
End If

If Valg5 = True Then
sFrom = sFrom & " Inner Join Selskap i " & " ON s.[organisasjonsnummer] = i.[organisasjonsnummer]"
sWhere = " And i.[Betydelige restanser] = " & CboRestanser
End If

Erik

What are you then doing with sFrom and sWhere? Since the value of
sFrom apparently does not depend on the combo box at all, should it
perhaps be defined outside the IF statements? The sWhere clause should
NOT be part of the INNER JOIN syntax, but rather of a WHERE clause;
and this clause will need to start with something other than the word
AND...

Could you post some more of the context of the code here, and perhaps
the error that you're getting?

 

[Guest]

Hi, im not very good at this, so here is my full code for building the sql string

(the "valg#" is a checkbox that activates the combo box on my form

Function BuildSqlString(sSQL As String) As Boolea

Dim sSelect As Strin
Dim sFrom As Strin
Dim sWhere As Strin
Dim where As Lon
Dim db As Databas

sSelect = "s.[Tips nummer] "
sFrom = "Kontrollmelding s

If Valg1 = True The
sFrom = sFrom & " Inner Join Selskap i " & " ON s.[organisasjonsnummer] = i.[organisasjonsnummer]
sWhere = " And i.[Registrert MVA] = " & CboMV
End I

If Valg2 = True The
sFrom = sFrom & " Inner Join Selskap i " & " ON s.[organisasjonsnummer] = i.[organisasjonsnummer]
sWhere = " And i.[Levert selvangivelse og NO] = " & CboSAN
End I

If Valg5 = True The
sFrom = sFrom & " Inner Join Selskap i " & " ON s.[organisasjonsnummer] = i.[organisasjonsnummer]
sWhere = " And i.[Betydelige restanser] = " & CboRestanse
End I

If Valg3 = True The
sWhere = sWhere & " And s.Tips_Aktuellperiode_Fra = " & cboAktuellperiodeFR
End I

If Valg4 = True The
sWhere = sWhere & " And s.TIPS_Aktuellperiode_Til = " & cboAktuellperiodeTI
End I

If Valg6 = True The
sWhere = sWhere & " s.[Berøringspunkter] = """ & CboBerøringspunkt & """
End I

If Valg7 = True The
sWhere = sWhere & " And s.OBS = " & CboOBSmerke & "" 'NB! her returneres en tall verd
End I

sSQL = "SELECT " & sSelec
sSQL = sSQL & "FROM " & sFro
If sWhere <> "" Then sSQL = sSQL & "WHERE " & Mid$(sWhere, 6

BuildSqlString = Tru

End Function