SQL VBA WHERE Clause Issue

[silentwolf]

Hi guys,

I am again a bit stuck and not really be able to work it out on my own.

Private Sub Form_Load()
    Dim strSQL As String
    Dim strSQLSelect As String
    Dim strSQLWHERE As String

'Combobox Aktualisieren
    With Me!cboObjStatus
        .AddItem "(Alle)"
        .AddItem "Aktiv"
        .AddItem "Inaktiv"
    End With

'Standartwerte Setzen
    Me!cboOrtAuswahl = Me.cboOrtAuswahl.ItemData(0)
    Me!cboObjStatus = Me!cboObjStatus.ItemData(1)
   
'Basisabfrage(SELECT TEIL)
    strSQL = "SELECT tblObjekte.objID, tblObjekte.objAdresse, tblObjekte.objOrt, IIf([kunFirma] Is Null,[kunVorname] & "" "" & [kunNachname],[kunFirma]) AS Kontakt, tblObjekte.objAktiv " & _
    "FROM tblKunden INNER JOIN tblObjekte ON tblKunden.kunID = tblObjekte.objkunIDREF " & _
    "ORDER BY tblObjekte.objID;"

'Erstes Kombinationsfeld auswerten
    If IsNull(Me!cboOrtAuswahl) Then
        MsgBox "Nothing selected"
    Else
        MsgBox "Es wurde " & Me!cboOrtAuswahl.Value & " ausgewählt"
