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

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


The semicolon is usually at the end of the sql statement

Try This...

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

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


The semicolon is usually at the end of the sql statement

Try This...

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

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

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

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

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

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


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

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


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