Access - Select a particular Record based on a text string

Good evening all,

I have a bit of code that I'm running into some trouble with.

Trying to have a combobox SELECT a record based upon a text string.

The table that I'm looking the value up in is named:

That Table has 2 fields Named (Data Type):
 ShiftTypeID (autonumber) and ShiftType (text)

My Code is:
Dim strUseShiftType As String
    strUseShiftType = "Clerk"

    Me.cboShiftTypeSelected.RowSource = "SELECT ShiftTypeID, ShiftType FROM Shift_Types WHERE ShiftType = " & strUseShiftType
    Me.cboShiftTypeSelected = Me.cboShiftTypeSelected.ItemData(0)

Open in new window

When I execute the code it opens an "Enter Parameter Value" "Clerk" input box. ??????
Who is Participating?
Gustav BrockConnect With a Mentor CIOCommented:
Try adding some single quotes:

Me.cboShiftTypeSelected.RowSource = "SELECT ShiftTypeID, ShiftType FROM Shift_Types WHERE ShiftType = '" & strUseShiftType & "'"

wlwebbAuthor Commented:
Thanks gustav!  That did it!  Don't understand why you have to add single vs double quotes.....
Gustav BrockCIOCommented:
That's because it shall be read as a string. If omitted, the value is supposed to be a number.
Thus, were you looking up an Id, it could have read:

  WHERE ShiftTypeId = " & lngUseShiftTypeId & ""

The last element (& "") is not needed but often appended anyway to show that the SQL statement ends here.

wlwebbAuthor Commented:
Ok, got it......  Thank you
Gustav BrockCIOCommented:
You are welcome!

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.