Solved

Access Module - Sql String

Posted on 2011-03-07
14
421 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
ID: 35061702
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
ID: 35061732
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
ID: 35061752
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
ID: 35071818
The SQL string works perfect when the code itself is placed right next to "CurrentDb.Execute".
0
 
LVL 28

Expert Comment

by:omgang
ID: 35071905
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
ID: 35073098
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
ID: 35073325
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:nulad
ID: 35074349
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
ID: 35074569
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
ID: 35084927
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
ID: 35085589
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
ID: 35088330
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
ID: 35088437
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
ID: 35094715
Thank you so much for all your help.  I appreciate it.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
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…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

911 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

18 Experts available now in Live!

Get 1:1 Help Now