Concatenate and Null values

skipit

Kid at heart
Local time
Today, 12:33
Joined
Feb 11, 2002
Messages
17
Morning all,

Thanks to help from this forum I have the following SQL for one of my queries:

SELECT [qryMain2-17].JCI_PN, [qryMain2-17].JeffCityPN, [qryMain2-17].ProgramID, [JCI_PN] & "
" & "" & "
" & [JeffCityPN] & "
" & "" & "
" & [Desc1] AS PartNum, "Center ILD:" & "
" & [CenterILD_Spec] & "
" & "CPF: " & "
" & [CenterCPFSpec] & "
" & "Hyst: " & "
" & [Hysteresis] & "
" & "Wing ILD: " & "
" & [WingILD_Spec] AS Specs
, "PPAP Wt:" & "
" & [PPAP_Weight] & "
" & "PPAP Date: " & "
" & [PartPPAP_Date] AS PPAPInfo, [qryMain2-17].Customer
FROM [qryMain2-17]
WHERE ((([qryMain2-17].ProgramID) Like "*" & [Forms]![frmReportInterface]![cboProgram]));​

The part of the qry SQL in question is the part in red. So in qry design grid my field for "Spec" looks like so:


Specs: "Center ILD:" & "
" & [CenterILD_Spec] & "
" & "CPF: " & "
" & [CenterCPFSpec] & "
" & "Hyst: " & "
" & [Hysteresis] & "
" & "Wing ILD: " & "
" & [WingILD_Spec]​

So the finished product would look similar to:
Specs:
Center ILD
200 - 300
CPF
120-200
and so on..

My question is, how do I not show the text "Center ILD" if my field [CenterILD_Spec] has a null or zero-length string value and still have all the other values for "Spec" showup if their respective fields are not null? I have tried using IIF and IsNull but keep getting errors. I tried just entering IsNull in the Criteira of the Design grid but no help.

I read thru the help files and they are lacking or maybe I am being dense today.

Anyway, any help I can get I will be thankful for. :cool:

Thanks Skip :D
 
Although the ampersand (&) is the standard concatenation operator, the plus (+) can also be used in some circumstances. The plus operates differently with null fields and this may help you. Read the help entries for both + and & for details.
 
Pat,

Thanks for the help. I read thru all the info I could find in the help files on concatenation operators & and +.

I replaced all the "&" with "+" but I still get the same results.

I have searched the forums and found a little help but have not found anything to do what I need. I just know this is a simple fix but I am at a loss. I will keep messing with until I either blow up the computer or get it to work.

Thanks and have a g'day, Skip :D
 
Good morning all,

I have found a solution for this problem I will list it below but first I have another question....

How do I not show a field if it's value is equal to - (minus sign)?? The code in the qry design grid is the same as my first question. Any help would be great.

Now the solution for my first problem:

Specs: IIf(IsNull([CenterILD_Spec]),"","Center ILD:" & "
" & [CenterILD_Spec]) & "
" & IIf(IsNull([CenterCPFSpec]),"","CPF: " & "
" & [CenterCPFSpec]) & "
" & IIf(IsNull([Hysteresis]),"","Hyst: " & "
" & [Hysteresis]) & "
" & IIf(IsNull([WingILD_Spec]),"","Wing ILD: " & "
" & [WingILD_Spec])​

Thanks Skip
 

Users who are viewing this thread

Back
Top Bottom