SQL generation

D

datacontrol

Registered User.
Local time
Today, 23:17
Joined
Jul 16, 2003
Messages
142
Hello!

I have a system of forms/subforms and queries set up. I got most of this code from a database already created called "sql example"

I need to revamp my code so that my form works correctly. There are 3 fields (combo boxes) from which the user selects a database item and a subform (appears below the combo boxes) with query results displayed.

So, what I need to do is query upon these fields (the combo boxes). I will need to create some conditional statement that covers all my bases, so that not matter what combination of the three combo boxes are selected, then an appropriate query is generated . For instance, if combo 3 if left null (empty) then the other 2 combos will represent the query and vice cersa, covering all possible combinations.

Please, I know this one is difficult, and I appreciate any help. My db is too large to attach here.

here is my current form (form1) code:

Option Compare Database
Option Explicit
Dim sqlvital, Queryflag, stDocName, SQLbase, SQLtemp, SQLwhole
Dim dbs As Database, qdf As QueryDef, rst As Recordset, Weekdef

Private Sub CmdReset_Click()
Reset
End Sub
Private Sub Combo1_Change()
SQLgen
Refresh
End Sub

Private Sub Combo2_Change()
SQLgen
Refresh
End Sub

Private Sub Combo3_Change()
SQLgen
Refresh
End Sub

Private Sub Combo4_Change()
SQLgen
Refresh
End Sub


Private Sub Form_Load()
DoCmd.Maximize
Reset
End Sub

Sub Reset()
Combo1.Value = ""
Combo2.Value = ""
Combo3.Value = ""
Combo4.Value = ""
SQLgen
Refresh
End Sub

Sub SQLgen()
On Error GoTo Err_SQLgen

' SQL Generation routine
SQLQuerysubform.SourceObject = "Query1subform"
SQLbase = "SELECT tbldst.dst_user, tbldst.dst_task_id, tbldst.dst_date FROM tbldst"
sqlvital = ";"
Queryflag = 0

'Combo1
If Combo1.Value = "" Then
Queryflag = 0
' "Combo1 All"
Else
Queryflag = 1
sqlvital = " WHERE (((tbldst.dst_user)=[Forms]![Form1]![Combo1])"
End If

'Combo2
If Combo2.Value = "" Then
'something
Else
If Queryflag = 1 Then
SQLtemp = " AND ((tbldst.dst_task_id)=[Forms]![Form1]![Combo2])"
LabelSQL.Caption = SQLtemp
sqlvital = sqlvital & SQLtemp
Else
Queryflag = 1
sqlvital = " WHERE (((tbldst.dst_task_id)=[Forms]![Form1]![Combo2])"
End If
End If

'Combo3
If Combo3.Value = "" Then
'something
Else
If Queryflag = 1 Then
SQLtemp = " AND ((tbldst.dst_date)=[Forms]![Form1]![Combo3])"
LabelSQL.Caption = SQLtemp
sqlvital = sqlvital & SQLtemp
Else
Queryflag = 1
sqlvital = " WHERE (((tbldst.dst_date)=[Forms]![Form1]![Combo3])"
End If '
End If

'Combo4
'If Combo4.Value = "" Then
'something
'Else
'If Queryflag = 1 Then
'SQLtemp = " AND ((DATA.HomePhone)=[Forms]![Form1]![Combo4])"
'LabelSQL.Caption = SQLtemp
'sqlvital = sqlvital & SQLtemp
'Else
'Queryflag = 1
'sqlvital = " WHERE (((DATA.HomePhone)=[Forms]![Form1]![Combo4])"
'End If '
'End If

' Delete old Query
stDocName = "SQLQuery"
DoCmd.DeleteObject acQuery, stDocName

' Return reference to current database.
Set dbs = CurrentDb

' Create new query.
Set qdf = dbs.CreateQueryDef("SQLQuery")

' Construct SQL statement including parameters.
' the following is the master SQL, SQLvital contains essential SQL clause
' Depending on selection
If sqlvital <> ";" Then sqlvital = sqlvital & "); "

qdf.SQL = SQLbase & sqlvital

SQLwhole = qdf.SQL
LabelSQL.Caption = SQLwhole
Set dbs = Nothing

SQLQuerysubform.SourceObject = "SQLQuerysubform"
Refresh

Exit_Err_SQLgen:
Exit Sub

Err_SQLgen:
LabelSQL.Caption = SQLbase & sqlvital
MsgBox "Error in SQL formula, try again !", vbCritical, "Error!!!"

End Sub

Private Sub Form_Resize()
SQLQuerysubform.Height = (Me.InsideHeight - SQLQuerysubform.Top) - 1000
SQLQuerysubform.Width = (Me.InsideWidth - 1000)
End Sub
 
