Problem with empty records

I

Ian McLeish

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
 
T

Tom Wickerath

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
Click to expand...

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
 
T

Tom Wickerath

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
Click to expand...

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
 
I

Ian McLeish

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

Ian

Tom Wickerath said:
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
Click to expand...

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
Click to expand...
 
T

Tom Wickerath

You're welcome.

Tom
_______________________________________

:

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

Ian
 
Top