sarching for numbers - a leading zero problem 2

[ANDREWeire]

I've designed a form with an Account search button on it.

It's all very simple code which pops up a parameter box into which the user types the account number. This account number can be anything from 00004412 (for example) to 99999999.

However, since the numbers are stored in the table as numbers and thus without leading zeroes, if a user types in 00012345, Access will not return the account. Access will only return the account if the user types 12345. I have got advice previously concerning this problem referring me to the FORMAT section of the design screen. This does not help me as it merely affects the way in which the field is viewed, as opposed to how the field is stored.

I would like a simple way of allowing the user to enter 00012345 to find the account.

Thanks.

 

[jebry]

Hi!

Could you post the code you are using for the search? That would help to determine what you need to do.
Jeff Bridgham
bridgham@purdue.edu

 

[ANDREWeire]

thanks for the reply: I've put the code below...

Private Sub AccountSearchButton_Click()
On Error GoTo err_AccountSearch
Dim ls_SQL As String

ls_SQL = "SELECT * FROM ACCOUNTS WHERE "
ls_SQL = ls_SQL & " [Account No] LIKE "
ls_SQL = ls_SQL & " [Enter Account Number] & '*'"


Form.RecordSource = ls_SQL
GoTo Finished
err_AccountSearch:
Select Case Err
Case 2001
GoTo Finished
Case Else
Error Err
End Select

Finished:

End Sub

 

[Maquis]

This sounds exactly like a problem I had a few days ago which Jebry helped me with. Will your account numbers always be 8 digits with the leading zeros? If so, try this.


ls_SQL = "SELECT * FROM ACCOUNTS WHERE "
ls_SQL = ls_SQL & "Format([Account No],"00000000&quot LIKE "
ls_SQL = ls_SQL & " [Enter Account Number] & '*' Maq B-)
<insert witty signature here>

 

[jebry]

Hi!

In the following code, I am assuming that Account number is stored as text:

Private Sub AccountSearchButton_Click()
On Error GoTo err_AccountSearch
Dim ls_SQL As String
Dim strAcctNum As String

strAcctNum = InputBox(&quot;Enter Account Number&quot
ls_SQL = &quot;SELECT * FROM ACCOUNTS WHERE &quot;
ls_SQL = ls_SQL & &quot; [Account No] LIKE '*&quot;
ls_SQL = ls_SQL & Format(CLng(strAcctNum)) & &quot;*'&quot;


Form.RecordSource = ls_SQL
GoTo Finished
err_AccountSearch:
Select Case Err
Case 2001
GoTo Finished
Case Else
Error Err
End Select

Finished:

End Sub

Let me know if this helps.
Jeff Bridgham
bridgham@purdue.edu

 

[amcg]

Thanks for the code - it's now working nicely.

Much obliged.