query criteria

[samjesse]

Hi

I have a Text field "For" with data entry like "25 - Door" with out the quotes.

in the query, I need to filter the records by the first number, so I added a field like this
Unit: CInt(Trim(Left([For],2)))
but I run it, I get some records which do not have the 2 numbers at the start showing #Error.

How can I only get the records which show the number?

thx

 

[xenou]

It's an interesting question. I'm not sure what the best approach is. I've tried a custom function that should work for any number of leading digits (1,2,3 or more - as long as the numbers are first):


Create a standard module and enter the following code. The declarations at the top should only appear once (Option Explicit and Option Compare Database). Also the declaration Private re as Object should also appear at the top before any subs or functions, and also only once.

Option Explicit
Option Compare Database

Private re As Object

Function Nums(ByRef arg)

    If re Is Nothing Then
        Set re = CreateObject("VBScript.RegExp")
        With re
            .Global = False
            .IgnoreCase = True
            .Multiline = False
            .Pattern = "^\d+"
        End With
    End If
    If re.Test(Nz(arg)) Then
        Nums = re.Execute(arg)(0)
    Else
        Nums = Null
    End If

End Function

Then use the function in a query:

SELECT Nums(Field1) As Exp1 FROM Table1;

 

[Kreszch68]

Or you use the IsNumeric function in your query.
The error is raised because you try to convert to integer, while the result is not always a number. Simply drop the Cint in your formula.

Unit: Trim(Left([For],2))

Add a new field, like:

Check: IsNumeric([Unit]) (returns -1 if tue, 0 if false)

Now you can filter the records on field 'check' where check = true.

 

[Kreszch68]

Remember that if you do this selection directly, Access can't evaluate the value for the field Unit for usage in the check field. Therefore you should do one of the following:

1 Use the query in a second query to do your selection
2 Place the field definition of the unit field into the check field, the check field will look like:

Check: IsNumeric(Trim(Left([For],2)))

 

[samjesse]

It's an interesting question. I'm not sure what the best approach is. I've tried a custom function that should work for any number of leading digits (1,2,3 or more - as long as the numbers are first):


Create a standard module and enter the following code. The declarations at the top should only appear once (Option Explicit and Option Compare Database). Also the declaration Private re as Object should also appear at the top before any subs or functions, and also only once.

Option Explicit
Option Compare Database

Private re As Object

Function Nums(ByRef arg)

    If re Is Nothing Then
        Set re = CreateObject("VBScript.RegExp")
        With re
            .Global = False
            .IgnoreCase = True
            .Multiline = False
            .Pattern = "^\d+"
        End With
    End If
    If re.Test(Nz(arg)) Then
        Nums = re.Execute(arg)(0)
    Else
        Nums = Null
    End If

End Function

Then use the function in a query:

SELECT Nums(Field1) As Exp1 FROM Table1;

I get a compile error. I use Access 2000.
rs at the start of the block gets highlighted in blue.

 

[xenou]

but I run it, I get some records which do not have the 2 numbers at the start showing #Error.

Can you give some examples of records that produce an error?

I've never used access 2000 but that's 11 years old so you may not have the correct VBScript library for using regex matching. We can ditch my earlier effort then. BTW, it would only have worked for positive integer values at the beginning of the string anyway.