Avoid typing zeros in a parameter query on customer file 1

[gahill]

Hi:
I am using Access 2000 to link to our Accounting Software data tables that are stored in Pervasive SQL.
The Customer Number field is text and 12 positions long.
I have written a parameter query so people can look up certain data but I don't know how to avoid having to type in leading zeros.
Example:000000001351
They majority are like the number above, however some are in the system like this:1001A.
Any help would be greatly appreciated.
Thank you
Gary W. Hill

 

[willir]

I am weak on Prevasive -- haven't touched in a long time, but here are a couple of approaches...

Simulate a combo box.
Have an unbound text field and an unbound subform with a continueous record - both designed to accommodate the CustomerNumber.

The end user starts to type only the numeric part CustomerNumber. The OnKey counts the characters typed. Once three or more characters are typed, the code executes a query using the typed information. Results are depeicted in the subform....

...Where CustomerNumber Like "'" & "*" & Me.GetCustmerNumber & "'"

(Do they use % for the wild card in Pervasive instead?)

Every time additional characters are typed, the subform refines the number of records.

You can add functionality by adding a command button on the subform to open the Customer form with the specific record selected.

The other approach is to encapsulate the entered criteria with wildcards and perform the search -- not as user friendly but less complicated.

Richard