Re: Wake up, 97 Query in 2000/03

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Albert D. Kallal (PleaseNOOOsPAMMkallal_at_msn.com)
Date: 10/24/04 

Date: Sat, 23 Oct 2004 23:16:23 -0600

"Chris Mills" <phad_nospam@cleardotnet.nz> wrote in message
news:OJES$FJuEHA.2948@TK2MSFTNGP15.phx.gbl...
> The only difference between A97 queries and giving no results on later
> Access,
> that I know of, is that it's sometimes now necessary to put
> WHERE Trim(<something>) = SOMETHING
> which would previously just have been
> WHERE <something> = SOMETHING
>
> I don't know if this is usual or not, but I got into the habit of doing it
> anyway from A2000 onwards. Since stored text fields are trimmed anyway,
> I'm
> not about to theorise.

Hum, I can't say I every needed to do the trim. And, you should know that
wrapping a field name like:

where trim([CompanyName] = 'My Cool company'

means that indexing can NOT be used! This will slow down your application by
VERY large amount. I would find out why that original query failed. (could
have been just one extra space or something!). Wrapping the field name in a
trim is not the best idea here.

Perhaps you imported some data from another source (like Excel...or perahps
some text file) that did not have the data in fields trimmed in the first
place. However, the solution to that is to run a update query with a trim
command to remove that extra stuff ONCE. This will eliminate the need for
the trim each time, and more important allow ms-access to using indexing.
So, you don't want to use any functions around fields unless you have to.... 

-- 
Albert D. Kallal   (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@msn.com
http://www.attcanada.net/~kallal.msn

Relevant Pages

  • Re: Wake up, 97 Query in 2000/03
    ... You make good points, Albert. ... Clearly, Trim means I was referring to a Text field, and if it was a floating ... I'm the only one to report it, ... > means that indexing can NOT be used! ...
    (microsoft.public.access.conversion)
  • Re: trimming extra spaces from large DB file
    ... MySQL has a trim function so I think the easiest way would be to load the ... > this list was put together, apparently there was extra space formatting ... > create such script. ...
    (alt.php)
  • Re: trimming extra spaces from large DB file
    ... MySQL has a trim function so I think the easiest way would be to load the ... > this list was put together, apparently there was extra space formatting ... > create such script. ...
    (comp.lang.php)
  • trimming extra spaces from large DB file
    ... apparently there was extra space formatting ... INSERT INTO `table_one` VALUES ('Jane Doe', ... I was thinking some kind of script could be put together to trim the ...
    (comp.lang.php)
  • trimming extra spaces from large DB file
    ... apparently there was extra space formatting ... INSERT INTO `table_one` VALUES ('Jane Doe', ... I was thinking some kind of script could be put together to trim the ...
    (alt.php)