Space at the end of the field

[Roy]

I use a A2K application.When downloading froma source,my data appears
like this:
Problem Id Product(s)
16957 TickettingApplication;
22620 TicketingApplication;
13021 TicketingServices;
11470 TicketingServices;
16886 TicketingServices ;
16990 TicketingServices ;
16512 EDTSOrderAdaptercomponent ;
22633 TRADINGSERVICES ;

I need the data where there is no space between the text and the semi
colon.(the semi colon follows the text).How to identify the space after
the text and have the semi colon immediately after the text?
To summarize,i want the data like,TicketingApplication; and not as
TicketingApplication ;

Thanks,
Roy

[Bruce Rusk]

You could do this by running an update query on the table after importing
it, something like this:

UPDATE tblTest SET tblTest.Field1 = Left([Field1],Len([Field1])-2) & ';'
WHERE (((tblTest.Field1) Like '* ;'));

"Roy" <pr********@yahoo.com> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.com...

I use a A2K application.When downloading froma source,my data appears
like this:
Problem Id Product(s)
16957 TickettingApplication;
22620 TicketingApplication;
13021 TicketingServices;
11470 TicketingServices;
16886 TicketingServices ;
16990 TicketingServices ;
16512 EDTSOrderAdaptercomponent ;
22633 TRADINGSERVICES ;

I need the data where there is no space between the text and the semi
colon.(the semi colon follows the text).How to identify the space after
the text and have the semi colon immediately after the text?
To summarize,i want the data like,TicketingApplication; and not as
TicketingApplication ;

Thanks,
Roy

[Roy]

Bruce,
It comes with a message saying 0 records updated.Meaning it
did not identify the space?

Thanks much,

Roy

[John Welch]

Are you asking how to prevent this situation from happening? If so, we'd
need more details about where this data is coming from.
If you are wanting to know how to clean up the data, you could use the
replace() function in visual basic and do something like
replace(strThisData, " ;" , ";"), perhaps looping through the data with a
recordset.

hope that helps
-John

"Roy" <pr********@yahoo.com> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.com...

I use a A2K application.When downloading froma source,my data appears
like this:
Problem Id Product(s)
16957 TickettingApplication;
22620 TicketingApplication;
13021 TicketingServices;
11470 TicketingServices;
16886 TicketingServices ;
16990 TicketingServices ;
16512 EDTSOrderAdaptercomponent ;
22633 TRADINGSERVICES ;

I need the data where there is no space between the text and the semi
colon.(the semi colon follows the text).How to identify the space after
the text and have the semi colon immediately after the text?
To summarize,i want the data like,TicketingApplication; and not as
TicketingApplication ;

Thanks,
Roy

[jimfortune]

Roy wrote:

I use a A2K application.When downloading froma source,my data appears
like this:
Problem Id Product(s)
16957 TickettingApplication;
22620 TicketingApplication;
13021 TicketingServices;
11470 TicketingServices;
16886 TicketingServices ;
16990 TicketingServices ;
16512 EDTSOrderAdaptercomponent ;
22633 TRADINGSERVICES ;

I need the data where there is no space between the text and the semi
colon.(the semi colon follows the text).How to identify the space after
the text and have the semi colon immediately after the text?
To summarize,i want the data like,TicketingApplication; and not as
TicketingApplication ;

Thanks,
Roy

Why not reverse the string and replace any spaces within the first two
or three characters with "" before reversing again? Air Code:
RevStr(Replace(Left(RevStr([Product(s)]), 3), " ", "") &
Right(RevStr([Product(s)], Len([Product(s)]) -
Len(Left(RevStr([Product(s)]), 3))))

If the Product(s) field can have a Null value you'll have to take care
of that also.

James A. Fortune

[Bruce Rusk]

> It comes with a message saying 0 records updated.Meaning it

did not identify the space?

Sounds that way.

You could check by running a select query:

SELECT * FROM tblTest WHERE tblTest.Field1 LIKE '* ;'

See how many records that returns (filling in your own table/field names, of
course). If doesn't return any records, there's something else going on --
perhaps invisible characters? Or what looks like a space is a tab?

You could also try:

UPDATE tblTest SET tblTest.Field1 = Left([Field1],Len([Field1])-2) & ";"
WHERE (Right(tblTest.Field1,2) = " ;");

[Roy]

Looks like some invisible character.Tried all the solutions ,your's as
well as the other two,with no luck.

Thanks,

Roy

[Roy]

Looks like some invisible character.Tried all the solutions ,your's as
well as the other two,with no luck.

Thanks,

Roy

[jimfortune]

Roy wrote:

Looks like some invisible character.Tried all the solutions ,your's as
well as the other two,with no luck.

Thanks,

Roy

See:

http://groups-beta.google.com/group/...0f165ea?hl=en&

Perhaps this is what is happening.

James A. Fortune

[jimfortune]

Roy wrote:

Looks like some invisible character.Tried all the solutions ,your's as
well as the other two,with no luck.

Thanks,

Roy

See:

http://groups-beta.google.com/group/...0f165ea?hl=en&

Perhaps this is what is happening.

James A. Fortune

[Roy]

James,

That link worked like a charm!!!!Thanks a lot.you saved me lots of
time and labor.

[Roy]

James,

That link worked like a charm!!!!Thanks a lot.you saved me lots of
time and labor.