Assign Custom Formatting to Numbers Using VBA (1 Viewer)

[rjp99]

Hello All,

I am using VBA to execute a 'Make Table' (named 'DT'). One of the fields in DT (named 'Dollars') contains numbers that have 6 to 8 digits; some are positive and some are negative.

These large numbers with no commas (or parentheses when they're negative) are incredibly hard to read.

Does anybody have any VBA coding that will change both the “Format” and the “Decimal Place” Field Properties on the table I make (i.e. ‘DT’). I want to the Format property to read (#,##0.00;(#,##0.00)) and the Decimal Place property to read “2”). This will allow me to display a number like -10326786.41 as (10,326,786.41) which is incredibly more readable.

Is it possible to do this programmatically; I’ve search the internet high and low and could find anything remotely helpful. Thanks in advance for any help you can provide.

 

[JHB]

Try the below code:

  Dim rst As DAO.Recordset
  
  Set rst = CurrentDb.OpenRecordset("DT")
  rst.Fields.Item("Dollars").Properties("Format") = " #,##0.00;(#,##0.00)"
  rst.Fields.Item("Dollars").Properties("Decimalplaces") = 2

 

[rjp99]

Thanks JHB!

The "Decimalplaces" statement works like a charm!

The "Format" statement however, produce a runtime error (3270). I did notice that there was a space betweem the open quote (") and the pound sign (#) in your original construction but I corrected for it and still got the error.

Unless there are any other ideas, I'm inclined to believe that customizing number formats in Access with via VBA just isn't possible. It's amazing to me that VBA is not designed to facilitate something so basic.

 

[JHB]

Sorry, I've forgotten it was a newly created table, (I've tested on one which already had a format), therefore you've to create the property "Format" first.

  Dim tdf As DAO.TableDef, prt As Property
  Set prt = CurrentDb.TableDefs("DT").Fields("Dollars").CreateProperty("Format", 10, " #,##0.00;(#,##0.00)")
  CurrentDb.TableDefs("DT").Fields("Dollars").Properties.Append prt
  CurrentDb.TableDefs("DT").Fields("Dollars").Properties("Decimalplaces") = 2

 

[rjp99]

Sorry, for late response JHB. Didn't check the forum yesterday (had some other work come up that took me away from the Access project momentarily).

The code worked beautifully!!

I do have one other question though; why is CreateProperty necessary. All of the involved objects (the table, the field and the format property itself) exist PRIOR TO attempting to change the formatting.

What am I missing here? Thanks a million for your help!!

 

[JHB]

Not all properties is available at runtime.
Did you read the below line?

 

[rjp99]

I read it but it didn’t get to what I really wanted to understand which is why the process for programmatically applying a custom 'Format' setting is designed as it is.

If I want to MANUALLY apply a custom setting to either of ‘Format’ or ‘’Decimal Point’, it’s a two-step process: 1) ‘access’ the property [via the general field list] and 2) ‘set’ it [by typing what you want].

When I want to PROGRAMMATICALLY apply a custom setting to 'Decimal Places', it’s a two-step process: 1) ‘access’ the property [CurrentDb.TableDefs("Tb").Fields("Fld").Properties ("Decimalplaces") and 2) ‘set’ it (i.e. “=2”).

When I want to apply a custom setting to 'Format' PROGRAMMATICALLY, it’s a four-step process: 1) create the ‘Format’ object, 2) append it, 3) access it and 4) set it.

If the MANUAL process for applying a custom setting to either of the properties is the same then why are the programmatic processes different? This seems illogical; I was trying determine if there’s a flaw in way I’m thinking about this.

 

[Galaxiom]

I read it but it didn’t get to what I really wanted to understand which is why the process for programmatically applying a custom 'Format' setting is designed as it is.

The Format property is a string. If it existed prior to use it would be an empty string and nothing would display. Hence it is only created when there is something to put in it.

Wanting a Format on a table suggests you have users looking directly at the table. Not a good idea. Normally a form or report is used viewing and the Format applied there.

If you really do want to view the table then instead of MakeTable you could use a preformatted table and append the records to it, clearing them out before they are replaced. This gives you a lot more control over the table structure.

 

[rjp99]

Thanks Galaxiom! I totally get the logic now!!

You’re very close in terms of what my original intent was. I did want users to see the contents in the table but I was going to give them that visibility via a sub-form.

I like to keep the number of tables in my databases to an absolute minimum (tracking what's in each one of them gets to be quite a chore) so my approach (when I posted my original comment) was going to be to create the underlying table whenever it was needed and then delete it after the user finished doing what needed to be done.

The sub-form 'adopts' its formatting from the underlying table and I didn’t know how to override the formatting on the sub-form (just now starting to figure out how forms work) so I was looking for a way to change the property on the underlying table.

I decided to dump the ‘create on demand’ approach early on and use the approach you suggested very early on but I was still curious as to why applying a programmatic custom setting to 'Format' property was so "involved"; your explanation makes perfect sense to me; thanks a million!!