Update a Field in One Table if There is Data in Another

[Seth Schrock]

I'm guessing that the problem was the use of the double quotes, so try replacing True with 'Yes' and False with 'No'. Note the use of single quotes instead of the double quotes.

[sheepchild]

amazing thank you that's sorted it. Thanks for all the help!

just to sort the start on Monday now!

[sheepchild]

just another quick one related to the query you constructed. How would I add in an extra field for example security

As every time I try and add another in it blacks it all?

[Seth Schrock]

strSQL2 = "SELECT tbl_Bookings.EventID, tbl_Bookings.EventTitle As [Course Title], tbl_Bookings.Status " & _
        ", tbl_Bookings.StartTime As [Start Time], tbl_Bookings.EndTime As [End Time], Security " & _
        ", tbl_Bookings.Contact, IIF(Count(CateringRequests.EventID)>0, True, False) As Catering " & _
        "FROM tbl_Bookings LEFT JOIN CateringRequests " & _
        "ON tbl_Bookings.EventID = CateringRequests.EventID " & _
        "WHERE MeetingDate = " & ctlDayBlock.Tag & " " & _
        "GROUP BY tbl_Bookings.EventID, EventTitle, Status, StartTime, EndTime, Contact " & _
        "ORDER BY tbl_Bookings.EventID"

Note the Security field at the end of the second line.

[sheepchild]

that is what i thought, but every time i put something in it all goes blank???

is it something with the block that shows the data?

[Seth Schrock]

Follow the instructions in post #27 and make sure that the query works.

[sheepchild]

getting a pop up saying
'you tried to execute a query that does not include the specified expression 'Security' as part of an aggregate function'

[Seth Schrock]

Ah, I forgot that it is an aggregate query.

strSQL2 = "SELECT tbl_Bookings.EventID, tbl_Bookings.EventTitle As [Course Title], tbl_Bookings.Status " & _
        ", tbl_Bookings.StartTime As [Start Time], tbl_Bookings.EndTime As [End Time], Security " & _
        ", tbl_Bookings.Contact, IIF(Count(CateringRequests.EventID)>0, True, False) As Catering " & _
        "FROM tbl_Bookings LEFT JOIN CateringRequests " & _
        "ON tbl_Bookings.EventID = CateringRequests.EventID " & _
        "WHERE MeetingDate = " & ctlDayBlock.Tag & " " & _
        "GROUP BY tbl_Bookings.EventID, EventTitle, Status, StartTime, EndTime, Contact, Security " & _
        "ORDER BY tbl_Bookings.EventID"

Note that I have now added it to the end of the GROUP BY line as well.

[sheepchild]

found the group thing withing the query but couldn't work out how to get it into the strSQL2

again thank you so much for the help!