S
Steve
I have the following SQL statement that I copied from a query to try to
put it into code.
I get a "Type Mismatch" error on the last line of the statement before
the DoCmdRunSQL runs. I <think> it's from the NewORNum, but can't be
sure. Patient.IDNUM is a long integer as is BNumber. I have tried
various combinations of single & double quotes around it, but cannot get
it to work. Anybody have an idea?
Dim NewORNum As String
Dim stSQLStmt As String
NewORNum = InputBox("What is the New Patient Number", "MMW Input")
stSQLStmt = "INSERT INTO Demog ( BNumber, [Last Name], [First Name],
Address, City, ST, Zip, Sex, DateofBirth, HPhone, " & _
"DefaultProvider, DefaultProviderInits, SSN,
DateCreated )" & _
"SELECT PATIENT.IDNUM, proper([LNAME]),
proper([FNAME]), proper([ADDRESS1]), " & _
"proper([CITY]), PATIENT.STATE, PATIENT.ZIPCODE,
PATIENT.SEX, " & _
"Left([DATEOFBIRTH],2) & " / " &
Mid([DATEOFBIRTH],4,2) & " / " & Right([DATEOFBIRTH],4), " & _
"PATIENT.HOME, IIf([DOCTOR]='SJR',4,1),
PATIENT.DOCTOR, " & _
"Left([SSN],3) & Mid([ssn],5,2) & Right([ssn],4),
PATIENT.DATEENTERED " & _
"FROM PATIENT " & _
"WHERE (((PATIENT.IDNUM)='" & NewORNum & "'));"
DoCmd.RunSQL stSQLStmt
Thanks
Steve
Reply to group only,
unless you can figure out my real address from the scrambling
put it into code.
I get a "Type Mismatch" error on the last line of the statement before
the DoCmdRunSQL runs. I <think> it's from the NewORNum, but can't be
sure. Patient.IDNUM is a long integer as is BNumber. I have tried
various combinations of single & double quotes around it, but cannot get
it to work. Anybody have an idea?
Dim NewORNum As String
Dim stSQLStmt As String
NewORNum = InputBox("What is the New Patient Number", "MMW Input")
stSQLStmt = "INSERT INTO Demog ( BNumber, [Last Name], [First Name],
Address, City, ST, Zip, Sex, DateofBirth, HPhone, " & _
"DefaultProvider, DefaultProviderInits, SSN,
DateCreated )" & _
"SELECT PATIENT.IDNUM, proper([LNAME]),
proper([FNAME]), proper([ADDRESS1]), " & _
"proper([CITY]), PATIENT.STATE, PATIENT.ZIPCODE,
PATIENT.SEX, " & _
"Left([DATEOFBIRTH],2) & " / " &
Mid([DATEOFBIRTH],4,2) & " / " & Right([DATEOFBIRTH],4), " & _
"PATIENT.HOME, IIf([DOCTOR]='SJR',4,1),
PATIENT.DOCTOR, " & _
"Left([SSN],3) & Mid([ssn],5,2) & Right([ssn],4),
PATIENT.DATEENTERED " & _
"FROM PATIENT " & _
"WHERE (((PATIENT.IDNUM)='" & NewORNum & "'));"
DoCmd.RunSQL stSQLStmt
Thanks
Steve
Reply to group only,
unless you can figure out my real address from the scrambling