[AccessD] AC2010-a pioneer with more arrows in the back...

I'd never  used one in Access (I have in SQL Server), so thought I'd have a go.  

I used the test DB, I knocked up earlier for Susan's concatentate function.  I thought I'd do 
something simple for a start so I thought I'd create a Product Code consisting of : 

Format$(Product_PK,"0000" & Left$([Product_Descr],4)

It threw the helpful error:

"The expression Format$(Product_PK,"0000" & Left$([Product_Descr],4) cannot be used in a 
calculated column"

A bit of testing reveals that you can't use Format/Format$ in a calculated field.

I wonder how many other restrictions I will come across when I try to use it in practice!

And before someone points it out:
 Right$("0000" & [Product_PK],4) & Left$([ProductDescr],4) is a useable  work-around :-)


-- 
Stuart

On 11 May 2012 at 17:13, Mark Simms wrote:

> I was pioneering the new "Calculated Column" Feature. My client demanded it
> in fact.
> 
> Looked good on the surface, but wow, the limitations are very bad.
> 
> After 4 hours of experimentation, I discovered:
> 
> Expressions "appear" to be limited to 2048 chars.
> 
> Expressions can't be modified after the column has been constructed. It must
> be deleted and redefined.
> 
> I had 48 fields to add-up (I DIDN'T DESIGN THIS !!!!).
> 
> So I created some VBA to create the expression dynamically; it would have
> taken forever to do by hand.
> 
> I had use Nz, but it would not accept it, so I used the alternative:
> IIF(IsNull(),0,[column])+..
> 
> That was accepted, but then the expression length went over 2048 chars.
> 
> IT GETS BETTER: When you open the linked table that has the expression, you
> get an error message:"expression exceeds 2048 characters".
> 
> However, the computation is CORRECT and all 48 field computations were
> accepted.
> 
> Queries run fine, forms run fine.
> 
> Another head-shaker..how can this be ?
> 
> It appears that the error message is ERRONEOUS and is thrown based on an
> invalid constant in MSFT's coding.
> 
> Perhaps it was an old 2007 limitation that was "lifted".
> 
> Otherwise, the calculation should have been wrong or displayed a null value.
> 
>  
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>