Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 438
  • Last Modified:

Access Module - Sql String

I have a function where the SQL string works fine when it is coded in right next to "CurrentDb.Execute".  The function does not work when the SQL string is set up as a variable, and then the variable is placed next to "CurrentDb.Execute" (see below).

Public Function ExperimentFunction()
Dim sAppendToTable As String  'Access Table to be used to export to a delimited text file
Dim squeryName As String        'Access query containing the results for Part I, II, III to be inserted into the RAW_DATA table
Dim sTestID As String               ' Cateogry for Test Type (AAAI, AAAII, or AAAIII, BBBI, BBBII etc.)
Dim sTestType As String           'Test Type (AAA, BBB, CCC, etc.)
Dim sSqlString As String           'The sql statement used to insert records into the RAW_DATA table

sAppendToTable = "RAW_DATA"
squeryName = "q_PartI_II_III"
sTestID = ""
sTestType = ""
sSqlString = "INSERT INTO " & sAppendToTable & "" _
            & " (GOVERNMENT_ID, FIRST_NAME, LAST_NAME, PREF_ADD, " _
            & " TEST_ID, TEST_TYPE, TEST_DATE_FORMATTED, RAW_SCORE) " _
            & " SELECT " & squeryName & ".GOVERNMENT_ID, " _
            & squeryName & ".FIRST_NAME, " _
            & squeryName & ".LAST_NAME, " _
            & squeryName & ".PREF_ADD, " _
            & "'" & sTestID & "'" & " AS TEST_ID, " _
            & "'" & sTestType & "'" & " AS TEST_TYPE, " _
            & " Format(getTestDate(),""mm/yy"") AS TEST_DATE_FORMATTED, " _
            & "(CLng(" & "[" & sTestType & "]" & ")) AS RAW_SCORE" _
            & " FROM " & squeryName & "" _
            & " WHERE " & squeryName & "." & "[" & sTestType & "]" & " Is Not Null " _
            & " AND NOT EXISTS (SELECT NULL FROM " & sAppendToTable & "" _
            & " WHERE (" & sAppendToTable & ".GOVERNMENT_ID = " & squeryName & ".GOVERNMENT_ID " _
            & " AND " & sAppendToTable & ".FIRST_NAME = " & squeryName & ".FIRST_NAME " _
            & " AND " & sAppendToTable & ".LAST_NAME = " & squeryName & ".LAST_NAME " _
            & " AND " & sAppendToTable & ".PREF_ADD = " & squeryName & ".PREF_ADD " _
            & " AND " & sAppendToTable & ".TEST_TYPE = " & "'" & sTestType & "'" _
            & " AND " & sAppendToTable & ".TEST_DATE_FORMATTED = " & squeryName & ".TEST_DATE " _
            & " AND " & sAppendToTable & ".RAW_SCORE = " & squeryName & "." & "[" & sTestType & "]" _
            & " ));"

'To delete any previous records in the "RAW_DATA" table
DoCmd.RunSQL "DELETE * FROM " & sAppendToTable
'------------------------------------------------------------------------
 'To set up variables for the AAA test type
   sTestID = "AAAII"
   sTestType = "AAA"
'To append the AAA test scores from the query "q_PartI_II_III_DCYrTermSession
'to the "RAW_DATA" table
CurrentDb.Execute sSqlString
0
nulad
Asked:
nulad
  • 7
  • 6
2 Solutions
 
omgangCommented:
Have you tried outputting the SQL string to the Immediate Window and then pasting it into a query to see if it will execute?  Are you receiving any errors?

Public Function ExperimentFunction()
Dim sAppendToTable As String  'Access Table to be used to export to a delimited text file
Dim squeryName As String        'Access query containing the results for Part I, II, III to be inserted into the RAW_DATA table
Dim sTestID As String               ' Cateogry for Test Type (AAAI, AAAII, or AAAIII, BBBI, BBBII etc.)
Dim sTestType As String           'Test Type (AAA, BBB, CCC, etc.)
Dim sSqlString As String           'The sql statement used to insert records into the RAW_DATA table

