Final SQL problem - but it ain't too easy

[Bazzlad]

My final query to complete my database.....

Here goes....

I have a VBA form with loads of options, and I want to build a query around these options.

As it is I think I need to use some sort of IF, THEN, ELSE of CASE statement, inside SQl, but I don't know if you can.


Example. I have 2 checkboxes.
One is
Repaired
the other is
Both


Now I can't reference them both in SQL because they apply to the same field on the table.

To work round this I built a textbox, and dependant on what was box is clicked it gives the textbox a value

1 = neither (which I class as NOT repaired)
2 = repaired
3 = both

Now in the query I want it to say something like:

select * from tblproblem

where case

case forms!frmtest!text1.value = 1
[repaired] = no

case forms!frmtest!text1.value = 2
[repaired] = yes

case forms!frmtest!text1.value = 1
' Don't call anything

end;

[shaithis]

Build your select statement in a varchar string and then execute that.......not sure about all your forms stuff as I only deal with pure SQL, I leave the VBA stuff to the web monkeys

i.e.

Declare @stmt varchar(255)

Set @stmt = 'select * from tblproblem where Repaired = '

If case forms!frmtest!text1.value = 1
Begin
set @stmt = @stmt + '''no'''
exec (@stmt)
end

if case forms!frmtest!text1.value = 2
Begin
set @stmt = @stmt + '''yes'''
exec (@stmt)
end


if case forms!frmtest!text1.value = 3
Begin
end

[Spud1]

yup looks to be the best way

If you were using oracle you could do it with PL/SQL (which supports decision statements), but just dynamically create your query in a similar way to above, since i guess your using mysql or access?

[Bazzlad]

bloody Access

[Bazzlad]

Cheers, I sorted it now.

For the interested I did it like this.....

Private Sub Command56_Click()

    Dim sSQL As String
    sSQL = "SELECT * FROM tblproblem "


    If Repaired.Value >= 0 Then

    Select Case Repaired.Value
    Case 1
    sql = sSQL & "WHERE repaired = no "
    GoTo 10
    
    Case 2
    sql = sSQL & "WHERE repaired = yes "
    GoTo 10
    
    Case 3
    sql = sSQL & "Where repaired between No and Yes "
    GoTo 10
        
End Select
    
    Else
    
        MsgBox "Error, please fill in all values", , "The Tech Database"
        
    Exit Sub
    
10

    If ext.Value >= 0 Then

    Select Case ext.Value
    Case 1
    asql = sql & "AND [external help enlisted] = 0 "
    GoTo 20
    
    Case 2
    asql = sql & "AND [external help enlisted] = -1 "
    GoTo 20
    
    Case 3
    asql = sql & "AND [external help enlisted] = 0 or -1 "
    GoTo 20
        
End Select
    
    Else
    
        MsgBox "Error, please fill in all values", , "The Tech Database"
        
    Exit Sub

20

    CurrentDb.QueryDefs("TempQuery").sql = asql & ";"
    DoCmd.OpenQuery "TempQuery"
    
    End If
    End If
    
End Sub