String formating

G

Garry

Hi, I have a text box thus

=[Ref] & " " &
Code: 
 & " " & [CountOverAll] & " " & [CountOverGroup] & " "
& [Marker]

This results in ABC 223 21 6 *

I want CountOverAll and CountOverGroup to be 5 digits

Result I require is ABC 223 00021 00006 *

Also if Ref or Code is null to close up the string so there are no excesive
spaces

Thank you
 
J

John W. Vinson

Hi, I have a text box thus

=[Ref] & " " &
Code: 
 & " " & [CountOverAll] & " " & [CountOverGroup] & " "
& [Marker]

This results in ABC 223 21 6 *

I want CountOverAll and CountOverGroup to be 5 digits

Result I require is ABC 223 00021 00006 *

Also if Ref or Code is null to close up the string so there are no excesive
spaces

Thank you
[/QUOTE]

A couple of tricks you can use: for one, the + and & operators both
concatenate strings, but + returns NULL if either argument is NULL; & treats a
null as an empty string.

Try

([Ref] + " ") & ([Code] + " ") & Right([CountOverAll[ & "00000", 5) &
Right([CountOverGroup] & "00000", 5) & " " & [Marker]
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com
Click to expand...
 
G

Gloops

So, you say, if Code is null, you do not want extra space after it.

Iif(IsNull(Code), "", Code + " ")

I presume it would also be a good idea to verify that Code is not an
empty string :

Iif(Nz(Code, "")="", "", Code + " ")

Are you sure you can use Ref as a field name ? Or is it a reserved word ?
 
G

Garry

Thanks very much

I cannot get the Right([CountOverGroup] & "00000", 5) to work though

The result is 00000 for some reason

Cheers, Garry
 
B

Bob Quintal

Thanks very much

I cannot get the Right([CountOverGroup] & "00000", 5) to work
though

The result is 00000 for some reason

Cheers, Garry
Click to expand...

try Right("00000" & [CountOverGroup] , 5)

Garry said:
Hi, I have a text box thus

=[Ref] & " " &
Code: 
 & " " & [CountOverAll] & " " &
[CountOverGroup] & " " & [Marker]

This results in ABC 223 21 6 *

I want CountOverAll and CountOverGroup to be 5 digits

Result I require is ABC 223 00021 00006 *

Also if Ref or Code is null to close up the string so there are
no excesive spaces

Thank you
[/QUOTE]
[/QUOTE]
Click to expand...
Click to expand...
 
G

Garry

Right("00000" & [CountOverGroup] , 5) is the way to go thanks very much

This is weird though

If I place a text box =[Ref] I get ABC

If I place a text box =([Ref] + " ") I get ABC

If I place a text box =
Code: 
 I get 223

if I place a text box =([Code] + " ") I get error

what could be wrong

Garry


[QUOTE="Garry"]
Thanks very much

I cannot get the Right([CountOverGroup] & "00000", 5) to work though

The result is 00000 for some reason

Cheers, Garry


[QUOTE="Garry"]
Hi, I have a text box thus

=[Ref] & " " & [Code] & " " & [CountOverAll] & " " & [CountOverGroup] & "
" & [Marker]

This results in ABC 223 21 6 *

I want CountOverAll and CountOverGroup to be 5 digits

Result I require is ABC 223 00021 00006 *

Also if Ref or Code is null to close up the string so there are no
excesive spaces

Thank you
[/QUOTE]
[/QUOTE]
 
G

Gloops

Garry wrote, on 25th Apr. 2012 14:16 UTC + 1 :
If I place a text box =
Code: 
 I get 223

if I place a text box =([Code] + " ") I get error[/QUOTE]

I guess Code is numeric.

Try Code & " "
Click to expand...
 
D

Douglas J Steele

You can't concatenate numbers to text: you need to convert the numeric
Code: 
 to string:

=(Cstr([Code]) & " ")

"Garry"  wrote in message 
Right("00000" & [CountOverGroup] , 5) is the way to go thanks very much

This is weird though

If I place a text box =[Ref] I get ABC

If I place a text box =([Ref] + " ") I get ABC

If I place a text box =[Code] I get 223

if I place a text box =([Code] + " ") I get error

what could be wrong

Garry


[QUOTE="Garry"]
Thanks very much

I cannot get the Right([CountOverGroup] & "00000", 5) to work though

The result is 00000 for some reason

Cheers, Garry


[QUOTE="Garry"]
Hi, I have a text box thus

=[Ref] & " " & [Code] & " " & [CountOverAll] & " " & [CountOverGroup] & "
" & [Marker]

This results in ABC 223 21 6 *

I want CountOverAll and CountOverGroup to be 5 digits

Result I require is ABC 223 00021 00006 *

Also if Ref or Code is null to close up the string so there are no
excesive spaces

Thank you
[/QUOTE]
[/QUOTE]
 
G

Garry

Thanks Douglas just the job . . .=(Cstr(
Code: 
) & " ")

One very last question

In an effort to keep columns tidy I run the following code to move data from
one column to an empty column as:

UPDATE [Client Data] SET [Client Data].Address1 = Trim([Address2]), [Client
Data].Address2 = Trim([Address3])
WHERE ((([Client Data].Address1) Is Null Or ([Client Data].Address1)=""));

etc etc

In my report the address label is

IIf(IsNull([ADDRESS1]),"",[ADDRESS1] & Chr(13) & Chr(10)) &
IIf(IsNull([ADDRESS2]),"",[ADDRESS2] & Chr(13) & Chr(10)) &
IIf(IsNull([ADDRESS3]),"",[ADDRESS3]

etc etc

There are still blank address lines in address block though

I have checked the fields and they appear to be empty

Is it possible to fix this

Garry

[QUOTE="Garry"]
Right("00000" & [CountOverGroup] , 5) is the way to go thanks very much

This is weird though

If I place a text box =[Ref] I get ABC

If I place a text box =([Ref] + " ") I get ABC

If I place a text box =[Code] I get 223

if I place a text box =([Code] + " ") I get error

what could be wrong

Garry


[QUOTE="Garry"]
Thanks very much

I cannot get the Right([CountOverGroup] & "00000", 5) to work though

The result is 00000 for some reason

Cheers, Garry


[QUOTE="Garry"]
Hi, I have a text box thus

=[Ref] & " " & [Code] & " " & [CountOverAll] & " " & [CountOverGroup] &
" " & [Marker]

This results in ABC 223 21 6 *

I want CountOverAll and CountOverGroup to be 5 digits

Result I require is ABC 223 00021 00006 *

Also if Ref or Code is null to close up the string so there are no
excesive spaces

Thank you
[/QUOTE]
[/QUOTE]
[/QUOTE]
 
D

Douglas J Steele

First thing to do would be determine whether it's Null or zero-length
strings that are causing your problem. If it is ZLS, try

IIf(Len([ADDRESS1] & "") = 0,"",[ADDRESS1] & Chr(13) & Chr(10)) &
IIf(Len([ADDRESS2] & "") = 0,"",[ADDRESS2] & Chr(13) & Chr(10)) &
IIf(Len([ADDRESS3] & "") = 0,"",[ADDRESS3]

or even

IIf(Len(Trim([ADDRESS1] & "")) = 0,"",[ADDRESS1] & Chr(13) & Chr(10)) &
IIf(Len(Trim([ADDRESS2] & "")) = 0,"",[ADDRESS2] & Chr(13) & Chr(10)) &
IIf(Len(Trim([ADDRESS3] & "")) = 0,"",[ADDRESS3]

"Garry" wrote in message
Thanks Douglas just the job . . .=(Cstr(
Code: 
) & " ")

One very last question

In an effort to keep columns tidy I run the following code to move data from
one column to an empty column as:

UPDATE [Client Data] SET [Client Data].Address1 = Trim([Address2]), [Client
Data].Address2 = Trim([Address3])
WHERE ((([Client Data].Address1) Is Null Or ([Client Data].Address1)=""));

etc etc

In my report the address label is

IIf(IsNull([ADDRESS1]),"",[ADDRESS1] & Chr(13) & Chr(10)) &
IIf(IsNull([ADDRESS2]),"",[ADDRESS2] & Chr(13) & Chr(10)) &
IIf(IsNull([ADDRESS3]),"",[ADDRESS3]

etc etc

There are still blank address lines in address block though

I have checked the fields and they appear to be empty

Is it possible to fix this

Garry

[QUOTE="Garry"]
Right("00000" & [CountOverGroup] , 5) is the way to go thanks very much

This is weird though

If I place a text box =[Ref] I get ABC

If I place a text box =([Ref] + " ") I get ABC

If I place a text box =[Code] I get 223

if I place a text box =([Code] + " ") I get error

what could be wrong

Garry


[QUOTE="Garry"]
Thanks very much

I cannot get the Right([CountOverGroup] & "00000", 5) to work though

The result is 00000 for some reason

Cheers, Garry


[QUOTE="Garry"]
Hi, I have a text box thus

=[Ref] & " " & [Code] & " " & [CountOverAll] & " " & [CountOverGroup] &
" " & [Marker]

This results in ABC 223 21 6 *

I want CountOverAll and CountOverGroup to be 5 digits

Result I require is ABC 223 00021 00006 *

Also if Ref or Code is null to close up the string so there are no
excesive spaces

Thank you
[/QUOTE]
[/QUOTE]
[/QUOTE]
 
G

Garry

Sorry Douglas

cannot get this to work at all =IIf(Len([ADDRESS1] & "") = 0,"",[ADDRESS1])

What do you suggest

Garry


Garry said:
Thanks Douglas just the job . . .=(Cstr(
Code: 
) & " ")

One very last question

In an effort to keep columns tidy I run the following code to move data
from one column to an empty column as:

UPDATE [Client Data] SET [Client Data].Address1 = Trim([Address2]),
[Client Data].Address2 = Trim([Address3])
WHERE ((([Client Data].Address1) Is Null Or ([Client Data].Address1)=""));

etc etc

In my report the address label is

IIf(IsNull([ADDRESS1]),"",[ADDRESS1] & Chr(13) & Chr(10)) &
IIf(IsNull([ADDRESS2]),"",[ADDRESS2] & Chr(13) & Chr(10)) &
IIf(IsNull([ADDRESS3]),"",[ADDRESS3]

etc etc

There are still blank address lines in address block though

I have checked the fields and they appear to be empty

Is it possible to fix this

Garry

[QUOTE="Garry"]
Right("00000" & [CountOverGroup] , 5) is the way to go thanks very much

This is weird though

If I place a text box =[Ref] I get ABC

If I place a text box =([Ref] + " ") I get ABC

If I place a text box =[Code] I get 223

if I place a text box =([Code] + " ") I get error

what could be wrong

Garry


[QUOTE="Garry"]
Thanks very much

I cannot get the Right([CountOverGroup] & "00000", 5) to work though

The result is 00000 for some reason

Cheers, Garry


Hi, I have a text box thus

=[Ref] & " " & [Code] & " " & [CountOverAll] & " " & [CountOverGroup] &
" " & [Marker]

This results in ABC 223 21 6 *

I want CountOverAll and CountOverGroup to be 5 digits

Result I require is ABC 223 00021 00006 *

Also if Ref or Code is null to close up the string so there are no
excesive spaces

Thank you
[/QUOTE]
[/QUOTE]
[/QUOTE]
Click to expand...
 
G

Gloops

Douglas J Steele wrote, on 26th Apr. 2012 13:53 UTC + 1 :
First thing to do would be determine whether it's Null or zero-length
strings that are causing your problem. If it is ZLS, try

IIf(Len([ADDRESS1] & "") = 0,"",[ADDRESS1] & Chr(13) & Chr(10)) &
IIf(Len([ADDRESS2] & "") = 0,"",[ADDRESS2] & Chr(13) & Chr(10)) &
IIf(Len([ADDRESS3] & "") = 0,"",[ADDRESS3]

or even

IIf(Len(Trim([ADDRESS1] & "")) = 0,"",[ADDRESS1] & Chr(13) & Chr(10)) &
IIf(Len(Trim([ADDRESS2] & "")) = 0,"",[ADDRESS2] & Chr(13) & Chr(10)) &
IIf(Len(Trim([ADDRESS3] & "")) = 0,"",[ADDRESS3]
Click to expand...


Hello,

What about putting both tests together ?
Iif(Nz(Address1, "") = "", "", Address1 + vbCrLf)

+ so on

That way, if Address1 is either null or an empty string, you add an
empty string, otherwise you add Address1 followed by vbCrLf. For the
records, the value of vbCrLf is VBA.Chr$(13) + VBA.Chr$(10).
 
G

Gloops

Garry wrote, on 26th Apr. 2012 10:30 UTC + 1 :
There are still blank address lines in address block though

I have checked the fields and they appear to be empty
Click to expand...

Did you study the help about the CanGrow and CanSchrink properties, that
must be coherent between :
- all the controls on one line (textboxes I imagine)
- the section in which they are included (detail I imagine)
 
D

Douglas J Steele

What do you get?

"Garry" wrote in message
Sorry Douglas

cannot get this to work at all =IIf(Len([ADDRESS1] & "") = 0,"",[ADDRESS1])

What do you suggest

Garry


Garry said:
Thanks Douglas just the job . . .=(Cstr(
Code: 
) & " ")

One very last question

In an effort to keep columns tidy I run the following code to move data
from one column to an empty column as:

UPDATE [Client Data] SET [Client Data].Address1 = Trim([Address2]),
[Client Data].Address2 = Trim([Address3])
WHERE ((([Client Data].Address1) Is Null Or ([Client Data].Address1)=""));

etc etc

In my report the address label is

IIf(IsNull([ADDRESS1]),"",[ADDRESS1] & Chr(13) & Chr(10)) &
IIf(IsNull([ADDRESS2]),"",[ADDRESS2] & Chr(13) & Chr(10)) &
IIf(IsNull([ADDRESS3]),"",[ADDRESS3]

etc etc

There are still blank address lines in address block though

I have checked the fields and they appear to be empty

Is it possible to fix this

Garry

[QUOTE="Garry"]
Right("00000" & [CountOverGroup] , 5) is the way to go thanks very much

This is weird though

If I place a text box =[Ref] I get ABC

If I place a text box =([Ref] + " ") I get ABC

If I place a text box =[Code] I get 223

if I place a text box =([Code] + " ") I get error

what could be wrong

Garry


[QUOTE="Garry"]
Thanks very much

I cannot get the Right([CountOverGroup] & "00000", 5) to work though

The result is 00000 for some reason

Cheers, Garry


Hi, I have a text box thus

=[Ref] & " " & [Code] & " " & [CountOverAll] & " " & [CountOverGroup] &
" " & [Marker]

This results in ABC 223 21 6 *

I want CountOverAll and CountOverGroup to be 5 digits

Result I require is ABC 223 00021 00006 *

Also if Ref or Code is null to close up the string so there are no
excesive spaces

Thank you
[/QUOTE]
[/QUOTE]
[/QUOTE]
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Formula and Formating Help 5
Problems saving docx to doc97 or rtf formats programmatically 3
Trimming a dynamically changing string 2
eval of concatenate function not displaying results 1
need a formula to find "*" in s string and multiply by preceding andfollowing values 5
Multiples conditions in an array 3
Spell Number Module 2
type mismatch error in macro in excel 0

Top