asked on

Ms Access forcing field data types in Mkae Table query

Hi

When running a saved Make Table query if I want to create a field not linked to any data I can use NewField:"" to create a text field or NewField:0 to create a number field. However is there any way of doing the same to create Date or Currency fields and can you define the type of number field

Many thanks

Ken

Jeffrey Coachman

Whats the need for this?, in case there is an alternate approach.

As you stated, you can create a new filed in the query, but the datatype is not really etched in stone.

Just doing this: NewField:0
Wont necessarily stop a text value form being used

So my guess is that you can do something like this to create a date field:
NewDate: cdate(SomeValue)

Or if you want it too just "look" like a date:
NewDate: format(SomeValue,"mm/dd/yyyy")

Perhaps I am not understanding something, because a calculated field in a query typically calculates from existing fields.

You cannot really create a "New" field in a query.

(though you can create a query that creates a new field in the table)

JeffCoachman

ASKER CERTIFIED SOLUTION

peter57r

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

kenabbott

ASKER

Many thanks - Newdate:#2099/01/01# works a treat. Any idea how you would do this with currency?

Rey Obrero (Capricorn1)

use this for the currency format of Amount field

Amount:ccur(0)

peter57r

the same principle...

newcur:ccur(0)
Using this technique, there isn't any way to set decimal places or currency symbol (AFAIK)

kenabbott

ASKER

Many thanks - just what I was looking for

Rey Obrero (Capricorn1)

<Using this technique, there isn't any way to set decimal places or currency symbol (AFAIK) >

it will follow your regional setting for decimal and currency symbol