#Error

  • Thread starter Thread starter Gary
  • Start date Start date
G

Gary

I am using Access 2003
I have a field called ProductName.
The kinds of products available - appear in the following examples:
AB1000
AB900
AB1000 ST

The numbers in the product name reflect the width of the product
(millimetres).
I tried to create a query with a customized width field to extract only the
number component from the product name and then to convert this mm value to
metres. I have the following:

Width: Mid([ProductName],3,4)/1000

This works fine. But..... I have a product that only has text.
eg. ABCDEF

The width column then produces the following error: #Error for this entry.
I can understand this. All I want to be able to do is allow the calculation
to provide the number 1 when this kind of product is entered.
I have tried the following:
Width:
IIf(IsNull(Mid([ProductName],3,4)/1000),1,(Mid([ProductName],3,4)/1000))

No luck with this. Still the same error. Any help would be appreciated.
 
Try

Width:
IIf(IsError(Mid([ProductName],3,4)/1000),1,(Mid([ProductName],3,4)/1000))

Or this type of method

Width:
IIf(Not (Mid([ProductName],3,1) Like
"[0-9]"),1,(Mid([ProductName],3,4)/1000))
 
I would write a small function that accepts your ProductName and returns the
number.

Function GetNumber(strText As String, _
Optional intDefault As Integer = 0) As Double
Dim intChar As Integer
Dim strChar As String
Dim strOut As String
For intChar = 1 To Len(strText)
strChar = Mid(strText, intChar, 1)
If InStr("0123456789", strChar) > 0 Then
strOut = strOut & strChar
End If
Next
If Len(strOut) > 0 Then
GetNumber = Val(strOut)
Else
GetNumber = intDefault
End If
End Function

If you don't know how to create functions,
-open a new, blank module
-copy the above function from "Function..." to "End Function"
-paste the function into the module
-save the module as "modUtilityFunctions"

You can use this function almost anywhere you can use other functions.
 
Back
Top