Syntax error in insert into statement

W

Wind54Surfer

Hi all,

I get this error when added I specify the fields in this query (I had to do
this because it won't work replicated-field number difference):
----------------------------------------------------------------------
Function build_sql(Schedule_ID As Date, operation As String) As String
build_sql = "Insert Into tblJobScheduleAuditLog
============================================
(ScheduleID, Scheduled, Jobdate, Status, Action, Timestamp)
============================================
Values (#"
build_sql = build_sql & ScheduleID & "# , "
build_sql = build_sql & "'" & _
DLookup("Scheduled", "JobSchedule", "ScheduleID=#" & _
Schedule_ID & "#") & "', "
build_sql = build_sql & "'" & _
DLookup("JobDate", "JobSchedule", "ScheduleID=#" & _
Schedule_ID & "#") & "', "
build_sql = build_sql & "'" & _
DLookup("Status", "JobSchedule", "ScheduleID=#" & _
Schedule_ID & "#") & "', "
build_sql = build_sql & "'" & operation & "', "
build_sql = build_sql & "#" & Now() & "#)"
End Function
---------------------------------------------------------------------------
The fields are as follows:

ScheduleID is Date/Time
Scheduled is Text
JobDate is Text
Status is Text
Action is Text
Timestamp is Date/Time

Any help really appreciated,
Emilio
 
D

Duane Hookom

Why don't you just start off in your first post with the results of the
function? I would never build an insert without explicitly naming each field
prior to the "Values (...".

Did you copy and paste this code? I'm surprised the Now() in the code
retains the ()s.
 
W

Wind54Surfer

Hi Duane,

I thought the proper thing to do if I am going to talk about apples instead
of oranges was to start another thread.

I am sorry, didn't know that was the policy of the Group to continue in the
same thread after the problem was solved, it won't happen again.

And yes I did cut ant paste, I am just wondering how everybody else does it?

Thanks for your response,
Emilio
 
D

Duane Hookom

What is the return value of your function? It may just be a line wrap issue
but I wouldn't expect this to compile:

build_sql = "Insert Into tblJobScheduleAuditLog
============================================
(ScheduleID, Scheduled, Jobdate, Status, Action, Timestamp)
============================================
Values (#"

That's why I questioned your cut and paste.

Have you considered my other suggestion to "explicitly naming each field"?
 
W

Wind54Surfer

Thanks again Duane,

This is the value:
----------------------------------------------------------------------------------
: ssql :"Insert Into tblJobScheduleAuditLog ([ScheduleID], Scheduled,
JobDate, Status, Action,Timestamp) Values (#1/10/2006 9:36:59 PM# ,
'6721-Slater-W-323', '1/2/2006', 'Done', 'Update', #1/11/2006 10:19:31 AM#)"
---------------------------------------------------------------------------------
The 2 double lines I just entered to highlight where the problem would be

'Not in code ============================================
(ScheduleID, Scheduled, Jobdate, Status, Action, Timestamp)
Click to expand...
'Not in code ============================================

Sorry to confuse you.

Emilio
 
D

Duane Hookom

What happens if you attempt to paste the sql into the SQL view of a blank
query?

--
Duane Hookom
MS Access MVP
--

Wind54Surfer said:
Thanks again Duane,

This is the value:
----------------------------------------------------------------------------------
: ssql :"Insert Into tblJobScheduleAuditLog ([ScheduleID], Scheduled,
JobDate, Status, Action,Timestamp) Values (#1/10/2006 9:36:59 PM# ,
'6721-Slater-W-323', '1/2/2006', 'Done', 'Update', #1/11/2006 10:19:31
AM#)"
---------------------------------------------------------------------------------
The 2 double lines I just entered to highlight where the problem would be

'Not in code ============================================
(ScheduleID, Scheduled, Jobdate, Status, Action, Timestamp)
Click to expand...
'Not in code ============================================

Sorry to confuse you.

Emilio

Duane Hookom said:
What is the return value of your function? It may just be a line wrap
issue
but I wouldn't expect this to compile:

build_sql = "Insert Into tblJobScheduleAuditLog
============================================
(ScheduleID, Scheduled, Jobdate, Status, Action, Timestamp)
============================================
Values (#"

That's why I questioned your cut and paste.

Have you considered my other suggestion to "explicitly naming each
field"?
Click to expand...
Click to expand...
 
W

Wind54Surfer

The same error happens and hightlights the word "Timestamp", I tried
enclosing in [] and it seems to solve it.

Thanks again,
Emilio

Duane Hookom said:
What happens if you attempt to paste the sql into the SQL view of a blank
query?

--
Duane Hookom
MS Access MVP
--

Wind54Surfer said:
Thanks again Duane,

This is the value:
----------------------------------------------------------------------------------
: ssql :"Insert Into tblJobScheduleAuditLog ([ScheduleID], Scheduled,
JobDate, Status, Action,Timestamp) Values (#1/10/2006 9:36:59 PM# ,
'6721-Slater-W-323', '1/2/2006', 'Done', 'Update', #1/11/2006 10:19:31
AM#)"
---------------------------------------------------------------------------------
The 2 double lines I just entered to highlight where the problem would be

'Not in code ============================================
(ScheduleID, Scheduled, Jobdate, Status, Action, Timestamp)
Click to expand...
'Not in code ============================================

Sorry to confuse you.

Emilio

Duane Hookom said:
What is the return value of your function? It may just be a line wrap
issue
but I wouldn't expect this to compile:

build_sql = "Insert Into tblJobScheduleAuditLog
============================================
(ScheduleID, Scheduled, Jobdate, Status, Action, Timestamp)
============================================
Values (#"

That's why I questioned your cut and paste.

Have you considered my other suggestion to "explicitly naming each
field"?

--
Duane Hookom
MS Access MVP
--

Hi Duane,

I thought the proper thing to do if I am going to talk about apples
instead
of oranges was to start another thread.

I am sorry, didn't know that was the policy of the Group to continue in
the
same thread after the problem was solved, it won't happen again.

And yes I did cut ant paste, I am just wondering how everybody else
does
it?

Thanks for your response,
Emilio

:

Why don't you just start off in your first post with the results of
the
function? I would never build an insert without explicitly naming each
field
prior to the "Values (...".

Did you copy and paste this code? I'm surprised the Now() in the code
retains the ()s.

--
Duane Hookom
MS Access MVP
--

message
Hi all,

I get this error when added I specify the fields in this query (I
had
to
do
this because it won't work replicated-field number difference):
----------------------------------------------------------------------
Function build_sql(Schedule_ID As Date, operation As String) As
String
build_sql = "Insert Into tblJobScheduleAuditLog
============================================
(ScheduleID, Scheduled, Jobdate, Status, Action, Timestamp)
============================================
Values (#"
build_sql = build_sql & ScheduleID & "# , "
build_sql = build_sql & "'" & _
DLookup("Scheduled", "JobSchedule", "ScheduleID=#" & _
Schedule_ID & "#") & "', "
build_sql = build_sql & "'" & _
DLookup("JobDate", "JobSchedule", "ScheduleID=#" & _
Schedule_ID & "#") & "', "
build_sql = build_sql & "'" & _
DLookup("Status", "JobSchedule", "ScheduleID=#" & _
Schedule_ID & "#") & "', "
build_sql = build_sql & "'" & operation & "', "
build_sql = build_sql & "#" & Now() & "#)"
End Function
---------------------------------------------------------------------------
The fields are as follows:

ScheduleID is Date/Time
Scheduled is Text
JobDate is Text
Status is Text
Action is Text
Timestamp is Date/Time

Any help really appreciated,
Emilio
Click to expand...
Click to expand...
Click to expand...
 

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

Data type mismatch in criteria expression 14
Syntax error (missing operator) in query expression 3
Time Scheduling Conflict Check 3
return without gosub error 0
CASE statement in query 3
Access07 Syntax Errors 3
return without gosub error 0
Using Max or DMax in DLookUp criteria? 1

Top