How do I handle errors in value function

  • Thread starter Thread starter Dave
  • Start date Start date
D

Dave

I have a text field that has numbers and text. I set up a query to convert
the data to a number and I get an error for the text data. So far so good.

I want to set up the query to return a preset value when it finds an error,
is that possible?

Example Value Function for 123456 returns 123456
Value Function for Dave returns Error
I would like the Error to be 10

Thanks
 
SELECT StuReg.RegID, StuReg.StartDate, StuReg.EmplID, Val([EmplID]) AS Test
FROM StuReg;

I want the Val([EmplID]) error to be the number 10.
 
Untested, but try the following -

SELECT ..., IIF(IsNumeric(EmplID),Val(EmplID),10) as Test ...
SELECT StuReg.RegID, StuReg.StartDate, StuReg.EmplID, Val([EmplID]) AS Test
FROM StuReg;

I want the Val([EmplID]) error to be the number 10.

[MVP] S.Clark said:
Post the SQL.
Click to expand...
Click to expand...
 
Thanks hopefully this will work but in the query it says I have the wrong
number of arguments for the if statement.

Dave

John Spencer (MVP) said:
Untested, but try the following -

SELECT ..., IIF(IsNumeric(EmplID),Val(EmplID),10) as Test ...
SELECT StuReg.RegID, StuReg.StartDate, StuReg.EmplID, Val([EmplID]) AS
Test
FROM StuReg;

I want the Val([EmplID]) error to be the number 10.

[MVP] S.Clark said:
Post the SQL.

--
Steve Clark, Access MVP
FMS, Inc.
www.fmsinc.com/consulting

I have a text field that has numbers and text. I set up a query to
convert
the data to a number and I get an error for the text data. So far so
good.

I want to set up the query to return a preset value when it finds an
error,
is that possible?

Example Value Function for 123456 returns 123456
Value Function for Dave returns Error
I would like the Error to be 10

Thanks
Click to expand...
Click to expand...
Click to expand...
 
That suggest to me that we have put a parentheses in the wrong place. I don't
see it in the code I have posted, but ...

Try eliminating everything but the expression and then adding other thing back
in as you get it to work. Absolute minimum query -

SELECT IIF(IsNumeric([EmpID]),Val([EmpID]),10)
FROM StuReg

If that fails, try adjusting it until it works.
Thanks hopefully this will work but in the query it says I have the wrong
number of arguments for the if statement.

Dave

John Spencer (MVP) said:
Untested, but try the following -

SELECT ..., IIF(IsNumeric(EmplID),Val(EmplID),10) as Test ...
SELECT StuReg.RegID, StuReg.StartDate, StuReg.EmplID, Val([EmplID]) AS
Test
FROM StuReg;

I want the Val([EmplID]) error to be the number 10.

Post the SQL.

--
Steve Clark, Access MVP
FMS, Inc.
www.fmsinc.com/consulting

I have a text field that has numbers and text. I set up a query to
convert
the data to a number and I get an error for the text data. So far so
good.

I want to set up the query to return a preset value when it finds an
error,
is that possible?

Example Value Function for 123456 returns 123456
Value Function for Dave returns Error
I would like the Error to be 10

Thanks
Click to expand...
Click to expand...
Click to expand...
 
Back
Top