Does this answer your question?

Code: 
Sub SQLgen()
On Error GoTo Err_SQLgen

    ' SQL Generation routine
    SQLQuerysubform.SourceObject = "Query1subform"
    SQLbase = "SELECT tbldst.dst_user, tbldst.dst_task_id, tbldst.dst_date FROM tbldst"

    Dim iLp As Integer
    
    For iLp = 1 To 4
        If Me.Controls("Combo" & iLp).Value <> "" Then
            If sqlvital <> "" Then
                sqlvital = sqlvital & " AND "
            End If
            Select Case iLp
                Case 1: sqlvital = sqlvital & " [tbldst].[dst_user]=" & Me.Controls("Combo" & iLp)
                Case 2: sqlvital = sqlvital & " [tbldst].[dst_task_id]=" & Me.Controls("Combo" & iLp)
                Case 3: sqlvital = sqlvital & " [tbldst].[dst_date]=" & Me.Controls("Combo" & iLp)
                Case 4: sqlvital = sqlvital & " [DATA].[HomePhone]=" & Me.Controls("Combo" & iLp)
            End Select
    Next iLp
        
    ' Construct SQL statement including parameters.
    ' the following is the master SQL, SQLvital contains essential SQL clause
    ' Depending on selection
    If sqlvital <> "" Then
        sqlvital = " Where " & sqlvital
    End If
    sqlvital = sqlvital & ";"
    
    ' Delete old Query
    stDocName = "SQLQuery"
    DoCmd.DeleteObject acQuery, stDocName
    
    ' Return reference to current database.
    Set dbs = CurrentDb

    ' Create new query.
    Set qdf = dbs.CreateQueryDef("SQLQuery")

    qdf.SQL = SQLbase & sqlvital
    
    SQLwhole = qdf.SQL
    LabelSQL.Caption = SQLwhole
    Set dbs = Nothing
    
    SQLQuerysubform.SourceObject = "SQLQuerysubform"
    Refresh

Exit_Err_SQLgen:
Exit Sub

Err_SQLgen:
LabelSQL.Caption = SQLbase & sqlvital
MsgBox "Error in SQL formula, try again !", vbCritical, "Error!!!"

End Sub
 
I tried to implement this, I get an error...."Next without for"

Next iLp.......


Thanks!
 
Code: 
Sub SQLgen()
On Error GoTo Err_SQLgen

    ' SQL Generation routine
    SQLQuerysubform.SourceObject = "Query1subform"
    SQLbase = "SELECT tbldst.dst_user, tbldst.dst_task_id, tbldst.dst_date FROM tbldst"

    Dim iLp As Integer
    
    For iLp = 1 To 4
        If Me.Controls("Combo" & iLp).Value <> "" Then
            If sqlvital <> "" Then
                sqlvital = sqlvital & " AND "
            End If
            Select Case iLp
                Case 1: sqlvital = sqlvital & " [tbldst].[dst_user]=" & Me.Controls("Combo" & iLp)
                Case 2: sqlvital = sqlvital & " [tbldst].[dst_task_id]=" & Me.Controls("Combo" & iLp)
                Case 3: sqlvital = sqlvital & " [tbldst].[dst_date]=" & Me.Controls("Combo" & iLp)
                Case 4: sqlvital = sqlvital & " [DATA].[HomePhone]=" & Me.Controls("Combo" & iLp)
            End Select
       [COLOR=red]End If[/COLOR]  'Copy and Paste (Best Friend Worst Enemy)
    Next iLp
        
    ' Construct SQL statement including parameters.
    ' the following is the master SQL, SQLvital contains essential SQL clause
    ' Depending on selection
    If sqlvital <> "" Then
        sqlvital = " Where " & sqlvital
    End If
    sqlvital = sqlvital & ";"
    
    ' Delete old Query
    stDocName = "SQLQuery"
    DoCmd.DeleteObject acQuery, stDocName
    
    ' Return reference to current database.
    Set dbs = CurrentDb

    ' Create new query.
    Set qdf = dbs.CreateQueryDef("SQLQuery")

    qdf.SQL = SQLbase & sqlvital
    
    SQLwhole = qdf.SQL
    LabelSQL.Caption = SQLwhole
    Set dbs = Nothing
    
    SQLQuerysubform.SourceObject = "SQLQuerysubform"
    Refresh

Exit_Err_SQLgen:
Exit Sub

Err_SQLgen:
LabelSQL.Caption = SQLbase & sqlvital
MsgBox "Error in SQL formula, try again !", vbCritical, "Error!!!"

End Sub
 
