Conditionals in SQL

I have the following SQL statement (in the code box below) that is used as the record source of a listbox

The columns in the listbox are:

EventID (not displayed), Member (ID), Last Name, First Name, Address Line 1, Town/City, Postcode/Zip, Balance to Pay, Status (not displayed), B&B Ref (not displayed), Booking ID (not displayed) and Reserved (Boolean – not displayed)

For all Bookings the listbox shows the records but on occasions an Event is more popular than the available spaces.  In these circumstances the application allows Reserved bookings to be taken.  The tJDWBookings.jdwb_Reserved field is True if the ‘booking’ is ‘Reserved’.

Ideally I want to make Reserved bookings look different in the listbox

Normally a line in the listbox looks like:

7464      Smith      Frank      119 High Street      Bath      BA1 2LX      £195.00

I would like ‘Reserved bookings' to look like this in the linebox

7481      Jones      Anne      1 Long Road      Ware      SG12 1JU      RESERVE

As ‘Reserved’ bookings do not have any financial data there is no Balance to Pay so the extreme right column that normally shows a balance would always show £0.00 for 'Reserved bookings'.

How do I rewrite the SQL statement to achieve this please?

SELECT tJDWBookings.jdwb_Event, tJDWBookings.jdwb_Member, tContact.con_LastName, tContact.con_FirstName, tContact.con_Address1, tContact.con_TownCity, tContact.con_PostcodeZip, tJDWBookings.jdwb_Balance, tJDWBookings.jdwb_Status, tJDWBookings.jdwb_BB, tJDWBookings.jdwb_BookingID, tJDWBookings.jdwb_Reserved
FROM (tJDWBookings INNER JOIN tContact ON tJDWBookings.jdwb_Member = tContact.con_ContactId) INNER JOIN tJDWEvents ON tJDWBookings.jdwb_Event = tJDWEvents.jdwe_EventID
WHERE (((tJDWBookings.jdwb_Event)=[Forms]![fJDWEvents]![jdwe_EventID]) AND((tJDWBookings.jdwb_Status)='OK'));

Open in new window

MikeDTEAsked:
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.

dougaugCommented:
if you are using ms sql server, see if this works:

SELECT tJDWBookings.jdwb_Event,
tJDWBookings.jdwb_Member,
tContact.con_LastName,
tContact.con_FirstName,
tContact.con_Address1,
tContact.con_TownCity,
tContact.con_PostcodeZip,
case when tJDWBookings.jdwb_Reserved then 'RESERVED'
                                     else convert(varchar, tJDWBookings.jdwb_Balance, 1)
end [jdwb_Balance],
tJDWBookings.jdwb_Status,
tJDWBookings.jdwb_BB,
tJDWBookings.jdwb_BookingID,
tJDWBookings.jdwb_Reserved
FROM (tJDWBookings INNER JOIN tContact ON tJDWBookings.jdwb_Member = tContact.con_ContactId) INNER JOIN tJDWEvents ON tJDWBookings.jdwb_Event = tJDWEvents.jdwe_EventID
WHERE (((tJDWBookings.jdwb_Event)=[Forms]![fJDWEvents]![jdwe_EventID]) AND((tJDWBookings.jdwb_Status)='OK'));
0
Dale FyeCommented:
In Access, it would look like the following.  This adds another field to the recordset, but you could just as easily delete the balance and Reserved columns, once you have created this computed column.

