• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1106
  • Last Modified:

Enter Parameter Value in Append Query

This is a simplified version of a problem I'm having with an append query.
If my code is as folows, the code runs fine:
  'SQL to insert orders into tblOrders$
  SqlStr = "INSERT INTO [tblOrders$] (ItemId, ReqDate, PdType) " & _
            "SELECT 1, Date(), Forms![frmOrdersPmt]![Payment] " & _
            "FROM tblMaterials " & _
            "WHERE (((tblMaterials.BGselect)=True));"
  DoCmd.RunSQL SqlStr

But, if my code is as folows, then I get a dialog box "Enter Parameter Value: strPayment"
  strPayment = Forms![frmOrdersPmt]![Payment]
  'SQL to insert orders into tblOrders$
  SqlStr = "INSERT INTO [tblOrders$] (ItemId, ReqDate, PdType) " & _
            "SELECT 1, Date(), strPayment " & _
            "FROM tblMaterials " & _
            "WHERE (((tblMaterials.BGselect)=True));"
  DoCmd.RunSQL SqlStr

I'd rather write the code the second way.  What is the problem?  Thanks for your help!
0
pcsettles
Asked:
pcsettles
  • 2
2 Solutions
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
 strPayment = Forms![frmOrdersPmt]![Payment]
  'SQL to insert orders into tblOrders$
  SqlStr = "INSERT INTO [tblOrders$] (ItemId, ReqDate, PdType) " & _
            "SELECT 1, Date()," & strPayment & _
            "FROM tblMaterials " & _
            "WHERE (((tblMaterials.BGselect)=True));"
  DoCmd.RunSQL SqlStr
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
You need to have your variables such as strPayment OUTSIDE of the double-quotes.  Otherwise, it'll be treated as literal text.

            "SELECT 1, Date(), " & strPayment & _
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Sorry ... if PdType is a TEXT field, you'd have to do this:

  SqlStr = "INSERT INTO [tblOrders$] (ItemId, ReqDate, PdType) " & _
            "SELECT 1, Date(),'" & strPayment & "' " _
            "FROM tblMaterials " & _
            "WHERE (((tblMaterials.BGselect)=True));"
0
 
pcsettlesAuthor Commented:
Thanks to jimhorn for giving the reason why the code did not work.  Thanks to LSMConsulting for pointing out that a text expression will have to be in single quotes.  I think the LSMConsulting missed one &.  The correct code needs to be: "SELECT 1, Date(),'" & strPayment & "' " & _
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.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now