How to handle This really Tough Question!!!!!!!!!!!!!!

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

==>Left([Measurements],InStr([Measurements],"X")-2) & "mm X " & Mid
([Measurements],InStr([Measurements],"X")+2) & "mm")

The above expression is used to add "mm" when user type a measurement from a
form and is preview in report
22 X 33(form) ==> 22mm X 33mm(report)

Unfortunately not all products is in measurement, some of them are
calculated in unit such as one door lock, two pieces of glasses,....

If i did not type any value or in wrong format in the field (#error) will
shown on the report

Can code solve this problem? I can accept any solution either to my form or
my report or in code.................as long as it solve the problem

Any solutions?

Thanks in advance

Kennykee
 
Ken,
Try this in the ControlSource of your report field...
=IIF(IsNull([Measurements],"",
Left([Measurements],InStr([Measurements],"X")-2) & "mm X " & Mid
([Measurements],InStr([Measurements],"X")+2) & "mm")

This will prevent an #error if the Measurement value is left balnk, or
Null.
However, bad data entry... or entries that don't follow the normal
pattern of "NN X NN" will always cause an error.

hth
Al Camp
 
I noticed that you didn't reply to my response I posted on your "Extracting
words and reinsert it" of 5/5/05. That's where you got the formula you're
using in this problem...
When someone offers help with a problem, it's considered good form to
reply... yea or nay.
Al Camp
 
I think you get what i want actually

But it seem the expression has a little bit of mistake
error message : The expression you entered contain function with wrong
number of argument

Original expression is
=[ProductCode] & " " & Trim([SkinDoorReplace] & " " & [Particular] & " " &
Left([Measurements],InStr([Measurements],"X")-2) & "mm X " &
Mid([Measurements],InStr([Measurements],"X")+2) & "mm")

I just want to prevent Null value.

Any solutions?

Thanks in advance

Kennykee



Al Camp said:
Ken,
Try this in the ControlSource of your report field...
=IIF(IsNull([Measurements],"",
Left([Measurements],InStr([Measurements],"X")-2) & "mm X " & Mid
([Measurements],InStr([Measurements],"X")+2) & "mm")

This will prevent an #error if the Measurement value is left balnk, or
Null.
However, bad data entry... or entries that don't follow the normal
pattern of "NN X NN" will always cause an error.

hth
Al Camp


kennykee said:
==>Left([Measurements],InStr([Measurements],"X")-2) & "mm X " & Mid
([Measurements],InStr([Measurements],"X")+2) & "mm")

The above expression is used to add "mm" when user type a measurement from
a
form and is preview in report
22 X 33(form) ==> 22mm X 33mm(report)

Unfortunately not all products is in measurement, some of them are
calculated in unit such as one door lock, two pieces of glasses,....

If i did not type any value or in wrong format in the field (#error) will
shown on the report

Can code solve this problem? I can accept any solution either to my form
or
my report or in code.................as long as it solve the problem

Any solutions?

Thanks in advance

Kennykee
Click to expand...
Click to expand...
 
I considered deeply on your what you wrote. First of all, i am sorry and
apologized for not replying your response posted. Moreover, your help is
highly appreciated. Then, i hope you can forgive on my carelessness before
this. "Your help is worth more than a million kilogrammes of gold"

Thank you.

Kennykee

Al Camp said:
I noticed that you didn't reply to my response I posted on your "Extracting
words and reinsert it" of 5/5/05. That's where you got the formula you're
using in this problem...
When someone offers help with a problem, it's considered good form to
reply... yea or nay.
Al Camp

kennykee said:
==>Left([Measurements],InStr([Measurements],"X")-2) & "mm X " & Mid
([Measurements],InStr([Measurements],"X")+2) & "mm")

The above expression is used to add "mm" when user type a measurement from
a
form and is preview in report
22 X 33(form) ==> 22mm X 33mm(report)

Unfortunately not all products is in measurement, some of them are
calculated in unit such as one door lock, two pieces of glasses,....

If i did not type any value or in wrong format in the field (#error) will
shown on the report

Can code solve this problem? I can accept any solution either to my form
or
my report or in code.................as long as it solve the problem

Any solutions?

Thanks in advance

Kennykee
Click to expand...
Click to expand...
 
If ProductCode or SkinDoorReplace or Particular have no value, that
doesn't cause an #error because they are "string" functions.
If Measurements has no value, it causes an #error because of the InStr
functions trying to return an integer value.

So, you need to add an IIF statement in your concatenation to not display
Measurement if it IsNull.
I quickly tested this concatenation and it works OK. All component
values (or some) can be empty without an #error.

=[ProductCode] & " " & Trim([SkinDoorReplace] & " " & [Particular] & " " &
IIf(IsNull([Measurements]),"",Left([Measurements],InStr([Measurements],"X")-2)
& "mm X " & Mid([Measurements],InStr([Measurements],"X")+2) & "mm"))

hth
Al Camp


kennykee said:
I think you get what i want actually

But it seem the expression has a little bit of mistake
error message : The expression you entered contain function with wrong
number of argument

Original expression is
=[ProductCode] & " " & Trim([SkinDoorReplace] & " " & [Particular] & " " &
Left([Measurements],InStr([Measurements],"X")-2) & "mm X " &
Mid([Measurements],InStr([Measurements],"X")+2) & "mm")

I just want to prevent Null value.

Any solutions?

Thanks in advance

Kennykee



Al Camp said:
Ken,
Try this in the ControlSource of your report field...
=IIF(IsNull([Measurements],"",
Left([Measurements],InStr([Measurements],"X")-2) & "mm X " & Mid
([Measurements],InStr([Measurements],"X")+2) & "mm")

This will prevent an #error if the Measurement value is left balnk, or
Null.
However, bad data entry... or entries that don't follow the normal
pattern of "NN X NN" will always cause an error.

hth
Al Camp


kennykee said:
==>Left([Measurements],InStr([Measurements],"X")-2) & "mm X " & Mid
([Measurements],InStr([Measurements],"X")+2) & "mm")

The above expression is used to add "mm" when user type a measurement
from
a
form and is preview in report
22 X 33(form) ==> 22mm X 33mm(report)

Unfortunately not all products is in measurement, some of them are
calculated in unit such as one door lock, two pieces of glasses,....

If i did not type any value or in wrong format in the field (#error)
will
shown on the report

Can code solve this problem? I can accept any solution either to my
form
or
my report or in code.................as long as it solve the problem

Any solutions?

Thanks in advance

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