Type Mismatch

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
 
J

John Vinson

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?
Click to expand...

If IDNUM is a Long Integer field then your query should have *no
quotes at all*. Try:


"WHERE (((PATIENT.IDNUM)=" & NewORNum & "));"

Text field criteria must be delimited by either " or '; date field
criteria must be delimited by #; number field criteria do not use any
delimiter.

John W. Vinson[MVP]
 
S

Steve

I tried that, too, and it doesn't work. NewORNum is defined as a string
(coming from an input box), even though it is only digits. Does that
make a difference? I don't think it so, but I'll consider anything!

Steve
 
J

John Vinson

I tried that, too, and it doesn't work. NewORNum is defined as a string
(coming from an input box), even though it is only digits. Does that
make a difference? I don't think it so, but I'll consider anything!
Click to expand...

Looking again, it may be the date fields: they must be delimited with
# characters, in mm/dd/yyyy format (or an unambigous format). Try

"SELECT PATIENT.IDNUM, proper([LNAME]),
proper([FNAME]), proper([ADDRESS1]), " & _
"proper([CITY]), PATIENT.STATE, PATIENT.ZIPCODE,
PATIENT.SEX, " & _
"Format(CDate([DateOfBirth], ""\#mm\/dd\/yyyy\#"")," & _
"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 & "'));"

John W. Vinson[MVP]
 
S

Steve

Correct me if I'm wrong, but when I get the error, and click on "debug"
the entire statement setting the stSQLStmt string is in yellow, but
there is a little yellow arrow on the last line. That's why I've been
thinking it was the NewORnum statement.
I'll try your date suggestion, but, as I said, I copied this from the
SQL statement from the query that works. all I changed was the NewORNum
part because the query's "[Input a number]" phrase needed to be better
controlled via code. (I try to go to the record after I import it).
Just running the query doesn't allow me to find the new record, at least
not at my programming skill level!! <g> What I was trying to do was use
an InputBox to capture the NewORNum data, import the record, then do a
FindRecord. Seemed reasonable to me until I hit this wall! I'll get
back you you after I try it...

Steve

John said:
I tried that, too, and it doesn't work. NewORNum is defined as a string
(coming from an input box), even though it is only digits. Does that
make a difference? I don't think it so, but I'll consider anything!
Click to expand...


Looking again, it may be the date fields: they must be delimited with
# characters, in mm/dd/yyyy format (or an unambigous format). Try

"SELECT PATIENT.IDNUM, proper([LNAME]),
proper([FNAME]), proper([ADDRESS1]), " & _
"proper([CITY]), PATIENT.STATE, PATIENT.ZIPCODE,
PATIENT.SEX, " & _
"Format(CDate([DateOfBirth], ""\#mm\/dd\/yyyy\#"")," & _
"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 & "'));"

John W. Vinson[MVP]
Click to expand...
 
J

John Vinson

Correct me if I'm wrong, but when I get the error, and click on "debug"
the entire statement setting the stSQLStmt string is in yellow, but
there is a little yellow arrow on the last line. That's why I've been
thinking it was the NewORnum statement.
I'll try your date suggestion, but, as I said, I copied this from the
SQL statement from the query that works. all I changed was the NewORNum
part because the query's "[Input a number]" phrase needed to be better
controlled via code. (I try to go to the record after I import it).
Just running the query doesn't allow me to find the new record, at least
not at my programming skill level!! <g> What I was trying to do was use
an InputBox to capture the NewORNum data, import the record, then do a
FindRecord. Seemed reasonable to me until I hit this wall! I'll get
back you you after I try it...
Click to expand...

aha!

If you're trying to retrieve a record which has not yet been saved to
the table - i.e. if it's still on the Form - then this query will in
fact not retrieve anything.


John W. Vinson[MVP]
 
S

Steve

No the data is coming from another table that I'm trying to import into
my "working table" on the form that is open. As I said, if my form
button code states

DoCmd.OpenQuery "PATIENT2Demog"

and nothing else, the data imports fine, but I can't capture the query's
input (inside the square brackets [] when constructing the query) so I
would know which new record to go to afterwards. My problem is trying
to copy the query code into the form code and still have it run correctly.


ok, so now I took out all the date manipulation and it works fine!!!
Why I had to mandipulate the date string in the query by not in the SQL
staement, I'm not sure, but it is working. It was the little yellow
arrow within the debugger that was pointing me in the wrong direction to
the wrong line. I guess it would be nice (if you have any input to the
programmers) to see if there is a way to identify exactly which phrase
was causing the error, not just a general "Type Mismatch".

Thanks for you help and concern

Steve

John said:
Correct me if I'm wrong, but when I get the error, and click on "debug"
the entire statement setting the stSQLStmt string is in yellow, but
there is a little yellow arrow on the last line. That's why I've been
thinking it was the NewORnum statement.
I'll try your date suggestion, but, as I said, I copied this from the
SQL statement from the query that works. all I changed was the NewORNum
part because the query's "[Input a number]" phrase needed to be better
controlled via code. (I try to go to the record after I import it).
Just running the query doesn't allow me to find the new record, at least
not at my programming skill level!! <g> What I was trying to do was use
an InputBox to capture the NewORNum data, import the record, then do a
FindRecord. Seemed reasonable to me until I hit this wall! I'll get
back you you after I try it...
Click to expand...


aha!

If you're trying to retrieve a record which has not yet been saved to
the table - i.e. if it's still on the Form - then this query will in
fact not retrieve anything.


John W. Vinson[MVP]
Click to expand...
 
Top