?
Solved

Enter Parameter Value in Append Query

Posted on 2007-07-30
4
Medium Priority
?
1,060 Views
Last Modified: 2010-07-27
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
Comment
Question by:pcsettles
  • 2
4 Comments
 
LVL 85
ID: 19593358
 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
 
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 150 total points
ID: 19593368
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
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 225 total points
ID: 19593372
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
 

Author Comment

by:pcsettles
ID: 19593800
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

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

850 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