That's a very nice piece of coding, Travis. A pleasure just to look at it, let alone trawl through the craftsmanship there. Excellent. I'm sure datacontrol appreciates the trouble you've gone to. Cheers
 
Yes, thank you..it does look like a good piece of code. However, I haven't been able to get it to work just yet.

I am going to keep trying!
 
Where are you having trouble?

Is the query not returning the values?

Are you getting an error?

I want to help.
 
I haven't had much time to look at it yet, however it looks as if the sql statement isn't being appended correctly.

it reads ...........FROM tbldst Where ; AND...............


I can't attach my entire db here....may I zip and email it to you? I also need to add combo boxes to search between dates, ie instead of home phone.


Contents of current form:

Sub SQLgen()
On Error GoTo Err_SQLgen

' SQL Generation routine
SQLQuerysubform.SourceObject = "Query1subform"
SQLbase = "SELECT tbldst.dst_user, tbldst.dst_task_id, tbldst.dst_date FROM tbldst"

Dim iLp As Integer

For iLp = 1 To 4
If Me.Controls("Combo" & iLp).Value <> "" Then
If sqlvital <> "" Then
sqlvital = sqlvital & " AND "
End If
Select Case iLp
Case 1: sqlvital = sqlvital & " [tbldst].[dst_user]=" & Me.Controls("Combo" & iLp)
Case 2: sqlvital = sqlvital & " [tbldst].[dst_task_id]=" & Me.Controls("Combo" & iLp)
Case 3: sqlvital = sqlvital & " [tbldst].[dst_date]=" & Me.Controls("Combo" & iLp)
Case 4: sqlvital = sqlvital & " [DATA].[HomePhone]=" & Me.Controls("Combo" & iLp)
End Select
End If 'Copy and Paste (Best Friend Worst Enemy)
Next iLp

' Construct SQL statement including parameters.
' the following is the master SQL, SQLvital contains essential SQL clause
' Depending on selection
If sqlvital <> "" Then
sqlvital = " Where " & sqlvital
End If
sqlvital = sqlvital & ";"

' Delete old Query
stDocName = "SQLQuery"
DoCmd.DeleteObject acQuery, stDocName

' Return reference to current database.
Set dbs = CurrentDb

' Create new query.
Set qdf = dbs.CreateQueryDef("SQLQuery")

qdf.SQL = SQLbase & sqlvital

SQLwhole = qdf.SQL
LabelSQL.Caption = SQLwhole
Set dbs = Nothing

SQLQuerysubform.SourceObject = "SQLQuerysubform"
Refresh

Exit_Err_SQLgen:
Exit Sub

Err_SQLgen:
LabelSQL.Caption = SQLbase & sqlvital
MsgBox "Error in SQL formula, try again !", vbCritical, "Error!!!"

End Sub
 
Last edited:
You may. My Email is in my profile
 
did you get my message?

I sent you an email through the site, but I need an actual email address......did you get mine?
 
current form 1 code (sunday 4:00 pm est)

I have it working better now(form1). The problem now is just the between query (combo3) and (combo4). It is saying:

select...from.....where date = date and between date and date.

Notice I changed date fields to "#".....also added a query3 which is linked to drop down combos 3 & 4.....


Current form1 code:



Option Compare Database
Option Explicit
Dim SQLvital, Queryflag, stDocName, SQLbase, SQLtemp, SQLwhole
Dim dbs As Database, qdf As QueryDef, rst As Recordset, Weekdef

Private Sub CmdReset_Click()
Combo1.Value = ""
Combo2.Value = ""
Combo3.Value = ""
Combo4.Value = ""
SQLgen
Refresh
End Sub
Private Sub Combo1_Change()
SQLgen
Refresh
End Sub

Private Sub Combo2_Change()
SQLgen
Refresh
End Sub

Private Sub Combo3_Change()
SQLgen
Refresh
End Sub

Private Sub Combo4_Change()
SQLgen
Refresh
End Sub




Private Sub Form_Load()
DoCmd.Maximize
Reset
End Sub

Sub Reset()
Combo1.Value = ""

Combo3.Value = ""
Combo4.Value = ""
SQLgen
Refresh

End Sub

Sub SQLgen()
On Error GoTo Err_SQLgen

' SQL Generation routine
SQLQuerysubform.SourceObject = "Query1subform"
SQLbase = "SELECT query1.dst_user, query1.dst_task_id, query1.dst_date FROM query1"

Dim iLp As Integer

SQLvital = ""

