SQL code problem

  • Thread starter Thread starter Bernd
  • Start date Start date
B

Bernd

Hi,
I use the following sql instruction in my database:

strSQL = "Insert Into tblProducts ([Date]) values ('" &
NewData & "')"

and now I want to add a 'WHERE clause', but everytime I
tryed to write the following instruction:

strSQL = "Insert Into tblProducts ([Date]) values ('" &
NewData & "') Where tblProducts.PatientNameID = forms!
MainForm!Combo0;"

The compiler gives me the following error message:
'Missing semicolon (;) at end of SQL statement'

But I just wrote the semicolon (;) at the end of the
instruction.
The following istructions in my code are the following,
but I don't think that they need ';' at the end:

If i = vbYes Then
strSQL = "Insert Into tblProducts ([Date]) values
('" & NewData & "') Where
tblProducts.PatientNameID = forms!MainForm!combo0;"
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

Clear is that I wrote the above SQL instruction on the
same line.
How is the exact code for this SQL instruction?
Thanks
Bernd
 
Berndt,

To begin with, having a field named Date, which is an Access reserved
keyword, is asking for trouble. Istongly recommend you change the field
name. That said: The error message re. the missing semicolon is misleading.
The real problem is the reference to the control on the form should not be
within the quotes; it should be taken outside, much like you did with
NewData (a variable, I take it):

strSQL = "Insert Into tblProducts ([Date]) values ('"
strSQL = strSQL & NewData & "') Where tblProducts.PatientNameID = '"
strSQL = strSQL & forms!MainForm!Combo0 & "'"

HTH,
Nikos
 
Hi,
thanks for your feedback.
I did the things that you wrote, but nothing is changing,
the compiler almost gives the same error:
'Missing semicolon (;) at end of SQL statement'
even if I add on your code at the end a semicolon.
What can I do to achive this error message?
Thanks
Bernd
-----Original Message-----
Berndt,

To begin with, having a field named Date, which is an Access reserved
keyword, is asking for trouble. Istongly recommend you change the field
name. That said: The error message re. the missing semicolon is misleading.
The real problem is the reference to the control on the form should not be
within the quotes; it should be taken outside, much like you did with
NewData (a variable, I take it):

strSQL = "Insert Into tblProducts ([Date]) values ('"
strSQL = strSQL & NewData & "') Where tblProducts.PatientNameID = '"
strSQL = strSQL & forms!MainForm!Combo0 & "'"

HTH,
Nikos

Hi,
I use the following sql instruction in my database:

strSQL = "Insert Into tblProducts ([Date]) values ('" &
NewData & "')"

and now I want to add a 'WHERE clause', but everytime I
tryed to write the following instruction:

strSQL = "Insert Into tblProducts ([Date]) values ('" &
NewData & "') Where tblProducts.PatientNameID = forms!
MainForm!Combo0;"

The compiler gives me the following error message:
'Missing semicolon (;) at end of SQL statement'

But I just wrote the semicolon (;) at the end of the
instruction.
The following istructions in my code are the following,
but I don't think that they need ';' at the end:

If i = vbYes Then
strSQL = "Insert Into tblProducts ([Date]) values
('" & NewData & "') Where
tblProducts.PatientNameID = forms!MainForm!combo0;"
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

Clear is that I wrote the above SQL instruction on the
same line.
How is the exact code for this SQL instruction?
Thanks
Bernd
Click to expand...


.
Click to expand...
 
You're trying to use an append query that has the target table's field as a
WHERE constraint. That is not going to work. Do you want to update an exist
record, not add a new record? If yes, use an update query:

strSQL = "UPDATE tblProducts SET [Date] = #" & _
Format(NewData, "mm/dd/yyyy") & "# WHERE " & _
"PatientNameID = [Forms]![MainForm]![Combo0];"

Note that the above is using a parameter, which will require resolving if
you plan to run it using DAO's Execute method. So, I'd change it to this,
assuming that the form is open:

strSQL = "UPDATE tblProducts SET [Date] = #" & _
Format(NewData, "mm/dd/yyyy") & "# WHERE " & _
"PatientNameID = " & [Forms]![MainForm]![Combo0] & ";"
 
I'd try this:

strSQL = "Insert Into tblProducts ([Date]) values (#" & _
NewData & "#) Where tblProducts.PatientNameID = " & _
forms!MainForm!Combo0 & ";"

