#Error

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Gary - 02 Jul 2006 12:56 GMT

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.

Reply to this Message

Ken Snell (MVP) - 02 Jul 2006 13:04 GMT

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))

Signature

Ken Snell
<MS ACCESS MVP>

>I am using Access 2003
> I have a field called ProductName.
[quoted text clipped - 22 lines]
>
> No luck with this. Still the same error. Any help would be appreciated.

Reply to this Message

Duane Hookom - 02 Jul 2006 13:13 GMT

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.

Signature

Duane Hookom
MS Access MVP

>I am using Access 2003
> I have a field called ProductName.
[quoted text clipped - 22 lines]
>
> No luck with this. Still the same error. Any help would be appreciated.

Reply to this Message