Numeric Field from MT Query (1 Viewer)

[aziz rasul]

How do I create a numeric field in a make table query where the default value is 0.00?

 

[Minty]

Just add it as a field value;

SELECT Field1, field2 ,[COLOR="Black"][B] 0 AS NumberField[/B][/COLOR] INTO YourNewTable
FROM SomeOtherTabelOrQuery;

 

[aziz rasul]

NumberField is a Long Integer. I want it to be a Decimal with 2 decimal places i.e. a default value of 0.00.

 

[Minty]

Sorry you need to make Single or Double value

SELECT Field1, field2 , CSng(0) AS NumberField INTO YourNewTable
FROM SomeOtherTabelOrQuery;

The fact it displays as 0 not 0.00 is down to your forms / reports design not the table.

As any number set to 0.00 will always be stored as 0 unless you tell Access to display it differently

 

[aziz rasul]

Ok that worked in terms of getting the 0 and it's created a Double field from the make-table query. I am not using a form\report but want to export the table into Excel where if the value is 0 I want it to show it in Excel as 0.00.

Is there a tweak in the MT query where I can force the new field to be Fixed with 2 decimal places?

 

[Minty]

No you would have to format it in Excel or use VBA to format the exported sheet.

You could try using Format(YourField,"0.00") in your export source query, but Excel interprets fields in a weird and mystical way only known to dark lords and gnomes.

A lot will depend on how you are actually carrying out the export.

 

[jdraw]

An option to using a MakeTable query is to build a table definition with the fields and datatypes you need. Then, use an append query to populate the table.

 

[aziz rasul]

Minty, that just creates a text field. I guess you have to create the filed as given in post #4 and the either adjust in Excel or change the field property in code.

jdraw, yes that is an option. I thought it would be easy to obtain the solution directly from a MT query.

Thanks both.

 

[The_Doc_Man]

Aziz, the problem is that the import code "knows" that Excel cells are formless, like the void, and they only take shape when being created individually. I'm talking about late data-type binding, as late as possible.

The only "true" way to get an Excel column formatted explicitly is to export the query in a way that is consistent with your goal and then go back using an Excel Application Object to reach into the worksheet and format the column.

 

[aziz rasul]

I understand and I agree. The underlying problem was that if a query would have done the trick (which is what the BOSS wanted as he doesn't want to deal with VBA code when I have left) then it would have been neat and tidy. Managers like things easy which they can maintain according to their ability.

At the end of the day I have had to sneak in some code to get it to work.

 

[The_Doc_Man]

Yes, understood. Don't you just LOVE bosses who want something but then try to tie your hands when you want to give them the best solution?

I was lucky. I proved my judgment to my boss over a decade before I retired, so when we had a big departmental project for Access, his only questions were about efficiency and the expected number of simultaneous users.