text converted to numeric with setvalue

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

Guest

I have a column (UseCaseNo) with values that look numeric to Access. The
values are a hierarchical numbering scheme: 01.01, 01.01.01, 01.02, 01.02.01,
01.02.02, etc. When I try to use setvalue to set an unbound text box
(SaveUseCaseNo) to the value of UseCaseNo, it appears that Access treats the
values as numeric.
10.07 is saved as 10.07 (good)
03.01 is saved as 3.01 (bad)
02.01.02 is saved as 0 (bad - all the values with more than one decimal
point are 0)

I have tried using Str() and Cstr(). Please help! I really need to save
this value as is, for later retrieving the record from the table.
 
I have a partial workaround.
if UseCaseNo is numeric, I use Cstr(), otherwise, just move it. This solves
all the problems except leading zeroes. I think I saw a solution for putting
leading zeroes back, but I can't know whether I need to do this. Any ideas?
 
JQ,

It is difficult to see what you are doing. Do you mean you have a bound
control on a form, for the UseCaseNo, which is a field of Text data
type? And on the same form you have an unbound textbox SaveUseCaseNo?
And then you have a macro using a SetValue action that will run on some
event or another on the form? Ok, so what are the arguments of the
macro? I can't imagine what could cause the type of problem you
described. If it's text, it should continue to be treated as text.
What version of Access are you using?
 
Steve,

I can't imagine what could cause this either. Like you describe, UseCaseNo
is a control on the form, that is bound to a table column which is text.
SaveUseCaseNo is an unbound text control. The macro is executed when a
record selector on a row of the subform is dbl-clicked.

If I just use setvalue to set SaveUseCaseNo to UseCaseNo, I get the errors
as described in the first post. If I check first to see if UseCaseNo
"IsNumeric", and if true, setvalue SaveUseCaseNo to Cstr(UseCaseNo), then (as
absurd as it sounds), when UseCaseNo contains two or more decimal points it
is now saved correctly.

But when UseCaseNo contains leading zeros (very often), and only one decimal
point, such as 02.01, the leading zero is dropped. This is a problem for me
because I don't have any way to know that the zero was dropped... if Cstr
doesn't work, then I don't think I can grab the first digit and see if it is
a zero. It is also a problem because I am saving the UseCaseNo in the first
place so that I can find matching rows in the table again after doing some
other stuff.

I'm not sure what arguments you mean. "setvalue" just has the two: Item and
expression. The full syntax is:

setvalue Item: [Forms]![frmTestPacket]![SaveUseCaseNo]
Expression: [Forms]![frmTestPacket]![sfrmUseCases]![UseCaseNo]
or
setvalue Item: [Forms]![frmTestPacket]![SaveUseCaseNo]
Expression:
Cstr([Forms]![frmTestPacket]![sfrmUseCases]![UseCaseNo])

I'm using Access 2003, although the file format is Access 2000, since some
of my users haven't upgraded yet.
 
JQ,

Thanks for the further explanation. I am not able to experiment with
this myself right now. But I would suggest setting up the SetValue
action like this, and see how it goes...
Item: [Parent]![SaveUseCaseNo]
Expression: Format([UseCaseNo])
 
Back
Top