Nz, Replace, and Format - Help Pls

  • Thread starter Thread starter Chris Lines
  • Start date Start date
C

Chris Lines

Guys

More help pls. I am using this string in my form header

=Replace("Post File No: |","|",Nz([TaskPostFileNo]," (New)"))

It works fine, but returns "Post File No" in lower case and without the
dividing forward slash. Looks like this ... admlh1234 ... but should look
like this ... ADMLH/12/34. There's a complicating factor. Post File No has
recently changed format so now it looks like this ... LH/12/34. I am using a
validation rule and capitalisation format in the underlying table.

If I understand other posts around conditional formatting, I need to add
FORMAT to my string to get it looking how I want it to look. I don't have any
idea how to structure the string. Very grateful for any help. Thanks in
advance.

Chris
 
BruceM

Thank you for your response.

The forward slashes are part of my work's file number reference. So, if I am
looking at a 2-year-old file I would see its reference number written as
ADMLH/12/34. Work changed the format recently, so if I am looking at last
week's files I would see the reference number as LH/12/34. Thanks to this
forum, I have figured out a way for my files database to cater for this
change in file number format from ADMLH/12/34 to LH/12/34. To enable the
different file formats, I am using a validation rule (rather than an input
mask) to handle the different formats. That aspect is working as I want it to.

What I am trying to do is display the file reference number (ADMLH/12/34 or
LH/12/34) in the header of my frmFilesDetails. The string should result in:

Post File No: LH/12/34 (or ADMLH/12/34)

I am displaying the reference number by using the string:

=Replace("Post File No: |","|",Nz([TaskPostFileNo]," (New)"))

But, the string is actually returning:

Post File No: lh/12/34 (or admlh/12/34).

On advice, I am storing in my table the literal characters lh1234. I am
trying to figure out how to make the string return what I want .. a mixture
of upper case and lower case as shown below.

Post File No: LH/12/34 (or ADMLH/12/34.

I hope this answers your question.

Thanks again for your help with this.

Chris

BruceM via AccessMonster.com said:
Why would there be forward slashes? Are they in TaskPostFileNo? Also, your
use of Replace may be overly complicated if you are trying to combine values.
You may be able to do something like:
="Post File No: " & Nz([TaskPostFileNo]," (New)")

You can format a text box for upper case by using a greater than sign (>) as
the Format in the Property Sheet for the text box.

Chris said:
Guys

More help pls. I am using this string in my form header

=Replace("Post File No: |","|",Nz([TaskPostFileNo]," (New)"))

It works fine, but returns "Post File No" in lower case and without the
dividing forward slash. Looks like this ... admlh1234 ... but should look
like this ... ADMLH/12/34. There's a complicating factor. Post File No has
recently changed format so now it looks like this ... LH/12/34. I am using a
validation rule and capitalisation format in the underlying table.

If I understand other posts around conditional formatting, I need to add
FORMAT to my string to get it looking how I want it to look. I don't have any
idea how to structure the string. Very grateful for any help. Thanks in
advance.

Chris
Click to expand...
Click to expand...
 
=Replace("Post File No: |","|",Nz([TaskPostFileNo]," (New)"))

But, the string is actually returning:

Post File No: lh/12/34 (or admlh/12/34).
Click to expand...

Just uppercase it by force:

UCase(Replace("Post File No: |","|",Nz([TaskPostFileNo]," (New)")))
 
John

Thank you for your advice. I've got it working the way I want it now thanks
to everyone's help. Thanks for your input.

Regards

Chris

John W. Vinson said:
=Replace("Post File No: |","|",Nz([TaskPostFileNo]," (New)"))

But, the string is actually returning:

Post File No: lh/12/34 (or admlh/12/34).
Click to expand...

Just uppercase it by force:

UCase(Replace("Post File No: |","|",Nz([TaskPostFileNo]," (New)")))
Click to expand...
 
Back
Top