Remove Space between

  • Thread starter Thread starter Allison
  • Start date Start date
A

Allison

I have this number 1-F31AI054229-01 AI-5754

I would like to remove the space between 01 & AI in the
above example.

I have it setup
=[field]& "-" &[field]&"-"[field] etc....

I tried &""& but I received an expressions error???
 
Correction this is how my label looks inside a query:

NEW GRANT ID: [FULL_GRANT_NUM]& " "& Right([CAN]),4)


Where would I put the information below??





-----Original Message-----
Allison

Access 2000 (and current) have a function called REPLACE.

You could try:

Replace([Field], " ", "", 1, , vbTextCompare)

HTH

--
Rob

FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
I have this number 1-F31AI054229-01 AI-5754

I would like to remove the space between 01 & AI in the
above example.

I have it setup
=[field]& "-" &[field]&"-"[field] etc....

I tried &""& but I received an expressions error???
Click to expand...


.
Click to expand...
 
Allison

I am a bit confused. Do you want the space between the fields
[Full_Grant_Num] and [Can]?
If the answer is no, then drop the " " & between the two fields to just
have:

[FULL_GRANT_NUM] & Right([CAN]),4)

If one of the fields has a space in it that you want to get rid of, then use
the REPLACE() function
on that one field. For example:

Replace([FULL_GRANT_NUM], " ", "", 1, , vbTextCompare) as
[FullGrantNum] & Right([CAN]),4)


Let me know how that goes for you.

--
Rob

FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Allison said:
Correction this is how my label looks inside a query:

NEW GRANT ID: [FULL_GRANT_NUM]& " "& Right([CAN]),4)


Where would I put the information below??





-----Original Message-----
Allison

Access 2000 (and current) have a function called REPLACE.

You could try:

Replace([Field], " ", "", 1, , vbTextCompare)

HTH

--
Rob

FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
I have this number 1-F31AI054229-01 AI-5754

I would like to remove the space between 01 & AI in the
above example.

I have it setup
=[field]& "-" &[field]&"-"[field] etc....

I tried &""& but I received an expressions error???
Click to expand...


.
Click to expand...
Click to expand...
 
Your formula is not working??? I'm receiving an expression
error or syntax REPLACE ([Field, " "," ",1, , vbText
Compare)as I'm receiving an error when I place the two on
the same lines.

[APPL_TYPE_CODE] & "-" & [ACTIVITY_CODE] & "-" &
[ADMIN_PHS_ORG_CODE] & " " & [SERIAL_NUM] & "-" &
[SUPPORT_YEAR] & " " & [SUFFIX_CODE] & "-" & Right([CAN],4)

I would like to reduce the space that is currently in
front of the SUPPORT_YEAR within the field. Can you
provide assistance??


-----Original Message-----
Allison

I am a bit confused. Do you want the space between the fields
[Full_Grant_Num] and [Can]?
If the answer is no, then drop the " " & between the two fields to just
have:

[FULL_GRANT_NUM] & Right([CAN]),4)

If one of the fields has a space in it that you want to get rid of, then use
the REPLACE() function
on that one field. For example:

Replace([FULL_GRANT_NUM], " ", "", 1, , vbTextCompare) as
[FullGrantNum] & Right([CAN]),4)


Let me know how that goes for you.

--
Rob

FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Correction this is how my label looks inside a query:

NEW GRANT ID: [FULL_GRANT_NUM]& " "& Right([CAN]),4)


Where would I put the information below??





-----Original Message-----
Allison

Access 2000 (and current) have a function called REPLACE.

You could try:

Replace([Field], " ", "", 1, , vbTextCompare)

HTH

--
Rob

FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
"Allison" <[email protected]> wrote
Click to expand...
in
message
I have this number 1-F31AI054229-01 AI-5754

I would like to remove the space between 01 & AI in the
above example.

I have it setup
=[field]& "-" &[field]&"-"[field] etc....

I tried &""& but I received an expressions error???




.
Click to expand...
Click to expand...


.
Click to expand...
 
Allison

I think I gave you TOO much on the syntax.

Just use the first 3 parameters of the REPLACE function. Don't add the rest.

So you will have Replace (<expression>, <search for>, <replace with>)

Sorry about that! =)

Let me know how it goes: (Email directly at: Rob @ fmsinc .
com )
--
Rob

FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Allison said:
Your formula is not working??? I'm receiving an expression
error or syntax REPLACE ([Field, " "," ",1, , vbText
Compare)as I'm receiving an error when I place the two on
the same lines.

[APPL_TYPE_CODE] & "-" & [ACTIVITY_CODE] & "-" &
[ADMIN_PHS_ORG_CODE] & " " & [SERIAL_NUM] & "-" &
[SUPPORT_YEAR] & " " & [SUFFIX_CODE] & "-" & Right([CAN],4)

I would like to reduce the space that is currently in
front of the SUPPORT_YEAR within the field. Can you
provide assistance??


-----Original Message-----
Allison

I am a bit confused. Do you want the space between the fields
[Full_Grant_Num] and [Can]?
If the answer is no, then drop the " " & between the two fields to just
have:

[FULL_GRANT_NUM] & Right([CAN]),4)

If one of the fields has a space in it that you want to get rid of, then use
the REPLACE() function
on that one field. For example:

Replace([FULL_GRANT_NUM], " ", "", 1, , vbTextCompare) as
[FullGrantNum] & Right([CAN]),4)


Let me know how that goes for you.

--
Rob

FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Correction this is how my label looks inside a query:

NEW GRANT ID: [FULL_GRANT_NUM]& " "& Right([CAN]),4)


Where would I put the information below??






-----Original Message-----
Allison

Access 2000 (and current) have a function called REPLACE.

You could try:

Replace([Field], " ", "", 1, , vbTextCompare)

HTH

--
Rob

FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
message


I have this number 1-F31AI054229-01 AI-5754

I would like to remove the space between 01 & AI in the
above example.

I have it setup
=[field]& "-" &[field]&"-"[field] etc....

I tried &""& but I received an expressions error???




.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top