Shay - Thanks for letting us know you found a solution.
Bill
--- In MS_Access_Professionals@yahoogroups.com, Shay Holmes <shaybellaholmes@...> wrote:
>
> 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@...> 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]
> > >
> >
> >
> >
>
>
> [Non-text portions of this message have been removed]
>
Selasa, 20 September 2011
[MS_AccessPros] Re: filtering out non printable characters
__._,_.___
MARKETPLACE
.
__,_._,___
Langganan:
Posting Komentar (Atom)
Tidak ada komentar:
Posting Komentar