Value but exporting as text

S

Schwimms

Hi,

I have a formula created in a query. I believe the formula does change the
text to a value, but, when I import it in from excel.. It is text again. I
have a refresh button in my excel file that updates my data from access.

This is the formula

IIf([CBC ECD] Is Null,"",IIf([Shipped Date] Is Null,Val(Round(Val(([CBC
ECD]-[Want Date])/30))),""))

I need it to be a value in excel without having to do any manipulation in
excel. I want this file to be all auto.
 
J

John W. Vinson

Hi,

I have a formula created in a query. I believe the formula does change the
text to a value, but, when I import it in from excel.. It is text again. I
have a refresh button in my excel file that updates my data from access.

This is the formula

IIf([CBC ECD] Is Null,"",IIf([Shipped Date] Is Null,Val(Round(Val(([CBC
ECD]-[Want Date])/30))),""))

I need it to be a value in excel without having to do any manipulation in
excel. I want this file to be all auto.
Click to expand...

Well... the "value" of a control can be Text, or Number, or Date/Time, or
Yes/No or any other valid datatype. I think you mean you want it to be a
number.

It's not being treated as a number because in your IIF statement you're
forcing it to be Text with the "" option. Access will compare the datatypes of
each possible result of the IIF and go with the "lowest common denominator";
since you can store a number in a Text field but you cannot store text in a
Number, if one of the choices is a literal text string (even an empty string),
the result of the IIF will be returned as Text.

Try replacing the "" with either NULL or some number which you will recognize
as a "no value supplied" number - 0, -999, whatever works in your application.
 
S

Schwimms

HAHA!!!

That works! Thank you. Access is very picky.

John W. Vinson said:
Hi,

I have a formula created in a query. I believe the formula does change the
text to a value, but, when I import it in from excel.. It is text again. I
have a refresh button in my excel file that updates my data from access.

This is the formula

IIf([CBC ECD] Is Null,"",IIf([Shipped Date] Is Null,Val(Round(Val(([CBC
ECD]-[Want Date])/30))),""))

I need it to be a value in excel without having to do any manipulation in
excel. I want this file to be all auto.
Click to expand...

Well... the "value" of a control can be Text, or Number, or Date/Time, or
Yes/No or any other valid datatype. I think you mean you want it to be a
number.

It's not being treated as a number because in your IIF statement you're
forcing it to be Text with the "" option. Access will compare the datatypes of
each possible result of the IIF and go with the "lowest common denominator";
since you can store a number in a Text field but you cannot store text in a
Number, if one of the choices is a literal text string (even an empty string),
the result of the IIF will be returned as Text.

Try replacing the "" with either NULL or some number which you will recognize
as a "no value supplied" number - 0, -999, whatever works in your application.
Click to expand...
 
Top