Avatar of JCJG
JCJG
 asked on

Access 2007 Make Table query number format

Hi, I am creating a make-table query with a number field.  I'd like the number field with 4 decimal places in the output table.  How do I do that?  Thanks.
SQL

Avatar of undefined
Last Comment
modus_operandi

8/22/2022 - Mon
MINDSUPERB

In design view of a Make Table Query use a Format function of the column you want to format:

E.g. Account1:Format([Account],"0.0000")

Sincerely,
Ed
peter57r

I don't think you can do this within the make-table query.

Mindsuperb's answer will create a text field, not a number field, although the results for the current data will show with 4 dp in that text field.  
ASKER CERTIFIED SOLUTION
Andrew_Webster

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
JCJG

ASKER
I am not getting a workable solution
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
peter57r

I  agree that the poster is not getting a workable solution.
That is because, as I said in my response, it can't be done in the way they are asking.
Andrew Weir gave an approach that is can be used to carry out the poster's requirements.

The table must be defined in advance and an append query used, or the table must be  modified  in code to set the number of decimal places.
Another code example.
CurrentDb.TableDefs("<tblname>").Fields("<fldname>").Properties("DecimalPlaces") = 4

Andrew_Webster

Thanks Peter, that's exactly right.  

JCJG, there is no magic way to do this, it's going to take several steps to make sure that it works as you want it to.  I've had to build solutions for problems like this many times, and it's exactly as Peter and I have described.
modus_operandi

Starting auto-close process to implement the recommendations of the participating Expert(s).
 
modus_operandi
EE Admin
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.