Number format problems in an unbound list box

Hi folks,

I have a main form which provides a way to select a particular item by double-clicking on it in a list box. The user can select how they want the list sorted by means of an option group; after update, the following code (partial) is executed:

 Select Case ![SearchType]
         Case 1 'Search by Company
          ![lstChoices].RowSource = ""
          ![lstChoices].ColumnCount = 7
          ![lstChoices].ColumnWidths = " 0 in; 1.4 in; 0.7 in; 0.75 in; 2 in; .8 in; .5 in"
          ![lstChoices].BoundColumn = 2
          ![lstChoices].Left = 375.048
          ![lstChoices].Width = 8900.05
          ![lstChoices].RowSource = "qryLeadSortByCompany"
      ---- etc.

Included in the list is a date field and a quantity field.  I have made sure that the formatting for quantity is set to standard with 0 decimal places all the way back to the table as well as in the query, and I want the date to be formatted as Short Date for the purposes of the list box (the table formats it as General Date).

When the list box executes with the query, I can't get the quantity field to obey the formatting, and curiously, with three of the four queries, Short Date holds, but reverts to General Date in the fourth query.

Does anyone know what's going on and how I can make this work?


Charlie T.

Who is Participating?
Jim P.Commented:
Are you using the Format(MyDateFld,"Short Date") or Format(MyNumFld,"##0") in your queries.  

The Format command returns a string regardless of the original data type.   You would need to wrap it with something like CDate(Format(MyDateFld,"Short Date")) after formatting it.
charlietouAuthor Commented:
Uh, no ...

I was just using the Properties / Format field in the query editor window.

You're saying the format / CDate functions should be part of the field definition?

So then what would the syntax be for formatting numbers with a comma and no decimal places?

Jim P.Commented:
Glad to be of assistance. May all your days get brighter and brighter.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.