Problem with empty records

[Guest]

Fred G told me about the following code which I used successfully to convert
a number into a meaning;
=IIf([FieldA]=0,"Whatever",IIf([FieldA]=1,"Up","Down"))

I was trying to construct another version which would input the word Add
into a field if the trigger field was not empty ( for mailmerging), otherwise
the new field would be blank. Similarly I used it to insert other symbols
(used to write out an optician's prescription) depending if it was required.

This works well on most records.

The database, which I didn't construct, and cannot modify, stores patient
details in one table, and references info from another table "exam_sight". My
formatting works fine where there is a patient record, and an "exam_sight" is
recorded, but where "exam_sight" is empty, it puts Add in the add field.

I basically set the Iff statement to input "Add" into my new field whenever
the trigger field was not blank.
Looking at the output, it seems that Access seems to be differentiating
between a field which is real and blank, and one which doesn't exist (the
entire line is blank barring the patient details ID at the start of the line).

I tried to catch this using a Null value (which maybe I didn't format
correctly as I am a complete newby);
IIf([Exam_Sight.FxAddL]=Null,"",IIf([Exam_Sight.FxAddL]="",""," Add")) AS AddL

but this didn't seem to work. Bit confused between 0 byte string lengths and
null fields, but either way the above doesn't work. Tried various formats of
the Null part of the statement also.

Is there a function in SQL like "if exists"?

If anyone could suggest anything, I would be really gratefull,

Ian McLeish

 

[Baz]

Fred G told me about the following code which I used successfully to convert
a number into a meaning;
=IIf([FieldA]=0,"Whatever",IIf([FieldA]=1,"Up","Down"))

I was trying to construct another version which would input the word Add
into a field if the trigger field was not empty ( for mailmerging), otherwise
the new field would be blank. Similarly I used it to insert other symbols
(used to write out an optician's prescription) depending if it was required.

This works well on most records.

The database, which I didn't construct, and cannot modify, stores patient
details in one table, and references info from another table "exam_sight". My
formatting works fine where there is a patient record, and an "exam_sight" is
recorded, but where "exam_sight" is empty, it puts Add in the add field.

I basically set the Iff statement to input "Add" into my new field whenever
the trigger field was not blank.
Looking at the output, it seems that Access seems to be differentiating
between a field which is real and blank, and one which doesn't exist (the
entire line is blank barring the patient details ID at the start of the line).

I tried to catch this using a Null value (which maybe I didn't format
correctly as I am a complete newby);
IIf([Exam_Sight.FxAddL]=Null,"",IIf([Exam_Sight.FxAddL]="",""," Add")) AS AddL

but this didn't seem to work. Bit confused between 0 byte string lengths and
null fields, but either way the above doesn't work. Tried various formats of
the Null part of the statement also.

Is there a function in SQL like "if exists"?

If anyone could suggest anything, I would be really gratefull,

Ian McLeish

There are two functions that will be of use/interest to you.

IsNull() is pretty self-explanatory, e.g. IsNull([Exam_Sight.FxAddL])

Nz() allows you to specify a value that you want returned if a value is
null. For example:

Nz([Exam_Sight.FxAddL],"") will return a zero-length string "" if
Exam_Sight.FxAddL is null, otherwise it will return Exam_Sight.FxAddL.

Note that the second argument for Nz() is optional, because the function
figures out what to return based on the datatype of the value. So, if
Exam_Sight.FxAddL is null:

Nz([Exam_Sight.FxAddL]) will return a zero-length string if
Exam_Sight.FxAddL is text, or zero if it is numeric.

 

[Guest]

Hi Ian,

I tried to catch this using a Null value (which maybe I didn't format
correctly as I am a complete newby);
IIf([Exam_Sight.FxAddL]=Null,"",IIf([Exam_Sight.FxAddL]="",""," Add")) AS AddL

You can't test for equality for null. You need to use the IsNull function
instead. You can also use the Nz function to convert a null to just about
anything you want. Try this instead:

IIf(IsNull([Exam_Sight.FxAddL]),"",IIf([Exam_Sight.FxAddL]="","","Add")) AS
AddL

The length function might work too. I haven't tested it in this case, but
try the following:

IIf(Len([Exam_Sight.FxAddL])=0,"","Add")) AS AddL

Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

Fred G told me about the following code which I used successfully to convert
a number into a meaning;
=IIf([FieldA]=0,"Whatever",IIf([FieldA]=1,"Up","Down"))

I was trying to construct another version which would input the word Add
into a field if the trigger field was not empty ( for mailmerging), otherwise
the new field would be blank. Similarly I used it to insert other symbols
(used to write out an optician's prescription) depending if it was required.

This works well on most records.

The database, which I didn't construct, and cannot modify, stores patient
details in one table, and references info from another table "exam_sight". My
formatting works fine where there is a patient record, and an "exam_sight" is
recorded, but where "exam_sight" is empty, it puts Add in the add field.

I basically set the Iff statement to input "Add" into my new field whenever
the trigger field was not blank.
Looking at the output, it seems that Access seems to be differentiating
between a field which is real and blank, and one which doesn't exist (the
entire line is blank barring the patient details ID at the start of the line).

