Single quotes on the date part fixed it:
UPDATE FAILUREREPORT
SET STR_DISPOSITION = 'SCRAP'
WHERE STR_DISPOSITION IS NULL
AND INT_PARTRETURNED = Yes
AND DT_PARTRETURNED IS NOT NULL
AND DATEDIFF('d', DT_PARTRETURNED, DATE()) > 30
On Fri, Sep 12, 2008 at 9:07 AM, Aaron Rouse <[EMAIL PROTECTED]> wrote:
> The Now() function though is an Access function and not the CF one. I just
> tried using the Date() function to see if that would change anything but no
> luck.
> I just tried the yes/no and 1/0 again just to make sure I was remembering
> correctly and also tried variations of that with cfqueryparam. I left it
> with a cfqueryparam and the bit type with the value of 1 because after
> searching through the app I saw that is how it did its searches.
>
> It is definitely that last line though since if I remove it then I get no
> errors. I will play with it some more and see what in there would be the
> cause. So far I have also tried passing in the other two optional pieces to
> the function but that changed nothing.
>
>
> On Fri, Sep 12, 2008 at 8:46 AM, Scott Stewart <[EMAIL PROTECTED]>wrote:
>
>> Couple of things to try,
>>
>> Access will accept "yes/no" or "1/0" depending on what you've selected
>> in the design of the DB.
>> default is "yes/no" (I think).
>>
>> wrap up the now() in "CreateODBCDate()" and see if that clears it up.
>> It's been ages since I've touched Access.
>>
>> Aaron Rouse wrote:
>> > Using 1 instead of Yes throws an error if I remember correctly because
>> that
>> > was one of my first guesses as well. I am going to try some of these
>> ideas
>> > this afternoon once I have access to it again(work got canceled due to
>> > hurricane Ike).
>> >
>> > On Fri, Sep 12, 2008 at 8:27 AM, Mark Kruger <[EMAIL PROTECTED]>
>> wrote:
>> >
>> >
>> >> My guess would be the bit column... Use a 0 or 1....
>> >>
>> >>
>> >> UPDATE FAILUREREPORT
>> >> SET STR_DISPOSITION = 'SCRAP'
>> >> WHERE STR_DISPOSITION IS NULL
>> >> AND INT_PARTRETURNED = 1
>> >> AND DT_PARTRETURNED IS NOT NULL
>> >> AND DATEDIFF("d", DT_PARTRETURNED, NOW()) > 30
>> >>
>> >> You might also try "= NULL" instead of "IS NULL". The driver is being
>> >> particular.
>> >>
>> >>
>> >>
>> >> -Mark
>> >>
>> >>
>> >>
>> >> Mark A. Kruger, CFG, MCSE
>> >> (402) 408-3733 ext 105
>> >> www.cfwebtools.com
>> >> www.coldfusionmuse.com
>> >> www.necfug.com
>> >>
>> >> -----Original Message-----
>> >> From: Aaron Rouse [mailto:[EMAIL PROTECTED]
>> >> Sent: Thursday, September 11, 2008 10:09 PM
>> >> To: CF-Talk
>> >> Subject: Re: CF and Access
>> >>
>> >> Yeah, the function is an Access function, just has the same name as CF.
>> I
>> >> thought maybe the Yes/No column was throwing it off initially and tried
>> a
>> >> cfqueryparam on that one but it helped none. I did not play with the
>> date
>> >> though and will see if maybe that is the root of it.
>> >>
>> >> On Thu, Sep 11, 2008 at 7:35 PM, Matt Quackenbush
>> >> <[EMAIL PROTECTED]>wrote:
>> >>
>> >>
>> >>> No problem with using DateDiff() as he is using it, since it is not
>> >>> surrounded by #. It has been a bazillion years since I've used
>> >>> Access, but as I recall, that particular error message has something
>> >>> to do with a lack of quotes (e.g. 'foo') on a field that the driver
>> >>> wants them on. Maybe the date field? Can't remember for sure.
>> >>>
>> >>>
>> >>> On Thu, Sep 11, 2008 at 7:28 PM, Mike Little wrote:
>> >>>
>> >>>
>> >>>> at a rough guess, i suspect that you cannot use an explicit cf
>> >>>> function such as datediff in the query.
>> >>>>
>> >>>>
>> >>>
>> >>>
>> >>
>> >>
>> >>
>> >
>> >
>>
>>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;203748912;27390454;j
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:312429
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