sAppendToTable = "RAW_DATA"
squeryName = "q_PartI_II_III"
sTestID = ""
sTestType = ""
sSqlString = "INSERT INTO " & sAppendToTable & "" _
            & " (GOVERNMENT_ID, FIRST_NAME, LAST_NAME, PREF_ADD, " _
            & " TEST_ID, TEST_TYPE, TEST_DATE_FORMATTED, RAW_SCORE) " _
            & " SELECT " & squeryName & ".GOVERNMENT_ID, " _
            & squeryName & ".FIRST_NAME, " _
            & squeryName & ".LAST_NAME, " _
            & squeryName & ".PREF_ADD, " _
            & "'" & sTestID & "'" & " AS TEST_ID, " _
            & "'" & sTestType & "'" & " AS TEST_TYPE, " _
            & " Format(getTestDate(),""mm/yy"") AS TEST_DATE_FORMATTED, " _
            & "(CLng(" & "[" & sTestType & "]" & ")) AS RAW_SCORE" _
            & " FROM " & squeryName & "" _
            & " WHERE " & squeryName & "." & "[" & sTestType & "]" & " Is Not Null " _
            & " AND NOT EXISTS (SELECT NULL FROM " & sAppendToTable & "" _
            & " WHERE (" & sAppendToTable & ".GOVERNMENT_ID = " & squeryName & ".GOVERNMENT_ID " _
            & " AND " & sAppendToTable & ".FIRST_NAME = " & squeryName & ".FIRST_NAME " _
            & " AND " & sAppendToTable & ".LAST_NAME = " & squeryName & ".LAST_NAME " _
            & " AND " & sAppendToTable & ".PREF_ADD = " & squeryName & ".PREF_ADD " _
            & " AND " & sAppendToTable & ".TEST_TYPE = " & "'" & sTestType & "'" _
            & " AND " & sAppendToTable & ".TEST_DATE_FORMATTED = " & squeryName & ".TEST_DATE " _
            & " AND " & sAppendToTable & ".RAW_SCORE = " & squeryName & "." & "[" & sTestType & "]" _
            & " ));"

Debug.Print sSqlString   <---- output to Immediate Window here

'To delete any previous records in the "RAW_DATA" table
DoCmd.RunSQL "DELETE * FROM " & sAppendToTable
'------------------------------------------------------------------------
 'To set up variables for the AAA test type
   sTestID = "AAAII"
   sTestType = "AAA"
'To append the AAA test scores from the query "q_PartI_II_III_DCYrTermSession
'to the "RAW_DATA" table
CurrentDb.Execute sSqlString


OM Gang
0
 
omgangCommented:
Also, you have variable assignments for sTestID and sTestType AFTER you construct the SQL string.  Try the Debug.Print I posted earlier and you'll see what I mean.  You must have those variable assignments prior to building the SQL statement sSqlString.
OM Gang
0
 
peter57rCommented:
ssqlstring is assigned its value in the long assignment statement

Changing the variables after that assignment won't affect ssqlstring.  If you want specifc values in the sql you must assign the variables before the sqlstring value is assigned.
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
nuladAuthor Commented:
The SQL string works perfect when the code itself is placed right next to "CurrentDb.Execute".
0
 
omgangCommented:
Well, neither of us are saying it doesn't.

Post the code that works.

Have you considered what either of us have said?  In the example code you posted you are constructing the SQL statement BEFORE you assign values to two variables used to construct the statement.

Have you tried my suggestion to print the SQL statement to the Immediate window so you can see exactly what the SQL statement looks like in the variable sSqlString?

OM Gang
0
 
nuladAuthor Commented:
You are both correct.  When the sTestID and sTestType variables are declared at the beginning it does work.  Thank you both for your replies.  And thank you OM Gang for the suggestion to use the Debug.Print to output the code to the Immediate window.  I had not used that before and it will greatly help me in the future.

