If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
Help with Coding
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? |
#3
|
|||
|
|||
Help with Coding
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? |
#4
|
|||
|
|||
Help with Coding
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? |
#5
|
|||
|
|||
Help with Coding
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? |
#6
|
|||
|
|||
Help with Coding
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? |
#7
|
|||
|
|||
Help with Coding
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] |
Thread Tools | |
Display Modes | |
|
|