Sear for text containing "#" confused with wild card "#" (1 Viewer)

[DevTycoon]

I have records that include # as part of their text string. How can I query for those records and return results containing #?.


FacilityTable
Facility Unit #141A
Facility Unit #241A
Facility #2F

If I search for "unit #" on the search form control

I want the query to return 2 of the three facilityName records in the facility table

Facility Unit #141A
Facility Unit #241A





Query criteria for facilityName:


Like "*" & [Forms]![frmMain]![Navigationsubform].Form!DS!SubFormControlName & "*"

 

[vbaInet]

First thing is your referencing looks wrong. What does DS refer to? And how many levels deep are you going?

 

[CJ_London]

put the # in square brackets. See this link for more information

http://office.microsoft.com/en-gb/access-help/examples-of-wildcard-characters-HA102809757.aspx

 

[vbaInet]

... in addition you can use the Replace() function to do that for you or validate it in a function and pass the function to the query, but I'm still curious about your referencing. It looks like you're going two levels deep?

 

[DevTycoon]

First thing is your referencing looks wrong. What does DS refer to? And how many levels deep are you going?

Level 1 = frmMain --> holds the navigation subform
Level 2 = frmFacilityRecordSearch --> Form that holds only a subform control named "DS"
Level 3 = frmFacilityRecordSearchDS --> this is where the subform control is located

DS is what I named the subform control similar to subform2 in the example shown below. I have a navigation subform I am using wich is similar to subform1

Forms!Mainform!Subform1.Form!Subform2.Form!ControlName

http://access.mvps.org/access/forms/frm0031.htm

The form control reference seems to work fine for me . Do you recomend a different code other than what I have used?

I am just using this as an example. Once I get the query to work I will include this in the search form button's code. The search form is the frmFacilityRecordSearchDS and has unbound form controls that are only used for search criteria for this query. The search form button has a function for the select statement and a function for each form control to build the where clause of the SQL. All the functions are concatednated to make the SQL statment that should return the same results that this query question I am asking is hoping to solve. Perhaps I can use your function recomendation to pass that to the search button durring the routine of building a SQL statment for all the form controls.

Also can you elaborate on the replace function you have mentioned?

 

[DevTycoon]

put the # in square brackets. See this link for more information

http://office.microsoft.com/en-gb/access-help/examples-of-wildcard-characters-HA102809757.aspx

are you suggesting this?

Like "*[" & [Forms]![frmMain]![Navigationsubform].Form!DS!SubFormControlName & "]*"

According to the source you mention:
http://office.microsoft.com/en-gb/access-help/examples-of-wildcard-characters-HA102809757.aspx

[ ]Matches characters within the brackets.b[ae]ll finds ball and bell, but not bill.

My immediate reaction without testing it says that it will not work. If my criteria look like "*[Unit #1]*"... according to your source the statment says find any string that contains a "U" or "n" or "i" or " " or "t" or "#" or "1". so yes it will search for the number sign I suppose but it also seems it will have undesireable results.

 

[DevTycoon]

put the # in square brackets. See this link for more information

http://office.microsoft.com/en-gb/access-help/examples-of-wildcard-characters-HA102809757.aspx

Reading you post again ...How would I only insert the brackets for that specific character? The user may want to search for part of the facility name including that character(#) and some numbers after the pound sign.

Is there some way to detect that # and then convert it to a string using a function calling CStr(Char(35))

 

[GinaWhipp]

Elaborating on CJ_London's post, to search for wildcard character you enter...

[#]

So, you are not changing you Like statement, you have to *explain* to Access you are actually *looking* for the pound sign. You will have to use this technique for any search involving a wildcard character. You should probably put a little help icon with a pop-up to enter example searches...

 

[CJ_London]

Suggest you read the link per post #3

 

[vbaInet]

DS is what I named the subform control similar to subform2 in the example shown below. I have a navigation subform I am using wich is similar to subform1

Forms!Mainform!Subform1.Form!Subform2.Form!ControlName

You can drop the ".Form" reference. A subform control inherits the fields and controls of it's Source Object and you use the exclamation mark to reference the controls. You only need ".Form" if you're referring to specific properties of the form object (or Source Object) itself.

Forms!Mainform!Subform1!Subform2!ControlName

My main concern here is why? And also where you're applying the built up query? If Subform1 is linked to Subform2 wouldn't that be a matter of searching the parent (Subform1) and the child (Subform2) will follow suit? How do your users know the boundaries of the search, i.e. is it searching within the Main form, or Subform1 or even Subform2?

Also can you elaborate on the replace function you have mentioned?

There are a couple of characters to escape or pad so that your query doesn't break or at least returns the correct result. For example double quote or single quote (depending on which one you're using to enclose the search string, '**' or "**") can break your query.

You are to replace # with [#]. Like CJ said, have another read. Once you understand it, you would have to build a function to character for all the characters that could cause your query to break and those escape sequences used in pattern matching.

 

[DevTycoon]

My main concern here is why? And also where you're applying the built up query? If Subform1 is linked to Subform2 wouldn't that be a matter of searching the parent (Subform1) and the child (Subform2) will follow suit? How do your users know the boundaries of the search, i.e. is it searching within the Main form, or Subform1 or even Subform2?

The navigation sub form is unbound and so is frmfacilityrecordsearch. The design was recomended by accessallinone.com because I use the browse to comand to inside the DS form. The users can browse to facility details by clicking on facility name or find facility repair information when they search facility repair records (Sorry for giving you little information on the true structure. The actual project is quite massive compared to my discrete question. I try to keep the posts short and only include most relevant info. Perhaps this would have been good info to provide initially.)

The only form that is bound to a record source is the frmfacilityrecordsearchDS. That is also the form that has the search criterial in the header and a button to apply the filter (Allen Brown's search form method).

For now I will instruct users to bracket the pound sign if they need to search strings with that character.

I am going to work on the function that would replace the characters that would break the query. If I have any issues I will post in the VBA forum. For now I will mark this thread as solved.

I appreciate everyone's participation in my questions and helping me understand the database program better.

 

[vbaInet]

Ok. Just making sure you're on the right track with your design.

All the characters in the link will need to be bracketed too. GinaWhipp's got a good suggestion on creating a help icon.

You will have to use this technique for any search involving a wildcard character. You should probably put a little help icon with a pop-up to enter example searches...

Good luck!