Combine String with wildcard characters

1
votes

I am working to write a VBA code that will return data from a specific row in an access database. The data that am I working with is pretty simple, and I am a complete newbie to VBA coding, so please don't judge me if this is a stupid question.

I have the following code, which runs after a user updates a text box in a form:

Sub Text2_AfterUpdate()

Dim VarA As Variant
Dim VarW As Variant



VarW = Left([Forms]![Return Student Info]![Text2], InStr([Forms]![Return Student Info]![Text2], "-") - 1)

VarA = DLookup("[Name First]&' '&[Name Last]&' '&[Student Number]", "Student Bookings (term 84)", _
"[Room Space Description] like 'VarW'?? ")

MsgBox (VarW)
Text0.Value = VarA

End Sub

I am trying to search in the DLookup function for the Variable VarW with two wildcard characters add on the end. If I were to replace VarW with 'Couch Center 224E-1', my intended result would appear.

There are two students living in one room, one in Couch Center 224E-1 and one in Couch Center 224E-2. Ideally I would extend the code to return both students using the wildcard characters (I know DLookup only returns one result).

Right now, DLookup only returns values if the direct value is typed into either the DLookup syntax or VarW.

2
ms-accessvba

2 Answers

1
votes

The problem was the criteria of your DLookUp. Try this:

Sub Text2_AfterUpdate()

Dim VarA As Variant
Dim VarW As Variant



VarW = Left(Me.Text2, InStr(Me.Text2, "-") - 1)

VarA = DLookup("[Name First] &' '& [Name Last] &' '& [Student Number]", "Student Bookings (term 84)", _
"[Room Space Description] like '" & VarW & "??'")

MsgBox (VarW)
Text0.Value = VarA

End Sub
0
votes

Firstly, you should concat where-string with VarW value

VarA = DLookup("[Name First] & ' ' & [Name Last] & ' ' & [Student Number]", "[Student Bookings (term 84)]", _
  "[Room Space Description] like '" & VarW & "??'")

Secondly, use Recordset. Also you can use "Me" for current form, instead of "[Forms]![Return Student Info]!"

Sub Text2_AfterUpdate()

  Dim VarA As Variant
  Dim VarW As Variant
  Dim Rs As Recordset

  VarW = Left(Me.Text2, InStr(Me.Text2, "-") - 1)

  Set Rs = CurrentDb.OpenRecordset("SELECT * FROM [Student Bookings (term 84)] WHERE [Room Space Description] like '" + VarW + "??'", dbOpenSnapshot)
  While Not Rs.EOF 'loop for each rows that query returned
    MsgBox (Rs![Name First] & " " & Rs![Name Last] & " " & Rs![Student Number])) 
  Wend
  Set Rs = Nothing
End Sub

Also, if you don't like "concat-style", there is another way. It's using stored parameter-queries.