For iLp = 1 To 4
If Me.Controls("Combo" & iLp).Value <> "" Then
If SQLvital <> "" Then
SQLvital = SQLvital & " AND "
End If
Select Case iLp
Case 1: SQLvital = SQLvital & " [query1].[dst_user]='" & Me.Controls("Combo" & iLp) & "'"
Case 2: SQLvital = SQLvital & " [query1].[dst_task_id]='" & Me.Controls("Combo" & iLp) & "'"
Case 3: SQLvital = SQLvital & " [query1].[dst_date]=#" & Me.Controls("Combo" & iLp) & "#"
Case 4: SQLvital = SQLvital & " [query1].[dst_date] between #" & Me.Controls("Combo" & iLp) & "# and #" & Me.Controls("Combo" & iLp) & "#"
End Select
End If
Next iLp

' Construct SQL statement including parameters.
' the following is the master SQL, SQLvital contains essential SQL clause
' Depending on selection
If SQLvital <> "" Then
SQLvital = " Where " & SQLvital
End If
SQLvital = SQLvital & ";"

' Delete old Query
stDocName = "SQLQuery"
DoCmd.DeleteObject acQuery, stDocName

' Return reference to current database.
Set dbs = CurrentDb

' Create new query.
Set qdf = dbs.CreateQueryDef("SQLQuery")

qdf.SQL = SQLbase & SQLvital

SQLwhole = qdf.SQL
LabelSQL.Caption = SQLwhole
Set dbs = Nothing

SQLQuerysubform.SourceObject = "SQLQuerysubform"
Refresh

Exit_Err_SQLgen:
Exit Sub

Err_SQLgen:
LabelSQL.Caption = SQLbase & SQLvital
MsgBox "Error in SQL formula, try again !", vbCritical, "Error!!!"

End Sub




Private Sub Form_Resize()
SQLQuerysubform.Height = (Me.InsideHeight - SQLQuerysubform.Top) - 1000
SQLQuerysubform.Width = (Me.InsideWidth - 1000)
End Sub
 
Hi datacontrol

If you have it all working now, I'd be keen to see your final code - me being a student of VBA. If it's easiest to zip it all up, that would be great because then I could study how you've done your forms and queries etc. Cheers
 
current dynamic sql code...continued

I think I need to change a part of the code to accomplish the between statement.....

If SQLvital <> "" Then
SQLvital = " Where " & SQLvital
End If
SQLvital = SQLvital & ";"

could I change it to something like this?


If ilp = 1 Then
SQLvital = " Where " & SQLvital
If ilp = 2 Then
SQLvital = " Where " & SQLvital
If ilp = 3 Then
SQLvital = " Where " & SQLvital
If ilp = 4 Then
SQLvital = " Where " & SQLvital --------?????? this line one would be key
End If
 
Basic concept is to not loop 1 to 4 but 1 to 3.

Then on Case three check to see if data exists (since the code you sent me seems to deal with Dates use ISDATE) in the COMBO 4. If it does create a Between... AND ... statement. If it doesn't create an equal statement.
 
Code: 
    For iLp = 1 To [COLOR=green]3[/COLOR]
        If Me.Controls("Combo" & iLp).Value <> "" Then
            If sqlvital <> "" Then
                sqlvital = sqlvital & " AND "
            End If
            Select Case iLp
                Case 1: sqlvital = 
                Case 2: sqlvital = 
                Case 3
                             IF ISDATE(Me.Controls("COMBO4").Value)=True Then
                                 sqlvital=sqlvital & " [query1].[dst_date] Between #" & Me.Controls("COMBO3").Value & "# AND #" & Me.Controls("COMBO4").Value & "#"
                             ELSE
                                 sqlvital=sqlvital & " [query1].[dst_date]=#" & Me.Controls("COMBO3").Value & "#"
                             END IF
            End Select
       End If  'Copy and Paste (Best Friend Worst Enemy)
    Next iLp
 
Travis = VBA master

Hey...thanks a million...this works wonders!

Sure beats the heck out of my 25 else if statements........

I seek to learn more about the For statement...

I will post my complete db for all to see in a few days.

Any preliminary ideas on how to feed thses query results to a report? I have a print report button in mind for the Form1....

Let me know....
 
You must log in or register to reply here.

Similar threads

D
Solved Problem with dates in sql
Replies
7
Views
410
disgracept
D
O
  • Solved
VBA Outlook SQL problem
2
Replies
20
Views
2,420
olxx
O
M
Solved Duplicate value check - complicated.
Replies
16
Views
1,257
Pat Hartman
P
N
Solved Changing Prices to SQL Server database via a form fail
Replies
6
Views
687
nector
N
N
Implementing Parameterized Queries in DAO for Enhanced Security in Access- SQL Server Application
Replies
12
Views
1,737
mdlueck
M

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom