Ms Access forcing field data types in Mkae Table query

kenabbott
kenabbott used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
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
Well you are OK with numbers...
you can do..
Newint:0       (creates long integer)
or
Newdbl:cdbl(0)    (creates a double)


But you cannot create a decimal field in this way. There is no representation available.

ANd for dates, you would have to supply a date value to force the creation of a datetime datatype..
Newdate:#2099/01/01#

Author

Commented:
Many thanks - Newdate:#2099/01/01# works a treat.  Any idea how you would do this with currency?
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Top Expert 2016

Commented:
use this for the currency format of Amount field

Amount:ccur(0)
the same principle...

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

Author

Commented:
Many thanks - just what I was looking for
Top Expert 2016

Commented:
<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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial