Full Version: Trouble With An Expression
UtterAccess Forums > Microsoft® Access > Access Forms

I'm designing a form that is used to interact with a SharePoint list that others use directly. The purpose is to reserve facilities, which are named in a combo box on the SharePoint list's InfoPath form. Now information about these same facilities is stored in a table in my Access database, and I need to use the primary key value for the facility that the SharePoint user selected, so a lookup has to occur.

My research has led me to the following expression, which should return the primary key value I need. The SharePoint user's input can be reviewed on this form. The combo box "cboFacility" contains the selection that the SharePoint user made, which includes the name of the facility (as stored in my Access database), a string consisting of a space, hyphen and another space, and some other information. So, I need the expression to extract the name of the facility from the string in the combo box, and then look up the primary key value in the database table, using the name as a criterion.

DLookup("FacilityID", "tblFacilities", "FacilityName=" & Left([Forms]![frmFacilityReservations_SP]![cboFacility], (InStr(1, [Forms]![frmFacilityReservations_SP]![cboFacility], " -")) - 1))

Instead of the primary key, the expression returns the following error:

Run-time error '2471':
The expression you entered as a query parameter produced this error: 'Rothrock'.

"Rothrock" is the name of the facility.

Any ideas what I need to do to resolve this?

Thank you so much!
I think you would need to have it as the following:

DLookup("FacilityID", "tblFacilities", "FacilityName= '" & Left([Forms]![frmFacilityReservations_SP]![cboFacility] & "'", (InStr(1, [Forms]![frmFacilityReservations_SP]![cboFacility], " -")) - 1))[code]

It needs to have quotes because it is a string. Try that and see if that does not fix it.
Not sure what the last part (InStr(1, [Forms]![frmFacilityReservations_SP]![cboFacility], " -")) - 1)), but if that is part of the search it also needs to be in quotes. When doing a search with strings it has to be encased in quotes, Numbers should be just numbers. Dates needs to be encased in # (like #12/12/2016#).
LeeAnn is on track with adding apostrophes, as surely FacilityName is a text field.
The OP is taking the left portion of a string in cboFacility before " -" and then searching for that value in FacilityName. Seems odd - I would think cboFacility would contain a list of values in FacilityName in the table. ?
DLookup("FacilityID", "tblFacilities", "FacilityName='" & Left([Forms]![frmFacilityReservations_SP]![cboFacility], (InStr(1, [Forms]![frmFacilityReservations_SP]![cboFacility], " -")) - 1) & "'")
Many thanks to both for the replies. The suggestion from jwhite solved my problem.

Again, thank you! thanks.gif
thumbup.gif LeeAnn and I are glad to assist.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.