Error in SQL statement apparantly

[CapnOats]

Afternoon all,

Wondering if one of you good people could give us a hand with my SQL string.

The debugger says there's a problem with the sql statement and that's all it says - pretty helpful

I've looked over my code, but i've prob over-looked the error - if you could see what's a miss it would be grand.

DoCmd.RunSQL _
"INSERT INTO Job ( " _
 & "[Recieved Date], [SE Reference], [FM Reference], [Assigned Date], [Assigned Hours], [Assigned Techie], [Customer]," _
 & "[Due Date], [L1 Check Ready], [L1 Check Date], [L2 Check Ready], [L2 Check Date], [Approved], [Floor Area] )" _
 & "VALUES (" & recDate & ", '" & seRef & "', '" & fmRef & "', " & assDate & ", " & assHours & ", '" & assTech & "', '" _
 & cust & "', " & dueDate & ", No, 0, No, 0, 0, 0)"

Regards,
Mike

 

[Crowley16]

ok, a few of the more obvious mistakes...

you need to put ## around your date values, like #12 Jul 04#

also, when you assign No to a control, I'm assuming those are yes/no values, use true or false, not yes or no, since yes or no isn't recognised.
If they are text fields, then put '' around the NO.

 

[CapnOats]

the dates are actually integers not date types - im importing lots of data from linked tables from different types of sql servers and it was easier just to define a number like 20040721 and convert to that.

i had the query working when i added a temp query and built it there, using real values for the variables and No for the Yes/No field and it worked fine - it's just since i've made it into a string in vba it's gone wierd.

and changing no to False doesn't make any difference.

Regards,
Mike

 

[Crowley16]

since you've made what into a string?

if the NO values are supposed to be a string, then you need to add '' around them, i.e. 'NO'

 

[CapnOats]

the whole sql statement - it worked in the design new query window but when i put "" round it and start substituting test values out and "&variableName&" in its place

Regards,
Mike

 

[Crowley16]

well, in that case it could be the values that you are trying to input...

put the entire statement into a msgbox and tell us the result

 

[lespaul]

So the fields in the table are:

[Recieved Date] - integer
[SE Reference] - string
[FM Reference] - string
[Assigned Date] - integer
[Assigned Hours] - integer
[Assigned Techie] - string
[Customer] - string
[Due Date] - integer
[L1 Check Ready] - logical
[L1 Check Date] - integer
[L2 Check Ready] - logical
[L2 Check Date] - integer
[Approved] - integer
[Floor Area] - integer

have you tried changing the No to False?

Have you used the debugger to see the acutal SQL statement that is built when you run the program?

Leslie

 

[CapnOats]

found the prob

immediatley before the sql section i assign data into all the variables - however i forgot assDate so that part of the sql string appeared as

20040722, 'se 2004-050', v3-cc-west-l2-181D-f', , 88.8, 'Mike', 'The Customer', 20040801, No, 0, No, 0, 0, 0

so there ends up being a bit with no value which seems to throw it - i define assDate and it works

all thanks to the mighty MsgBox! huzzah!

Regards,
Mike