asked on

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

dougaug

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'));

Dale Fye

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'));

MikeDTE

ASKER

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

SOLUTION

Dale Fye

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

MikeDTE

ASKER

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

mbizup

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

ASKER CERTIFIED SOLUTION

mbizup

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

MikeDTE

ASKER

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!

MikeDTE

ASKER

Hi mbizup

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

Thanks once again

Regards
Mike

MikeDTE

ASKER

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

Dale Fye

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'

MikeDTE

ASKER

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

Dale Fye

glad we could help

MikeDTE

ASKER

Thanks to both of you. Once I could see how ... then the rest was just logical. Works a treat!

mbizup

Glad to help out, Mike!