vba - SqlPassThough error? -

vba - SqlPassThough error? -

i have vbscript didn’t write. trying pull cellphone number , won’t pull it. every time seek alter sql query sends me error telling me the

“the specified filed ‘sqlpassthough.workphone’ refer more 1 table listed in clause of sql statement.”

workphone isn’t selected? little confused.

attribute vb_name = "module1" global const filedirectory string = "c:\users\0-\documents" global const filename string = "ortho.txt" global const filedirandname string = filedirectory & "\" & filename global runcount integer alternative compare database function main() on error goto main_err dim dbs database set dbs = currentdb 'dim outmail outlook.mailitem docmd.setwarnings (false) 'only run if not weekend if isweekend = true docmd.quit end if 'create query def addquerydef 'transfer file docmd.transfertext acexportdelim, "talksoft export specification", "exportquery", filedirandname, false, "" 'check create date on file create sure today's file created if getfilestatus() = true filecreatestatus = "success" else filecreatestatus = "failure" end if 'update table status dtnow = now() filereccount = gettablecounts() strsql = "insert transaction (transdatetime, transstatus, transrecordcount) values ( '" & dtnow & "','" & filecreatestatus & "'," & filereccount & ")" docmd.runsql strsql 'if file failed create, send e-mail notification 'if filecreatestatus = "failure" ' strmsg = "the talksoft file today did not finish successfully. please repair." & vbcrlf & vblf ' strmsg = strmsg & "" ' email = ";" ' docmd.sendobject , , acformatrtf, email, , , "talksoft file creation failure", strmsg, false ' me.createitem (outlook.olitemtype.olmailitem) ' set myolapp = createobject("outlook.application") ' set outmail = myolapp.createitem(olmailitem) ' outmail ' .subject = "talksoft file creation failure" ' .to = email ' .body = strmsg ' .send ' '.display ' end 'sendkeys "{left}", true 'sendkeys "{enter}", true 'end if 'cleanup dbs.querydefs("sqlpassthrough").sql = "select" set dbs = nil runcount = runcount + 1 main_exit: docmd.setwarnings (true) exit function main_err: msgbox err.description resume main_exit end function function getfilestatus() boolean on error goto getfilestatus_err 'get file create date strfiledate = getfilecreatedate((filedirandname)) 'make sure string returned can converted date if isdate(strfiledate) dtfiledate = cdate(strfiledate) 'check create date on file , compare today's date if dtfiledate <> date getfilestatus = false else getfilestatus = true end if else getfilestatus = false exit function end if getfilestatus_exit: exit function getfilestatus_err: getfilestatus = false resume getfilestatus_exit end function function gettablecounts() on error goto gettablecounts_err dim dbs database, tbldef tabledef, doc document dim inti integer dim strtabname string dim rsttablecount dao.recordset set dbs = currentdb dbs set rsttablecount = dbs.openrecordset("select count(*) recordcount exportquery") rcdcount = rsttablecount.fields(0).value end gettablecounts = rcdcount set dbs = nil set rsttablecount = nil gettablecounts_exit: exit function gettablecounts_err: gettablecounts = 0 resume gettablecounts_exit end function function getfilecreatedate(strfilename string) string on error goto getfilecreatedate_err dim fs, f, s set fs = createobject("scripting.filesystemobject") set f = fs.getfile(strfilename) s = format(f.datecreated, "mm/dd/yyyy") getfilecreatedate = s getfilecreatedate_exit: exit function getfilecreatedate_err: getfilecreatedate = "" if err.number <> 53 msgbox error$ ' don't show error if "file not found" resume getfilecreatedate_exit end function public function isweekend() boolean dim dttoday date dttoday = date intweekday = weekday(dttoday) if intweekday = 1 or intweekday = 7 isweekend = true else isweekend = false end if end function function addquerydef() on error goto addquerydef_err dim dbs database, tbldef tabledef set dbs = currentdb datefrom = year(date) & "-" & month(date) & "-" & day(date) & " 00:00:00" '2009-12-23 00:00:00 newdate = format(dateadd("d", 14, date), "yyyy/mm/dd") if len(month(newdate)) = 1 newmonth = "0" & month(newdate) else newmonth = month(newdate) end if if len(day(newdate)) = 1 newday = "0" & day(newdate) else newday = day(newdate) end if dateto = year(newdate) & "-" & newmonth & "-" & newday & " 00:00:01" strsql = "select book.name, apptdef.appttype, rptappt.patient_name, rptappt.apptdate, rptappt.apptstatus, rptappt.appttime, rptappt.id, person.altphone, person.cellphone, locreg.abbrevname" strsql = strsql & " ((ml.rptappt rptappt inner bring together ml.book book on rptappt.bookid=book.id) inner bring together ml.apptdef apptdef on rptappt.appttype=apptdef.id) inner bring together ml.person person on rptappt.pid=person.pid inner bring together ml.locreg locreg on book.locid=locreg.locid" strsql = strsql & " (book.name='henry md' or book.name='khoury md' or book.name='livingston md' or book.name='rosquete md' or book.name='van orden md')and rptappt.apptstatus=0 , not (rptappt.appttype in ('1480584143000910','1461059906350610','1562520476700690','1585320602356120','1631620534009750') or rptappt.patient_name='eicu,' or rptappt.patient_name='eicu, night' or rptappt.patient_name='hold' or rptappt.patient_name='hosp, coverage' or rptappt.patient_name='medical, education' or rptappt.patient_name='meeting' or rptappt.patient_name='on, call' or rptappt.patient_name='out of office' or rptappt.patient_name='sleep, study (con)' or rptappt.patient_name='sleep, study (int)' or rptappt.patient_name='v1' or rptappt.patient_name='v2' or rptappt.patient_name='vacation') , rptappt.apptdate >= timestamp '" & datefrom & "' , rptappt.apptdate < timestamp '" & dateto & "'" strsql = strsql & " order book.name, rptappt.apptdate" dbs.querydefs("sqlpassthrough").sql = strsql set dbs = nil addquerydef_exit: exit function addquerydef_err: resume addquerydef_exit end function

vba ms-access-2007