• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 346
  • Last Modified:

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:
[Shift_Types]

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. ??????
0
wlwebb
Asked:
wlwebb
  • 3
  • 2
1 Solution
 
Gustav BrockCIOCommented:
Try adding some single quotes:

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

/gustav
0
 
wlwebbAuthor Commented:
Thanks gustav!  That did it!  Don't understand why you have to add single vs double quotes.....
0
 
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.

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

/gustav
0

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now