Solved

Access Module - Sql String

Posted on 2011-03-07
14
418 Views
Last Modified: 2012-08-13
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
Comment
Question by:nulad
  • 7
  • 6
14 Comments
 
LVL 28

Expert Comment

by:omgang
Comment Utility
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
 
LVL 28

Expert Comment

by:omgang
Comment Utility
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
 
LVL 77

Assisted Solution

by:peter57r
peter57r earned 100 total points
Comment Utility
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
 

Author Comment

by:nulad
Comment Utility
The SQL string works perfect when the code itself is placed right next to "CurrentDb.Execute".
0
 
LVL 28

Expert Comment

by:omgang
Comment Utility
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
 

Author Comment

by:nulad
Comment Utility
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
 
LVL 28

Expert Comment

by:omgang
Comment Utility
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
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 

Author Comment

by:nulad
Comment Utility
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
 
LVL 28

Expert Comment

by:omgang
Comment Utility
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
 

Author Comment

by:nulad
Comment Utility
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
 
LVL 28

Expert Comment

by:omgang
Comment Utility
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
 

Author Comment

by:nulad
Comment Utility
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
 
LVL 28

Accepted Solution

by:
omgang earned 400 total points
Comment Utility
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
 

Author Closing Comment

by:nulad
Comment Utility
Thank you so much for all your help.  I appreciate it.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now