Help with Coding

Here is what I currently have:

SELECT [dbo_tams_icp_ce t1].icp, [dbo_tams_icp_ce t1].sortord, [dbo_tams_se
t1].part_nbr, [dbo_tams_se t1].cage, [dbo_tams_se t1].mfr_desc, [test 1
t].[Auth Qty], [dbo_tams_icp_ce t1].item_nbr,
IIf(IsError(CInt([dbo_tams_icp_ce t1]![ce_qty])),0,CInt([dbo_tams_icp_ce
t1]![ce_qty])) AS qty, IIf([test 1 t]![Auth Qty]=[qty],"T","F") AS Complete
FROM ([test 1 t] RIGHT JOIN [dbo_tams_icp_ce t1] ON [test 1 t].item_nbr =
[dbo_tams_icp_ce t1].item_nbr) INNER JOIN [dbo_tams_se t1] ON
[dbo_tams_icp_ce t1].item_nbr = [dbo_tams_se t1].item_nbr
WHERE ((([dbo_tams_icp_ce t1].icp) Like "17-20*" Or ([dbo_tams_icp_ce
t1].icp) Like "17-50*"))
ORDER BY [dbo_tams_icp_ce t1].icp, [dbo_tams_icp_ce t1].sortord
WITH OWNERACCESS OPTION;

The problem is my IsError does not seem to be working. The qty field still
produces #Error if the field is null, 0, or contains AR. Where am I going
wrong?

What are you expecting the IsError() function to find? What data do you
expect to find in that field?

Regards

Jeff Boyce
Microsoft Office/Access MVP

"DevilDog1978" wrote in message
...
Here is what I currently have:

SELECT [dbo_tams_icp_ce t1].icp, [dbo_tams_icp_ce t1].sortord,
[dbo_tams_se
t1].part_nbr, [dbo_tams_se t1].cage, [dbo_tams_se t1].mfr_desc, [test 1
t].[Auth Qty], [dbo_tams_icp_ce t1].item_nbr,
IIf(IsError(CInt([dbo_tams_icp_ce t1]![ce_qty])),0,CInt([dbo_tams_icp_ce
t1]![ce_qty])) AS qty, IIf([test 1 t]![Auth Qty]=[qty],"T","F") AS
Complete
FROM ([test 1 t] RIGHT JOIN [dbo_tams_icp_ce t1] ON [test 1 t].item_nbr =
[dbo_tams_icp_ce t1].item_nbr) INNER JOIN [dbo_tams_se t1] ON
[dbo_tams_icp_ce t1].item_nbr = [dbo_tams_se t1].item_nbr
WHERE ((([dbo_tams_icp_ce t1].icp) Like "17-20*" Or ([dbo_tams_icp_ce
t1].icp) Like "17-50*"))
ORDER BY [dbo_tams_icp_ce t1].icp, [dbo_tams_icp_ce t1].sortord
WITH OWNERACCESS OPTION;

The problem is my IsError does not seem to be working. The qty field still
produces #Error if the field is null, 0, or contains AR. Where am I going
wrong?

Instead of trying to trap the error, check to see if the value isNumeric and
therefore can be converted (as long as it is less than 32k).

SELECT [dbo_tams_icp_ce t1].icp, [dbo_tams_icp_ce t1].sortord, [dbo_tams_se
t1].part_nbr, [dbo_tams_se t1].cage, [dbo_tams_se t1].mfr_desc, [test 1
t].[Auth Qty], [dbo_tams_icp_ce t1].item_nbr,

