Modify Query to ignore $$$$

[unclejemima]

I get a error in a query from a table called Trandata with the column called Account.  Every once in a while the Point of Sale system will throw in a $$$$ instead of a numberical value (typically 4 digits)

There is logic from the system to do this, and it does not cause any problems...but is there any way I can modify the query so that if there is a $$$$ entry in the Trandata(account) to not load that line so I don't constantly get the errors?

Thanks,

 

[Sandeep641]

go to edit queries, right click account column replace errors with null or what ever u want

[unclejemima]

I tried to set to null, () and blank...but it would not accept those values (it a whole number column)

Best I could do is 0.  Is there any special way to force them to null or blank instead of zero?

Also, do I have to do this each time I refresh my dataset...or will it remember my selection to replace errors with whatever for that column.

Thanks!!

[Sandeep641 · Accepted solution]

you have to type "null" after choosing replace errors, it should work irrespective of the column type

It will automatically replace all values to null eveytime you refresh, it would remember.

[parry2k]

I guess in Edit Query, make sure data type is text, then remove $$$$ using replace function and then change the data type back to number.