Advertisement

07.21.2008 at 03:43PM PDT, ID: 23583671
[x]
Attachment Details
[x]
The Solution Rating System

With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.

  • The Grade of the Solution
  • The Zone Rank of the Expert Providing the Solution
  • The Number of Author and Expert Comments
  • The Number of Experts Contributing
  • The Feedback of the Community

Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.

If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.

Thank you!
9.9

Dynamic SQL in Access VBA produces incorrect results

Asked by haident in Access Coding/Macros, Microsoft Access Database, SQL Query Syntax

Tags: , , , , ,

I am generating SQL code in Private Sub CloseForm_Click() and passing it as an argument, strSQL, to the form, "2007 Job Log" which I open inside CloseForm_Click() with the following command:
DoCmd.OpenForm "2007 Job Log", acNormal, , , acFormAdd, acWindowNormal, strSQL

The SQL statement that the code generates, which I checked in a MsgBox, executes on the form (see Private Sub Form_Open(Cancel As Integer) below), but the recordset contains no results.  If I take the same SQL code and turn it into a query to run against the underlying table, I get results in the recordset (as I would expect).

Here is the SQL:
SELECT [2007 JOB LOG].*
FROM [2007 JOB LOG]
WHERE ([Appraiser] LIKE "I*")      'This is a capital I
ORDER BY [Job Number] DESC;

The only difference in the generated SQL is that it has spaces instead of carriage returns / line feeds.

Can anyone explain why I get two different results with the same SQL?  Or better yet, how I can get the generated SQL to work?Start Free Trial 
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:

Private Sub CloseForm_Click()
    Dim strIsFilled As String
 
    'Check to see if there is an argument
    strIsFilled = Nz([Forms]![FilterSort]![FieldList].[Value], "")
    
    Dim strField As String
    Dim strValue As String
    Dim strFieldPos As String
    Dim intAscDesc As Integer
    Dim strAscDesc As String
    Dim strSQL As String
    
    If (Not (strIsFilled = "")) Then
        strField = [Forms]![FilterSort]![FieldList].[Value]
        strFieldPos = [Forms]![FilterSort]![FieldPosition].[Value]
        intAscDesc = [Forms]![FilterSort]![Sort Order_Frame].Value
    
        If (intAscDesc = 0) Then
            strAscDesc = "ASC"
        Else
            strAscDesc = "DESC"
        End If
 
        strSQL = "SELECT [2007 JOB LOG].* "
        strSQL = strSQL & "FROM [2007 JOB LOG] "
        
        If (strFieldPos = "Whole field") Then
            strValue = """" & [Forms]![FilterSort]![Value].[Value] & """"
            strSQL = strSQL & "WHERE ([" & strField & "] = " & strValue & ") "
        ElseIf (strFieldPos = "Start of the field") Then
            strValue = """" & [Forms]![FilterSort]![Value].[Value]
            strSQL = strSQL & "WHERE ([" & strField & "] LIKE " & "" & strValue & "*" & """" & ") "
        Else
            strValue = [Forms]![FilterSort]![Value].[Value]
            strSQL = strSQL & "WHERE ([" & strField & "] LIKE " & """" & "*" & strValue & "*" & """" & ") "
        End If
        
        strSQL = strSQL & "ORDER BY [Job Number] " & strAscDesc
        strSQL = strSQL & ";"
    Else
        strSQL = ""
    End If
        
    'Hide the FilterSort Form
    Me.Visible = False
 
    'MsgBox ("Opening 2007 Job Log with this SQL - '" & strSQL & "'")
    DoCmd.Close acForm, "2007 Job Log", acSaveYes
    DoCmd.OpenForm "2007 Job Log", acNormal, , , acFormAdd, acWindowNormal, strSQL
    
End Sub
 
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Sub Form_Open(Cancel As Integer)
    Dim strArg As String
    Dim strSQL As String
 
    'Check to see if there is an argument
    strArg = Nz([Forms]![2007 Job Log].OpenArgs, "")
    
    'MsgBox "Argument = " & strArg
    
    If Len(strArg) > 0 Then
        ' ok we have an argument
        strSQL = strArg
    Else
        ' no argument
        strSQL = "SELECT [2007 JOB LOG].* "
        strSQL = strSQL & "FROM [2007 JOB LOG] "
        strSQL = strSQL & "ORDER BY [2007 JOB LOG].[Job Number] ASC;"
    End If
 
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    
    'Use the ADO connection that Access uses
    Set cn = CurrentProject.AccessConnection
    
    'Create an instance of the ADO Recordset class, and set its properties
    Set rs = New ADODB.Recordset
    
    With rs
        Set .ActiveConnection = cn
        .Source = strSQL
        '.LockType = adLockOptmistic
        '.CursorType = adOpenKeyset
        .Open
    End With
    
    'Set the form's Recordset property to the ADO recordset
    Set Me.Recordset = rs
    
    Set rs = Nothing
    Set cn = Nothing
 
End Sub
[+][-]07.21.2008 at 04:35PM PDT, ID: 22055360

View this solution now by starting your 7-day free trial. Setting up your free trial is quick, easy, and secure. We will return you to this solution, unlocked, when you're done.

 

About this solution

Zones: Access Coding/Macros, Microsoft Access Database, SQL Query Syntax
Tags: Microsoft, Access, 2007, VBA / SQL, N / A, N / A
Sign Up Now!
Solution Provided By: LSMConsulting
Participating Experts: 1
Solution Grade: A
 
 
 
Loading Advertisement...
20080924-EE-VQP-38 / EE_QW_2_20070628