Build sql string - what to do

[Erik]

Hi, im trying to build a sql query that works through a form that allows the user to select different parameters (frmCriteria). My problem is that when i must join to tables in three of my comboboxes on frmCriteri and get an error. I thing this method by a frmCriteria is a well known method so it must be thats stupid here.. anyway.. here is the code im using in my function part of the 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

**cbo equales the combo boxes on frmCriteria and Valg# is the checkboxes used to activate the combobox.
sSQL = "SELECT " & sSelec
sSQL = sSQL & "FROM " & sFro
If sWhere <> "" Then sSQL = sSQL & "WHERE " & Mid$(sWhere, 6

BuildSqlString = Tru

End Functio

Any ideas on how to get this to work - its very important that Valg# is separate choises.. the user can chose one or more of this.

Hope someone can help me!

Thnx..

Eri

 

[Roger Carlson]

It depends a lot what the data types of your fields are. For instance, if
cboMVA is text, then it would be:

sWhere = " And i.[Registrert MVA] = '" & CboMVA & "'"

text valuse must be surrounded by quotes. Similarly, if CboSANO was a
date/time field, then you would need to surround it with pound signs (#)

sWhere = " And i.[Registrert MVA] = #" & CboMVA & "#"

Another thing to do to see where you are going wrong is to put Debug.Print
lines in your code at strategic spots to see how the strings are actually
being built. Like so:
If Valg1 = True Then
sFrom = sFrom & " Inner Join Selskap i " & " ON s.[organisasjonsnummer] =
i.[organisasjonsnummer]"
sWhere = " And i.[Registrert MVA] = " & CboMVA
End If
Debug.Print sWhere
Debug.Print sFrom

You will see how the code is being evaluated in the Immediate Window. If
you put it at the end, it will show you the entire string, which you can
copy and paste into a blank query. Running the query will give you more
help.
--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

Hi, im trying to build a sql query that works through a form that allows

the user to select different parameters (frmCriteria). My problem is that
when i must join to tables in three of my comboboxes on frmCriteri and get
an error. I thing this method by a frmCriteria is a well known method so it
must be thats stupid here.. anyway.. here is the code im using in my
function part of the form:

Function BuildSqlString(sSQL As String) As Boolean

Dim sSelect As String
Dim sFrom As String
Dim sWhere As String
Dim where As Long
Dim db As Database

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


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

**cbo equales the combo boxes on frmCriteria and Valg# is the checkboxes used to activate the combobox.
sSQL = "SELECT " & sSelect
sSQL = sSQL & "FROM " & sFrom
If sWhere <> "" Then sSQL = sSQL & "WHERE " & Mid$(sWhere, 6)

BuildSqlString = True

End Function

Any ideas on how to get this to work - its very important that Valg# is

separate choises.. the user can chose one or more of this.

 

[Erik]

Hi Roger..

Thank you for your help.. as always very helpful.. I now have found out what the error was. See below for the new code. I have put an "error" to the right of the code to mark out the "bad" parts.

Function BuildSqlString(sSQL As String) As Boolea
'tar ikke med kommmune her da dette vil begrense treff utfra begresningen på område
'må finne ut hvordan få med dette uten at svaret begrense

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

sSelect = "s.[Tips nummer] " 'Det må brukes hake parenteser rundt navn med mellomro
'sWhere = "1 = 1
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]" --erro
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]" --erro
sWhere = " And i.[Betydelige restanser] = " & CboRestanse
End If

 

[Roger Carlson]

Of course! <sound of hand slapping forehead> You are repeating the From line
in each If. That way if you have two values, you are putting the Join in a
multiple times. Since the From clause is the same for each IF, you should
take it out and place it above the first IF.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

Hi Roger..

Thank you for your help.. as always very helpful.. I now have found out

what the error was. See below for the new code. I have put an "error" to the
right of the code to mark out the "bad" parts.

Function BuildSqlString(sSQL As String) As Boolean
'tar ikke med kommmune her da dette vil begrense treff utfra begresningen på området
'må finne ut hvordan få med dette uten at svaret begrenses

Dim sSelect As String
Dim sFrom As String
Dim sWhere As String
Dim where As Long
Dim db As Database

sSelect = "s.[Tips nummer] " 'Det må brukes hake parenteser rundt navn med mellomrom
'sWhere = "1 = 1 "
sFrom = "Kontrollmelding s "

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]" --error

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]" --error

sWhere = " And i.[Betydelige restanser] = " & CboRestanser
End If