Eso que comentas sucede por que tienes que separar lo que es 'fijo' de la SQL, te comento...
Código:
MiRecordSet.Open "select piezas, producto2, total from notas_detalle where notanumero =" & val(text1.Text) & "," & MiConexion, adOpenDynamic, adLockOptimistic
Al ser numerico va sin comillas, pero si es un string va entre comillas simples.