sql string formatting

[Tim Johnson]

I copied the SQL statement below directly from the SQL
view of the query window.

I would like to use it in a procedure but cannot get the
correct string formatting for me to use it to open a
recordset with codes.

Thanks for any help.

SELECT Sum(tblDonationsDetails.Amount) AS SumOfAmount
FROM tblDonationsDetails
WHERE (((DatePart("yyyy",[DonationsDate]))=[Forms]!
[frmGrandTotals]![FiscalYear]));

 

[Dirk Goldgar]

I copied the SQL statement below directly from the SQL
view of the query window.

I would like to use it in a procedure but cannot get the
correct string formatting for me to use it to open a
recordset with codes.

"With codes"? Do you mean "in code"?

Thanks for any help.

SELECT Sum(tblDonationsDetails.Amount) AS SumOfAmount
FROM tblDonationsDetails
WHERE (((DatePart("yyyy",[DonationsDate]))=[Forms]!
[frmGrandTotals]![FiscalYear]));

How about something like this:

Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset( _
"SELECT Sum(Amount) AS SumOfAmount " & _
"FROM tblDonationsDetails " & _
"WHERE DatePart('yyyy',[DonationsDate])=" & _
[Forms]![frmGrandTotals]![FiscalYear])

I simplified it a bit, but the main points are (a) I substituted single
quotes for double quotes around "yyyy" so as to avoid conflicting with
the double quotes around the SQL string itself, and (b) I kept the
form!control reference outside the quotes, so that the actual value of
the control will be concatenated into the SQL string. If you leave the
reference inside the string, it becomes a parameter for which you must
explicitly provide a value -- DAO doesn't know about form and control
references.