It sounds as if you are using a make table query.
If so and you want to insert a blank field of a specific type you can try the
following expressions.
for a number field
Field: AcctCode: IIF(True,Null,999999.99)
For a text field
Field: AcctCode: IIF(True,Null,"XXXXXXXXXXX")
For a dateField
Field: AcctDate: IIF(TRUE, Null,#12/12/12#)
If you are using the parameter box then you have two choices:
1) Define the parameters using the parameter dialog.
== Click in the table area but not on a table so you select the query
== Select Query: Parameters from the menu
== Enter the exact parameter string and select its data type
2) Use expressions like (which will error if you don't enter a valid value -
no nulls allowed)
Field: AcctCode: CText([Enter Account Code])
Field: AcctDate: CDate([Enter Account Date])
Field: AcctTotal: CDbl({Enter Total Amount])
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Sorry, Jerry.
I try to explain.
I imported a table from the others to my DB and I have to make changes to
the table in my own DB. Besides some updates have been done for the imported
table, I have to add some new columns (fields) to the table and inputting a
code for the newly added column too. When I run the table qry to make a new
table, I add the extra columns (fields) with a parameter value box, so I can
input the "code" to the newly added column at the same time (cuz all the
fields are carrying the same code to start with...)
e.g. field AcctCode : ([Enter 16205]) and GrossTotal:Val([Enter 0])
So the new column (field) "AcctCode" in the newly made table will become
Binary type and the "GrossTotal" will become "number" type of which I can
append new numeric data's from other tables but not text. Similarly, I can't
append any numeric data to the "binary". I think the VAL does the tric to
allow me appending numeric data.
My question is: is there a similar expression code like "Val" to use for
"Text" type ?
Probebly Binary type will have no problem for appending any Text data from
other tables ?
thanks
Karen
Jerry Whittle said:
Your question is somewhat confusing. If you mean that you want to alter a
table by adding a new column, you must use SQL as the query design/QBE grid
doesn't handle data definition queries.
Below is an example of adding a text column named "NewField" limited to 25
characters to the "abc" table.
ALTER TABLE abc ADD COLUMN NewField TEXT(25);
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
:
I want to create an extra field from a table qry for a table.
If I want the field as Number type, I will use, e.g. ExtraNumber: Val([Enter
0]).
I don't know how to add a field as Text type ? e.g. ExtraCode: ([Enter
0340), then it becomes Binary, apparently there should be a function code
typed in front ?
Please help and please in query design view, not in SQL.
thanks
karen
