INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Problems with inline SQL for an insert query

Problems with inline SQL for an insert query

(OP)
Hi,
I am Using Access 2010/VBA. I am having problems creating inline SQL INSERT. Here's my code. The strSQL contains the sql and it doesn't like it no matter what I do:
Set db3 = CurrentDb()
Set rst3 = db3.OpenRecordset("tbl_Mass_Upload_Template_Specialist", dbOpenDynaset)
Do Until rst3.EOF
DoCmd.SetOrderBy "Specialist"
hold_Specialist = rst3![Specialist] 'errors here with No current record
If hold_Prev_Specialist <> hold_Specialist Then
hold_Prev_Specialist = hold_Specialist
Export_Report
End If
hold_GLN = Nz(rst3![GLN], " ")
hold_VBU = rst3![VBU]
hold_Highest_Level_GTIN = Nz(rst3![Highest_Level_GTIN], " ")
hold_Lowest_Level_GTIN = Nz(rst3![Lowest_Level_GTIN], " ")
hold_Assortment_Number = rst3![Assortment_Number]
hold_Item_Number = rst3![Item_Number]
hold_Model_Number = Nz(rst3![Model_Number], " ")
hold_Country = Nz(rst3![Country], " ")
hold_Internal = Nz(rst3![Internal], " ")
hold_Category = Nz(rst3![Category], " ")
hold_Item_Type = Nz(rst3![Item_Type], " ")
hold_Item_Description = Nz(rst3![Item_Description], " ")
hold_Customer_Description = Nz(rst3![Customer_Description], " ")
strSQL = "INSERT Into tbl_Mass_Upload_Template_Specialist_Next (Specialist, GLN, VBU, Highest_Level_GTIN, Lowest_Level_GTIN, Assortment_Number, Item_Number, Model_Number, Country, Internal, Category, Item_Type, Item_Description, Customer_Description) Values (hold_Specialist, hold_GLN, hold_VBU, hold_Highest_Level_GTIN, hold_Lowest_Level_GTIN, hold_Assortment_Number, hold_Item_Number, hold_Model_Number, hold_Country, hold_Internal, hold_Category, hold_Item_Type, hold_Item_Description, hold_Customer_Description)"
DoCmd.RunSQL strSQL
rst3.MoveNext
Loop

I've tried it 2 ways wondering if I need to put text fields in quotes, one with and one without.
With:
strSQL = "INSERT Into tbl_Mass_Upload_Template_Specialist_Next (Specialist, GLN, VBU, Highest_Level_GTIN, Lowest_Level_GTIN, Assortment_Number, Item_Number, Model_Number, Country, Internal, Category, Item_Type, Item_Description, Customer_Description) Values ('" & hold_Specialist & "', "'" & hold_GLN & "'', hold_VBU, '" & hold_Highest_Level_GTIN & "'', '" & hold_Lowest_Level_GTIN & "'', hold_Assortment_Number, hold_Item_Number, '" & hold_Model_Number & "'', '" & hold_Country & "'', '" & hold_Internal & "'', '" & hold_Category & "'', '" & hold_Item_Type & "'', '" & hold_Item_Description & "'', '" & hold_Customer_Description & "'')"


Without:
strSQL = "INSERT Into tbl_Mass_Upload_Template_Specialist_Next (Specialist, GLN, VBU, Highest_Level_GTIN, Lowest_Level_GTIN, Assortment_Number, Item_Number, Model_Number, Country, Internal, Category, Item_Type, Item_Description, Customer_Description) Values (hold_Specialist, hold_GLN, hold_VBU, hold_Highest_Level_GTIN, hold_Lowest_Level_GTIN, hold_Assortment_Number, hold_Item_Number, hold_Model_Number, hold_Country, hold_Internal, hold_Category, hold_Item_Type, hold_Item_Description, hold_Customer_Description)"

For the one without quotes it doesn't find the field/parameter.

For the one with quotes it gives me an invalid SELECT INSERT.

Appreciate any assistance!

RE: Problems with inline SQL for an insert query

That is pretty difficult to tackle in one bite, and impossible to debug. Break it down into pieces

In insert queries your string needs to resolve to something like

CODE -->

