MS Access VBA data type mismatch in function -

MS Access VBA data type mismatch in function -

i have database containing locations of water wells , ton of properties associated wells. tables linked well_id (name of well), "short text" info type, can tell. within database there existing queries i'm trying info (i don't want mess tables in case create error , mess up).

i've created form user inputs utm coordinates easting , northing, search radius, clicks "search" button. upon clicking search, procedure creates recordset of [qryutm_nad83], calculates radial distance of each , if within specified search radius, stored in new [search_results] table using insert into.

now, 1 time identified meeting search criteria well_id stored, , passed function searches through recordset of different query [qryformation]. query has one-to-many relationship there record each geologic layer, each having same well_id (i.e. each has multiple layers these layers have same well_id). need concatenate these layers 1 string, pass them search function , add together [search_results] table. however, info type mismatch error in sql statement.

here's code have: (i've omitted parts of code maintain short all)

private sub searchbutton_click() dim wellid string, mysql string, newlitho string 'creating new recordsets [qryutm_nad83] table dim rs1 dao.recordset '[zone] user specified, helps me narrow search little set rs1 = currentdb.openrecordset("select [qryutm_nad83].* " & _ "from [qryutm_nad83] " & _ "where [zone] = " & frmzone, dbopendynaset) 'moving through recordset rs1.movefirst while not rs1.eof 'calculated radius, r , (omitted) if r < searchradius wellid = val(rs1.fields("well_id").value) newlitho = lithotransform(wellid) mysql = "insert [search_results] " & _ "([well_id], [easting], [northing], [radius], [lithology]) " & _ "values (" & wellid & ", " & x & ", " & y & ", " & r & ", " & newlitho & ")" currentdb.execute mysql rs1.movenext end if loop end sub

the function: (error occurs in "set rs2..." - info type mismatch)

public function lithotransform(currentid string) string 'currentid identified beingness within search radius dim rs2 dao.recordset dim mysql2 string 'sql statement , new recordset of looking @ in search debug.print currentid mysql2 = "select [qryformation].* " & _ "from [qryformation] " & _ "where [well_id] = " & currentid set rs2 = currentdb.openrecordset(mysql2, dbopendynaset) 'move through recordset rs2 , concatenating new string 'bunch of code here 'close recordset set 'nothing' end function

since well_id text type, include quotes around value of currentid when include in where clause. quickest prepare ...

class="lang-vb prettyprint-override">mysql2 = "select [qryformation].* " & _ "from [qryformation] " & _ "where [well_id] = '" & currentid & "'"

however, switch parameter query instead , thereby avoid issues quotes. here untested illustration ...

class="lang-vb prettyprint-override">dim db dao.database dim qdf dao.querydef dim rs2 dao.recordset dim mysql2 string mysql2 = "select [qryformation].* " & _ "from [qryformation] " & _ "where [well_id] = [which_id]" set db = currentdb set qdf = db.createquerydef(vbnullstring, mysql2) qdf.parameters("which_id") = currentid set rs2 = qdf.openrecordset

use access help scheme check functions, syntax, etc. in code in case made errors. since you're new access, advantage comfortable help system.

ms-access access-vba