SQL Statement help to Access database 1

[tjtindal]

I have written a database for my office which contains alot of contact information, including phone numbers. I tell my boss all the time, just use the last 4 digits of the phone number to search, but it goes in one ear and out the other and she constantly searches for:

8478645

When the database has the number in it as:

847-8645

So she gets NO results, of course. I understand why, she doesn't. Now, my question is, what's the absolute best way to make the database ignore the - dashes when the sql statement is thrown at the db?

I thought about deleting all the dashes from the db and then just showing it as formatted with a dash when it's called. Like, if len(txtPhone.text) = 7 then add the dash after the first 3, or whatever. I can do that. But I kinda just wanted it to ignore them if possible. I dunno, I don't want to tell her she has to type 847*8645 from now on. I want 847-8645 AND 8478645 to yield the same results.

Help!

 

[PatrickIRL]

If you use the format function it might work ...

Format(txtPhone.text),"###-####")
[/CODE

Patrick

 

[tjtindal]

You're talking about using the format code AFTER I've called the records, right? Or are you talking about formatting what she's typed in as it's hitting the db?

Like...

newVar = format(txtPhone.text), "###-####")
"SELECT * FROM Phone WHERE phoneNum LIKE '%" & newVar & "%' ORDER BY phoneNum"

 

[PatrickIRL]

Second option, send the formatted string to the db as in your sql statement

 

[tjtindal]

Okay, now I feel like a doofus asking this question. Let's say txtSearch.text has in it "8478645". How can I make sure that's all numbers and doesn't contain any letters?

 

[Swi]

Private Sub Command1_Click()
MsgBox IsNumeric("8478645")
MsgBox IsNumeric("847-8645")
End Sub

Swi

 

[Andrzejek]

Do the search on this Forum about IsNumeric, it's been very interesting discussion about it.

What I do is:

Private Sub txtSearch_KeyPress(KeyAscii As Integer)

If (KeyAscii > 47 And KeyAscii < 58) Or KeyAscii = 8 Then
    'Only numbers are entered - it is OK
Else
    KeyAscii = 0
End If

End Sub

KeyAscii = 8 is a Backspace

HTH

---- Andy

 

[tjtindal]

That is exactly what I did...thanks