Nz, Replace, and Format - Help Pls

[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

 

[Chris Lines]

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

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.

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

 

[John W. Vinson]

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

But, the string is actually returning:

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

Just uppercase it by force:

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

 

[Chris Lines]

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

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

But, the string is actually returning:

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

Just uppercase it by force:

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