CONCAT issue A2K

Topic

New Reply

Hans.

I have used the concat function that you renamed to concatenate from one of the precious forum topics.

It works great, until I try to get a little fancy.

In query AA_works I query on the query qry_show_to_line

I use qry_show_to_line to concatenate company name and web site. (the normal concatenate using & between to fields)

To strip all the goofy text out of the hypertext field I use.. another function I am sure I got from here
xg_GetWordsBetween(“The Lazy Fox”, “The”, “Fox”) (the module name is basStrfn if that helps)

So I end up with a field named xmanudata and it has data like this ‘http://www.website.com companyname’

Then in the AA_works query I use the CONCATENATE function on xmanudata.. and get an error…

Runtime error 3464

Datatype mismatch in criteria expresion

and then the debugger takes me to this line in your concatenat functionat.

Set rst = dbs.OpenRecordset(strSQL)

I post the db here. I renamed AA_works to AA_xNotworking so that you

So AA_works still works when Concatenate in only on the copanyname field.

and AA_xNotworking fails and you can see error….

Any thoughts…

Thanks.

Jason

Reply | Quote

Replies

The xmanudata field in qry_show_to_line returns #Error for several records, and this causes the error in the Concatenate function.

The error occurs when tbl_contact_1!website is Null (blank, empty). The xg_GetWordsBetween function expects a string as first argument, not a Null.
To work around this, change the definition of xg_GetWordsBetween to the following:

Function xg_GetWordsBetween(sMain As Variant, s1 As String, s2 As String) As String

Reply | Quote

Hans.

Thank you!! As always you are on it. It works. Thanks again.

Jason

Reply | Quote