Update query troiuble

[David44Coder]

UPDATE Table2 SET Table2.iType = "ABCD"
WHERE (((Table2.iType)="") AND ((Table2.MyID)=9) AND ((Table2.aName)="Costs"));

If iType is Null will this cause a problem? I was trying to include NZ() or IsNull but the query builder kept reported an error.

 

[arnelgp]

you already have 2 "exact" criteria, so i don't think you need to test whether iType is null or not:

UPDATE Table2 SET Table2.iType = "ABCD"
WHERE ((Table2.MyID)=9) AND ((Table2.aName)="Costs");

or

UPDATE Table2 SET Table2.iType = "ABCD"
WHERE ((Table2.MyID)=9)

 

[The_Doc_Man]

In theory, since you are looking for "equals empty string" this will work on any empty strings. However, if you want to includes nulls to be the same as an empty string for iType, you might have written:

UPDATE Table2 SET iType = "ABCD" WHERE ( NZ( iType, "" ) = "" ) AND ( MyID = 9 ) AND ( aName = "Costs" ) ;

Note that you don't need to qualify any of the fields with "Table2" because you never reference another table in this query. Basically, since you named one and only 1 table, there is no place else to look. You don't need the extra parentheses either. But I know you got them from the grid-based query-builder 'cause I think it likes parenthesis a lot. In fact, you could leave out the parentheses around the MyID and aName comparisons and they would still work.

 

[David44Coder]

Many thanks both, great to get that info. And to remove some parenthese is welcome as much easier to read/edit.
I never though to include the extra argument for NZ() so maybe why mine failed.
I have struck a few problems with nulls so though to handle them from the start.
Although the query builder is so good, in my case I usually test there, then build sql in module. Is there any way to copy the query build output into code ?

 

[The_Doc_Man]

Is there any way to copy the query build output into code ?

When done building the query-grid style of query, switch the query to SQL view, which is text that can be cut & pasted.
Then you could take that query into a VBA line such as

CurrentDB.Execute "<<<paste your query string here>>>"

For better debugging, though, I might do it in two steps

strSQL = "<<<paste your query string here>>>"
CurrentDB.Execute strSQL

I would do that in two steps so that if I had problems, I could put a breakpoint in there to see what was in the string before I ran it. Now, you would correctly ask, WHY would I need to do it that way?

In the simple case where it is a fixed string, maybe you wouldn't. BUT if you dynamically BUILT that string based on your query grid but also on something else that had to be added at run time, then for debugging you might wish to split the string definition from the execution step.

 

[Pat Hartman]

When your queries are static, ie, nothing structural changes, you might as well use the point and click of the QBE to avoid silly typos. The three variables do NOT make the query dynamic since they change nothing about the structure. You can just use arguments that get filled at run time.

 

[David44Coder]

Doc_Man if I paste your query shown in Msg 3 as you suggest, I run problems with the chr$(34) characters and the line won't compile.
Although I eventually get query's working I can struggle with the quotes and formatting a literal string Vs a variable.
Are there any rules or methods for making this easier?
Pat, my query is built at run time, fired from a click event and the values it uses are in the active record at the time. I think this means it's not static, but not 100% sure.

 

[arnelgp]

you can try this one:

Dim sql As String
sql = "UPDATE Table2 SET iType = 'ABCD' WHERE ((iType & '') = '') AND ((MyID) = 9) AND ((aName) = 'Costs')"

 

[The_Doc_Man]

SQL allows you to use either the double-quote (", which is CHR$(34)) or single-quote (', which is also the apostrophe) for quoting. You must, of course, keep them balanced. So perhaps:

strSQL = "UPDATE Table2 SET iType = 'ABCD' WHERE ( NZ( iType, '' ) = '' ) AND ( MyID = 9 ) AND ( aName = 'Costs' ) ;"

Specifically, the quoting characters in the NZ section are apostrophes as are the quoting characters for Costs.

 

[Pat Hartman]

my query is built at run time, fired from a click event and the values it uses are in the active record at the time. I think this means it's not static, but not 100% sure.

As I said, variables provided are run time do NOT make a query dynamic.

Select fld1, fld2, fld3
From YourTable
Where fld1 = Forms!yourform!fld1 AND fld2 = Forms!yourform!fld2;

This allows you to use the QBE to build the query and you even get intellisense if you need to refer to form controls.

If you are going to run the query using DAO, then you need to populate the parameters.

Here's an example:

    Dim db                      As DAO.Database
     Dim qd                      As DAO.QueryDef
    
    set db = CurrentDB()               
    Set qd = db.QueryDefs!qCopyAppendQuoteAcc
        qd.Parameters!EnterOldQuoteDetailID = OldQuoteDetailID
        qd.Parameters!EnterNewQuoteDetailID = NewQuoteDetailID
    qd.Execute dbSeeChanges