[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL - Return a field value if it exists, otherwise, return a specific text.

Posted on 2011-10-03
7
Medium Priority
?
350 Views
Last Modified: 2012-06-21
background info, i am trying to create a list view that shows thumbail images, if they have been created (and image name saved in my table).  

I'm wonderinig if there is an easy way I can do this, so it works with both access and sql server, so that I can achieve this logic:

Select all my courses from the course list, left joining with my section table, which has the thumbnail image name stored in one column (but may be blank).  There is a chance no record is returned from this 2nd table at all.

In the query below, i want Thumbnail's value to be the image name returned, OR "NoThumbnailYet.jpg", if it returns no value.

Select C.Course_Number, S.Thumbnail from Courses C Left JOIN Sections S on C.Course_Number=S.Course_Number Order by C.Course_Number

Is there a basic syntax I should use, or is this something I am better off doing after returning the full query, during building  my data table to bind to the list view.  I know I can do the manipulating after the fact, just was hoping for a more simple solution.

0
Comment
Question by:Robert Hamel
7 Comments
 
LVL 49

Expert Comment

by:Dale Fye
ID: 36903648
In Access, the function is NZ().  In SQL Server, it is COALESCE()

Select C.Course_Number, NZ(S.Thumbnail, "NoThumbnailYet.jpg"
From Courses C Left JOIN Sections S on C.Course_Number=S.Course_Number Order by C.Course_Number

0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 501 total points
ID: 36903650
no issue:
Select C.Course_Number, COALESCE(S.Thumbnail, 'NoThumbnailYet.jpg') Thumbnail from Courses C Left JOIN Sections S on C.Course_Number=S.Course_Number Order by C.Course_Number

Open in new window

0
 
LVL 49

Assisted Solution

by:Dale Fye
Dale Fye earned 498 total points
ID: 36903657
oops, missing closing parenthesis

Select C.Course_Number, NZ(S.Thumbnail, "NoThumbnailYet.jpg")
From Courses C Left JOIN Sections S on C.Course_Number=S.Course_Number Order by C.Course_Number

Another method for handling this, if you want to use the same syntax for Access and SQL Server backends is to use your original query, and then use the Current event of your form to test for the value of the field [Thumbnail] before setting the Picture property of an image control.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Robert Hamel
ID: 36903870
apparently the NZ function doesn't work outside of access itself.  Using it in ASP application is not supported... getting:  Undefined function 'NZ' in expression

Microsoft says it isn't supported:
http://support.microsoft.com/default.aspx?scid=kb;en-us;294698&Product=acc
0
 
LVL 13

Expert Comment

by:Ryan
ID: 36904063
If speed isn't too big of a deal, you can also use
SELECT...IIF(S.Thumbnail<>Null,S.Thumbnail,"NoThumbnailYet.jpg")...

This code would be run client side, so its a bit slower.
0
 
LVL 6

Accepted Solution

by:
worthyking1 earned 501 total points
ID: 36905531
You can also simply do it while you're looping through the recordset to create your list view, for example:

set rs = con.execute("Select C.Course_Number, S.Thumbnail from Courses C Left JOIN Sections S on C.Course_Number=S.Course_Number Order by C.Course_Number")

while not rs.eof

  ' populate your vars
  Course_Number = CInt(rs("Course_Number"))
  If Trim(rs("Thumbnail"))<>"" then Thumbnail = Trim(rs("Thumbnail")) else Thumbnail = "NoThumbnailYet.jpg"
  Sections = rs("Sections")  

  ' now do your list output however you want using the vars, for example
  response.write "Course number: " & Course_Number & "<br>"
  response.write "<img src=""" & Thumbnail  & """><br>"

rs.movenext()
wend
0
 

Author Closing Comment

by:Robert Hamel
ID: 36909728
NZ didn't work for a web app, but was still good to know.  Coalesce works great.  In the end, being able to have the same logic for both access and SQL server was ideal for simplicity, so I went with something similar to the loop.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question