Change Line of Code in Module (using Access 2000)

I have function listed below in the code section.

Would you please help to change following line "Where [LoanID]=" & sVar & "")

This line was correct when I have LoanID with Type Number (Size Double) in the table.
Currently Data Type in Table changed from Number to Text.  How can I adjust line because when I run function I have run time error "No current record".  Once I changed Data Type back to Number everything is working.  Thank you.
Function ConcatVar(sVar As Variant) As String
Dim rs As DAO.Recordset, s As String

Set rs = CurrentDb.OpenRecordset("select * From [Combined-Tax-Data] Where [LoanID]=" & sVar & "")

    rs.MoveFirst
 
    Do Until rs.EOF
      s = s & rs("[Tax_Explanation]") & ";"
      rs.MoveNext
    Loop

    ConcatVar = Left(s, Len(s) - 1)
End Function

Open in new window

maximyshkaAsked:
Who is Participating?
 
jppintoConnect With a Mentor Commented:
Set rs = CurrentDb.OpenRecordset("select * From [Combined-Tax-Data] Where [LoanID]='" & sVar & "'")

0
 
jppintoConnect With a Mentor Commented:
Please note that I've added two ' (one after the [LoanID] an one between the "")
0
 
sshah254Commented:
Or

Set rs = CurrentDb.OpenRecordset("select * From [Combined-Tax-Data] Where [LoanID]=""" & sVar & """")

Single or double apostrophes - either one should work fine.

Ss
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
try this:

CurrentDb.OpenRecordset("select * From [Combined-Tax-Data] Where [LoanID]=" & Chr(34) &  sVar & Chr(34) )

mx
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Please keep in mind that contrary to popular belief ... single quotes are problematic in Access criteria expressions ... because in this case for example ... if sVar  happens to contain a single quote ... aka apostrophe ... like O'Brien etc ... your expression will fail.  Using double quotes - Chr(34) avoids this issue.

mx
0
 
maximyshkaAuthor Commented:
Thanks MX.  Sorry about points
0
All Courses

From novice to tech pro — start learning today.