Snoopy24

I am currently writing a organizer type program that has 27 buttons at the top (one for each letter in the alphabet and one for #) in an array. When one of the buttons is clicked, it goes through the database and returns all results that start with the letter that was clicked. Everything for the letters works great but where I am stuck is the searching of numbers (0-9). I don't really want to do a search for every number. I search for the letters as follows:

Char = Index + 64 'Calculates the character code for the letter of the button
With DBConnect.rsGames
.Sort = "Title ASC"
If Not .BOF Then .MoveFirst
.Find "Title like '" & Chr$(Char) & "*'"

Now how would I go about using this to search for ANY number and not specific ones

wengwashere

you could do range search... where number between this and that...

Andyh

If this is sqlserver (I don't know about Access) you can use regular expressions:

.Find "Title like '[0-9]%'"

MKoslof

Instead of using the Find method, I would simply use SQL. You could write an SQL SELECT statement that finds all numbers or values LIKE your condition..you would need to use wildcards within your SQL statement (% for ADO, * for DAO).

But if your heart is set on the Find method you can use it with Access...the .Find method should look like this:


You need to use a valid field within the opened recordset.

Andyh

his problem is not using a wildcard search, It is to find records starting with a numeric character.

MKoslof

Use a function, such as isnumeric . If we need to check BEYOND the first character, we can write a function that loop until Len() of the recordset value finding ANY numerical characters. But then, what is the final result supposed to be? Say I find that a value starts with a numeric character, then what? If it starts with a number, are we adding it to a Grid control, displaying it somewhere. Something like this:

Andyh

You can't put isnumeric into sql . Read snoopy's problem - he wants to retrieve records that START with a numeric character (not just contain a numeric value) when they press the "#" button as opposed to a button with A-Z on it. e.g. "99 red balloons"

this is done by using regular expression searches:

1) object method: .Find "Title like '[0-9]%'"

2) Sql string: select * from table where Title like '[0-9]%'

MKoslof

Actually, no. YES you can put ISNUMERIC in an SQL Statement...have you tried it before?

And point two, his example uses the .find method, which implies he is OPENING a recordset and SEARCHING through it...something that is exactly the same as using a While loop and incrementing through the recordset.

But I appreciate your input and questions

Snoopy24

Thanx to all those that replied. I have tried some of the solutions posted here and am still unable to get this thing to work. As I am not very familiar with SQL statements, I would like to use another solution. Maybe I can explain a bit better. When you click the button for #, it goes to the database and finds the first result with a number as the first character. Once the result is found, it posts all the information on the form. Then there are 2 buttons (Next and previous) which can be used to search for more records that begin with a number. The problem I have is finding the records that begin with a number. The database field that I'm searching is "Title" and has entries like this:
21 Grams
2 Fast 2 Furious
3 Kings
Ace Ventura
Bad Boys
ect...
The database has over 800 records and growing so to loop through each record to see if it is numeric sounds like a lot of extra processing. Reading through the help files I found 2 other commands that might help (Seek and Filter) but haven't got them to work either

MKoslof

So use SQL for this...is this an Access mdb, try this:

(*return all records where the field in question STARTS with a number)

SELECT *
FROM Table1
WHERE IsNumeric(Left(field1,1))

The only *other solution* is to open the recordset and test each record, something I also showed a brief example of. There really is no other way to do this because you need to evaluate the records.

Andyh

You cannot use .find unless you load the result set with the whole table in the first place. What you should do is (like I have said before) is use regular expressions. Every time a user presses a button re-run a SQL search with new criteria:

if the user presses a Alpha-character button run this sql

If the user presses the "#" button use this sql

This will work in access and SQLserver; If you don't believe me then run this sql in Northwind.mdb:

MKoslof

No, if Access, just test for ISNUMERIC. If SQL Server, you could use a CASE within your SELECT to test for the ISNUMERIC function, which returns 0 or 1 in SQL Server. If your condition is simply to find any value that starts with a number, the LIKE search is not needed.

Snoopy24

If I were to use that SQL statement would it return the first result it finds with a number at the beginning and if so how would I tell it to find the next record with a number at the beginning when they press the "Next" button?

MKoslof

If you only want the first instance you still need to open the recordset..as I have stated and AndyH has stated. Basically, use the ISNUMERIC() call to open a recordset with only numeric characters as the first character. Then, you use can the .Find method, the .FindFirst method or loop until EOF to bring in specific values.

Snoopy24

Thanx to all that have posted so quickly. I got it working with Isnumeric.

MKoslof

No problem...good luck to you!