format SQL statement

[tim johnson]

I copied the SQL statement below but unable to format it
correctly to use in a procedure. I am getting a datatype
error.


INSERT INTO tblAuxillaryDetails ( TransDate, Debit,
Comments, AuxillaryID )
SELECT tblTransactions.DatePaid, tblSplitBudgetCat.Debit,
tblSplitBudgetCat.AuxillaryComments,
tblSplitBudgetCat.AuxillaryName
FROM tblTransactions INNER JOIN tblSplitBudgetCat ON
tblTransactions.TransactionsID =
tblSplitBudgetCat.TransactionsID
WHERE (((tblTransactions.DatePaid)=[Forms]![frmTrans]!
[DatePaid]) AND ((tblSplitBudgetCat.Debit)=[Forms]!
[frmTrans]![frmTransSubSub]![Debit]) AND
((tblSplitBudgetCat.AuxillaryComments)=[Forms]![frmTrans]!
[frmTransSubSub]![AuxillaryComments]) AND
((tblSplitBudgetCat.AuxillaryName)=[Forms]![frmTrans]!
[frmTransSubSub]![AuxillaryName]));

these are the datatypes used

DatePaid datatype = date/time
Debit and Credit datatype = currency
AuxillaryComments datatype = Memo
AuxillaryName datatype = Number



Question

How do I correctly format the string.
I also tried to save the query and open it using the
statement:


rst.Open "Query11", cnn, adOpenKeyset, , adCmdTableDirect

I get a message saying Cant open action query... What is
the correct way to open this action query so it runs?

Thanks for any help

 

[Allen Browne]

Concatenate the values into the string, and use the appropriate delimiters:

strSQL = "SELECT INTO ... " & _
"WHERE ((tblTransactions.DatePaid = " & _
Format([Forms]![frmTrans]![DatePaid], "\#mm\/dd\/yyyy\#") & _
") AND (tblSplitBudgetCat.Debit = " & _
[Forms]![frmTrans]![frmTransSubSub].Form![Debit] & _
") AND (tblSplitBudgetCat.AuxillaryComments = """ & _
[Forms]![frmTrans]![frmTransSubSub].Form![AuxillaryComments] & _
""") AND (tblSplitBudgetCat.AuxillaryName = " & _
[Forms]![frmTrans]![frmTransSubSub].Form![AuxillaryName] & "));"