SQL help

[Savant]

Hey



Where can I find out the syntax for Programming SQL in Access? In the
queries that I attempt to create, there's always a "property not found"
and it doesn't explain where the error lies...Does anyone know of a
document or website that explains SQL Syntax in relation to Access?



Thanks

 

[Cynthia]

The fastest way is to use the Access query builder to
create your query. Click the SQLView under the View menu
to see how access built the query. Then you can copy and
paste into your code and/or modify as necessary.

 

[Jennifer]

Hi. sorry if this is obvious, but I'm new to VBA. I've
tried this myself, but am getting syntax errors and the
arrangement of the brackets in the SQL view is quite
different from that of the stated syntax in the RunSQL
help. Below is the code exactly as copied and pasted. I
presume I need to put brackets around the SQL statement,
but that doesn't help. Any advice?

Sub append()
DoCmd.RunSQL INSERT INTO tblMainTable ( [VALUE], [COUNT],
AREA, MEAN, STD )
SELECT maya98.VALUE, maya98.COUNT, maya98.AREA,
maya98.MEAN, maya98.STD
FROM maya98;
End Sub

 

[John Viescas]

Jennifer-

RunSQL requires a string literal or variable. Try this:

Sub append()
DoCmd.RunSQL "INSERT INTO tblMainTable " & _
"( [VALUE], [COUNT], AREA, MEAN, STD ) " & _
"SELECT maya98.VALUE, maya98.COUNT, maya98.AREA, " & _
"maya98.MEAN, maya98.STD FROM maya98;"
End Sub


--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411

Hi. sorry if this is obvious, but I'm new to VBA. I've
tried this myself, but am getting syntax errors and the
arrangement of the brackets in the SQL view is quite
different from that of the stated syntax in the RunSQL
help. Below is the code exactly as copied and pasted. I
presume I need to put brackets around the SQL statement,
but that doesn't help. Any advice?

Sub append()
DoCmd.RunSQL INSERT INTO tblMainTable ( [VALUE], [COUNT],
AREA, MEAN, STD )
SELECT maya98.VALUE, maya98.COUNT, maya98.AREA,
maya98.MEAN, maya98.STD
FROM maya98;
End Sub

-----Original Message-----
The fastest way is to use the Access query builder to
create your query. Click the SQLView under the View menu
to see how access built the query. Then you can copy and
paste into your code and/or modify as necessary.
anyone
know of a
.

 

[Guest]

Yes John,

This string literal idea is great when one wants to insert string variables, however I am attempting to insert
strings, dates (and for a little spice) double values into a table. I am trying to run the following query using
a string variable to break things up:

msg = "INSERT INTO Valuation (PlantNum, Date, FV, IRV) "
msg = msg + "VALUES (" & stPNum + ", "
msg = msg & valDate + ", "
msg = msg & dblFV
msg = msg + ", " & dblIRV
msg = msg + ");"
DoCmd.RunSQL msg, True

valDate is a Date variable and dblFV & dblIRV are obviously double variables.

The msg constructs properly, however when one tries to run the DoCmd operation, Access returns Error 3134 -
Syntax Error. Frustrated, I re-wrote the insert query as follows (it should all be on one line, wrap before the first
ampersand on the second line):

msg = "INSERT INTO Valuation (PlantNum, Date, FV, IRV) VALUES (" & stPNum & ", " & valDate & ", "
& dblFV & ", " & ", " & dblIRV & ");"
DoCmd.RunSQL msg, True

Again this provides me with a syntax error. Any suggestions? Sorry if the answer is straightforward to the guns
on this system

Shane

----- John Viescas wrote: -----

Jennifer-

RunSQL requires a string literal or variable. Try this:

Sub append()
DoCmd.RunSQL "INSERT INTO tblMainTable " & _
"( [VALUE], [COUNT], AREA, MEAN, STD ) " & _
"SELECT maya98.VALUE, maya98.COUNT, maya98.AREA, " & _
"maya98.MEAN, maya98.STD FROM maya98;"
End Sub


--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411

Hi. sorry if this is obvious, but I'm new to VBA. I've
tried this myself, but am getting syntax errors and the
arrangement of the brackets in the SQL view is quite
different from that of the stated syntax in the RunSQL
help. Below is the code exactly as copied and pasted. I
presume I need to put brackets around the SQL statement,
but that doesn't help. Any advice?

Sub append()

DoCmd.RunSQL INSERT INTO tblMainTable ( [VALUE], [COUNT],
AREA, MEAN, STD )
SELECT maya98.VALUE, maya98.COUNT, maya98.AREA,
maya98.MEAN, maya98.STD
FROM maya98;
End Sub

 