[COLOR=#ff0000]'here I am nor sure how to get the cboOrtAuswahl to filter my SQL Statement ....objOrt is a field [/COLOR]        
        strSQLWHERE = "objOrt =" & Me!cboOrtAuswahl
    End If
    
'SELECT TEIL zu strSQL Hinzufügen
    strSQL = strSQLSelect

    Me!lstObjekte.RowSource = strSQL
    Me!lstObjekte.Requery

End Sub

I marked above where the issue is and I am not able to work it aout..
The combobox returnes the value what I select in the combobox but the strSQLWHERE is not able to update... It is most likely again a small thing but for me a big one

Hope someone could help??

Many thanks

Albert

 

[CharlesChuckieCharles]

Its a reference problem


instead of


strSQLWHERE = "objOrt =" & Me!cboOrtAuswahl



try using


strSQLWHERE = "objOrt =" & Me!cboOrtAuswahl.ItemData(0)

or (1) or where ever the target STRING is

 

[silentwolf]

Hi CharlesChuckieCharles,

many thanks for your reply.. Unfortunately I am still struggling with makeing it work..
In fact I am trying to use two comboboxes one called cboOrtAuswahl wich is supose to filter the Town.. and another to filter either if the objekt is aktive or not ... and all..
In my form I have those two comboboxes and one listbox...

The cboOrtAuswahl gets its Value from a table not linked to any other tables... tblOrte .. ortID and ortName(text)
the cboObjStatus gets its Values from Value List.. All;Aktiv; Inaktiv, set in form_Load event with addItem...

The SQL Statement shows the Ort=Town in the 4 column and the objAktiv in column 5

objAktiv is a Yes/No field..

I am trying to get an example from a book working for my needs but am struggling alot to make this work..

Maybe you could give me a hand with this? Would be much appreciated!

Albert

 

[CharlesChuckieCharles]

Ort=Town

Maybe it should equal

Ort='Town'

 

[silentwolf]

Hi Charles,

sorry about that I was just meant that ort means town in english

I misslead this I do appologize about this!!

 

[Norie]

Albert

Why do you have 3 different string variables - strSQL, strSQLSelect and strSQLWHERE - apparently related to the SQL?

Also, where are you actually using strSQLWHERE and where are you setting the value of strSQLSelect?

Actually I'm pretty confused by the whole code to be honest.

For a start you set strSQL here,

    strSQL = "SELECT tblObjekte.objID, tblObjekte.objAdresse, tblObjekte.objOrt, IIf([kunFirma] Is Null,[kunVorname] & "" "" & [kunNachname],[kunFirma]) AS Kontakt, tblObjekte.objAktiv " & _
    "FROM tblKunden INNER JOIN tblObjekte ON tblKunden.kunID = tblObjekte.objkunIDREF " & _
    "ORDER BY tblObjekte.objID;"

then you set strSQLWHERE here,

    If IsNull(Me!cboOrtAuswahl) Then
        MsgBox "Nothing selected"
    Else
        MsgBox "Es wurde " & Me!cboOrtAuswahl.Value & " ausgewählt"
'here I am nor sure how to get the cboOrtAuswahl to filter my SQL Statement ....objOrt is a field         
        strSQLWHERE = "objOrt =" & Me!cboOrtAuswahl
    End If

but nowhere do you connect the two variables.

Then there's this,

  strSQL = strSQLSelect

which, since strSQLSelect hasn't been set to any value, sets strSQL to an empty string.

 

[silentwolf]

Hi Norie,

it is a example from a book and he the autor has used three SQL Variables to have firstly the strSQL, 2. the strSQLWHERE for the first combobox.. and strSQLSELECT for the second combobox..

I like the listbox to be filtered in either cboOrtAuswahl and if the cboObjektStatus..

So I could find alle Objects in certain towns.. but also filter if the Object is aktive or inaktive (YES/No) field and if there is all selected than there should be all eiter aktiv or inaktiv Objekts be shown..
I am pretty hopeless with SQL and filter but would like to do it with SQL so I have not hundrets of queries in my database...

Hope this is a bit more clear?

Albert

 

[Norie]

Albert

It's clear what you want to do but the code isn't clear at all.

If you were constructing an SQL that was to include a WHERE clause I would expect to see the code that creates the WHERE clause before the code that creates the full SQL statement.

So something like this.

Private Sub Form_Load()
Dim strSQL As String
Dim strSQLWHERE As String

    'Combobox Aktualisieren
    With Me!cboObjStatus
        .AddItem "(Alle)"
        .AddItem "Aktiv"
        .AddItem "Inaktiv"
    End With

    'Standartwerte Setzen
    Me!cboOrtAuswahl = Me.cboOrtAuswahl.ItemData(0)
    Me!cboObjStatus = Me!cboObjStatus.ItemData(1)

    'Erstes Kombinationsfeld auswerten
    If IsNull(Me!cboOrtAuswahl) Then
        MsgBox "Nothing selected"
    Else
        MsgBox "Es wurde " & Me!cboOrtAuswahl.Value & " ausgewählt"
        'here I am nor sure how to get the cboOrtAuswahl to filter my SQL Statement ....objOrt is a field
        strSQLWHERE = "objOrt ='" & Me!cboOrtAuswahl & "'"
    End If
    
    'Basisabfrage(SELECT TEIL)
    strSQL = "SELECT tblObjekte.objID, tblObjekte.objAdresse, tblObjekte.objOrt, IIf([kunFirma] Is Null,[kunVorname] & "" "" & [kunNachname],[kunFirma]) AS Kontakt, tblObjekte.objAktiv " & _
             "FROM tblKunden INNER JOIN tblObjekte ON tblKunden.kunID = tblObjekte.objkunIDREF " & _
             strSQLWHERE & _
             "ORDER BY tblObjekte.objID;"

    Me!lstObjekte.RowSource = strSQL
    Me!lstObjekte.Requery

End Sub

Another thing that I don't quite understand is why the code is in the Form_Load event sub.

That event is triggered when the form initially opened, I would have thought if you wanted to filter using the combobox(es) the code would be in something like a Change event.

 

[CharlesChuckieCharles]

Ort=Town

Maybe it should equal

Ort='Town'

I meant that is the SQL if the comparitor is a string it should be surrounded by ' in the statement even if the source is a control

Select * from cities where country = 'United States'

would be coded

"Select * from cities where country = " & "'" & cboCountries.text & "'"

 

[xenou]

        strSQLWHERE = "objOrt ='" & Me!cboOrtAuswahl & "'"

Also, the where clause doesn't have WHERE in it.

Needs to be:

        strSQLWHERE = " WHERE objOrt ='" & Me!cboOrtAuswahl & "'"

You should always debug.print your string sql so you can inspect it and even run it in a sql query window - this is the best way to make sure it is right.