define fields

  • Thread starter Thread starter MER
  • Start date Start date
M

MER

Is there a way to define new fields on the fly in the
SELECT statement. In IBI FOCUS, you can create fields to
add to each output record of a "SELECT" (Table File). Can
you do the same in ACCESS SQL?
 
You can generate dynamic fields with a value an and alias like this:

SELECT "This is not a table field" AS Field1, Null AS Field2,
Table1.SomeField,
FROM Table1;
 
I don't know why I am always compelled
to do this, and I apologize in advance, but...

Null AS Field2

will create a binary field.....
that's not good. 8-)

One workaround to return a Null field that is not
binary that Michel used once was

IIF(-1,Null,"") AS NullTextField
or
IIF(-1,Null,0) AS NullNumField

if I remember correctly.
 
can you do something like:

SELECT "This is not a table field: IF Table1.tele# isnot
empty then set value to Table1.tele# else if Table1.home#
isnot empty then set value to Table1.home# else if
Table1.cell# is not empty then set value to Table1.cell#
else set value to Table1.tele#" AS Field1, Null AS Field2,
Table1.SomeField,
FROM Table1;
 
You could use nested IIf() statements (Immediate If), or a Switch()
statement to achieve what you are asking for. Test for Is Null, as Empty
does not apply.

Alternatively, you could build a relational structure with the various type
of number in a related table instead of trying to manage fields in this way.
 
-----Original Message-----
You could use nested IIf() statements (Immediate If), or
Click to expand...
a Switch()


This sounds good, but I don't know anything about IIF or
Switch.

Could you show me an example or point me to a web site
where I can read about it and see examples?
 
Access does have a help file, and that would be a good place to start.

Press Ctrl+G to open the immediate window.
Enter:
IIf
and press F1
 
IsNull is a vba function that returns true or false.

IsNull(SomeObject) returns true or false where SomeObject could be a field or
a variable of the variant type.

In a query against a field you might use [FieldName] is Null or [FieldName] Is
Not Null
 
Back
Top