Hi! Could someone please tell me 1

[snowyej]

Hi!

Could someone please tell me what I'm missing in this piece of code:

DoCmd.OpenForm "frmAppsTesting", , , "[AppName] like '*' & [txtSearch] & '*'"

Basically, I have my search form. It has an unbound text box (txtSearch) and a button. The user puts in their search word in the text box and clicks the button and it pulls up the frmAppsTesting form. It should filter the form to show the records where their search word is in the AppName field.

When I put in a test word and click the button, a window comes up that says "txtSearch" and has a blank field. If I type my search word in again it will then pull up the form with the filtered records. What am I doing that causes that little window to pop up??

I'm sure the answer is really simple. Any help is greatly appreciated.

Thanks much!

Elizabeth

 

[outpatient]

I always get this problem when I refer to the control incorrectly. I'd try using Me.textsearch to reference the txt box. Its just a guess though, I normally have to play around for a while to fix that type of thing.-Sam Greene

 

[snowyej]

Nope, that still produced the same results. Tried me.txtsearch and me!txtSearch and both did the same thing.

Thanks for the fast response though!

Elizabeth

 

[outpatient]

Perhaps it doesn't need to be in quotes??

I had this problem this the following code which is now fixed. Note the single quotes after [Tech Name]. That may have something to do with it.

strEmail = DLookup("email", "Technician", "[Tech Name]= '" & [Forms]![Customers]![Problem Entry]![Assigned To] & "'&quot
MsgBox (strEmail)

DoCmd.SendObject acReport, "unresolved trouble calls", "SnapshotFormat(*.snp)", strEmail, "", "", "trouble calls", "", False, ""
End If

 

[snowyej]

That code looks really similar to a piece of code I wrote for users to look up their issues that they entered into the table. That was easier because they select their name from a drop down list and it exactly matches what is in the field.

My current problem is that the user types a text search into a field. This searches a field in another form to see if it contains the search word. For example, the user could type Access and it will pull up records where the application looks like:

MS Access 97
Microsoft Access XP
BLah blah blah - In house Access application

Etc...

Make sense? So I need the wild card characters in the like statement. I think that's where the quotes are throwing me for a loop.

My initial statement looked like:

<b>
DoCmd.OpenForm &quot;frmAppsTesting&quot;, , , &quot;[AppName] like '*' & [txtSearch] & '*'&quot;
</b>

I tried taking the quotes out from around the asterick's (*) and that didn't do anything. Tried the following after [AppName];

like '&quot; '*' & txtSearch & '*' &quot;'&quot;

and that didn't work...

I've tried every combination of double quotes and single quotes I can think of and nothing seems to work right.

ARG!!

 

[JohnLowell]

Try this:

&quot;[AppName] Like &quot; & CHR(34) & &quot;*&quot; & txtSearch & &quot;*&quot; & CHR(34)

 

[KyleS]

Hi,
If I remember correctly (and I rarely do ;-)) SQL in VBA isn't too fond of the Asterisk, so give this a shot:

DoCmd.OpenForm &quot;frmAppsTesting&quot;, , , &quot;[AppName] LIKE '%&quot; & [txtSearch] & &quot;%[color]'&quot;

Hope this helps Kyle :

 

[snowyej]

THAT WORKED! Yay! Thanks so much!

What does the chr(34) do?

(I'm still kind of a newbie at the coding side of Access).

Thanks again,
Elizabeth

 

[KyleS]

Let's try that again:

DoCmd.OpenForm &quot;frmAppsTesting&quot;, , , &quot;[AppName] LIKE '%&quot; & [txtSearch] & &quot;%'&quot;
Kyle :

 

[snowyej]

Didn't like that. My filter came up with no results. John Lowell's suggestion worked great though.

Thanks!
Elizabeth

 

[KyleS]

Chr(34) = &quot; Kyle :

 

[riny]

Chr(34) is the quotation character.
So you were on the right track !

 

[outpatient]

Kyle,
About the % wildcard, I think thats for Oracle, access doesn't seem to mind. Sam Greene
anyone in need of a rock induced headache? if so

http://www.mp3.com/outpatient

 

[KyleS]

See, there I go trying to think again. I should know to stop, one of these days I'm gonna hurt myself...;-)

Thanks Sam, Kyle :

 

[snowyej]

Thanks for everyone's help! I really appreciate it. My crazy database is coming right along. My co-workers better appreciate me!

Elizabeth

 

[snowyej]

Oh, one more thing. Does anyone know where I can find a list of the &quot;chr()&quot; operators (or whatever..)? And, is there a difference between chr$() and chr()? I have used chr$(10) in my database and noticed that it has a $ in it, and the one you guys gave me does not.

Thanks!
Elizabeth