[AccessD] SPAM-LOW: Re: Bug in access

What is truly odd is that even wrapping it in another function (such as format() ) still triggers 
the error.

I have written an application to shuffle fields around, and allow my client to create a custom sql 
statement dynamically.  The function takes the contents of fields and interprets them, then 
"renames" them.

field1 returns the value of Field1, but in a specific field name of the query.  So Field1 - Field2 
works (returns the subtraction), format(field1,"000.000") works etc.  The client tried to do a 
Field1 + Field2 and got a null value.  It turns out that the source values are strings, and the + 
operator CONCATS two strings, but other operators cause an implicit conversion to a numeric value 
and work correctly.  I was trying to wrap the two field names in a function to force a numeric 
conversion when I ran into this.  I ended up just using Format(cSng(Field1) + cSng(Field2), 
"000.00") to get around it.



John W. Colby
www.ColbyConsulting.com


David McAfee wrote:
> Good find. I guess I use it in VBA, which is what was bugging me. I
> never saw this error before.
> 
> Oh well, as JC said, Shiny new  Ribbon for A2007, but bugs still exist.
> 
> I like the MS fix "Create a custom function".  Hey MS! Fix it!
> 
> 
> 
> On Tue, Nov 10, 2009 at 1:00 PM, Jack and Pat <drawbridgej at sympatico.ca> wrote:
>> Found this via Google -- related to ACC2000
>>
>> http://support.microsoft.com/kb/225931
>>
>> "The CDec() function is supported in Visual Basic for Applications code, but
>> not in Access queries."
>> "Create a custom function that uses the CDec() function. Call this custom
>> function from your Access query. For example:
>>
>>   1. Create a new module and type the following code:
>>
>>      Function NewCDec(MyVal)
>>         NewCDec = CDec(MyVal)
>>      End Function
>>
>>
>>   2. Save and close the module.
>>   3. Type MyID: NewCDec([CategoryID]) in the Field row of a query. Note
>> that when you run this function, it returns a valid value for the MyID
>> field."
>>
>> jack
>>
>> -----Original Message-----
>> From: accessd-bounces at databaseadvisors.com
>> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
>> Sent: Tuesday, November 10, 2009 3:48 PM
>> To: Access Developers discussion and problem solving
>> Subject: Re: [AccessD] SPAM-LOW: Re: Bug in access
>>
>> Sorry,
>>
>> I miswrote the email.  the failing function is cDec - convert to decimal.
>> cDec("123.45") in the
>> debug window returns 123.45 as a NUMBER.
>>
>> m: cDec("123.45") - an aliased field - in the query window immediately
>> causes an error.
>>
>> Sorry for the confusion.
>>
>> John W. Colby
>> www.ColbyConsulting.com
>>
>>
>> Steve Schapel wrote:
>>> --------------------------------------------------
>>> From: "Heenan, Lambert" <Lambert.Heenan at chartisinsurance.com>
>>> Sent: Wednesday, November 11, 2009 7:09 AM
>>>
>>>> The following SQL runs just fine for me, I Access 2003.
>>>>
>>>> SELECT CDbl("123.45") AS SomeDouble, BankNames_tbl.BankID
>>>> FROM BankNames_tbl;
>>> Same for me in Access 2007.  No problem.
>>>
>>> I have used CDbl in queries plenty of times, with no problem.  And I have
>>> never heard mention of the type of behaviour you are experiencing, John.
>> So
>>> it's an oddity.
>>>
>> --
>> AccessD mailing list
>> AccessD at databaseadvisors.com
>> http://databaseadvisors.com/mailman/listinfo/accessd
>> Website: http://www.databaseadvisors.com
>> No virus found in this incoming message.
>> Checked by AVG - www.avg.com
>> Version: 9.0.704 / Virus Database: 270.14.59/2494 - Release Date: 11/10/09
>> 02:38:00
>>
>>
>> --
>> AccessD mailing list
>> AccessD at databaseadvisors.com
>> http://databaseadvisors.com/mailman/listinfo/accessd
>> Website: http://www.databaseadvisors.com
>>
>