define fields

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?
 
A

Allen Browne

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;
 
G

Gary Walter

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. :cool:

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.
 
M

MER

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;
 
A

Allen Browne

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.
 
M

MER

-----Original Message-----
You could use nested IIf() statements (Immediate If), or
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?
 
A

Allen Browne

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
 
J

John Spencer (MVP)

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top