calculate null text field

  • Thread starter Thread starter HL
  • Start date Start date
H

HL

Dear Helpers,

I would like to creat a calculated field in a query that merch 3 text fields
together, like, Index: [field A]&[field B]&[field C]. I don't want to replace
the null field with anything so that if [field A] and [field B] are null and
only [field C] contains data, the calculated [Index] field will look exactly
like [field C].

Is it possible? I hope it is possible coz the Index field is a major field
in my database.

Thanks a lot to my helpers!!
 
What happens if you type:
[field A] & [field B] & [field C]
into the Field row in query design, and choose Ascending in the Sort row
under that?

(BTW, don't alias it as INDEX: that's a reserved word.)
 
Dear Allen,

Thanks for your help. I think I have fixed that problem, but I have a new
problem. I want to replace a field with another field if it is null. The
following is the expression I use.

Prod Name: IIf(IsNull([T Mt Disc]![Prod Nm Chi]),[T Mt Disc]![Prod Nm
Eng],[T Mt Disc]![Prod Nm Chi])

The result that it return is exactly the same as [Prod Nm Chi]. [Prod Name]
is blank where [Prod Nm Chi] is blank.

What's the problem?

Thanks!
HL

Allen Browne said:
What happens if you type:
[field A] & [field B] & [field C]
into the Field row in query design, and choose Ascending in the Sort row
under that?

(BTW, don't alias it as INDEX: that's a reserved word.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

HL said:
Dear Helpers,

I would like to creat a calculated field in a query that merch 3 text
fields
together, like, Index: [field A]&[field B]&[field C]. I don't want to
replace
the null field with anything so that if [field A] and [field B] are null
and
only [field C] contains data, the calculated [Index] field will look
exactly
like [field C].

Is it possible? I hope it is possible coz the Index field is a major field
in my database.

Thanks a lot to my helpers!!
Click to expand...
Click to expand...
 
Again, that should work.

Perhaps it's a zero-length string instead of a null. Ask Access what's going
on. In another column in query design, try:
IsChiNull: ([T Mt Disc]![Prod Nm Chi] Is Null)
When you run the query, it should return -1 (i.e. True) if null, else 0 (for
False.)

Or is it possible that [Prod Nm Eng] is also null?

To avoid a VBA function call, I would have written the line like this, but
it's functionally equivalent:
ProdName: IIf([T Mt Disc]![Prod Nm Chi] Is Null,
[T Mt Disc]![Prod Nm Eng], [T Mt Disc]![Prod Nm Chi])
 
Or try the following which tests for both Nulls and zero-length strings

ProdName: IIf([T Mt Disc].[Prod Nm Chi] & "" ="", [T Mt Disc].[Prod Nm Eng],
[T Mt Disc].[Prod Nm Chi])

If that fails, there is the remote possibility that the field contains
multiple spaces and then you need the more complex

ProdName: IIf(Len(Trim([T Mt Disc]![Prod Nm Chi] & "")) =0, [T Mt
Disc].[Prod Nm Eng], [T Mt Disc].[Prod Nm Chi])


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

Allen Browne said:
Again, that should work.

Perhaps it's a zero-length string instead of a null. Ask Access what's
going on. In another column in query design, try:
IsChiNull: ([T Mt Disc]![Prod Nm Chi] Is Null)
When you run the query, it should return -1 (i.e. True) if null, else 0
(for False.)

Or is it possible that [Prod Nm Eng] is also null?

To avoid a VBA function call, I would have written the line like this, but
it's functionally equivalent:
ProdName: IIf([T Mt Disc]![Prod Nm Chi] Is Null,
[T Mt Disc]![Prod Nm Eng], [T Mt Disc]![Prod Nm Chi])

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

HL said:
Dear Allen,

Thanks for your help. I think I have fixed that problem, but I have a new
problem. I want to replace a field with another field if it is null. The
following is the expression I use.

Prod Name: IIf(IsNull([T Mt Disc]![Prod Nm Chi]),[T Mt Disc]![Prod Nm
Eng],[T Mt Disc]![Prod Nm Chi])

The result that it return is exactly the same as [Prod Nm Chi]. [Prod
Name]
is blank where [Prod Nm Chi] is blank.

What's the problem?

Thanks!
HL
Click to expand...
Click to expand...
 
Dear Allen and John,

Thank you so so so much for your help!

I tried "IsChiNull: ([T Mt Disc]![Prod Nm Chi] Is Null)" and it returns "0"

Then I tried "ProdName: IIf([T Mt Disc].[Prod Nm Chi] & "" ="", [T Mt
Disc].[Prod Nm Eng], [T Mt Disc].[Prod Nm Chi])" and it fixed the problem

I guess the field is zero length (though I don't quite understand what it
means, by the way, I import the table from excel. I am pretty sure the fields
were null in excel).

