Error 3075

[Guest]

Hey, Ive been looking at this far too long think it needs new set of eyes:

The following sql statement fails when it trys to insert varNumber into
[Variation Number]. The field types match and varNumber is not null.

Statement:
sqlStr = "INSERT INTO [Tbl_View Variations]([Project Number], [Variation
Number], [Variation Description], [Variation Cost], [Date Created], [Start
Date], Month) VALUES" & _
"(" & project & "," & varNumber & "," & description & "," & poValue &
"," & dateCreated & ", " & startDate & ", " & months & _
");"

 

[Guest]

Hi Andrew,
After [Tbl_View Variations] and the VALUES statement I think you must add a
space.
Perhaps you must also bracket the month field 'cause I think it's a reserved
word...

sqlStr = "INSERT INTO [Tbl_View Variations] ([Project Number], [Variation
Number], [Variation Description], [Variation Cost], [Date Created], [Start
Date], [Month]) VALUES " & _
"(" & project & "," & varNumber & "," & description & "," & poValue &
"," & dateCreated & ", " & startDate & ", " & months & _
");"

HTH Paolo

 

[Alex Dybenko]

Hi,
string values you have to put in quotes, and dates - in #mm/dd/yyyy# format,
perhaps this is a source of your problem


--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

 

[Douglas J. Steele]

Dates need to be delimited with # characters, and they need to be in a
format that Access will correctly recognize. Since you have no control over
what Short Date format your users may choose through Regional Settings (in
the Control Panel), that means you should use the Format function to ensure
they're valid:

sqlStr = "INSERT INTO [Tbl_View Variations]([Project Number], " & _
"[Variation Number], [Variation Description], [Variation Cost], " & _
"[Date Created], [Start Date], [Month]) VALUES " & _
"(" & project & "," & varNumber & ",'" & description & "'," & _
poValue & "," & Format(dateCreated, "\#yyyy\-mm\-dd\#") & ", " & _
Format(startDate, "\#yyyy\-mm\-dd\#") & ", " & months & _
");"

Note, too, that I assumed your Variation Description field is text, so I
added additional quotes around the value of description. Exagerated for
clarity, that's

"(" & project & "," & varNumber & " ,' " & description & " ' ," & _

Note that should the description include an apostrophe, that will fail. If
that's a possibility, try:

"(" & project & "," & varNumber & " ,'" & Replace(description, "'", "''") &
"'," & _

where that's Replace(description, " ' ", " ' ' ")

As well, Paolo's correct that the field name Month needs to be enclosed in
square brackets, since Month is a reserved word. For a good discussion on
reserved words, check what Allen Browne has at
http://www.allenbrowne.com/AppIssueBadWord.html

 

[John Spencer]

So, what are your field types? For instance, I suspect that DateCreated and
Start Date are date fields and unless the variables you are referencing also
contain the date delimiters this query is going to fail. As a guess you
need to add delimiters for the date fields and the description field - which
is almost certainly a text field. If project number is a text field you
will also need to add quotes around the project variable

All that said you probably need a statement that looks more like the
following.

sqlStr = "INSERT INTO [Tbl_View Variations] " & _
" ([Project Number], [Variation Number], [Variation Description]" & _
", [Variation Cost], [Date Created], [Start Date], Month)" & _
" VALUES ("'' & project & ''"," & varNumber & ", """ & description & """, "
& _
poValue & ",#" & dateCreated & "#, #" & startDate & "#, " & months & ");"

I suggest you add a debug.print statement after the string is constructed,
so that you can look at it in the immediate window to see if it appears to
be correctly formed.

Debug.Print sqlStr
STOP

If the string looks correct, continue on. If it fails, you can copy the
string and paste it into a query and see why it fails.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..