error 3129 (1 Viewer)

Reshmi mohankumar

Registered User.
Local time
Today, 20:47
Joined
Dec 5, 2017
Messages
101
Dim dayend, stock, upd As String
dayend = "insert into tbl_dayend(dayend,dayenddate) values ('1','" & txtnxtdt & "')"
stock = "INSERT INTO Tbl_stonestockopening (stonesubgroup, stonename, size, closinpcs, closingweightcts)" _
& "select stonesubgroup, stonename, size, clspcs, clswt from qry_stock_byname3"
upd = " update tbl_stonestockopening set transactiondate='" & txtdate & "',fy='" & txtfy & "',openingdate='" & txtdate + 1 & "'" _
& "where transactiondate is null"

DoCmd.RunSQL stock
DoCmd.RunSQL upd
DoCmd.RunSQL dayend


here "qry_stock_byname3" is a nested query
 

Gasman

Enthusiastic Amateur
Local time
Today, 15:17
Joined
Sep 21, 2011
Messages
14,044
What is error 3129 :banghead:

Debug.Print stock
Debug.Print upd
Debug.Print dayend

Which one do you get the error on?

Also the Dim statement should be

Code:
Dim stock as string, upd as string, dayend as string
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:17
Joined
Oct 29, 2018
Messages
21,358
Hi. What is the description for error 3129? Also, you might want to do a Debug.Print of your variables because you may have run-on words in them, which could result in a syntax error.
 

missinglinq

AWF VIP
Local time
Today, 11:17
Joined
Jun 20, 2003
Messages
6,423
Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.

Linq ;0)>
 
Last edited:

Cronk

Registered User.
Local time
Tomorrow, 02:17
Joined
Jul 4, 2013
Messages
2,770
Code:
dayend = "insert into tbl_dayend(dayend,dayenddate) values ('1','" & txtnxtdt & "')"
Are DayEnd and dayEndDate text fields?

Code:
upd = " update tbl_stonestockopening set transactiondate='" & txtdate & "',fy='" & txtfy & "',openingdate='" & txtdate + 1 & "'" _
& "where transactiondate is null"
Are transactionDate and openingDate text fields?
 

Reshmi mohankumar

Registered User.
Local time
Today, 20:47
Joined
Dec 5, 2017
Messages
101
Code:
dayend = "insert into tbl_dayend(dayend,dayenddate) values ('1','" & txtnxtdt & "')"
Are DayEnd and dayEndDate text fields?

Code:
upd = " update tbl_stonestockopening set transactiondate='" & txtdate & "',fy='" & txtfy & "',openingdate='" & txtdate + 1 & "'" _
& "where transactiondate is null"
Are transactionDate and openingDate text fields?




dayend field is true/false
dayenddate is date/time
transactiondate is date/time
openingdate is date/time fields
 

Gasman

Enthusiastic Amateur
Local time
Today, 15:17
Joined
Sep 21, 2011
Messages
14,044
Well as the DBguy mentioned, I can see concatenation from what you have posted.
That is why I suggested Debug.Print each string to show where it is incorrect?

That would also help with whatever type each variable is.

Fix that, then run manually one at at time to identify where the error lies.
 

Cronk

Registered User.
Local time
Tomorrow, 02:17
Joined
Jul 4, 2013
Messages
2,770
That explains the error - you need to put date values into date/time fields, not strings.


Before running the docmd.runsql statements, add the line to your code

Code:
debug.Print stock, upd, dayend
and you will see what is being generated.


Your code should be something like
Code:
upd  = " update tbl_stonestockopening set transactiondate=#" &  format(txtdate,"mm/dd/yyyy") & "#,fy=#" &  format(txtfy,"mm/dd/yyyy") & "#,openingdate=#" &  format((txtdate+1),"mm/dd/yyyy") & "#" _
    & " where transactiondate is null"
 

Reshmi mohankumar

Registered User.
Local time
Today, 20:47
Joined
Dec 5, 2017
Messages
101
That explains the error - you need to put date values into date/time fields, not strings.


Before running the docmd.runsql statements, add the line to your code

Code:
debug.Print stock, upd, dayend
and you will see what is being generated.


Your code should be something like
Code:
upd  = " update tbl_stonestockopening set transactiondate=#" &  format(txtdate,"mm/dd/yyyy") & "#,fy=#" &  format(txtfy,"mm/dd/yyyy") & "#,openingdate=#" &  format((txtdate+1),"mm/dd/yyyy") & "#" _
    & " where transactiondate is null"




thank you i got cleared and got succeded, application completed. Thank you everyone for supporting.
 

Users who are viewing this thread

Top Bottom