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