The reason I did not want to declare the sTestID and sTestType variables until right before the "CurrentDb.Execute sSqlString" line, was because I want to run the sSqlString multiple times, changing the sTestID and sTestType before the sSqlString is executed again and inserts the data into the table.

I quess I am going about this incorrectly.  Any suggestions?  
0
 
omgangCommented:
You'll have to reassign the SQL statement to sSqlString each time.  Here's how it works:
one you assign a value to a variable the variable retains that value until you explicitly change it.

Each time you change the values of sTestID and sTestType you need to reassign sSqlString.

OM Gang
0
 
nuladAuthor Commented:
Thank you for your reply.  I tried to add the below code after the original code and it did not work.  
How are the variables sTestID and sTestType changed explicitly, and how is the sSqlString reassigned?  

'To change the variables and run again
'To append the BBB test scores from the query in squeryName
'to the sAppendToTable
   sTestID = ""
   sTestType = ""
   sSqlString = ""
   sTestID = "BBBII"
   sTestType = "BBB"
   sSqlString = "INSERT INTO " & sAppendToTable & "" _
            & " (GOVERNMENT_ID, FIRST_NAME, LAST_NAME, PREF_ADD, " _
            & " TEST_ID, TEST_TYPE, TEST_DATE_FORMATTED, RAW_SCORE) " _
            & " SELECT " & squeryName & ".GOVERNMENT_ID, " _
            & squeryName & ".FIRST_NAME, " _
            & squeryName & ".LAST_NAME, " _
            & squeryName & ".PREF_ADD, " _
            & "'" & sTestID & "'" & " AS TEST_ID, " _
            & "'" & sTestType & "'" & " AS TEST_TYPE, " _
            & " Format(getTestDate(),""mm/yy"") AS TEST_DATE_FORMATTED, " _
            & "(CLng(" & "[" & sTestType & "]" & ")) AS RAW_SCORE" _
            & " FROM " & squeryName & "" _
            & " WHERE " & squeryName & "." & "[" & sTestType & "]" & " Is Not Null " _
            & " AND NOT EXISTS (SELECT NULL FROM " & sAppendToTable & "" _
            & " WHERE (" & sAppendToTable & ".GOVERNMENT_ID = " & squeryName & ".GOVERNMENT_ID " _
            & " AND " & sAppendToTable & ".FIRST_NAME = " & squeryName & ".FIRST_NAME " _
            & " AND " & sAppendToTable & ".LAST_NAME = " & squeryName & ".LAST_NAME " _
            & " AND " & sAppendToTable & ".PREF_ADD = " & squeryName & ".PREF_ADD " _
            & " AND " & sAppendToTable & ".TEST_TYPE = " & "'" & sTestType & "'" _
            & " AND " & sAppendToTable & ".TEST_DATE_FORMATTED = " & squeryName & ".TEST_DATE " _
            & " AND " & sAppendToTable & ".RAW_SCORE = " & squeryName & "." & "[" & sTestType & "]" _
            & " ));"


   CurrentDb.Execute sSqlString
0
 
omgangCommented:
What did not work?  Are you receiving any errors?  Please post your entire procedure in including the Sub/Function delclare statement to the End Sub/Function statement.
OM Gang
0
 
nuladAuthor Commented:
The code runs the below code successfully:
DoCmd.RunSQL "DELETE * FROM " & sAppendToTable
It also runs the first "CurrentDb.Execute" statement successfully:
CurrentDb.Execute sSqlString

It successfully deletes rows in the sAppendToTable, and then it inserts data from the sSqlString.

The problem happens right after that.  It happens after my incorrect attempt to "change the values of sTestID and sTestType and reassign the sSqlString".  

