Do not use value!

[ChristopherL]

Hi!
I want to make a copy table where I copy all the values from a database and filter out the values that I want.

In one of the columns theres a number value that can't be changed manually to text, for that value I want to convert it and ignore the null values.

So I only want to show the values that are visible.

I tried with TRY: IIf([PQ_OPTIONVAL_D]=Null;0;Val([PQ_OPTIONVAL_D]))

But what can I use instead of "0" to just ignore it?
Right now it just shows #error but I dont even want the record to show.

Many thanks
/ C​

 

[Brianwarnock]

I don't understand all of your post but one issue to correct is that looking for NULL fields you do not say = null but IS NULL

Brian

 

[ChristopherL]

I don't understand all of your post but one issue to correct is that looking for NULL fields you do not say = null but IS NULL

Brian

Many thanks that was the issue, however I've come up with another problem now..
Anything you knw?

I want to update a column [Value_Diff_SEK] to a value gained from [Value_Diff]*[rate]. (it takes the value and multiplies it with the swedish SEK rate).

However, for those values that already have the swedish SEK as currency, it skips and those not add to the column.

So what I want to do is update those with the wrong currency to the proper currency and just copy the others over to the column in an update query!

Anyone got a solution for iff statement or anything?

 

[Brianwarnock]

How do you determine which values are seek and which are not.

Basically you will write an Iif in the update to row

= iif(currency =sek,value,value*rate)

Brian

Sorry sek should be"sek"

This correction is for future readers

 

[Mihail]

Show as some simple dates.
Have you a currency field ? How you know what currency is used in a certain record ?

 

[ChristopherL]

How do you determine which values are seek and which are not.

Basically you will write an Iif in the update to row

= iif(currency =sek,value,value*rate)

Brian

Thanks alot Brian !
However when I put it like this it still doesnt copy the value for the currency thats alrdy sek!

IIf ([CURRENCY]=SEK; [Value_Diff_SEK]; [RATE]*[Value_Diff])

 

[Brianwarnock]

If SEK. Is text it should be in "" , if that's not the issue then I am lost.

Brian

 

[ChristopherL]

If SEK. Is text it should be in "" , if that's not the issue then I am lost.

Brian

IIf([CURRENCY]="SEK";[Value_Diff];[RATE]*[Value_Diff])

Still doesnt work.. I'll keep testing stuff tho and see if it works out!
Thanks alot for the help so far tho, I've come alot longer since when I started so !

 

[Mihail]

Why to not update only that records that have not "SEK" in the Currency field ?
So, in the Criteria row, (Currency field) type: <>"SEK" .
In the Update row (Value_Diff field) write: [RATE]*[Value_Diff]

 

[Brianwarnock]

Why to not update only that records that have not "SEK" in the Currency field ?
So, in the Criteria row, (Currency field) type: <>"SEK" .
In the Update row (Value_Diff field) write: [RATE]*[Value_Diff]

But he also wants to copy the value over if the currency is SEK , I don't understand why the code he has does not work.

Brian

 

[ChristopherL]

Why to not update only that records that have not "SEK" in the Currency field ?
So, in the Criteria row, (Currency field) type: <>"SEK" .
In the Update row (Value_Diff field) write: [RATE]*[Value_Diff]

That worked, did it in two seperate updates instead, one for all but sek and one for only sek.

Thank you Mihail!

 

[Mihail]

If work is great !!! Glad to help you.

@Brian
Me too (don't understand why your code doesn't work). All seems to be OK.

@Christopher
I wish to warn you that all this "update" smell (at least for me) as a NOT normalized database.