Data type mismatch text to expression search Tal 12/8/2008 9:07:01 PM Hi all, I am trying to search a query where the field is actual an expression comprised of a subquery. I am getting a type mismatch error. Here is the code: strWhere = "([expClientMainName] LIKE "" * " & Me.txtClientSearch & "" * ")" I'm not even sure if this is possible. Many thanks for your help.

Re: Data type mismatch text to expression search "Jeff Boyce" <nonsense[ at ]nonsense.com> 12/8/2008 10:52:05 PM Have you added a breakpoint in the code so you can inspect the value in "strWhere"? Regards Jeff Boyce Microsoft Office/Access MVP "Tal" <Tal[ at ]discussions.microsoft.com> wrote in message news:75BD9BAD-2D05-402D-83C9-53D442AAAB78[ at ]microsoft.com... [Quoted Text] > Hi all, > > I am trying to search a query where the field is actual an expression > comprised of a subquery. > I am getting a type mismatch error. Here is the code: > > strWhere = "([expClientMainName] LIKE "" * " & Me.txtClientSearch & "" * > ")" > > I'm not even sure if this is possible. > Many thanks for your help. > >

Re: Data type mismatch text to expression search "Mike Painter" <mddotpainter[ at ]sbcglobal.net> 12/8/2008 11:14:54 PM Tal wrote: [Quoted Text] > Hi all, > > I am trying to search a query where the field is actual an expression > comprised of a subquery. > I am getting a type mismatch error. Here is the code: > > strWhere = "([expClientMainName] LIKE "" * " & Me.txtClientSearch & > "" * ")" > strWhere = "([expClientMainName] LIKE "" * " & Me.txtClientSearch & "* "")" Should do it. (I usually use single quote double quote) You can always run the string through a msgbox and see where the quotes fall. >

Re: Data type mismatch text to expression search "Douglas J. Steele" <NOSPAM_djsteele[ at ]NOSPAM_gmail.com> 12/9/2008 12:37:50 AM "Mike Painter" <mddotpainter[ at ]sbcglobal.net> wrote in message news:NTh%k.9080$x%.4363[ at ]nlpi070.nbdc.sbc.com... [Quoted Text] > Tal wrote: >> Hi all, >> >> I am trying to search a query where the field is actual an expression >> comprised of a subquery. >> I am getting a type mismatch error. Here is the code: >> >> strWhere = "([expClientMainName] LIKE "" * " & Me.txtClientSearch & >> "" * ")" >> > strWhere = "([expClientMainName] LIKE "" * " & Me.txtClientSearch & "* > "")" > > Should do it. (I usually use single quote double quote) > You can always run the string through a msgbox and see where the quotes > fall. Of course, the spaces around the asterisks shouldn't be there. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please)

Re: Data type mismatch text to expression search Tal 12/9/2008 2:20:00 PM Hi Jeff, I'm not sure I know what a breakpoint is. Can you please be more explicit. Many thanks. Tal "Jeff Boyce" wrote: [Quoted Text] > Have you added a breakpoint in the code so you can inspect the value in > "strWhere"? > > Regards > > Jeff Boyce > Microsoft Office/Access MVP > > "Tal" <Tal[ at ]discussions.microsoft.com> wrote in message > news:75BD9BAD-2D05-402D-83C9-53D442AAAB78[ at ]microsoft.com... > > Hi all, > > > > I am trying to search a query where the field is actual an expression > > comprised of a subquery. > > I am getting a type mismatch error. Here is the code: > > > > strWhere = "([expClientMainName] LIKE "" * " & Me.txtClientSearch & "" * > > ")" > > > > I'm not even sure if this is possible. > > Many thanks for your help. > > > > > > >

Re: Data type mismatch text to expression search Tal 12/9/2008 2:23:00 PM Hi Douglas, I keep removing the spaces and it keeps putting them back in automatically, with the type mismatch error. I am very much a newbie, so very explicit advise is very very useful. Thanks, Tal "Douglas J. Steele" wrote: [Quoted Text] > "Mike Painter" <mddotpainter[ at ]sbcglobal.net> wrote in message > news:NTh%k.9080$x%.4363[ at ]nlpi070.nbdc.sbc.com... > > Tal wrote: > >> Hi all, > >> > >> I am trying to search a query where the field is actual an expression > >> comprised of a subquery. > >> I am getting a type mismatch error. Here is the code: > >> > >> strWhere = "([expClientMainName] LIKE "" * " & Me.txtClientSearch & > >> "" * ")" > >> > > strWhere = "([expClientMainName] LIKE "" * " & Me.txtClientSearch & "* > > "")" > > > > Should do it. (I usually use single quote double quote) > > You can always run the string through a msgbox and see where the quotes > > fall. > > Of course, the spaces around the asterisks shouldn't be there. > > -- > Doug Steele, Microsoft Access MVP > http://I.Am/DougSteele > (no private e-mails, please) > > > >

