Type Mismatch Error

[calihiker]

Hi, I am getting a "type/mismatch" error on this line of code that is supposed o assign the select statement to the rowsource of my list box...

Me.lstContactNames.RowSource = " SELECT [All Contacts].[First Name (Personal Name)], [All Contacts].[Last Name (Family Name)] FROM [All Contacts] WHERE ((([All Contacts].[First Name (Personal Name)]) Like Forms![Find Contact]!txtFName.Text & " * &quot); "

Any suggestions?

Thanks

 

[Nelviticus]

You're allocating a string to the Rowsource property. As far as VBA is concerned, the string you're assigning starts with:

	" SELECT [All ...

and ends with the second quotation mark here:

	... txtFName.Text & "

You've then got some extra stuff on the end like this:

	* "));"

which is giving the error.

As a human I know that the quote marks around the * are supposed to be part of the string. In order to tell this to VBA and stop it from thinking that the second " marks the end of the string, you need to use double quote marks like this:

Me.lstContactNames.RowSource = " SELECT [All Contacts].[First Name (Personal Name)], [All Contacts].[Last Name (Family Name)] FROM [All Contacts] WHERE ((([All Contacts].[First Name (Personal Name)]) Like Forms![Find Contact]!txtFName.Text & "" * "")); "

Hope that helps.

N.

 

[RoyVidar]

Hi!

If you've alredy solved this, please disregard this post.

As Nelviticus says, you're allocating a string to a rowsource property. I'm afraid you must work even more on the qualifyers, quotas etc. I don't even get Nelviticus solution to the * to work. The coding below, however, shold work.

Me.lstContactNames.RowSource = "SELECT [All Contacts].[First Name (Personal Name)], [All Contacts].[Last Name (Family Name)] FROM [All Contacts] WHERE ((([All Contacts].[First Name (Personal Name)]) Like '" & Forms![Find Contact]!txtFName & chr(42) & "'"

Explanation:
When building a string, you need to ensure that the VALUE from the form gets into the string, not the reference, so in your version you get the reference (Like Forms![Find Contact]!txtFName) while in this version you get whatever value resides in the control on the form. Then you need text qualifiers on both sides, which is apostrophe ('), and then the star. I've never been able to get any such characters into a string using quotes, I've alvays had to use the chr-function to with the correct ASCII value to put such into strings. Chr(42) = '*'.

Enjoy, Roy-Vidar

 

[RoyVidar]

Oups - a litle quick there. Since I'm not using parenthesis myself, I removed the ones on the end, but not the ones in the midle - this should work;-)

Me.lstContactNames.RowSource = "SELECT [All Contacts].[First Name (Personal Name)], [All Contacts].[Last Name (Family Name)] FROM [All Contacts] WHERE [All Contacts].[First Name (Personal Name)] Like '" & Forms![Find Contact]!txtFName & chr(42) & "'"

Roy-Vidar