Help with Coding

D

DevilDog1978

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?
 
J

Jeff Boyce

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
 
J

John Spencer MVP

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
 
D

DevilDog1978

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 said:
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
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?
Click to expand...
Click to expand...
 
D

DevilDog1978

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 said:
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 said:
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?
Click to expand...
Click to expand...
 
J

John Spencer MVP

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
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 said:
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
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?
Click to expand...
Click to expand...
Click to expand...
 
J

John W. Vinson

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.
Click to expand...

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...!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SQL not working 8
Help to optimize qureries 0
crosstab query fails when based on query with subquery? 6
Whats wrong with my query 8
My Brain hurts. Query works in all but one case. 2
Object Variable or With Block Variable Not Set 3
Help with nulls, Nz, etc 5
Help with query 5

Top