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

Filter on string containg # in Access database through VB6

Hi Experts,
I am working on a desk top application with VB6 and Access database.
How can I  filter records containing "#"' in their string for Access? It does not return any records.My T SQL looks like:

If Trim(txtFltrDes) <> "" Then
     sSQL = sSQL & " AND Desc2 like  '" & Trim(txtFltrLoc) & "*'"
End If
         
     sSQL = sSQL & " Order By Address"
     Set rstdevs = db.OpenRecordset(sSQL, dbOpenDynaset)

It does not return any records if txtFltrDes text box has a string with # sign.
So I tried the following but no success!

If Trim(txtFltrDes) <> "" Then
          'sTempDesc = Replace(txtFltrDes.Text, "#", Chr$(35)) '
          i = InStr(1, txtFltrDes.Text, "#")
          If i > 0 Then
          sTempDesc = Left(txtFltrDes.Text, i - 1)
          sTempDesc = sTempDesc & Chr$(35) & Right(txtFltrDes.Text, Len(txtFltrDes.Text) - i)
         Else
           sTempDesc = txtFltrDes.Text
          End If
          'sSQL = sSQL & " AND Desc1 like  '" & Trim(txtFltrDes) & "*'"
           sSQL = sSQL & " AND Desc1 like  '" & sTempDesc & "*'"
     End If
     If Trim(txtFltrLoc) <> "" Then
          sSQL = sSQL & " AND Desc2 like  '" & Trim(txtFltrLoc) & "*'"
     End If
         
     sSQL = sSQL & " Order By Address"
     Set rstdevs = dbDevices.OpenRecordset(sSQL, dbOpenDynaset)
I read it somewhere that With # sign in a string, Access treats it like a numerical entry . Is there any way around it?
Any help will be greatly appreciated.
0
RekhaShah
Asked:
RekhaShah
  • 3
  • 2
1 Solution
 
vaidhyanathan1Commented:
You need to give # as  [#]. Refer this url for more info. http://office.microsoft.com/en-us/access/HA011715361033.aspx
0
 
RekhaShahAuthor Commented:
Sorry, this was posted twice.  This has been resolved
0
 
RekhaShahAuthor Commented:
CAn not use # as part of the string in Access95
0
 
vaidhyanathan1Commented:
You stated that it is a duplicate request, it will be good if you share the post id here. It will help viewers to know the details. Also you mentioned that we cannot use wild card character escape in access 95 and I verified the documentation, its not saying anything about wild card character restrictions.
0
 
RekhaShahAuthor Commented:
I have verified in my Dev environment.  I converted my Access95 database to later version(Access 2000). My logic worked fine. I plugged back the older database and it stopped working.
0
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.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

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