stop Access from multipying a string that's not a number.

[RZ15]

Hi,

I have a query and one of the fields in this query is as such:

Total: [PURPRI]*[ORDQTY]-[DISCNT]

The data type for the field [DISCNT] is a string (even though the field contains a number). For 10 out of the few thousand records, someone has entered in multiple spaces (ie: " ") in the fields. I don't know why they would do that, but it produces an error and when I export into excel, it exports it out without a total for those records. Is there a way I can tell Access to not subtract anything if [DISCNT] is not a number?

Thanks in advance,

Raza

[RZ15]

Oh yeah, and I don't have control over the database, it is located on a server and I don't have access to change anything. I can only pull data from it, so I can't go in and change the data type for the problem field.

[FishVal]

Hi, Raza.

Not sure whether it will work if you run the query from Excel, but anyway try the following
[PURPRI]*[ORDQTY]-Val([DISCNT])

[NeoPa]

Oh yeah, and I don't have control over the database, it is located on a server and I don't have access to change anything. I can only pull data from it, so I can't go in and change the data type for the problem field.

Definitely a good point to add there Raza ;)
BTW does your discount apply only the once per order as coded, or should it apply to each unit? If the latter then you will need to rearrange things somewhat as :

[ORDQTY] * ([PURPRI] - Val([DISCNT]))

[RZ15]

Definitely a good point to add there Raza ;)
BTW does your discount apply only the once per order as coded, or should it apply to each unit? If the latter then you will need to rearrange things somewhat as :

[ORDQTY] * ([PURPRI] - Val([DISCNT]))

Reading up on the Val() function, I believe this is what i need. Thank you for the help.

[NeoPa]

No problem.
Glad I was able to.

BTW - DID your formula need to apply discount per line?

[RZ15]

You were right. I mapped this from an existing crystal report that was created before I got here into access and now that you mentioned it, it did look off. I assumed too much when I did that.

The DISCNT is a percentage discount off the unit price. So now I have:

IIf(IsNull([DISCNT]), [PURPRI]*[ORDQTY], [PURPRI]*((100-Val([DISCNT]))/100)*[ORDQTY])

[NeoPa]

Try instead :

[ORDQTY]*[PURPRI]*(100-Val(Nz([DISCNT],'0')))/100

[RZ15]

yes, that's good too. I suppose it is cleaner.

I actually never bothered to look up what Nz() actually does until now, though I knew it existed. I would just usually "IIF" my way out of the situation.

[NeoPa]

In situations like this Nz() is much cleaner and easier to understand logic-wise.
Glad it helped anyway :)