Drop leading zero in search box

[AmyLynnHill]

Greetings!
I have a form with one unbound text box and an unbound list box.

The listbox has a query as the row source. the query has a field (SSN) that is a number field. it drops leading zeros. (I cant change it to a text field, it is linked to an oracle database.)

The text field is used as input by the user to search the listbox. (lstBox.requery, on the afterupdate event)

the problem is when the user puts in leading zeros to search for the ssn it doesnt find it, only if the leading zeros are not input.

anyone know of a why around this?

 

[DJkarl]

If you are using a query as the recordsource change your SSN to an expression with something like

String(9-Len([SSN]),'0') & [SSN] AS FixedSSN

 

[AmyLynnHill]

Sorry, I dont understand what you are saying. Do i put that in the query QBE or in VB code somewhere?

 

[Steve R.]

OracleVariable=Ltrim(YourTextbox)

another option (but it may not work if you have non-numeric characters)

OracleVariable=VAL(YourTextbox)

 

[DJkarl]

Sorry, I dont understand what you are saying. Do i put that in the query QBE or in VB code somewhere?

You could put this into the query.

So if your original query looked like this

SELECT SSN FROM YOURTABLE

You would change it to

SELECT String(9-Len([SSN]),'0') & [SSN] AS FixedSSN FROM YOURTABLE

your query is likely more complex than this, but hopefully this gives you an idea of what could be done.

 

[WayneRyan]

Amy,

You can always use the BeforeUpdate or AfterUpdate event to:

While Left(Me.txtSSN, 1) = "0"
   Me.txtSSN = Mid(Me.txtSSN, 2)
   Wend
'
' Then requery your listbox ...
'

hth,
Wayne