Coldfusion How can I update my drop down query to deal with a null value?

I have a drop down query combining 2 fields - problem is the SH_HULLNO field has 2 records with null values and I receive and error:
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.
 
The error occurred in E:/WEB/www/UpdateRecord.cfm: line 18
16 : </cfquery>
17 :
18 :  <cfquery name="DropDown1" datasource="DB">
19 : SELECT  DISTINCT        SH_HULL, SH_ID , SH_HULLNO, SH_HULL + ' ' + SH_HULLNO as SHIP
20 : FROM                    TBL_SHIP

How can I alter my code to disregard a null value in the SH_HULLNO field?

Code:
<cfquery name="DropDown1" datasource="DB">
SELECT  DISTINCT        SH_HULL, SH_ID , SH_HULLNO, SH_HULL + ' ' + SH_HULLNO as SHIP
FROM                    TBL_SHIP
ORDER BY SH_HULL
</cfquery>
DJPr0Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
try

SELECT  DISTINCT        SH_HULL, SH_ID , SH_HULLNO, SH_HULL + ' ' + SH_HULLNO as SHIP
FROM                    TBL_SHIP
WHERE  SH_HULLNO & ""<>""
ORDER BY SH_HULL

or


SELECT  DISTINCT        SH_HULL, SH_ID , SH_HULLNO, SH_HULL + ' ' + SH_HULLNO as SHIP
FROM                    TBL_SHIP
WHERE  SH_HULLNO is not null
ORDER BY SH_HULL
0
DJPr0Author Commented:
Thanks for your reply Rey.

I thought the error was the null value but apparently it's not due to I added values to the null fields and still received the same error.

I did start receiving this error when I switched the field type from text to number.

Could it be  that I'm trying to combine different types of fields?
SH_HULL + ' ' + SH_HULLNO as SHIP
(text field)       (number field)
0
Rey Obrero (Capricorn1)Commented:
try

[SH_HULL] + ' ' + Cstr([SH_HULLNO]) as SHIP

or

[SH_HULL] & ' ' & [SH_HULLNO] as SHIP
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I don't know much about ColdFusion and the language vagaries, but if SH_HULLNO Is a Numeric field you may need to convert it to string. In VBA we'd use Cstr:

SELECT  DISTINCT        SH_HULL, SH_ID , SH_HULLNO, SH_HULL + ' ' + Cstr(SH_HULLNO) as SHIP
0
Mark ElySenior Coldfusion DeveloperCommented:
Change DropDown1 to this

<cfquery name="DropDown1" datasource="DB">
SELECT  DISTINCT (SH_HULL) AS SH_HULL, SH_ID , SH_HULLNO, SH_HULL + ' ' + SH_HULLNO as SHIP
FROM  TBL_SHIP
WHERE SH_HULL IS NOT NULL
ORDER BY SH_HULL
</cfquery>

Open in new window

0
DJPr0Author Commented:
Thanks Rey, this works with one problem:
[SH_HULL] + ' ' + Cstr([SH_HULLNO]) as SHIP
WHERE  SH_HULLNO is not null

I would like to display the value of SH_HULL in the drop down with the SH_HULLNO that contain null values.
Example:
DD 12
JL 55
LP     - null value for SH_HULLNO
LI      - null value for SH_HULLNO
0
Rey Obrero (Capricorn1)Commented:
try

[SH_HULL] + ' ' + IIF(IsNull([SH_HULLNO]),"Null", Cstr([SH_HULLNO])) as SHIP

'remove the Where clause
0
DJPr0Author Commented:
Receiving error:
Error Executing Database Query.

[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.
 
The error occurred in E:/WEB/Input/UpdateRecord.cfm: line 18

17 :
18 :  <cfquery name="DropDown1" datasource="DB">
19 : SELECT  DISTINCT        [SH_HULL] + ' ' + IIF(IsNull([SH_HULLNO]),"Null", Cstr([SH_HULLNO])) as SHIP
20 : FROM                    TBL_SHIP
SQLSTATE        07002
DATASOURCE        DB
VENDORERRORCODE        -3010
SQL         SELECT DISTINCT SH_HULL + ' ' + IIF(IsNull([SH_HULLNO]),"Null", Cstr([SH_HULLNO])) as SHIP FROM TBL_SHIP


Code:
 <cfquery name="DropDown1" datasource="DB">
SELECT  DISTINCT        SH_HULL + ' ' + IIF(IsNull([SH_HULLNO]),"Null", Cstr([SH_HULLNO])) as SHIP
FROM                    TBL_SHIP

</cfquery>
0
Rey Obrero (Capricorn1)Commented:
replace the " with '

[SH_HULL] + ' ' + IIF(IsNull([SH_HULLNO]),'Null', Cstr([SH_HULLNO])) as SHIP
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DJPr0Author Commented:
Thanks Rey.

It worked when I also added the SH_ID:
SELECT  DISTINCT  SH_ID,      [SH_HULL] + ' ' + IIF(IsNull([SH_HULLNO]),'Null', Cstr([SH_HULLNO])) as SHIP
FROM                    TBL_SHIP
0
DJPr0Author Commented:
Thanks Rey!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.