Anyway, thank you very much. You make my day!

Merry Christmas!

John Spencer said:
Or try the following which tests for both Nulls and zero-length strings

ProdName: IIf([T Mt Disc].[Prod Nm Chi] & "" ="", [T Mt Disc].[Prod Nm Eng],
[T Mt Disc].[Prod Nm Chi])

If that fails, there is the remote possibility that the field contains
multiple spaces and then you need the more complex

ProdName: IIf(Len(Trim([T Mt Disc]![Prod Nm Chi] & "")) =0, [T Mt
Disc].[Prod Nm Eng], [T Mt Disc].[Prod Nm Chi])


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

Allen Browne said:
Again, that should work.

Perhaps it's a zero-length string instead of a null. Ask Access what's
going on. In another column in query design, try:
IsChiNull: ([T Mt Disc]![Prod Nm Chi] Is Null)
When you run the query, it should return -1 (i.e. True) if null, else 0
(for False.)

Or is it possible that [Prod Nm Eng] is also null?

To avoid a VBA function call, I would have written the line like this, but
it's functionally equivalent:
ProdName: IIf([T Mt Disc]![Prod Nm Chi] Is Null,
[T Mt Disc]![Prod Nm Eng], [T Mt Disc]![Prod Nm Chi])

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

HL said:
Dear Allen,

Thanks for your help. I think I have fixed that problem, but I have a new
problem. I want to replace a field with another field if it is null. The
following is the expression I use.

Prod Name: IIf(IsNull([T Mt Disc]![Prod Nm Chi]),[T Mt Disc]![Prod Nm
Eng],[T Mt Disc]![Prod Nm Chi])

The result that it return is exactly the same as [Prod Nm Chi]. [Prod
Name]
is blank where [Prod Nm Chi] is blank.

What's the problem?

Thanks!
HL
Click to expand...
Click to expand...
Click to expand...
 
In Access (and other database software), a Null is not the same thing as a
zero-length string.

Try:
Len([T Mt Disc]![Prod Nm Chi] Is Null)

If the query returns zero, it is a zero-length string.
If it returns 1 even though you don't see anything there, it may contain a
space.
If ths query result is blank in this column, it contains a Null.

For an introduction to Nulls, see:
Nulls: Do I need them?
at:
http://allenbrowne.com/casu-11.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

HL said:
Dear Allen and John,

Thank you so so so much for your help!

I tried "IsChiNull: ([T Mt Disc]![Prod Nm Chi] Is Null)" and it returns
"0"

Then I tried "ProdName: IIf([T Mt Disc].[Prod Nm Chi] & "" ="", [T Mt
Disc].[Prod Nm Eng], [T Mt Disc].[Prod Nm Chi])" and it fixed the problem

I guess the field is zero length (though I don't quite understand what it
means, by the way, I import the table from excel. I am pretty sure the
fields
were null in excel).

Anyway, thank you very much. You make my day!

Merry Christmas!

John Spencer said:
Or try the following which tests for both Nulls and zero-length strings

ProdName: IIf([T Mt Disc].[Prod Nm Chi] & "" ="", [T Mt Disc].[Prod Nm
Eng],
[T Mt Disc].[Prod Nm Chi])

If that fails, there is the remote possibility that the field contains
multiple spaces and then you need the more complex

ProdName: IIf(Len(Trim([T Mt Disc]![Prod Nm Chi] & "")) =0, [T Mt
Disc].[Prod Nm Eng], [T Mt Disc].[Prod Nm Chi])


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

Allen Browne said:
Again, that should work.

Perhaps it's a zero-length string instead of a null. Ask Access what's
going on. In another column in query design, try:
IsChiNull: ([T Mt Disc]![Prod Nm Chi] Is Null)
When you run the query, it should return -1 (i.e. True) if null, else 0
(for False.)

Or is it possible that [Prod Nm Eng] is also null?

To avoid a VBA function call, I would have written the line like this,
but
it's functionally equivalent:
ProdName: IIf([T Mt Disc]![Prod Nm Chi] Is Null,
[T Mt Disc]![Prod Nm Eng], [T Mt Disc]![Prod Nm Chi])

Dear Allen,

Thanks for your help. I think I have fixed that problem, but I have a
new
problem. I want to replace a field with another field if it is null.
The
following is the expression I use.

Prod Name: IIf(IsNull([T Mt Disc]![Prod Nm Chi]),[T Mt Disc]![Prod Nm
Eng],[T Mt Disc]![Prod Nm Chi])

The result that it return is exactly the same as [Prod Nm Chi]. [Prod
Name]
is blank where [Prod Nm Chi] is blank.

What's the problem?

Thanks!
HL
Click to expand...
Click to expand...
Click to expand...
 
Back
Top