sql string formatting

  • Thread starter Thread starter Tim Johnson
  • Start date Start date
T

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]));
 
Tim Johnson said:
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.
Click to expand...

"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]));
Click to expand...

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.
 
Back
Top