Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.
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.
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
I wear a lot of hats...
"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.
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.
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.
You could use a conversion function in the SQL to make sure that you have a field that is the correct datatype.
SELECT CCur(MyNumberColumn) INTO MyTable
The problem that you have is one of formatting, and that will have to be set using either DDL to alter the column format (tough-ish in Access), or using ADOX (ok-ish), or by hand.
A solution that is used all the time in the data warehousing world is to use more than one layer of tables. Use the make table code to pull the data in (or better use a "kill and fill" truncate and load), but then transfer it to a staging table that's in the correct format, validate it, then pull it in to the main database.
SELECT CCur(MyNumberColumn) INTO MyTable
The problem that you have is one of formatting, and that will have to be set using either DDL to alter the column format (tough-ish in Access), or using ADOX (ok-ish), or by hand.
A solution that is used all the time in the data warehousing world is to use more than one layer of tables. Use the make table code to pull the data in (or better use a "kill and fill" truncate and load), but then transfer it to a staging table that's in the correct format, validate it, then pull it in to the main database.
Experts Exchange Solution brought to you by
Your issues matter to us.
Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.
Start your 7-day free trial
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("<tbln
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("<tbln
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.
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.
Starting auto-close process to implement the recommendations of the participating Expert(s).
modus_operandi
EE Admin
modus_operandi
EE Admin
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax
From novice to tech pro — start learning today.
-
Microsoft ApplicationsCertification: MOS Microsoft Office Specialist - Outlook 2007 Ad… 147 lessons
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
E.g. Account1:Format([Account],
Sincerely,
Ed