ACCESS FEED: Re: [MS_AccessPros] Re: filtering out non printable characters

Selasa, 20 September 2011

Re: [MS_AccessPros] Re: filtering out non printable characters

Sorry for taking so long to get back to this - it's our crazy time at work.

So, filtering for char(9) in T-SQL demonstrated that the tab character is,
in fact, stored in the database and not a wild-crazy-Access-bug thing.

Using CASE WHEN field = CHAR(9) THEN NULL ELSE field END worked for cleaning
up my report.

Thank you!

~*~*~*~*~*~*~
Shay Bella Holmes
~*~*~*~*~*~*~

On Tue, Aug 30, 2011 at 8:56 AM, Bill Mosca <wrmosca@comcast.net> wrote:

> **
>
>
> Shay
>
> Interesting how I somehow knew it was a tab, huh? As to why it does not
> show in SSMS, I can't' tell you...except maybe
> SSMS is smart enough to know there reallyl is no need to show it that I
> know of. <g>
>
> I'll try and stop in every once in a while to see if you post an update.
>
> Bill
>
>
> --- In MS_Access_Professionals@yahoogroups.com, Shay Holmes
> <shaybellaholmes@...> wrote:
> >
> > At this point, the only one that's come up is the tab character. I still
> > can't find anything about why Access would show it and SS Management
> Studio
> > won't ....
> >
> > So, I'll give it a shot and let you know if that helps :)
> >
> > ~*~*~*~*~*~*~
> > Shay Bella Holmes
> > ~*~*~*~*~*~*~
> >
> >
> >
> > On Tue, Aug 30, 2011 at 8:44 AM, Bill Mosca <wrmosca@...> wrote:
> >
> > > **
> > >
> > >
> > > Shay
> > >
> > > Is it always the same character? if so, you will need to get the ASCII
> > > number for it and use Replace().
> > >
> > > Let's say the unprintable character is a Tab. ASCII number is 9.
> > >
> > > SELECT Replace([MyField],chr(9),"") As MyFixedField
> > > FROM MyTable
> > >
> > > Replace() is not going to actually change the data. It just temporarily
> > > changes the result as it appears in the recortdset.
> > >
> > > Regards,
> > > Bill Mosca, Founder - MS_Access_Professionals
> > > http://www.thatlldoit.com
> > > Microsoft Office Access MVP
> > > https://mvp.support.microsoft.com/profile/Bill.Mosca
> > >
> > >
> > > --- In MS_Access_Professionals@yahoogroups.com, Shay Holmes
> > > <shaybellaholmes@> wrote:
> > > >
> > > > Working with Access 2010 & SQL Server 2008 has given me 2 questions
> ...
> > > >
> > > > 1) Why would Access show a non printable character that SSMS does not
> > > show?
> > > > 2) How can I write a query (in T-SQL if not ANSI 92 SQL) that returns
> > > only
> > > > non zero length & printable characters? I cannot change anything to
> do
> > > with
> > > > SQL Server; I can only read from the tables.
> > > >
> > > > I tested my pass through queries in SSMS and it all looks fine, but
> when
> > > I
> > > > display one of them in Access I'm seeing a non-printable character (a
> tab
> > > > character) in the last column of a SELECT * FROM [table] pass through
> > > (query
> > > > is for data techs to be able to diagnose bad data problems).
> > > >
> > > > The tables (on MSSQL) are populated via a bulk insert procedure that
> > > > converts nulls to zero length strings. I'm supposed to be creating a
> > > related
> > > > query that shows the frequency of certain columns containing data,
> and
> > > I'm
> > > > told that the non printable character isn't hurting anything else and
> > > won't
> > > > be fixed any time soon.
> > > >
> > > > Any help would be most appreciated.
> > > >
> > > > ~*~*~*~*~*~*~
> > > > Shay Bella Holmes
> > > > ~*~*~*~*~*~*~
> > > >
> > > >
> > > > [Non-text portions of this message have been removed]
> > > >
> > >
> > >
> > >
> >
> >
> > [Non-text portions of this message have been removed]
> >
>
>
>