VBA to set 4 decimal places

[NWildblood]

Hi - any help gratefully received !
Am brand new to an team where a colleague has asked me to help her out and find a solution to enforce 4 decimal places in a table that is being updated using a query.
The SQL in the query fails to enforce this in the table (SQL below), so I was wondering if there's some simple VB to do so.?

I am not a regular vb coder, and have tried but failed miserably so far latest example being:

_______________

Sub SetFormat()

CurrentDb.TableDefs("GEOGIS_Data_with_TME_FINAL").Fields("FirstOfS_MLG").Properties("DecimalPlaces") = 4

End Sub

_______________


For reference, the SQL is here

UPDATE GEOGIS_Data_with_TME_FINAL SET GEOGIS_Data_with_TME_FINAL.FirstOfS_MLG = Format([GEOGIS_Data_with_TME_FINAL].[FirstOfS_MLG],"0.0000"), GEOGIS_Data_with_TME_FINAL.FirstOfF_MLG = Format([GEOGIS_Data_with_TME_FINAL].[FirstOfF_MLG],"0.0000");


"No-one got everything done by Friday except Robinson Crusoe...

 

[PHV]

What is the data type of GEOGIS_Data_with_TME_FINAL.FirstOfS_MLG ?
I'd define it as currency (money).

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Andrzejek]

If your field [tt]GEOGIS_Data_with_TME_FINAL.FirstOfS_MLG[/tt] is defined as a Number, there is no need to format it to 0.0000, because 5.5000 is still 5.5 and that's what DB keeps.

If you want to display it to the user as 5.5000, then Format it when presented to the user.

If you want to have it in the DB as 5.5000, then declare your field as Text :-(

Have fun.

---- Andy

 

[NWildblood]

Thanks Andy - the owner of the DB isn't around for me to ask why she has specifically gone for this requirement, and I agree, on the face of it it's a no brainer; however she's quite an experienced programmer.

In the meantime thanks for your input - and yours PHV - much appreciated

"No-one got everything done by Friday except Robinson Crusoe...