Pass a parameter in VBA based on external variable value

I have a docmd in VBA that is an append query, but I need to check an external datapoint and depending on that datapoint, the append query should alter the text that is appended to the table.  Am I back to not getting the quotes correct or should the strType be in another format?

If Forms!frmImportMenuFSC.cboForecast = "Budget" Then
    strType = "BUDFSCHSN"
ElseIf Forms!frmImportMenuFSC.cboForecast = "R1" Then
    strType = "R1FSCHSN"
ElseIf Forms!frmImportMenuFSC.cboForecast = "R2" Then
    strType = "R2FSCHSN"
End If


strAppendData = "INSERT INTO tblFSCDetail ( SAP, WorksheetID, ForecastYear, Jan, " & _
    "Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, [Dec] ) " & _
    "SELECT tblTempRetail.SAP," & " ' strType ' " & " AS WorksheetID, " & _
    "FORMS!frmImportMenuFSC.txtForecastYear AS ForecastYear, " & _
    "tblTempRetail.Jan, tblTempRetail.Feb, tblTempRetail.Mar, " & _
    "tblTempRetail.Apr , tblTempRetail.May, tblTempRetail.Jun, " & _
    "tblTempRetail.Jul, tblTempRetail.Aug, tblTempRetail.Sep, " & _
    "tblTempRetail.Oct, tblTempRetail.Nov, tblTempRetail.Dec " & _
    "FROM tblTempRetail "
ssmith94015Asked:
Who is Participating?
 
UniqueDataConnect With a Mentor Commented:
Instead of:
 "SELECT tblTempRetail.SAP," & " ' strType ' " & " AS WorksheetID, " & _

Try:
 "SELECT tblTempRetail.SAP, '" &  strType   & "' AS WorksheetID, " & _

Michael
0
 
ImoutwestCommented:
For strType as a string:
>& " ' strType ' " &
should be & "'" & strType & "'" & 

0
 
Rey Obrero (Capricorn1)Commented:
try this

SELECT tblTempRetail.SAP," & strType & " AS WorksheetID, " &
0
 
ssmith94015Author Commented:
UniqueData, I do not know what you did because I thought I had tried a version of what wrote, but it did not work I just copied your and it works perfectly.  I think I am getting really tired, I had a nap a few days ago.....  Thank you both.
0
 
Rey Obrero (Capricorn1)Commented:
nevermind my post, thought you are passing a name of field.....
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.