SQL Synatx Incorrect

D

DS

My SQL code keeps stopping it says that it wants a semi-colon at the
end. I tried but I can't seem to find where it wants it!
Could it be the [UsageDate] = Date(). I need the usage date to equal
the current date.

CurrentDb.Execute ("INSERT INTO PayApplied
(SalesID,PaymentNameID,PaymentAmount,DocID) " & _
"Values(" & Forms!CheckPayment!SalesID & ",9," &
Forms!DepositPad!TxtDepositAmount & "," & Forms!DepositPad!TxtDepositID
& ") " & _
"UPDATE Deposits SET [Applied]=True AND [UsageDate]=Date() " & _
"WHERE Deposits.[DepositID] = Forms!DepositPad![TxtDepositID]")

Any help, I appreciate.
Thanks
DS
 
J

Jeff

DS said:
My SQL code keeps stopping it says that it wants a semi-colon at the
end. I tried but I can't seem to find where it wants it!
Could it be the [UsageDate] = Date(). I need the usage date to equal
the current date.

CurrentDb.Execute ("INSERT INTO PayApplied
(SalesID,PaymentNameID,PaymentAmount,DocID) " & _
"Values(" & Forms!CheckPayment!SalesID & ",9," &
Forms!DepositPad!TxtDepositAmount & "," & Forms!DepositPad!TxtDepositID
& ") " & _
"UPDATE Deposits SET [Applied]=True AND [UsageDate]=Date() " & _
"WHERE Deposits.[DepositID] = Forms!DepositPad![TxtDepositID]")

Any help, I appreciate.
Thanks
DS

The semicolon is usually at the end of the sql statement

Try This...

"WHERE Deposits.[DepositID] = Forms!DepositPad![TxtDepositID];")

Jeff C
 
D

DS

Jeff said:
DS said:
My SQL code keeps stopping it says that it wants a semi-colon at the
end. I tried but I can't seem to find where it wants it!
Could it be the [UsageDate] = Date(). I need the usage date to equal
the current date.

CurrentDb.Execute ("INSERT INTO PayApplied
(SalesID,PaymentNameID,PaymentAmount,DocID) " & _
"Values(" & Forms!CheckPayment!SalesID & ",9," &
Forms!DepositPad!TxtDepositAmount & "," &
Forms!DepositPad!TxtDepositID & ") " & _
"UPDATE Deposits SET [Applied]=True AND [UsageDate]=Date() " & _
"WHERE Deposits.[DepositID] = Forms!DepositPad![TxtDepositID]")

Any help, I appreciate.
Thanks
DS


The semicolon is usually at the end of the sql statement

Try This...

"WHERE Deposits.[DepositID] = Forms!DepositPad![TxtDepositID];")

Jeff C
Thanks Jeff, Hey what happens if you don't have the semi colon and
access doesn't ask for it?
DS
 
R

RoyVidar

DS wrote in message said:
Jeff said:
DS said:
My SQL code keeps stopping it says that it wants a semi-colon at the end.
I tried but I can't seem to find where it wants it!
Could it be the [UsageDate] = Date(). I need the usage date to equal the
current date.

CurrentDb.Execute ("INSERT INTO PayApplied
(SalesID,PaymentNameID,PaymentAmount,DocID) " & _
"Values(" & Forms!CheckPayment!SalesID & ",9," &
Forms!DepositPad!TxtDepositAmount & "," & Forms!DepositPad!TxtDepositID &
") " & _
"UPDATE Deposits SET [Applied]=True AND [UsageDate]=Date() " & _
"WHERE Deposits.[DepositID] = Forms!DepositPad![TxtDepositID]")

Any help, I appreciate.
Thanks
DS


The semicolon is usually at the end of the sql statement

Try This...

"WHERE Deposits.[DepositID] = Forms!DepositPad![TxtDepositID];")

Jeff C
Thanks Jeff, Hey what happens if you don't have the semi colon and access
doesn't ask for it?
DS

I think Access is complaining about you firing off two separate SQL
statements
(action queries) in one go.

Try separate them

CurrentDb.Execute ("INSERT INTO PayApplied
(SalesID,PaymentNameID,PaymentAmount,DocID) " & _
"Values(" & Forms!CheckPayment!SalesID & ",9," &
Forms!DepositPad!TxtDepositAmount & "," & Forms!DepositPad!TxtDepositID
& ") ")

CurrentDB.Execute ("UPDATE Deposits SET [Applied]=True AND
[UsageDate]=Date() " & _
"WHERE Deposits.[DepositID] = " & Forms!DepositPad![TxtDepositID])

(I also ensured the value of the reference entered the last statement,
not the
reference)

and see if that works.
 
D

Douglas J. Steele

RoyVidar said:
DS wrote in message said:
Jeff said:
DS wrote:

My SQL code keeps stopping it says that it wants a semi-colon at the
end. I tried but I can't seem to find where it wants it!
Could it be the [UsageDate] = Date(). I need the usage date to equal
the current date.