Insert into sometable (StrField, NumberField, DateField, OtherField) values ('someString', 123, #10/11/2016#, NULL) 

So break this into digestable pieces. I provided some helper functions to wrap the strings and dates

Leave all variables as variants so you can return NULL
Then for every string field do something like

CODE -->

hold_GLN = rst3![GLN]
hold_GLN = sqlTxt(hold_GLN) 
Do the same for dates using the sqlDate function

In your NZ function you do not want a space, you need to return NULL. Think about it. Your string would look something like
Insert into sometable (StrField, NumberField, DateField) values (, 123, #10/11/2016#)
Instead you want
Insert into sometable (StrField, NumberField, DateField) values (NULL, 123, #10/11/2016#)

Break it into smaller pieces

CODE

dim strSql as string
dim StrValues as string
'This functions does the concatenation of the values into a long string a lot easier than working with a bunch of & ",'" & "',"
strValues = InsertValues(hold_Specialist, hold_GLN, hold_VBU, hold_Highest_Level_GTIN, hold_Lowest_Level_GTIN, hold_Assortment_Number, hold_Item_Number, hold_Model_Number, hold_Country, hold_Internal, hold_Category, hold_Item_Type, hold_Item_Description, hold_Customer_Description)
'verify the value string
debug.print strValues 

strSql = INSERT Into tbl_Mass_Upload_Template_Specialist_Next
strSql = strSql & " (Specialist, GLN, VBU, Highest_Level_GTIN, Lowest_Level_GTIN, Assortment_Number, Item_Number, Model_Number, Country, Internal, Category, Item_Type, Item_Description, Customer_Description)"
strSql = strSql & " VALUES " & strValues
'verify the result
debug.print strSql 


CODE

Public Function sqlTxt(varItem As Variant) As Variant
  If Not IsNull(varItem) Then
    varItem = Replace(varItem, "'", "''")
    sqlTxt = "'" & varItem & "'"
  end if
End Function

Public Function SQLDate(varDate As Variant) As Variant
     If IsDate(varDate) Then
        If DateValue(varDate) = varDate Then
            SQLDate = Format$(varDate, "\#mm\/dd\/yyyy\#")
        Else
            SQLDate = Format$(varDate, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
        End If
    End If
End Function

Public Function insertValues(ParamArray varValues() As Variant) As String
  Dim varValue As Variant
  For Each varValue In varValues
    If IsNull(varValue) Then varValue = "NULL"
    If insertValues = "" Then
      insertValues = "(" & varValue
    Else
      insertValues = insertValues & ", " & varValue
    End If
  Next varValue
  If Not insertValues = "" Then
    insertValues = insertValues & ")"
  End If
End Function 

Now show us the debug string and maybe we can see what is wrong.

RE: Problems with inline SQL for an insert query

Also, when posting, wrap your code into TGML [code] tags so it is easier to read.
See how much nicer MayP's post is...?

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Problems with inline SQL for an insert query

(OP)
Wow this is some great education, thanks!!

I wanted to start first with what you said about the NZ Function:
In your NZ function you do not want a space, you need to return NULL. Think about it. Your string would look something like
Insert into sometable (StrField, NumberField, DateField) values (, 123, #10/11/2016#)
Instead you want
Insert into sometable (StrField, NumberField, DateField) values (NULL, 123, #10/11/2016#)

I coded this: hold_Item_Description = Nz(rst3![Item_Description], Null) and when I debug it gives me run-time error '94' Invalid use of Null which is what I was getting before and started using the NZ function. I typed Null as NULL but it changed it to Null. I can put it in quotes "NULL" but I am not sure what the sql will do with it or even if it recognizes it as NULL.

Thanks for all your help!

RE: Problems with inline SQL for an insert query

(OP)
MajP,
I made the changes you suggested and here are the debug strings:

strValues = (CASSANDRA CURETON, '0837365000105', 62564, '00141181354978', '0837365000105', 28747, 612204, 'EIDW5705PB', 'USA', 'Yes', 'Gas Riding Lawn Mo', 'Stock', 'NULL', 'NULL')

strSql =
INSERT Into tbl_Mass_Upload_Template_Specialist_Next
(Specialist, GLN, VBU, Highest_Level_GTIN, Lowest_Level_GTIN, Assortment_Number, Item_Number, Model_Number, Country, Internal, Category, Item_Type, Item_Description, Customer_Description) VALUES (CASSANDRA CURETON, '0837365000105', 62564, '00141181354978', '0837365000105', 28747, 612204, 'EIDW5705PB', 'USA', 'Yes', 'Gas Riding Lawn Mo', 'Stock', 'NULL', 'NULL')

I received an error Run time error '3075' Syntax error (missing operator) in query expression 'CASSANDRA CURETON'. I did get that error yesterday when I attempted the correct punctuation for the strValues so this run seems to be matching that run. Although I like the functions you provided! Is it missing something else?

Thanks!

RE: Problems with inline SQL for an insert query

(OP)
Wait I think I see something.
I'll reply back.

RE: Problems with inline SQL for an insert query

If Specialist is a text field, you need quotes around CASSANDRA CURETON,
If you want Nulls in Item_Description and Customer_Description, you cannot have quotes around 'NULL', otherwise you will end up with the word NULL in Item_Description and Customer_Description fields.

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Problems with inline SQL for an insert query

The way I do this is I dimension all my variables as variants and do not do any NZ functions. The function insertValues takes care of the Null values that are passed in.

dim fld1 as variant
dim fld2 as variant
dim fld3 as variant

then I do not do any NZ

fld1 = rs!fld1
'if fld1 is a text field I next do
fld1 = sqlTXT(fld1) ' If it is null it returns a null value not 'NULL'
fld2 = rs!fld2
'if fld2 is a date field i do
fld2 = sqlDate(fld2)
'if fld3 is numeric
fld3 = rs!fld3

'Then you can get your value string
strvalues = insertValues(fld1,fld2,fl3)

If there are any null values passed in it returns the string like this taking care of the null
('SomeString',#10/11/2016#, NULL)

RE: Problems with inline SQL for an insert query

Here is a better function I found. It is a single function so much easier to work with.

CODE

Public Function CSql( _
    ByVal Value As Variant) _
    As String

    Const vbLongLong    As Integer = 20
    Const SqlNull       As String = " Null"

    Dim Sql             As String
    Dim LongLong        As Integer

    #If Win32 Then
        LongLong = vbLongLong
    #End If
    #If Win64 Then
        LongLong = VBA.vbLongLong
    #End If

    Select Case VarType(Value)
        Case vbEmpty            '    0  Empty (uninitialized).
            Sql = SqlNull
        Case vbNull             '    1  Null (no valid data).
            Sql = SqlNull
        Case vbInteger          '    2  Integer.
            Sql = Str(Value)
        Case vbLong             '    3  Long integer.
            Sql = Str(Value)
        Case vbSingle           '    4  Single-precision floating-point number.
            Sql = Str(Value)
        Case vbDouble           '    5  Double-precision floating-point number.
            Sql = Str(Value)
        Case vbCurrency         '    6  Currency.
            Sql = Str(Value)
        Case vbDate             '    7  Date.
            Sql = Format(Value, " \#yyyy\/mm\/dd hh\:nn\:ss\#")
            If DateValue(Value) = Value Then
               Sql = Format$(Value, "\#mm\/dd\/yyyy\#")
            Else
               Sql = Format$(Value, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
            End If
        Case vbString           '    8  String.
            Sql = Replace(Trim(Value), "'", "''")
            If Sql = "" Then
                Sql = SqlNull
            Else
                Sql = " '" & Sql & "'"
            End If
        Case vbObject           '    9  Object.
            Sql = SqlNull
        Case vbError            '   10  Error.
            Sql = SqlNull
        Case vbBoolean          '   11  Boolean.
            Sql = Str(Abs(Value))
        Case vbVariant          '   12  Variant (used only with arrays of variants).
            Sql = SqlNull
        Case vbDataObject       '   13  A data access object.
            Sql = SqlNull
        Case vbDecimal          '   14  Decimal.
            Sql = Str(Value)
        Case vbByte             '   17  Byte.
            Sql = Str(Value)
        Case LongLong           '   20  LongLong integer (Valid on 64-bit platforms only).
            Sql = Str(Value)
        Case vbUserDefinedType  '   36  Variants that contain user-defined types.
            Sql = SqlNull
        Case vbArray            ' 8192  Array.
            Sql = SqlNull
        Case Else               '       Should not happen.
            Sql = SqlNull
    End Select

    CSql = Sql & " "

End Function 

Then dimension everything as a string

dim hold_Date as string
dim hold_number as string
dim hold_text as string
then
hold_Date = CSql(rs3!hold_date)
hold_number = CSql(rs3!hold_number)
hold_text = CSql(rs3!hold_text)

RE: Problems with inline SQL for an insert query

(OP)
Thanks! The SQL is working and I have it as you had suggested up to the time 14:49. I just have a deadline of Friday so at this point I am very pleased! I had some issues with the Variant where the SQL could not run again. I didn't have time to try the last post with the CSql but will give it a try in when I have a moment.

I want to thank you for all of your help, you've saved the day!

RE: Problems with inline SQL for an insert query

I have tested that function, and works well. Makes writing these long inserts a lot easier.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close