[Guest]

All

Here is my revised code, still reporting the same syntax error

DoCmd.RunSQL "INSERT INTO Valuation " &
"(PlantNum, Date, FV, IRV) " &
"VALUES (" & stPNum & ", " & valDate &
", " & dblFV & ", " & dblIRV & ");"

 

[Duane Hookom]

Try add the proper delimiters:
DoCmd.RunSQL "INSERT INTO Valuation " & _
"(PlantNum, Date, FV, IRV) " & _
"VALUES (""" & stPNum & """, #" & valDate & _
"#, " & dblFV & ", " & dblIRV & ")"

 

[Guest]

Duane,

Thanks very much for your help. Unfortunately the error persists; here is my new code - what else could
be the problem? Are there any other ways to skin this cat.

DoCmd.RunSQL "INSERT INTO Valuation " & _
"(PlantNum, Date, FV, IRV) " & _
"VALUES (""" & stPNum & """, #" & valDate & _
"#, " & dblFV & ", " & dblIRV & ")"

A few points:
-after 'VALUES(' it is 3 quotation marks, and again before the first pound sign
-should there not be a semicolon after the last closing parenthesis? Have tried it, didn't work
-before this statement is executed I compare the date of a valuation record having matching PlantNum to
ensure no new record is inserted if a valuation is preexisting. its syntax is:

If (recSet2!PlantNum = stPNum) Then
If (recSet2!Date <> valDate) Then

ie. if the current valuation record matches the required PlantNumber and does not match the current date
it is intended to add another record. Not sure if that helps but could point the finger of blame somewhere

Again, stPlantNum is a string, valDate is a Date, dblFV and dblIRV are doubles. The result is the same whether
I attempt to add only the key fields (PlantNum, Date) or all

 

[Guest]

Folks,

Ignore that last post, the following code does the job:

DoCmd.RunSQL "INSERT INTO Valuation " & _
"([PlantNum], [Date], [FV], [IRV]) " & _
"VALUES (""" & stPNum & """, #" & valDate & _
"#, " & dblFV & ", " & dblIRV & ")"

Something as simple as square brackets. Why am I not surprised?!

Shane (aka chops)
----- Shane wrote: -----

Duane,

Thanks very much for your help. Unfortunately the error persists; here is my new code - what else could
be the problem? Are there any other ways to skin this cat.

DoCmd.RunSQL "INSERT INTO Valuation " & _
"(PlantNum, Date, FV, IRV) " & _
"VALUES (""" & stPNum & """, #" & valDate & _
"#, " & dblFV & ", " & dblIRV & ")"

A few points:
-after 'VALUES(' it is 3 quotation marks, and again before the first pound sign
-should there not be a semicolon after the last closing parenthesis? Have tried it, didn't work
-before this statement is executed I compare the date of a valuation record having matching PlantNum to
ensure no new record is inserted if a valuation is preexisting. its syntax is:

If (recSet2!PlantNum = stPNum) Then
If (recSet2!Date <> valDate) Then

ie. if the current valuation record matches the required PlantNumber and does not match the current date
it is intended to add another record. Not sure if that helps but could point the finger of blame somewhere

Again, stPlantNum is a string, valDate is a Date, dblFV and dblIRV are doubles. The result is the same whether
I attempt to add only the key fields (PlantNum, Date) or all

 

[JSand42737]

Folks,

Ignore that last post, the following code does the job:

DoCmd.RunSQL "INSERT INTO Valuation " & _
"([PlantNum], [Date], [FV], [IRV]) " & _
"VALUES (""" & stPNum & """, #" & valDate & _
"#, " & dblFV & ", " & dblIRV & ")"

Something as simple as square brackets. Why am I not surprised?!

On looking closely at it, it is probably only Date that needs the square
brackets, as it is a reserved word. You may run into the same sort of problem
elsewhere in the database due to the use of this, so you may wish to rename the
field to something else.

 

[Guest]

Jon,

Indeed, actually this is the only line of code in the project which has this nomenclature - I did think that could be
a problem. Thanks for the tip though

Shane

----- JSand42737 wrote: -----

Folks,

Ignore that last post, the following code does the job:
DoCmd.RunSQL "INSERT INTO Valuation " & _

"([PlantNum], [Date], [FV], [IRV]) " & _
"VALUES (""" & stPNum & """, #" & valDate & _
"#, " & dblFV & ", " & dblIRV & ")"

Something as simple as square brackets. Why am I not surprised?!

On looking closely at it, it is probably only Date that needs the square
brackets, as it is a reserved word. You may run into the same sort of problem
elsewhere in the database due to the use of this, so you may wish to rename the
field to something else.