It happens when it attempts to run the code again, the CurrentDb.Execute sSqlString, at the end of the function.  It displays:  "Run-time error '3464': Data type mismatch in criteria expression"
AccessVbaCode-20110309-1033.docx
0
 
omgangCommented:
As the error message indicates there is a problem with the WHERE clause in the second SQL statement.  I've copied the pertinent sections and pasted below.  They're not exactly the same.  Look at the seventh line down.  The first SQL statment has
& " AND " & sAppendToTable & ".PREF_ADD = " & squeryName & ".PREFERRED_ADD " _
but the second SQL statement has
& " AND " & sAppendToTable & ".PREF_ADD = " & squeryName & ".PREF_ADD " _

you need to fix that and the second SQL statement should work unless there are other typos.
OM Gang


           
        FIRST
            & " FROM " & squeryName & "" _
            & " WHERE " & squeryName & "." & "[" & sTestType & "]" & " Is Not Null " _
            & " AND NOT EXISTS (SELECT NULL FROM " & sAppendToTable & "" _
            & " WHERE (" & sAppendToTable & ".GOVERNMENT_ID = " & squeryName & ".GOVERNMENT_ID " _
            & " AND " & sAppendToTable & ".FIRST_NAME = " & squeryName & ".FIRST_NAME " _
            & " AND " & sAppendToTable & ".LAST_NAME = " & squeryName & ".LAST_NAME " _
            & " AND " & sAppendToTable & ".PREF_ADD = " & squeryName & ".PREFERRED_ADD " _
            & " AND " & sAppendToTable & ".TEST_TYPE = " & "'" & sTestType & "'" _
            & " AND " & sAppendToTable & ".TEST_DATE_FORMATTED = " & squeryName & ".TEST_DATE " _
            & " AND " & sAppendToTable & ".RAW_TEST_SCORE = " & squeryName & "." & "[" & sTestType & "]" _
            & " ));"



        SECOND
            & " FROM " & squeryName & "" _
            & " WHERE " & squeryName & "." & "[" & sTestType & "]" & " Is Not Null " _
            & " AND NOT EXISTS (SELECT NULL FROM " & sAppendToTable & "" _
            & " WHERE (" & sAppendToTable & ".GOVERNMENT_ID = " & squeryName & ".GOVERNMENT_ID " _
            & " AND " & sAppendToTable & ".FIRST_NAME = " & squeryName & ".FIRST_NAME " _
            & " AND " & sAppendToTable & ".LAST_NAME = " & squeryName & ".LAST_NAME " _
            & " AND " & sAppendToTable & ".PREF_ADD = " & squeryName & ".PREF_ADD " _
            & " AND " & sAppendToTable & ".TEST_TYPE = " & "'" & sTestType & "'" _
            & " AND " & sAppendToTable & ".TEST_DATE_FORMATTED = " & squeryName & ".TEST_DATE " _
            & " AND " & sAppendToTable & ".RAW_TEST_SCORE = " & squeryName & "." & "[" & sTestType & "]" _
            & " ));"
0
 
nuladAuthor Commented:
Attached is the actual code.  Both of the WHERE clauses are the same.  I placed the "Debug.Print sSqlString" after both of the "CurrentDb.Execute sSqlString" command lines to view the SQL statement in the Immediate window (thank you again for showing me that).

I receive "Run-time error '3464': Data Type mismatch in criteria expression".

Under the Immediate window, it only the prints the first SQL statement relating to the first "CurrentDb.Execute sSqlString".   It does not print the second SQL statement after the second ""CurrentDb.Execute sSqlString" command line.
AccessVbaCode-20110309-1546.docx
0
 
omgangCommented:
Move the second Debug.Print to before the CurrentDb.Execute sSqlString command.  The reason it's not executing is because of the error you are receiving.  By placing the Debug comman in front of the CurrentDb.Execute you'll get a print out to the Immediate window before the error occurs.
OM Gang
0
 
nuladAuthor Commented:
Thank you so much for all your help.  I appreciate it.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now