Inserting a variable value in an insert-select query

[worthmi]

Hello,

I am having a lot of problems writing a query to run in a vba module. I am trying to use an insert-select statement for all of the values except for one, and for that exception, I am trying to use a variable value or another query.

Here is the code:

strDwnSystemsSQL = "INSERT INTO active_steps"
strDwnSystemsSQL = strDwnSystemsSQL & " ([System],[Step_Number],[Task_Name],[Communication_Type],[Time_Interval],[Step_Type],[Priority],[Facility],[Primary_Group],[Secondary_Group],[Primary_Person],[Secondary_Person],[Step_Notes], [Downtime_Name]) "
strDwnSystemsSQL = strDwnSystemsSQL & "SELECT [System],[Step_Number],[Task_Name],[Communication_Type],[Time_Interval],[Step_Type],[Priority],[Facility],[Primary_Group],[Secondary_Group],[Primary_Person],[Secondary_Person],[Step_Notes]," & strDwnTimeName
strDwnSystemsSQL = strDwnSystemsSQL & " FROM System_Steps WHERE [System] = " & strDwnSystems

I get the following error when I run the above code:

Run-time error '3464' Datatype mismatch in criteria expression

Thank you.

 

[pbaldy]

The error would normally indicate that the field in the criteria is of a text type; you've treated it as a number. If that's the case, try

strDwnSystemsSQL = strDwnSystemsSQL & " FROM System_Steps WHERE [System] = '" & strDwnSystems & "'"

 

[worthmi]

I tried the code that you gave me, but I am still receiving errors. Now I am getting following error:

Run-time error '3075': Syntax error (missing operator) in query expression "[System] = "Misys PM/Tiger'''

I tried changing the last set of quotes to double quotes, but that did not resolve the problem either.

The WHERE clause is reading data I copied from a listbox; the code I used to copy from the listbox to a string is included below:

For i = 0 To Me.lstNewSystemsDown.ListCount - 1
    If i = 0 Then
        strDwnSystems = "'" & Me.lstNewSystemsDown.ItemData(0) & "'"
    Else
        strDwnSystems = strDwnSystems & " OR " & "'" & Me.lstNewSystemsDown.ItemData(i) & "'"
    End If
Next i

I am not certain if there will be more than value in the listbox, so I have to prepare for both events; that is why used and IF-THEN-ELSE structure.

Thank you for your help.

 

[pbaldy]

Add this after the string is built:

Debug.Print strDwnSystems

which will print the finished string out to the Immediate window. That will probably make it obvious where the problem is. It doesn't look like you're going to be building a valid string.

 

[pbaldy]

I should have added that the mistake it looks like you're making is a string that will look like:

WHERE Field = Value1 OR Value2 OR Value3

a valid string would be

WHERE Field = Value1 OR Field = Value2 OR Field = Value3

I'd build an IN clause, along the lines of this:

http://www.baldyweb.com/multiselect.htm

 

[worthmi]

Following your advice resolved the syntax errors; however, whenever I run the query, it will not append any records, but when I remove the "WHERE" clause, it will copy the whole table over. That, I think, would rule out any structural issues with the tables and the "SELECT" and "FROM" clauses. Do you have any suggestions? This is my first time doing any significant VBA programming.

'======================================================================================================================
'
' Star Part 2 -- Insert data into the active steps table.
'
'
'======================================================================================================================

For i = 0 To Me.lstNewSystemsDown.ListCount - 1
    If i = 0 Then
        strDwnSystems = "'" & Me.lstNewSystemsDown.ItemData(i) & "'"
    Else
        strDwnSystems = strDwnSystems & " , " & "'" & Me.lstNewSystemsDown.ItemData(i) & "'"
    End If
Next i
strDwnSystems = "(" & strDwnSystems & ")"
'Debug.Print strDwnSystems

strDwnSystemsSQL = "INSERT INTO active_steps"
strDwnSystemsSQL = strDwnSystemsSQL & " ([System],[Step_Number],[Task_Name],[Communication_Type],[Time_Interval],[Step_Type],[Priority],[Facility],[Primary_Group],[Secondary_Group],[Primary_Person],[Secondary_Person],[Step_Notes], [Downtime_Name]) "
strDwnSystemsSQL = strDwnSystemsSQL & "SELECT [System],[Step_Number],[Task_Name],[Communication_Type],[Time_Interval],[Step_Type],[Priority],[Facility],[Primary_Group],[Secondary_Group],[Primary_Person],[Secondary_Person],[Step_Notes], " & strDwnTimeName
strDwnSystemsSQL = strDwnSystemsSQL & " FROM [System_Steps] "
strDwnSystemsSQL = strDwnSystemsSQL & " WHERE [System] IN " & strDwnSystems





'MsgBox (strDwnSystemsSQL) 'Test the SQL statement as it is being built.
    
DoCmd.RunSQL strDwnSystemsSQL, dbFailOnError

 

[pbaldy]

What was the result of the Debug.Print I recommended?

 

[worthmi]

Here are the results of the Debug.Print functions. The first one is the Debug.Print you requested, and the second is a copy of the query.

('PMM' , 'Misys PM/Tiger')


INSERT INTO active_steps ([System],[Step_Number],[Task_Name],[Communication_Type],[Time_Interval],[Step_Type],[Priority],[Facility],[Primary_Group],[Secondary_Group],[Primary_Person],[Secondary_Person],[Step_Notes], [Downtime_Name]) SELECT [System],[Step_Number],[Task_Name],[Communication_Type],[Time_Interval],[Step_Type],[Priority],[Facility],[Primary_Group],[Secondary_Group],[Primary_Person],[Secondary_Person],[Step_Notes], 'test_20100404_2020' FROM [System_Steps] WHERE [System] IN ('PMM' , 'Misys PM/Tiger')

Thank you for all of your help.

 

[pbaldy]

The WHERE clause looks okay for a text data type. Can you post the db, or a sample of it?

 

[worthmi]

I resolved the problem. In the process of editing a relationship somewhere, the System field in the System_Steps table was changed to numeric. As you are probably aware, I am very new at this, so sorry for using so much of your time. Your assistance was invaluable though.

 

[pbaldy]

No problem, glad you got it sorted out.