SELECT tJDWBookings.jdwb_Event,
tJDWBookings.jdwb_Member,
tContact.con_LastName,
tContact.con_FirstName,
tContact.con_Address1,
tContact.con_TownCity,
tContact.con_PostcodeZip,
IIF(tJDWBookings.jdwb_Reserved, 'RESERVED', Format(tJDWBookings.jdwb_Balance, "currency") as ResOrBal,
tJDWBookings.jdwb_Balance,
tJDWBookings.jdwb_Status,
tJDWBookings.jdwb_BB,
tJDWBookings.jdwb_BookingID,
tJDWBookings.jdwb_Reserved
FROM (tJDWBookings INNER JOIN tContact ON tJDWBookings.jdwb_Member = tContact.con_ContactId) INNER JOIN tJDWEvents ON tJDWBookings.jdwb_Event = tJDWEvents.jdwe_EventID
WHERE (((tJDWBookings.jdwb_Event)=[Forms]![fJDWEvents]![jdwe_EventID]) AND((tJDWBookings.jdwb_Status)='OK'));
0
MikeDTEAuthor Commented:
Hi

It is Access SQL as fyed noted so I tried his SQL statement but I get an missing operator syntax error in teh IIF statement.  It indicates the 'as' as being the problem.

I just pasted the SQL code into the access Query builder (Access 2003) and tried to view in Datasheet view.
SELECT tJDWBookings.jdwb_Event, tJDWBookings.jdwb_Member, tContact.con_LastName, tContact.con_FirstName, tContact.con_Address1, tContact.con_TownCity, tContact.con_PostcodeZip, IIF(tJDWBookings.jdwb_Reserved, 'RESERVED', Format(tJDWBookings.jdwb_Balance, "currency") as ResOrBal, tJDWBookings.jdwb_Balance, tJDWBookings.jdwb_Status, tJDWBookings.jdwb_BB, tJDWBookings.jdwb_BookingID, 
tJDWBookings.jdwb_Reserved
FROM (tJDWBookings INNER JOIN tContact ON tJDWBookings.jdwb_Member = tContact.con_ContactId) INNER JOIN tJDWEvents ON JDWBookings.jdwb_Event = tJDWEvents.jdwe_EventID
WHERE (((tJDWBookings.jdwb_Event)=[Forms]![fJDWEvents]![jdwe_EventID]) AND((tJDWBookings.jdwb_Status)='OK'));

Open in new window

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.

Dale FyeCommented:
I missed a closing ")", add it after "Currency"

SELECT tJDWBookings.jdwb_Event
, tJDWBookings.jdwb_Member
, tContact.con_LastName
, tContact.con_FirstName
, tContact.con_Address1
, tContact.con_TownCity
, tContact.con_PostcodeZip
, IIF(tJDWBookings.jdwb_Reserved, 'RESERVED', Format(tJDWBookings.jdwb_Balance, "currency")) as ResOrBal
, tJDWBookings.jdwb_Balance
, tJDWBookings.jdwb_Status
, tJDWBookings.jdwb_BB
, tJDWBookings.jdwb_BookingID
, tJDWBookings.jdwb_Reserved
FROM (tJDWBookings INNER JOIN tContact ON tJDWBookings.jdwb_Member = tContact.con_ContactId) INNER JOIN tJDWEvents ON JDWBookings.jdwb_Event = tJDWEvents.jdwe_EventID
WHERE (((tJDWBookings.jdwb_Event)=[Forms]![fJDWEvents]![jdwe_EventID]) AND((tJDWBookings.jdwb_Status)='OK'));
0
MikeDTEAuthor Commented:
Hi fyed

I am now getting a Sytax error in JOIN operation

FROM (tJDWBookings INNER JOIN tContact ON tJDWBookings.jdwb_Member = tContact.con_ContactId) INNER JOIN tJDWEvents ON JDWBookings.jdwb_Event = tJDWEvents.jdwe_EventID
0
mbizupCommented:
See if this does what you need -

SELECT tJDWBookings.jdwb_Event, tJDWBookings.jdwb_Member, tContact.con_LastName, tContact.con_FirstName, tContact.con_Address1, tContact.con_TownCity, tContact.con_PostcodeZip, tJDWBookings.jdwb_Balance, tJDWBookings.jdwb_Status, tJDWBookings.jdwb_BB, tJDWBookings.jdwb_BookingID, "RESERVED" AS isReserved
FROM (tJDWBookings INNER JOIN tContact ON tJDWBookings.jdwb_Member = tContact.con_ContactId) INNER JOIN tJDWEvents ON tJDWBookings.jdwb_Event = tJDWEvents.jdwe_EventID
WHERE tJDWBookings.jdwb_Event =[Forms]![fJDWEvents]![jdwe_EventID] ANDtJDWBookings.jdwb_Status ='OK' AND tJDWBookings.jdwb_Reserved = True
0
mbizupCommented:
Or if you need them all in the same listbox:

SELECT tJDWBookings.jdwb_Event, tJDWBookings.jdwb_Member, tContact.con_LastName, tContact.con_FirstName, tContact.con_Address1, tContact.con_TownCity, tContact.con_PostcodeZip, tJDWBookings.jdwb_Balance, tJDWBookings.jdwb_Status, tJDWBookings.jdwb_BB, tJDWBookings.jdwb_BookingID, iif(tJDWBookings.jdwb_Reserved = True
, "Reserved", tJDWBookings.jdwb_Reserved)
FROM (tJDWBookings INNER JOIN tContact ON tJDWBookings.jdwb_Member = tContact.con_ContactId) INNER JOIN tJDWEvents ON tJDWBookings.jdwb_Event = tJDWEvents.jdwe_EventID
WHERE tJDWBookings.jdwb_Event =[Forms]![fJDWEvents]![jdwe_EventID] AND tJDWBookings.jdwb_Status='OK'


(Not sure why Fyed's statement didn't work)
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
MikeDTEAuthor Commented:
Hi mbizup

Your statement had a rogue bracket which I removed:

SELECT tJDWBookings.jdwb_Event, tJDWBookings.jdwb_Member, tContact.con_LastName, tContact.con_FirstName, tContact.con_Address1, tContact.con_TownCity, tContact.con_PostcodeZip, tJDWBookings.jdwb_Balance, tJDWBookings.jdwb_Status, tJDWBookings.jdwb_BB, tJDWBookings.jdwb_BookingID, "RESERVED" AS isReserved
FROM (tJDWBookings INNER JOIN tContact ON tJDWBookings.jdwb_Member = tContact.con_ContactId) INNER JOIN tJDWEvents ON tJDWBookings.jdwb_Event = tJDWEvents.jdwe_EventID
WHERE tJDWBookings.jdwb_Event=[Forms]![fJDWEvents]![jdwe_EventID] AND tJDWBookings.jdwb_Status ='OK' AND tJDWBookings.jdwb_Reserved = True

What this does is isolate the reserved booking rather than showing all bookings with a different final colum

Fyed's solution is almost there!
0
MikeDTEAuthor Commented:
Hi mbizup

Yes - your second solution works like a (my) dream!

Thanks once again

Regards
Mike
0
MikeDTEAuthor Commented:
Precipitate

... it works ... nearly

The idea is to have one common column that holds either the balance due or the work 'RESERVE' - the latter in the case of a Reserved booking.

In mbizup's statement the Expr1 column contains only '0' or RESERVE and column jdwb_Balance shows the balance only.

fyed's follwing IIF statement needs incorporating into mbizup's statement.

IIF(tJDWBookings.jdwb_Reserved, 'RESERVED', Format(tJDWBookings.jdwb_Balance, "currency")) as ResOrBal







SELECT tJDWBookings.jdwb_Event, tJDWBookings.jdwb_Member, tContact.con_LastName, tContact.con_FirstName, tContact.con_Address1, tContact.con_TownCity, tContact.con_PostcodeZip, tJDWBookings.jdwb_Balance, tJDWBookings.jdwb_Status, tJDWBookings.jdwb_BB, tJDWBookings.jdwb_BookingID, IIf(tJDWBookings.jdwb_Reserved=True,"RESERVE",tJDWBookings.jdwb_Reserved) AS Expr1
FROM (tJDWBookings INNER JOIN tContact ON tJDWBookings.jdwb_Member = tContact.con_ContactId) INNER JOIN tJDWEvents ON tJDWBookings.jdwb_Event = tJDWEvents.jdwe_EventID
WHERE (((tJDWBookings.jdwb_Event)=[Forms]![fJDWEvents]![jdwe_EventID]) AND ((tJDWBookings.jdwb_Status)='OK'));

Open in new window

0
Dale FyeCommented:

Try this FROM/WHERE clause

FROM (tJDWBookings INNER JOIN tContact ON tJDWBookings.jdwb_Member = tContact.con_ContactId)
INNER JOIN tJDWEvents ON JDWBookings.jdwb_Event = tJDWEvents.jdwe_EventID
WHERE tJDWBookings.jdwb_Event=[Forms]![fJDWEvents]![jdwe_EventID]
AND tJDWBookings.jdwb_Status='OK'

0
MikeDTEAuthor Commented:
OK Guys

By combining fyed's and mbizups statements I have arrived at th following - and it works!
SELECT tJDWBookings.jdwb_Event, tJDWBookings.jdwb_Member, tContact.con_LastName, tContact.con_FirstName, tContact.con_Address1, tContact.con_TownCity, tContact.con_PostcodeZip, IIF(tJDWBookings.jdwb_Reserved, 'RESERVE', Format(tJDWBookings.jdwb_Balance, "currency")) as ResOrBal, tJDWBookings.jdwb_Balance, tJDWBookings.jdwb_Status, tJDWBookings.jdwb_BB, tJDWBookings.jdwb_BookingID
FROM (tJDWBookings INNER JOIN tContact ON tJDWBookings.jdwb_Member = tContact.con_ContactId) INNER JOIN tJDWEvents ON tJDWBookings.jdwb_Event = tJDWEvents.jdwe_EventID
WHERE tJDWBookings.jdwb_Event =[Forms]![fJDWEvents]![jdwe_EventID] AND tJDWBookings.jdwb_Status='OK'

Open in new window

0
Dale FyeCommented:
glad we could help
0
MikeDTEAuthor Commented:
Thanks to both of you.  Once I could see how ... then the rest was just logical.  Works a treat!
0
mbizupCommented:
Glad to help out, Mike!
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.