IIf(IsNumeric(CInt([dbo_tams_icp_ce t1]![ce_qty])=False ,0
,CInt([dbo_tams_icp_ce t1]![ce_qty])) AS qty

, IIf([test 1 t]![Auth Qty]=[qty],"T","F") AS Complete
FROM ([test 1 t] RIGHT JOIN [dbo_tams_icp_ce t1] ON [test 1 t].item_nbr =
[dbo_tams_icp_ce t1].item_nbr) INNER JOIN [dbo_tams_se t1] ON
[dbo_tams_icp_ce t1].item_nbr = [dbo_tams_se t1].item_nbr
WHERE ((([dbo_tams_icp_ce t1].icp) Like "17-20*" Or ([dbo_tams_icp_ce
t1].icp) Like "17-50*"))
ORDER BY [dbo_tams_icp_ce t1].icp, [dbo_tams_icp_ce t1].sortord
WITH OWNERACCESS OPTION;

That could still fail if the ce_qty field holds a value greater than 32767.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

DevilDog1978 wrote:
Here is what I currently have:

SELECT [dbo_tams_icp_ce t1].icp, [dbo_tams_icp_ce t1].sortord, [dbo_tams_se
t1].part_nbr, [dbo_tams_se t1].cage, [dbo_tams_se t1].mfr_desc, [test 1
t].[Auth Qty], [dbo_tams_icp_ce t1].item_nbr,
IIf(IsError(CInt([dbo_tams_icp_ce t1]![ce_qty])),0,CInt([dbo_tams_icp_ce
t1]![ce_qty])) AS qty, IIf([test 1 t]![Auth Qty]=[qty],"T","F") AS Complete
FROM ([test 1 t] RIGHT JOIN [dbo_tams_icp_ce t1] ON [test 1 t].item_nbr =
[dbo_tams_icp_ce t1].item_nbr) INNER JOIN [dbo_tams_se t1] ON
[dbo_tams_icp_ce t1].item_nbr = [dbo_tams_se t1].item_nbr
WHERE ((([dbo_tams_icp_ce t1].icp) Like "17-20*" Or ([dbo_tams_icp_ce
t1].icp) Like "17-50*"))
ORDER BY [dbo_tams_icp_ce t1].icp, [dbo_tams_icp_ce t1].sortord
WITH OWNERACCESS OPTION;

The problem is my IsError does not seem to be working. The qty field still
produces #Error if the field is null, 0, or contains AR. Where am I going
wrong?

John the highest quantity in the ce_qty field will never be more than 20. The
field is not numeric and I was trying to convert it to numeric data in this
query as well.

"John Spencer MVP" wrote:

Instead of trying to trap the error, check to see if the value isNumeric and
therefore can be converted (as long as it is less than 32k).

SELECT [dbo_tams_icp_ce t1].icp, [dbo_tams_icp_ce t1].sortord, [dbo_tams_se
t1].part_nbr, [dbo_tams_se t1].cage, [dbo_tams_se t1].mfr_desc, [test 1
t].[Auth Qty], [dbo_tams_icp_ce t1].item_nbr,

IIf(IsNumeric(CInt([dbo_tams_icp_ce t1]![ce_qty])=False ,0
,CInt([dbo_tams_icp_ce t1]![ce_qty])) AS qty

, IIf([test 1 t]![Auth Qty]=[qty],"T","F") AS Complete
FROM ([test 1 t] RIGHT JOIN [dbo_tams_icp_ce t1] ON [test 1 t].item_nbr =
[dbo_tams_icp_ce t1].item_nbr) INNER JOIN [dbo_tams_se t1] ON
[dbo_tams_icp_ce t1].item_nbr = [dbo_tams_se t1].item_nbr
WHERE ((([dbo_tams_icp_ce t1].icp) Like "17-20*" Or ([dbo_tams_icp_ce
t1].icp) Like "17-50*"))
ORDER BY [dbo_tams_icp_ce t1].icp, [dbo_tams_icp_ce t1].sortord
WITH OWNERACCESS OPTION;

That could still fail if the ce_qty field holds a value greater than 32767.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

DevilDog1978 wrote:
Here is what I currently have:

SELECT [dbo_tams_icp_ce t1].icp, [dbo_tams_icp_ce t1].sortord, [dbo_tams_se
t1].part_nbr, [dbo_tams_se t1].cage, [dbo_tams_se t1].mfr_desc, [test 1
t].[Auth Qty], [dbo_tams_icp_ce t1].item_nbr,
IIf(IsError(CInt([dbo_tams_icp_ce t1]![ce_qty])),0,CInt([dbo_tams_icp_ce
t1]![ce_qty])) AS qty, IIf([test 1 t]![Auth Qty]=[qty],"T","F") AS Complete
FROM ([test 1 t] RIGHT JOIN [dbo_tams_icp_ce t1] ON [test 1 t].item_nbr =
[dbo_tams_icp_ce t1].item_nbr) INNER JOIN [dbo_tams_se t1] ON
[dbo_tams_icp_ce t1].item_nbr = [dbo_tams_se t1].item_nbr
WHERE ((([dbo_tams_icp_ce t1].icp) Like "17-20*" Or ([dbo_tams_icp_ce
t1].icp) Like "17-50*"))
ORDER BY [dbo_tams_icp_ce t1].icp, [dbo_tams_icp_ce t1].sortord
WITH OWNERACCESS OPTION;

The problem is my IsError does not seem to be working. The qty field still
produces #Error if the field is null, 0, or contains AR. Where am I going
wrong?

If CInt([dbo_tams_icp_ce t1]![ce_qty]) yeilds an error I want Access to
change the value to 0, if it is not an error than I want the value from
CInt([dbo_tams_icp_ce t1]![ce_qty]) to populate the field.

"Jeff Boyce" wrote:

What are you expecting the IsError() function to find? What data do you
expect to find in that field?

Regards

Jeff Boyce
Microsoft Office/Access MVP

"DevilDog1978" wrote in message
...
Here is what I currently have:

SELECT [dbo_tams_icp_ce t1].icp, [dbo_tams_icp_ce t1].sortord,
[dbo_tams_se
t1].part_nbr, [dbo_tams_se t1].cage, [dbo_tams_se t1].mfr_desc, [test 1
t].[Auth Qty], [dbo_tams_icp_ce t1].item_nbr,
IIf(IsError(CInt([dbo_tams_icp_ce t1]![ce_qty])),0,CInt([dbo_tams_icp_ce
t1]![ce_qty])) AS qty, IIf([test 1 t]![Auth Qty]=[qty],"T","F") AS
Complete
FROM ([test 1 t] RIGHT JOIN [dbo_tams_icp_ce t1] ON [test 1 t].item_nbr =
[dbo_tams_icp_ce t1].item_nbr) INNER JOIN [dbo_tams_se t1] ON
[dbo_tams_icp_ce t1].item_nbr = [dbo_tams_se t1].item_nbr
WHERE ((([dbo_tams_icp_ce t1].icp) Like "17-20*" Or ([dbo_tams_icp_ce
t1].icp) Like "17-50*"))
ORDER BY [dbo_tams_icp_ce t1].icp, [dbo_tams_icp_ce t1].sortord
WITH OWNERACCESS OPTION;

The problem is my IsError does not seem to be working. The qty field still
produces #Error if the field is null, 0, or contains AR. Where am I going
wrong?

IsNumeric checks to see if the value (a string or a number) is a number. In
other words, if it looks like a number to the human eye, IsNumeric will in all
probability return true.

So the string "22,123.55" when tested by IsNumeric will return True.

Nulls will return false and strings with any non-number characters (other than
leading or trailing negative or positive signs and the decimal and thousands
delimiters) will return false.

Dates will return false
zero-length strings will return false.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

DevilDog1978 wrote:
John the highest quantity in the ce_qty field will never be more than 20. The
field is not numeric and I was trying to convert it to numeric data in this
query as well.

"John Spencer MVP" wrote:

Instead of trying to trap the error, check to see if the value isNumeric and
therefore can be converted (as long as it is less than 32k).

SELECT [dbo_tams_icp_ce t1].icp, [dbo_tams_icp_ce t1].sortord, [dbo_tams_se
t1].part_nbr, [dbo_tams_se t1].cage, [dbo_tams_se t1].mfr_desc, [test 1
t].[Auth Qty], [dbo_tams_icp_ce t1].item_nbr,

IIf(IsNumeric(CInt([dbo_tams_icp_ce t1]![ce_qty])=False ,0
,CInt([dbo_tams_icp_ce t1]![ce_qty])) AS qty

, IIf([test 1 t]![Auth Qty]=[qty],"T","F") AS Complete
FROM ([test 1 t] RIGHT JOIN [dbo_tams_icp_ce t1] ON [test 1 t].item_nbr =
[dbo_tams_icp_ce t1].item_nbr) INNER JOIN [dbo_tams_se t1] ON
[dbo_tams_icp_ce t1].item_nbr = [dbo_tams_se t1].item_nbr
WHERE ((([dbo_tams_icp_ce t1].icp) Like "17-20*" Or ([dbo_tams_icp_ce
t1].icp) Like "17-50*"))
ORDER BY [dbo_tams_icp_ce t1].icp, [dbo_tams_icp_ce t1].sortord
WITH OWNERACCESS OPTION;

That could still fail if the ce_qty field holds a value greater than 32767.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

DevilDog1978 wrote:
Here is what I currently have:

SELECT [dbo_tams_icp_ce t1].icp, [dbo_tams_icp_ce t1].sortord, [dbo_tams_se
t1].part_nbr, [dbo_tams_se t1].cage, [dbo_tams_se t1].mfr_desc, [test 1
t].[Auth Qty], [dbo_tams_icp_ce t1].item_nbr,
IIf(IsError(CInt([dbo_tams_icp_ce t1]![ce_qty])),0,CInt([dbo_tams_icp_ce
t1]![ce_qty])) AS qty, IIf([test 1 t]![Auth Qty]=[qty],"T","F") AS Complete
FROM ([test 1 t] RIGHT JOIN [dbo_tams_icp_ce t1] ON [test 1 t].item_nbr =
[dbo_tams_icp_ce t1].item_nbr) INNER JOIN [dbo_tams_se t1] ON
[dbo_tams_icp_ce t1].item_nbr = [dbo_tams_se t1].item_nbr
WHERE ((([dbo_tams_icp_ce t1].icp) Like "17-20*" Or ([dbo_tams_icp_ce
t1].icp) Like "17-50*"))
ORDER BY [dbo_tams_icp_ce t1].icp, [dbo_tams_icp_ce t1].sortord
WITH OWNERACCESS OPTION;

The problem is my IsError does not seem to be working. The qty field still
produces #Error if the field is null, 0, or contains AR. Where am I going
wrong?

On Wed, 08 Jul 2009 12:37:53 -0400, John Spencer MVP
wrote:

IsNumeric checks to see if the value (a string or a number) is a number. In
other words, if it looks like a number to the human eye, IsNumeric will in all
probability return true.

So the string "22,123.55" when tested by IsNumeric will return True.

Nulls will return false and strings with any non-number characters (other than
leading or trailing negative or positive signs and the decimal and thousands
delimiters) will return false.

Or scientific notation: I remember one poster some years ago was tripped up
because a string like "123E51" was (correctly) seen as numeric. #E# or #D#
will hit this problem.

Takes me back to Fortran II in 1968...!
--

John W. Vinson [MVP]