Value but exporting as text

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Schwimms - 22 Feb 2008 18:07 GMT

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.

Reply to this Message

John W. Vinson - 22 Feb 2008 21:36 GMT

>Hi,
>
[quoted text clipped - 9 lines]
>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.

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.

Signature

John W. Vinson [MVP]

Reply to this Message

Schwimms - 25 Feb 2008 18:01 GMT

HAHA!!!

That works! Thank you. Access is very picky.

> >Hi,
> >
[quoted text clipped - 23 lines]
> 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.

Reply to this Message