Syntax SQL Help! (A2K)

Topic

New Reply

A few months ago I copied the SQL of a query designed in the query grid for use as the record source of a combo box. I had a game with the syntax, but eventually found the correct syntax for the SQL.

I am going through the same process again!

I can

Reply | Quote

Replies

The University of Bristol has a pretty good set of Access on-line tutorials to teach students. In particular for the Access dialect of SQL I would try:

http://www.cse.bris.ac.uk/~ccmjs/acc97-r5.htm%5B/url%5D

…but the rest of the materials are worth looking at, too. The document referenced uses an example database that you can also download. Good Luck!

Reply | Quote

Thanks for the prompt reply Dean. And that Bristol site is pretty good.

What I need help with is the syntax of the following.

This is the SQL of a query generated by the query grid. I want to use it as a record source in ADO.

UPDATE tbl_Old_MatlPitXpp INNER JOIN tbl_New_MatlPitXpp ON tbl_Old_MatlPitXpp.AddOrder = tbl_New_MatlPitXpp.AddOrder SET tbl_New_MatlPitXpp.XPitPrX = [tbl_New_MatlPitXpp]![XPitPrX]+[Forms]![frmPriceInc]![txtPriceChg], tbl_New_MatlPitXpp.IncreaseApplied = True, tbl_New_MatlPitXpp.QuoDate = Date()
WHERE (((tbl_New_MatlPitXpp.CODE)=[Forms]![frmPriceInc]![cboPits]) AND ((tbl_Old_MatlPitXpp.IncreaseApplied)=True));

The part [Forms]![frmPriceInc]![txtPriceChg] refers to a text box on the form which would hold data like 0.80.

I think you do something like

Reply | Quote

What do you mean, you want to use it as a recordsource in ADO? Your SQL is for an update query, and action queries don’t return recordsets. If you mean you’re trying to get the correct SQL to execute an update query, that’s different.

If you want to execute the query from code, you could build the SQL string like this (I use aliases to improve readability. You can use them or not):

strSQL = “UPDATE DISTINCTROW tbl_Old_MatlPitXpp As Old INNER INNER tbl_New_MatlPitXpp As New ON Old.AddOrder = New.AddOrder ” _
& “SET New.XPitPrX = New.XPitPrX + ” & [Forms]![frmPriceInc]![txtPriceChg] & ” , ” _
& “New.IncreaseApplied = True, New.QuoDate = #” & Date() & “# ” _
& “WHERE (((New.CODE)=” & [Forms]![frmPriceInc]![cboPits] & “) AND ” _
& “((Old.IncreaseApplied)=True));”

This assumes that CODE is a numeric field and that this code is not being run from within frmPriceInc. It it were built and executed from within that form, you would use the Me operator to refer to the controls. If the IncreaseApplied field is a Yes/No field, then don’t put quotes around the True values or it will definitely fail.

Once you built the SQL string, you could assign it to the CommandText property of the Command object and execute it.

There are quite a few books on SQL, including a new one specifically oriented toward Jet SQL and SQL Server. That book is “SQL: Access to SQL Server”, by Harkins and Reid.

Reply | Quote