How to Delete the First Character if it Equals a Specific Character

[mdurschlag]

I am trying to prevent display of the first character in a field, but only if the first character is a zero. So for example, I have a field named ContainerSize, and some of the data listed in the underlying table are as follows:

05 mL
30 mL
05 mL ID
10 mL ID

I would like to have the data appear in my report as:

5 mL
30 mL
5 mL ID
10 mL ID

I cannot seem to find the right combination of functions to do this. Any suggestions?

Thank you.

 

[pbaldy]

Try

IIf(Left(FieldName, 1) = 0, Mid(FieldName, 2), FieldName)

 

[mdurschlag]

The solution works in most cases. I had found a similar one. However, one of the cases (I should have added originally), does not contain any numbers. The cell content may be (few examples):

Box
NA
05 mL
10 mL

The code works as a control source for the report for the last 2 examples, but not the first two examples. I don't know why - since they should be evaluated as not containing a zero!

 

[pbaldy]

Try

IIf(Left(FieldName, 1) = "0", Mid(FieldName, 2), FieldName)

 

[mdurschlag]

That worked, thanks. I thought I had tried that and received an error stating that it was a variable mismatch, but it worked now.

 

[pbaldy]

Glad it worked for you.

 

[Galaxiom]

How about this alternative:

Format(Left(fieldname,2),"#0") & Mid(fieldname,3)

It only works for two digits but it can be modified for any number of digits if you want.

 

[mdurschlag]

I tried that new solution, but it caused an error in report. Thank you anyway!