CurrentDb.Execute ("INSERT INTO PayApplied
(SalesID,PaymentNameID,PaymentAmount,DocID) " & _
"Values(" & Forms!CheckPayment!SalesID & ",9," &
Forms!DepositPad!TxtDepositAmount & "," & Forms!DepositPad!TxtDepositID
& ") " & _
"UPDATE Deposits SET [Applied]=True AND [UsageDate]=Date() " & _
"WHERE Deposits.[DepositID] = Forms!DepositPad![TxtDepositID]")

Any help, I appreciate.
Thanks
DS

I think Access is complaining about you firing off two separate SQL
statements
(action queries) in one go.

Try separate them

CurrentDb.Execute ("INSERT INTO PayApplied
(SalesID,PaymentNameID,PaymentAmount,DocID) " & _
"Values(" & Forms!CheckPayment!SalesID & ",9," &
Forms!DepositPad!TxtDepositAmount & "," & Forms!DepositPad!TxtDepositID &
") ")

CurrentDB.Execute ("UPDATE Deposits SET [Applied]=True AND
[UsageDate]=Date() " & _
"WHERE Deposits.[DepositID] = " & Forms!DepositPad![TxtDepositID])

(I also ensured the value of the reference entered the last statement, not
the reference)

and see if that works.

That's correct: unlike, say, SQL Server, Access does not allow SQL
statements to be concatenated.

For the record, the semicolon is never mandatory in Access.
 
D

DS

Douglas said:
DS wrote in message said:
Jeff wrote:

DS wrote:


My SQL code keeps stopping it says that it wants a semi-colon at the
end. I tried but I can't seem to find where it wants it!
Could it be the [UsageDate] = Date(). I need the usage date to equal
the current date.

CurrentDb.Execute ("INSERT INTO PayApplied
(SalesID,PaymentNameID,PaymentAmount,DocID) " & _
"Values(" & Forms!CheckPayment!SalesID & ",9," &
Forms!DepositPad!TxtDepositAmount & "," & Forms!DepositPad!TxtDepositID
& ") " & _
"UPDATE Deposits SET [Applied]=True AND [UsageDate]=Date() " & _
"WHERE Deposits.[DepositID] = Forms!DepositPad![TxtDepositID]")

Any help, I appreciate.
Thanks
DS

I think Access is complaining about you firing off two separate SQL
statements
(action queries) in one go.

Try separate them

CurrentDb.Execute ("INSERT INTO PayApplied
(SalesID,PaymentNameID,PaymentAmount,DocID) " & _
"Values(" & Forms!CheckPayment!SalesID & ",9," &
Forms!DepositPad!TxtDepositAmount & "," & Forms!DepositPad!TxtDepositID &
") ")

CurrentDB.Execute ("UPDATE Deposits SET [Applied]=True AND
[UsageDate]=Date() " & _
"WHERE Deposits.[DepositID] = " & Forms!DepositPad![TxtDepositID])

(I also ensured the value of the reference entered the last statement, not
the reference)

and see if that works.


That's correct: unlike, say, SQL Server, Access does not allow SQL
statements to be concatenated.

For the record, the semicolon is never mandatory in Access.
Thanks, I'll lick this SQL thing eventually!
DS
 
D

DS

RoyVidar said:
DS wrote in message said:
Jeff said:
DS wrote:

My SQL code keeps stopping it says that it wants a semi-colon at the
end. I tried but I can't seem to find where it wants it!
Could it be the [UsageDate] = Date(). I need the usage date to
equal the current date.

CurrentDb.Execute ("INSERT INTO PayApplied
(SalesID,PaymentNameID,PaymentAmount,DocID) " & _
"Values(" & Forms!CheckPayment!SalesID & ",9," &
Forms!DepositPad!TxtDepositAmount & "," &
Forms!DepositPad!TxtDepositID & ") " & _
"UPDATE Deposits SET [Applied]=True AND [UsageDate]=Date() " & _
"WHERE Deposits.[DepositID] = Forms!DepositPad![TxtDepositID]")

Any help, I appreciate.
Thanks
DS



The semicolon is usually at the end of the sql statement

Try This...

"WHERE Deposits.[DepositID] = Forms!DepositPad![TxtDepositID];")

Jeff C

Thanks Jeff, Hey what happens if you don't have the semi colon and
access doesn't ask for it?
DS


I think Access is complaining about you firing off two separate SQL
statements
(action queries) in one go.

Try separate them

CurrentDb.Execute ("INSERT INTO PayApplied
(SalesID,PaymentNameID,PaymentAmount,DocID) " & _
"Values(" & Forms!CheckPayment!SalesID & ",9," &
Forms!DepositPad!TxtDepositAmount & "," & Forms!DepositPad!TxtDepositID
& ") ")

CurrentDB.Execute ("UPDATE Deposits SET [Applied]=True AND
[UsageDate]=Date() " & _
"WHERE Deposits.[DepositID] = " & Forms!DepositPad![TxtDepositID])

(I also ensured the value of the reference entered the last statement,
not the
reference)

and see if that works.
Thanks Roy, That worked.
DS
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

A Better Way 4
SQL HANGS 1
SQL Woes 7
Form Not Visible 2
Better Coding 2 SQL Commands 3
fConcatChild issues 7
EOF or BOF Error in access (back end is on SQL server) 1

Top