format SQL statement

  • Thread starter Thread starter tim johnson
  • Start date Start date
T

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
 
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] & "));"
 
Back
Top