Re: Data type mismatch text to expression search "Jeff Boyce" <nonsense[ at ]nonsense.com> 12/9/2008 3:37:26 PM Tal You'll need to familiarize yourself with Visual Basic for Applications if you are going to use a breakpoint. The topic could probably fill 1-2 hours of classroom instruction, more time than I can devote in this (volunteer) newsgroup. I'd suggest opening the VBA window from Access and using HELP to learn more. Good luck! Regards Jeff Boyce Microsoft Office/Access MVP "Tal" <Tal[ at ]discussions.microsoft.com> wrote in message news:7DB27354-6909-402C-B50F-88E858411D3A[ at ]microsoft.com... [Quoted Text] > Hi Jeff, > > I'm not sure I know what a breakpoint is. Can you please be more > explicit. > Many thanks. > Tal > > "Jeff Boyce" wrote: > >> Have you added a breakpoint in the code so you can inspect the value in >> "strWhere"? >> >> Regards >> >> Jeff Boyce >> Microsoft Office/Access MVP >> >> "Tal" <Tal[ at ]discussions.microsoft.com> wrote in message >> news:75BD9BAD-2D05-402D-83C9-53D442AAAB78[ at ]microsoft.com... >> > Hi all, >> > >> > I am trying to search a query where the field is actual an expression >> > comprised of a subquery. >> > I am getting a type mismatch error. Here is the code: >> > >> > strWhere = "([expClientMainName] LIKE "" * " & Me.txtClientSearch & "" >> > * >> > ")" >> > >> > I'm not even sure if this is possible. >> > Many thanks for your help. >> > >> > >> >> >>

Re: Data type mismatch text to expression search Tal 12/9/2008 6:34:09 PM For anyone still following this thread. I changed the String to a Variant and it worked. Does this make sense? Tal "Tal" wrote: [Quoted Text] > Hi Douglas, > > I keep removing the spaces and it keeps putting them back in automatically, > with the type mismatch error. > I am very much a newbie, so very explicit advise is very very useful. > > Thanks, > Tal > > "Douglas J. Steele" wrote: > > > "Mike Painter" <mddotpainter[ at ]sbcglobal.net> wrote in message > > news:NTh%k.9080$x%.4363[ at ]nlpi070.nbdc.sbc.com... > > > Tal wrote: > > >> Hi all, > > >> > > >> I am trying to search a query where the field is actual an expression > > >> comprised of a subquery. > > >> I am getting a type mismatch error. Here is the code: > > >> > > >> strWhere = "([expClientMainName] LIKE "" * " & Me.txtClientSearch & > > >> "" * ")" > > >> > > > strWhere = "([expClientMainName] LIKE "" * " & Me.txtClientSearch & "* > > > "")" > > > > > > Should do it. (I usually use single quote double quote) > > > You can always run the string through a msgbox and see where the quotes > > > fall. > > > > Of course, the spaces around the asterisks shouldn't be there. > > > > -- > > Doug Steele, Microsoft Access MVP > > http://I.Am/DougSteele > > (no private e-mails, please) > > > > > > > >

Re: Data type mismatch text to expression search "Jeff Boyce" <nonsense[ at ]nonsense.com> 12/9/2008 7:15:20 PM Tal Is there a chance your underlying table contains Nulls in that field? Regards Jeff Boyce Microsoft Office/Access MVP "Tal" <Tal[ at ]discussions.microsoft.com> wrote in message news:0C0104F5-B934-4A1F-AE9C-F9AE2150D338[ at ]microsoft.com... [Quoted Text] > For anyone still following this thread. > I changed the String to a Variant and it worked. > Does this make sense? > Tal > > "Tal" wrote: > >> Hi Douglas, >> >> I keep removing the spaces and it keeps putting them back in >> automatically, >> with the type mismatch error. >> I am very much a newbie, so very explicit advise is very very useful. >> >> Thanks, >> Tal >> >> "Douglas J. Steele" wrote: >> >> > "Mike Painter" <mddotpainter[ at ]sbcglobal.net> wrote in message >> > news:NTh%k.9080$x%.4363[ at ]nlpi070.nbdc.sbc.com... >> > > Tal wrote: >> > >> Hi all, >> > >> >> > >> I am trying to search a query where the field is actual an >> > >> expression >> > >> comprised of a subquery. >> > >> I am getting a type mismatch error. Here is the code: >> > >> >> > >> strWhere = "([expClientMainName] LIKE "" * " & Me.txtClientSearch & >> > >> "" * ")" >> > >> >> > > strWhere = "([expClientMainName] LIKE "" * " & Me.txtClientSearch & >> > > "* >> > > "")" >> > > >> > > Should do it. (I usually use single quote double quote) >> > > You can always run the string through a msgbox and see where the >> > > quotes >> > > fall. >> > >> > Of course, the spaces around the asterisks shouldn't be there. >> > >> > -- >> > Doug Steele, Microsoft Access MVP >> > http://I.Am/DougSteele >> > (no private e-mails, please) >> > >> > >> > >> >

