nulad
asked on
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/
& "(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_DCYrTermSe
'to the "RAW_DATA" table
CurrentDb.Execute sSqlString
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/
& "(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_DCYrTermSe
'to the "RAW_DATA" table
CurrentDb.Execute sSqlString
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
OM Gang
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The SQL string works perfect when the code itself is placed right next to "CurrentDb.Execute".
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
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
ASKER
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?
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?
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
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
ASKER
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/
& "(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
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/
& "(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
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
OM Gang
ASKER
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
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
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 & "]" _
& " ));"
& " 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 & "]" _
& " ));"
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you so much for all your help. I appreciate it.
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/
& "(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_DCYrTermSe
'to the "RAW_DATA" table
CurrentDb.Execute sSqlString
OM Gang