I am assuming that NewData is a date, and therefore needs to be delimited
with the pound sign (#). I am further assuming that PatientNameID is a
numeric rather than text, so it does not need to be delimited with
aprostrphes ('). I don't THINK the semicolon at the end is necessary, but
given the error message, I added it. Try experimenting with it and without
it. Sometimes error messages don't always identify the real error.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
Bernd,

That's odd... I still think the error message is misleading, and the real
problem is something else. By the way, I have assumed field PatientNameID to
be text. Have I assumed wrong? If it's numeric, then the syntax should be:

strSQL = "Insert Into tblProducts ([Date]) values ('"
strSQL = strSQL & NewData & "') Where tblProducts.PatientNameID = "
strSQL = strSQL & forms!MainForm!Combo0
(without the single quotes around the combo reference).

If that doesn't fix it:

Use a few extra lines of code to print the values of NewData and
Forms!MainForm!Combo0 in the immediate window (Ctrl+G to open), like:
Debug.Print NewData
Debug.Print Forms!MainForm!Combo0

Put these lines right before or right after the strSQL build. You may have
to temporarily comment out the CurrentDb.Execute to run the code up to
there, so you can check the values. Are the values what you expected, or is
there something wrong?

If the values are correct and you still can't fix it, copy your code snippet
and post it, in case there's some other syntax flaw that folls the eye.

HTH,
Nikos

Hi,
thanks for your feedback.
I did the things that you wrote, but nothing is changing,
the compiler almost gives the same error:
'Missing semicolon (;) at end of SQL statement'
even if I add on your code at the end a semicolon.
What can I do to achive this error message?
Thanks
Bernd
-----Original Message-----
Berndt,

To begin with, having a field named Date, which is an Access reserved
keyword, is asking for trouble. Istongly recommend you change the field
name. That said: The error message re. the missing semicolon is misleading.
The real problem is the reference to the control on the form should not be
within the quotes; it should be taken outside, much like you did with
NewData (a variable, I take it):

strSQL = "Insert Into tblProducts ([Date]) values ('"
strSQL = strSQL & NewData & "') Where tblProducts.PatientNameID = '"
strSQL = strSQL & forms!MainForm!Combo0 & "'"

HTH,
Nikos

Hi,
I use the following sql instruction in my database:

strSQL = "Insert Into tblProducts ([Date]) values ('" &
NewData & "')"

and now I want to add a 'WHERE clause', but everytime I
tryed to write the following instruction:

strSQL = "Insert Into tblProducts ([Date]) values ('" &
NewData & "') Where tblProducts.PatientNameID = forms!
MainForm!Combo0;"

The compiler gives me the following error message:
'Missing semicolon (;) at end of SQL statement'

But I just wrote the semicolon (;) at the end of the
instruction.
The following istructions in my code are the following,
but I don't think that they need ';' at the end:

If i = vbYes Then
strSQL = "Insert Into tblProducts ([Date]) values
('" & NewData & "') Where
tblProducts.PatientNameID = forms!MainForm!combo0;"
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

Clear is that I wrote the above SQL instruction on the
same line.
How is the exact code for this SQL instruction?
Thanks
Bernd
Click to expand...


.
Click to expand...
Click to expand...
 
Hey Bernd,

The INSERT statement doesn't have a WHERE clause, that is why it is
complaining.

It seems to me you are trying to do an UPDATE, which would be:

strSQL = "UPDATE tblProducts SET [Date] = '" &
NewData & "') WHERE tblProducts.PatientNameID = forms!
MainForm!Combo0"

Take care and let me know if it worked

Mauricio Silva
 
Hi Ken,
when I try to insert the code you wrote below, with the
first statement the compiler gives me the following error
message:
"Too few parameters. Expected 2"

And when I insert the second statement you wrote it gives
me the following error message:

"Too few parameters. Expected 1"

Why is happen this? And how can I resolve it?
P.s. Any way I don't know if it will work, because the
record where I have to insert the date doesn't exist,
because the patientname I insert before isnt't the same
record, because it is in a related table, but not in the
same table.
Thanks
Bernd
-----Original Message-----
You're trying to use an append query that has the target table's field as a
WHERE constraint. That is not going to work. Do you want to update an exist
record, not add a new record? If yes, use an update query:

strSQL = "UPDATE tblProducts SET [Date] = #" & _
Format(NewData, "mm/dd/yyyy") & "# WHERE " & _
"PatientNameID = [Forms]![MainForm]![Combo0];"

Note that the above is using a parameter, which will require resolving if
you plan to run it using DAO's Execute method. So, I'd change it to this,
assuming that the form is open:

strSQL = "UPDATE tblProducts SET [Date] = #" & _
Format(NewData, "mm/dd/yyyy") & "# WHERE " & _
"PatientNameID = " & [Forms]![MainForm]![Combo0] & ";"

--

Ken Snell
<MS ACCESS MVP>

Hi,
I use the following sql instruction in my database:

strSQL = "Insert Into tblProducts ([Date]) values ('" &
NewData & "')"

and now I want to add a 'WHERE clause', but everytime I
tryed to write the following instruction:

strSQL = "Insert Into tblProducts ([Date]) values ('" &
NewData & "') Where tblProducts.PatientNameID = forms!
MainForm!Combo0;"

The compiler gives me the following error message:
'Missing semicolon (;) at end of SQL statement'

But I just wrote the semicolon (;) at the end of the
instruction.
The following istructions in my code are the following,
but I don't think that they need ';' at the end:

If i = vbYes Then
strSQL = "Insert Into tblProducts ([Date]) values
('" & NewData & "') Where
tblProducts.PatientNameID = forms!MainForm!combo0;"
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

Clear is that I wrote the above SQL instruction on the
same line.
How is the exact code for this SQL instruction?
Thanks
Bernd
Click to expand...


.
Click to expand...
 
Hi Mauricio,
I inserted the statement that you send me, but the
compiler now gives me the following error message:
"Too few parameters. Expected 2"

Why this is happen and what can I do?
Thanks
Bernd
-----Original Message-----
Hey Bernd,

The INSERT statement doesn't have a WHERE clause, that is why it is
complaining.

It seems to me you are trying to do an UPDATE, which would be:

strSQL = "UPDATE tblProducts SET [Date] = '" &
NewData & "') WHERE tblProducts.PatientNameID = forms!
MainForm!Combo0"

Take care and let me know if it worked

Mauricio Silva


Bernd said:
Hi,
I use the following sql instruction in my database:

strSQL = "Insert Into tblProducts ([Date]) values ('" &
NewData & "')"

and now I want to add a 'WHERE clause', but everytime I
tryed to write the following instruction:

strSQL = "Insert Into tblProducts ([Date]) values ('" &
NewData & "') Where tblProducts.PatientNameID = forms!
MainForm!Combo0;"

The compiler gives me the following error message:
'Missing semicolon (;) at end of SQL statement'

But I just wrote the semicolon (;) at the end of the
instruction.
The following istructions in my code are the following,
but I don't think that they need ';' at the end:

If i = vbYes Then
strSQL = "Insert Into tblProducts ([Date]) values
('" & NewData & "') Where
tblProducts.PatientNameID = forms!MainForm!combo0;"
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

Clear is that I wrote the above SQL instruction on the
same line.
How is the exact code for this SQL instruction?
Thanks
Bernd
Click to expand...
.
Click to expand...
 
I think there is a lot of confusion on our part as to what you want to
achieve.

From this note, I am understanding that you indeed want to insert a new
record into tblProducts table. As such, please explain what you're trying to
do with the WHERE clause. In which table is the field PatientNameID found?

You're getting the error about 1 parameter in my second example most likely
because PatientNameID is not a field in tblProducts, so the query is asking
you what it is.

Please provide a more complete description of what you are trying to do.
--

Ken Snell
<MS ACCESS MVP>



Hi Ken,
when I try to insert the code you wrote below, with the
first statement the compiler gives me the following error
message:
"Too few parameters. Expected 2"

And when I insert the second statement you wrote it gives
me the following error message:

"Too few parameters. Expected 1"

Why is happen this? And how can I resolve it?
P.s. Any way I don't know if it will work, because the
record where I have to insert the date doesn't exist,
because the patientname I insert before isnt't the same
record, because it is in a related table, but not in the
same table.
Thanks
Bernd
-----Original Message-----
You're trying to use an append query that has the target table's field as a
WHERE constraint. That is not going to work. Do you want to update an exist
record, not add a new record? If yes, use an update query:

strSQL = "UPDATE tblProducts SET [Date] = #" & _
Format(NewData, "mm/dd/yyyy") & "# WHERE " & _
"PatientNameID = [Forms]![MainForm]![Combo0];"

Note that the above is using a parameter, which will require resolving if
you plan to run it using DAO's Execute method. So, I'd change it to this,
assuming that the form is open:

strSQL = "UPDATE tblProducts SET [Date] = #" & _
Format(NewData, "mm/dd/yyyy") & "# WHERE " & _
"PatientNameID = " & [Forms]![MainForm]![Combo0] & ";"

--

Ken Snell
<MS ACCESS MVP>

Hi,
I use the following sql instruction in my database:

strSQL = "Insert Into tblProducts ([Date]) values ('" &
NewData & "')"

and now I want to add a 'WHERE clause', but everytime I
tryed to write the following instruction:

strSQL = "Insert Into tblProducts ([Date]) values ('" &
NewData & "') Where tblProducts.PatientNameID = forms!
MainForm!Combo0;"

The compiler gives me the following error message:
'Missing semicolon (;) at end of SQL statement'

But I just wrote the semicolon (;) at the end of the
instruction.
The following istructions in my code are the following,
but I don't think that they need ';' at the end:

If i = vbYes Then
strSQL = "Insert Into tblProducts ([Date]) values
('" & NewData & "') Where
tblProducts.PatientNameID = forms!MainForm!combo0;"
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

Clear is that I wrote the above SQL instruction on the
same line.
How is the exact code for this SQL instruction?
Thanks
Bernd
Click to expand...


.
Click to expand...
Click to expand...
 
The only think I could guess is that the name of the field is wrong. Is it
PatientNameId ?

[email protected] said:
Hi Mauricio,
I inserted the statement that you send me, but the
compiler now gives me the following error message:
"Too few parameters. Expected 2"

Why this is happen and what can I do?
Thanks
Bernd
-----Original Message-----
Hey Bernd,

The INSERT statement doesn't have a WHERE clause, that is why it is
complaining.

It seems to me you are trying to do an UPDATE, which would be:

strSQL = "UPDATE tblProducts SET [Date] = '" &
NewData & "') WHERE tblProducts.PatientNameID = forms!
MainForm!Combo0"

Take care and let me know if it worked

Mauricio Silva


Bernd said:
Hi,
I use the following sql instruction in my database:

strSQL = "Insert Into tblProducts ([Date]) values ('" &
NewData & "')"

and now I want to add a 'WHERE clause', but everytime I
tryed to write the following instruction:

strSQL = "Insert Into tblProducts ([Date]) values ('" &
NewData & "') Where tblProducts.PatientNameID = forms!
MainForm!Combo0;"

The compiler gives me the following error message:
'Missing semicolon (;) at end of SQL statement'

But I just wrote the semicolon (;) at the end of the
instruction.
The following istructions in my code are the following,
but I don't think that they need ';' at the end:

If i = vbYes Then
strSQL = "Insert Into tblProducts ([Date]) values
('" & NewData & "') Where
tblProducts.PatientNameID = forms!MainForm!combo0;"
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

Clear is that I wrote the above SQL instruction on the
same line.
How is the exact code for this SQL instruction?
Thanks
Bernd
Click to expand...
.
Click to expand...
Click to expand...
 
Try this:

strSQL = "UPDATE tblProducts SET [Date] = '" & NewData & "') WHERE
tblProducts.PatientNameID = " & forms!MainForm!Combo0

Brian

Mauricio Silva said:
The only think I could guess is that the name of the field is wrong. Is it
PatientNameId ?

[email protected] said:
Hi Mauricio,
I inserted the statement that you send me, but the
compiler now gives me the following error message:
"Too few parameters. Expected 2"

Why this is happen and what can I do?
Thanks
Bernd
-----Original Message-----
Hey Bernd,

The INSERT statement doesn't have a WHERE clause, that is why it is
complaining.

It seems to me you are trying to do an UPDATE, which would be:

strSQL = "UPDATE tblProducts SET [Date] = '" &
NewData & "') WHERE tblProducts.PatientNameID = forms!
MainForm!Combo0"

Take care and let me know if it worked

Mauricio Silva


:

Hi,
I use the following sql instruction in my database:

strSQL = "Insert Into tblProducts ([Date]) values ('" &
NewData & "')"

and now I want to add a 'WHERE clause', but everytime I
tryed to write the following instruction:

strSQL = "Insert Into tblProducts ([Date]) values ('" &
NewData & "') Where tblProducts.PatientNameID = forms!
MainForm!Combo0;"

The compiler gives me the following error message:
'Missing semicolon (;) at end of SQL statement'

But I just wrote the semicolon (;) at the end of the
instruction.
The following istructions in my code are the following,
but I don't think that they need ';' at the end:

If i = vbYes Then
strSQL = "Insert Into tblProducts ([Date]) values
('" & NewData & "') Where
tblProducts.PatientNameID = forms!MainForm!combo0;"
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

Clear is that I wrote the above SQL instruction on the
same line.
How is the exact code for this SQL instruction?
Thanks
Bernd

.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top