The # sign as a literal pre/postfix (pound/hash/octothorpe/number sign) around a date is a JET database
'feature'. Through a driver/provider you use the ' sign as a literal pre/postfix.
This is one of those places that it is best to use a parameterized query or better yet a stored procedure (yes,
QueryDefs in MSAccess can be 'executed' as a stored procedure).
And of course, only one set of literals are necessary when used.
- dave
Ryan Everhart wrote:
My test query on an Access database did not require the ticks around the
pound signs. The pound signs alone worked fine.
Ryan
On 7/11/06, *Sona Solanki * <[EMAIL PROTECTED]
<mailto:[EMAIL PROTECTED]>> wrote:
you need quotes around the pound signs.
On 7/10/06, *Ray Hughes* < [EMAIL PROTECTED]
<mailto:[EMAIL PROTECTED]>> wrote:
Hi Ryan,
This query with # signs
SELECT Q01, Q02, Q03
FROM Cars
WHERE dateCreated BETWEEN #2006-01-05# AND #2006-01-11#
Returns no records.
Thanks
Regrads
>From: "Ryan Everhart" <[EMAIL PROTECTED]
<mailto:[EMAIL PROTECTED]>>
>Reply-To: Dallas/Fort Worth ColdFusion User Group Mailing
>List< [email protected] <mailto:[email protected]>>
>To: "Dallas/Fort Worth ColdFusion User Group Mailing
>List"< [email protected] <mailto:[email protected]>>
>Subject: Re: [DFW CFUG] Trouble Obtaining a date range from an
ACCESS
>database
>Date: Mon, 10 Jul 2006 14:41:00 -0500
>
>Ray,
>I just tried some stuff and this is what I came up with... try
adding, if
>you can, pound signs and remove the single ticks. I'm not sure
why, but
>some how this tells access that it is a date.
>
>WHERE dateCreated BETWEEN #2006-01-05# AND #2006-01-11#
>
>Note I tired this in Access stand alone, not through ColdFusion
to Access.
>
>Ryan
>
>
>
>On 7/10/06, Ray Hughes <[EMAIL PROTECTED]
<mailto:[EMAIL PROTECTED]>> wrote:
> >
> >Hi All,
> >
> >Thanks everyone I am still searching.
> >
> >Regards
> >
> >This query:
> >SELECT dateCreated
> >FROM cars
> >WHERE dateCreated BETWEEN '2006-01-05' AND '2006-01-11'
> >
> >Returns an error
> >Error: Data type mismatch in criteria expression.
(State:22005, Native
> >Code:
> >FFFFF42A)
> >
> >
> >This query:
> >SELECT dateCreated
> >FROM cars
> >WHERE dateCreated BETWEEN {d '2006-01-05'} AND {d '2006-01-11'}
> >
> >Returns ZERO Records
_______________________________________________
Reply to DFWCFUG:
[email protected]
Subscribe/Unsubscribe:
http://lists1.safesecureweb.com/mailman/listinfo/list
List Archives:
http://www.mail-archive.com/list%40list.dfwcfug.org/
http://www.mail-archive.com/list%40dfwcfug.org/
DFWCFUG Sponsors:
www.HostMySite.com
www.teksystems.com/