Re: Data type mismatch text to expression search Tal 12/9/2008 7:40:01 PM Yup! Would that affect whether it ought to be a string or a variant? I will include an If Null option, but I didn't think that could affect a type mismatch error? Interesting. "Jeff Boyce" wrote: [Quoted Text] > Tal > > Is there a chance your underlying table contains Nulls in that field? > > Regards > > Jeff Boyce > Microsoft Office/Access MVP > > "Tal" <Tal[ at ]discussions.microsoft.com> wrote in message > news:0C0104F5-B934-4A1F-AE9C-F9AE2150D338[ at ]microsoft.com... > > For anyone still following this thread. > > I changed the String to a Variant and it worked. > > Does this make sense? > > Tal > > > > "Tal" wrote: > > > >> Hi Douglas, > >> > >> I keep removing the spaces and it keeps putting them back in > >> automatically, > >> with the type mismatch error. > >> I am very much a newbie, so very explicit advise is very very useful. > >> > >> Thanks, > >> Tal > >> > >> "Douglas J. Steele" wrote: > >> > >> > "Mike Painter" <mddotpainter[ at ]sbcglobal.net> wrote in message > >> > news:NTh%k.9080$x%.4363[ at ]nlpi070.nbdc.sbc.com... > >> > > Tal wrote: > >> > >> Hi all, > >> > >> > >> > >> I am trying to search a query where the field is actual an > >> > >> expression > >> > >> comprised of a subquery. > >> > >> I am getting a type mismatch error. Here is the code: > >> > >> > >> > >> strWhere = "([expClientMainName] LIKE "" * " & Me.txtClientSearch & > >> > >> "" * ")" > >> > >> > >> > > strWhere = "([expClientMainName] LIKE "" * " & Me.txtClientSearch & > >> > > "* > >> > > "")" > >> > > > >> > > Should do it. (I usually use single quote double quote) > >> > > You can always run the string through a msgbox and see where the > >> > > quotes > >> > > fall. > >> > > >> > Of course, the spaces around the asterisks shouldn't be there. > >> > > >> > -- > >> > Doug Steele, Microsoft Access MVP > >> > http://I.Am/DougSteele > >> > (no private e-mails, please) > >> > > >> > > >> > > >> > > > >

Re: Data type mismatch text to expression search "Jeff Boyce" <nonsense[ at ]nonsense.com> 12/11/2008 12:24:06 AM If I recall correctly, "strings" don't do Nulls. Variants handle nulls, though... Regards Jeff Boyce Microsoft Office/Access MVP "Tal" <Tal[ at ]discussions.microsoft.com> wrote in message news:22EA80B5-D97B-4A4A-9A76-30924910C9EA[ at ]microsoft.com... [Quoted Text] > Yup! Would that affect whether it ought to be a string or a variant? > > I will include an If Null option, but I didn't think that could affect a > type mismatch error? > > Interesting. > > "Jeff Boyce" wrote: > >> Tal >> >> Is there a chance your underlying table contains Nulls in that field? >> >> Regards >> >> Jeff Boyce >> Microsoft Office/Access MVP >> >> "Tal" <Tal[ at ]discussions.microsoft.com> wrote in message >> news:0C0104F5-B934-4A1F-AE9C-F9AE2150D338[ at ]microsoft.com... >> > For anyone still following this thread. >> > I changed the String to a Variant and it worked. >> > Does this make sense? >> > Tal >> > >> > "Tal" wrote: >> > >> >> Hi Douglas, >> >> >> >> I keep removing the spaces and it keeps putting them back in >> >> automatically, >> >> with the type mismatch error. >> >> I am very much a newbie, so very explicit advise is very very useful. >> >> >> >> Thanks, >> >> Tal >> >> >> >> "Douglas J. Steele" wrote: >> >> >> >> > "Mike Painter" <mddotpainter[ at ]sbcglobal.net> wrote in message >> >> > news:NTh%k.9080$x%.4363[ at ]nlpi070.nbdc.sbc.com... >> >> > > Tal wrote: >> >> > >> Hi all, >> >> > >> >> >> > >> I am trying to search a query where the field is actual an >> >> > >> expression >> >> > >> comprised of a subquery. >> >> > >> I am getting a type mismatch error. Here is the code: >> >> > >> >> >> > >> strWhere = "([expClientMainName] LIKE "" * " & Me.txtClientSearch >> >> > >> & >> >> > >> "" * ")" >> >> > >> >> >> > > strWhere = "([expClientMainName] LIKE "" * " & Me.txtClientSearch >> >> > > & >> >> > > "* >> >> > > "")" >> >> > > >> >> > > Should do it. (I usually use single quote double quote) >> >> > > You can always run the string through a msgbox and see where the >> >> > > quotes >> >> > > fall. >> >> > >> >> > Of course, the spaces around the asterisks shouldn't be there. >> >> > >> >> > -- >> >> > Doug Steele, Microsoft Access MVP >> >> > http://I.Am/DougSteele >> >> > (no private e-mails, please) >> >> > >> >> > >> >> > >> >> > >> >> >>