Update query with blank fields

[Mary M]

Trying to update a Notes Filed with a combination of several other fields,
some of which are blank. The query only seems to work for records that have
all fields populated. An example of the desired output whould be...

F1 F2 F3 F4 NOTES
2 4 2 4
1 2 3 4 1 2 3 4

However, the result against this data is as follows...


F1 F2 F3 F4 NOTES
2 4
1 2 3 4 1 2 3 4

I am using F1+" "+F2 etc.

Is there a way to modify the query so that it will not fail if there are
blank fields?

Many thanks in advance for any assistance.

 

[Michel Walsh]

Use &. + propagates the nulls, while & does not.


LEFT( (", "+ f1) & (", " + f2) & (", " + f3) & ( ", " + f4) , 3 )


should return 2, 4 and 1, 2, 3, 4


Indeed, if the field is null, then the + also make null the result implying
it as one of the argument.



Vanderghast, Access MVP

 

[John Spencer]

Michel,

I think you meant to use MID instead of LEFT

MID((", "+ f1) & (", " + f2) & (", " + f3) & ( ", " + f4),3)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Michel Walsh]

<blush> definitively !

Vanderghast, Access MVP