Query with Function very slow

[dtours]

I have a query that returns room booking details for a particular trip. One of the fields in the query uses a function which returns the last names of the people in each room but this is slowing down the query where it can take up to 12 seconds to open the form (and this is on a fast system). Just wondering if anyone can suggest another way to return the last names that would be fast.



I can post code if needed.

Thanks in advance,
John

[June7]

Yes, UDF code can make queries run slow. Why are you using a UDF to get last names on a form? Maybe should use a listbox or subform.

[dtours]

Yes, UDF code can make queries run slow. Why are you using a UDF to get last names on a form? Maybe should use a listbox or subform.

I need to format last names separated by I comma in a datasheet which is already a subform. Don't see how a listbox would solve this issue.

[June7]

Are you using a UDF because first name field might be Null and don't want comma if there is not first name? Will there always be last name?

An IIf() expression in query or textbox can handle that and run faster.

[lastname] & IIf(Not IsNull([firstname]), ", ", "") & [firstname]

[dtours]

No I am only using last names but there could be as many as 4 people in a room. A comma will only be added if there is more than one person in in a room. The function grabs the RoomID and queries the database to see who is in that room with names separated by a comma. The it does the same for the next record and so on.

[June7]

Okay, that is concatenating data from multiple related records to display in one field and does require UDF. Normally this is done on a report and not a form. The only way to speed up, as suggested, is with dependent list box or related subform (http://www.fmsinc.com/MicrosoftAcces...edSubforms.asp).

[CJ_London]

struggling to understand what you are trying to achieve. Why not post the sql and the function code you are using at the moment together with what the result should look like. The image in post #3 is presumably not it and I don't see how it can be the source data since there is no room number - or is that the Beds value?

Other things to check include is your table properly indexed and do you really need to be bringing through data from 9 years ago?

[dtours]

The RoomID is hidden on the form but it is in the underlying query.

Here is the UDF:

Function GuestNames(lngRoomID As Long) As String

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim strGuestNames As String
Set dbs = CurrentDb()
strSQL = "SELECT tblCustomers.LastName " _
& "FROM (tblCustomers INNER JOIN tblTourBookings " _
& "ON tblCustomers.CustomerID = tblTourBookings.CustomerID) " _
& "INNER JOIN tblCustomerRooms " _
& "ON tblTourBookings.TourBookingID = tblCustomerRooms.TourBookingID " _
& "WHERE tblCustomerRooms.RoomID = " & lngRoomID & " " _
& "ORDER BY tblCustomers.LastName;"
Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)
If Not rst.EOF Then
rst.MoveFirst
Do While Not rst.EOF
strGuestNames = strGuestNames & rst!LastName
rst.MoveNext
If Not rst.EOF Then
strGuestNames = strGuestNames & ", "
End If
Loop
GuestNames = strGuestNames
Else
GuestNames = "Enter Name(s)"
End If

rst.Close
Set rst = Nothing
dbs.Close
End Function

It actually works quite well on my laptop but when running the front-end on my laptop connected to the back-end on the network it can take of to 12 seconds to open the form.

[CJ_London]

but when running the front-end on my laptop connected to the back-end on the network it can take of to 12 seconds to open the form

this is more an issue with your network performance rather than a slow query/function. However it looks to me like your function sql could be improved - I can't see how it is limiting it to checkin/out dates from tblRooms. Also a small improvement may be had by changing dbOpenDynaset to dbOpenSnapshot