Number format problems in an unbound list box

Posted on 2006-05-01
Medium Priority
Last Modified: 2008-03-06
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.

Question by:charlietou
  • 2
LVL 38

Accepted Solution

Jim P. earned 1600 total points
ID: 16578950
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.

Author Comment

ID: 16579087
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?

LVL 38

Expert Comment

by:Jim P.
ID: 16586629
Glad to be of assistance. May all your days get brighter and brighter.

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question