By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,493 Members | 1,176 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,493 IT Pros & Developers. It's quick & easy.

Space at the end of the field

P: n/a
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

Nov 13 '05 #1
Share this Question
Share on Google+
11 Replies


P: n/a
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

Nov 13 '05 #2

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

Thanks much,

Roy

Nov 13 '05 #3

P: n/a
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

Nov 13 '05 #4

P: n/a
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

Nov 13 '05 #5

P: n/a
> 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) = " ;");

Nov 13 '05 #6

P: n/a
Roy
Looks like some invisible character.Tried all the solutions ,your's as
well as the other two,with no luck.

Thanks,

Roy

Nov 13 '05 #7

P: n/a
Roy
Looks like some invisible character.Tried all the solutions ,your's as
well as the other two,with no luck.

Thanks,

Roy

Nov 13 '05 #8

P: n/a
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

Nov 13 '05 #9

P: n/a
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

Nov 13 '05 #10

P: n/a
Roy
James,

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

Nov 13 '05 #11

P: n/a
Roy
James,

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

Nov 13 '05 #12

This discussion thread is closed

Replies have been disabled for this discussion.