Usage of "#" or " ' " as an Date deliminator

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Project details: Access front End
SQL Back End
Why in Access Programming, sometimes using " # " (Pound sign) as date
deliminator works and sometimes have to use " ' "(Single quote)?
How can we decied when to use specific one?
 
When I use ADO CurrentProject.Connection this is what I use.

strSQL4 = "SELECT StartTons, StartTonsAdjust, " _
& "SampleTons, ShipmentTons, EndTons, " _
& "ShiftTonsAdjust, UserID " _
& "FROM tbl_Prod_Shift " _
& "WHERE (((ShiftDate)=#" & myvardate & "#) " _
& "AND ((Shift)=" & shft & ") AND ((Storage)=" & stor &
")); "


When I pass the SQL directly to SQL server using ADO OLEDB then this is what
I use.

strSQL = "SELECT tbl_coal_perf.fld_Date, tbl_coal_perf.fld_Crushed,
" _
& "tbl_coal_perf.fld_plan_tons, tbl_coal_perf.fld_truck_hours "
_
& "FROM tbl_coal_perf " _
& "INNER JOIN lst_supv ON tbl_coal_perf.fld_Supv_Coal =
lst_supv.ID " _
& "WHERE (tbl_coal_perf.fld_Date " _
& "BETWEEN CONVERT(DATETIME, '" & date1 & "', 102) AND " _
& "CONVERT(DATETIME, '" & date2 & "', 102)) AND " _
& "(lst_supv.fld_name = '" & shtname(j) & "') "

All date variables (myvardate, date1 and date2) are dimensioned as string.
To avoid confusion I always Dim dates as string and then delimit them
according to what the connection expects.

--
Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
| Project details: Access front End
| SQL Back End
| Why in Access Programming, sometimes using " # " (Pound sign) as date
| deliminator works and sometimes have to use " ' "(Single quote)?
| How can we decied when to use specific one?
 
It depends on which component processes your explicit date value.

If it is VBA or JET, the date delimiter is #. VBA and JET often do
type-casting if you use ' as delimiter but you cannot rely on the automatic
type-casting.

If it is the SQL Server back end, the date delimiter is '. However, there
are so many options so you best bet is to check the B.O.L. for either the
MS-SQL CAST() or CONVERT() function.
 
Back
Top