getting rid of 000's

[huskies]

I have a combo box in a form that pulls up ID #'s from a main table.. When people search for a specific ID # in the form they must enter for example.. 000198 in order to pull that ID and Name from the table. Is there any code to eliminate the person having to type those 000's? So they would just have to type 198 for 000198 or 1 for 000001? Thanks!

 

[namliam]

Change the datatype to a number by using Cint, CLong or CDbl

If you need your leading zero's back after that... Format(YourNumber,"000000")
or alternatively Right("000000" & YourNumber, 6)

Happy coding

 

[Banana]

Alternatively, use this SQL as your rowsource, assuming that the '000981' and '000001' are keys of some kind:

SELECT YourData, CDbl(YourData) FROM SourceTable;

This way, no VBA coding is needed.

HTH.

 

[huskies]

I already have code in the rowsource that drags the data from my main table.. Can I also implement that code in there or not? Also namliam not seeing what oyu are saying with that code.. where/how do I need to insert that coding?

 

[namliam]

Banana's way is actually easier... Use your rowsource to do this.

 

[huskies]

I agree, but if my RowSource already contains this...
SELECT DISTINCT [Recoverables Main].[ID] FROM [Recoverables Main] ORDER BY [ID];
where do I throw the code Banana gave me into that?

 

[Banana]

Make it so:

SELECT DISTINCT [Recoverables Main].[ID], CDbl([ID]) AS FormattedID FROM [Recoverables Main] ORDER BY [ID];

You also need to make sure that the listbox's properties are set as following:

Column Counts : 2
Column Width : 0;

HTH.

 

[huskies]

Thank you so much!! Have a good one!