I tried to catch this using a Null value (which maybe I didn't format
correctly as I am a complete newby);
IIf([Exam_Sight.FxAddL]=Null,"",IIf([Exam_Sight.FxAddL]="",""," Add")) AS AddL

but this didn't seem to work. Bit confused between 0 byte string lengths and
null fields, but either way the above doesn't work. Tried various formats of
the Null part of the statement also.

Is there a function in SQL like "if exists"?

If anyone could suggest anything, I would be really gratefull,

Ian McLeish

 

[Guest]

Looks like my last suggestion doesn't work <sad face>
Try this instead:

IIf(Len([Exam_Sight.FxAddL])>0,"Add","") AS Addl


Tom
_______________________________________________

:

Hi Ian,

I tried to catch this using a Null value (which maybe I didn't format
correctly as I am a complete newby);
IIf([Exam_Sight.FxAddL]=Null,"",IIf([Exam_Sight.FxAddL]="",""," Add")) AS AddL

You can't test for equality for null. You need to use the IsNull function
instead. You can also use the Nz function to convert a null to just about
anything you want. Try this instead:

IIf(IsNull([Exam_Sight.FxAddL]),"",IIf([Exam_Sight.FxAddL]="","","Add")) AS
AddL

The length function might work too. I haven't tested it in this case, but
try the following:

IIf(Len([Exam_Sight.FxAddL])=0,"","Add")) AS AddL

Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

Fred G told me about the following code which I used successfully to convert
a number into a meaning;
=IIf([FieldA]=0,"Whatever",IIf([FieldA]=1,"Up","Down"))

I was trying to construct another version which would input the word Add
into a field if the trigger field was not empty ( for mailmerging), otherwise
the new field would be blank. Similarly I used it to insert other symbols
(used to write out an optician's prescription) depending if it was required.

This works well on most records.

The database, which I didn't construct, and cannot modify, stores patient
details in one table, and references info from another table "exam_sight". My
formatting works fine where there is a patient record, and an "exam_sight" is
recorded, but where "exam_sight" is empty, it puts Add in the add field.

I basically set the Iff statement to input "Add" into my new field whenever
the trigger field was not blank.
Looking at the output, it seems that Access seems to be differentiating
between a field which is real and blank, and one which doesn't exist (the
entire line is blank barring the patient details ID at the start of the line).

I tried to catch this using a Null value (which maybe I didn't format
correctly as I am a complete newby);
IIf([Exam_Sight.FxAddL]=Null,"",IIf([Exam_Sight.FxAddL]="",""," Add")) AS AddL

but this didn't seem to work. Bit confused between 0 byte string lengths and
null fields, but either way the above doesn't work. Tried various formats of
the Null part of the statement also.

Is there a function in SQL like "if exists"?

If anyone could suggest anything, I would be really gratefull,

Ian McLeish

 

[Guest]

Thanks very much Tom, the Null one works a treat,
Thanks again,

Ian

Hi Ian,

I tried to catch this using a Null value (which maybe I didn't format
correctly as I am a complete newby);
IIf([Exam_Sight.FxAddL]=Null,"",IIf([Exam_Sight.FxAddL]="",""," Add")) AS AddL

You can't test for equality for null. You need to use the IsNull function
instead. You can also use the Nz function to convert a null to just about
anything you want. Try this instead:

IIf(IsNull([Exam_Sight.FxAddL]),"",IIf([Exam_Sight.FxAddL]="","","Add")) AS
AddL

The length function might work too. I haven't tested it in this case, but
try the following:

IIf(Len([Exam_Sight.FxAddL])=0,"","Add")) AS AddL

Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

Fred G told me about the following code which I used successfully to convert
a number into a meaning;
=IIf([FieldA]=0,"Whatever",IIf([FieldA]=1,"Up","Down"))

I was trying to construct another version which would input the word Add
into a field if the trigger field was not empty ( for mailmerging), otherwise
the new field would be blank. Similarly I used it to insert other symbols
(used to write out an optician's prescription) depending if it was required.

This works well on most records.

The database, which I didn't construct, and cannot modify, stores patient
details in one table, and references info from another table "exam_sight". My
formatting works fine where there is a patient record, and an "exam_sight" is
recorded, but where "exam_sight" is empty, it puts Add in the add field.

I basically set the Iff statement to input "Add" into my new field whenever
the trigger field was not blank.
Looking at the output, it seems that Access seems to be differentiating
between a field which is real and blank, and one which doesn't exist (the
entire line is blank barring the patient details ID at the start of the line).

I tried to catch this using a Null value (which maybe I didn't format
correctly as I am a complete newby);
IIf([Exam_Sight.FxAddL]=Null,"",IIf([Exam_Sight.FxAddL]="",""," Add")) AS AddL

but this didn't seem to work. Bit confused between 0 byte string lengths and
null fields, but either way the above doesn't work. Tried various formats of
the Null part of the statement also.

Is there a function in SQL like "if exists"?

If anyone could suggest anything, I would be really gratefull,

Ian McLeish

 

[Guest]

You're welcome.

Tom
_______________________________________

:

Thanks very much Tom, the Null one works a treat,
Thanks again,

Ian