add a row to query in MS-ACCESS SQL

Question

I'm trying to add to the following query:

    strSQL = "SELECT  fldName, blkName, CDbl(fldValue) " & _
        "FROM dbSecurities2 as S " & _
        "WHERE " & _
        "S.isin='" & Code & "' " & _
        "AND " & _
        "S.fldName='" & fldName & "' "

A row that makes the sum of the fldValue like:

    strSQL = "SELECT  fldName, blkName, CDbl(fldValue) " & _
        "FROM dbSecurities2 as S " & _
        "UNION " & _
        "SELECT Sum(fldValue) AS fldValue " & _
        "WHERE " & _
        "S.isin='" & Code & "' " & _
        "AND " & _
        "S.fldName='" & fldName & "' "

the error is:

Run -time error '3141'. The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect

Define "doesn't work" ... is there an error? If so, what is the error? Part of the trouble is likely because the union select does not have a FROM clause and is not querying the same number of columns. — Paul T.
– Paul T., Commented Dec 4, 2020 at 14:03
You're right the error is Run -time error '3141'. The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect — Giovanni
– Giovanni, Commented Dec 4, 2020 at 14:08
Can you add a small data example to the question (from the first query), along with an example of the desired output? Best to add this info as text and not as pictures. — Paul T.
– Paul T., Commented Dec 4, 2020 at 14:15

Giovanni · Accepted Answer · 2020-12-05 17:34:36Z

1

I found this is working:

   strSQL = "SELECT fldName, blkName, CDbl(fldValue) " & _
        "FROM dbSecurities2 as S " & _
        "WHERE " & _
        "S.isin='" & Code & "' " & _
        "AND " & _
        "S.fldName='" & fldName & "' " & _
        "UNION " & _
        "SELECT '' AS fldName, 'Total' AS Total, Sum(CDbl(fldValue)) " & _
        "FROM dbSecurities2 AS B " & _
        "WHERE " & _
        "B.isin='" & Code & "' " & _
        "AND " & _
        "B.fldName='" & fldName & "' "

answered Dec 5, 2020 at 17:34

Giovanni

616 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gustav · Accepted Answer · 2020-12-05 09:19:32Z

0

This should run as expected:

    strSQL = "SELECT fldName, blkName, CDbl(fldValue) " & _
        "FROM dbSecurities2 AS S " & _
        "WHERE " & _
        "S.isin='" & Code & "' " & _
        "AND " & _
        "S.fldName='" & fldName & "' " & _
        "UNION ALL " & _
        "SELECT TOP 1 "", "Total", Sum(CDbl(fldValue)) " & _
        "FROM dbSecurities2"

If you have Null values, use Nz:

    strSQL = "SELECT fldName, blkName, CDbl(Nz(fldValue, 0)) " & _
        "FROM dbSecurities2 AS S " & _
        "WHERE " & _
        "S.isin='" & Code & "' " & _
        "AND " & _
        "S.fldName='" & fldName & "' " & _
        "UNION ALL " & _
        "SELECT TOP 1 "", "Total", Sum(CDbl(Nz(fldValue, 0))) " & _
        "FROM dbSecurities2"

edited Dec 5, 2020 at 9:19

answered Dec 4, 2020 at 17:29

Gustav

56.5k7 gold badges33 silver badges65 bronze badges

4 Comments

Giovanni Over a year ago

Hi Gustav, I adjusted your expression in the second SELECT as :"SELECT top 1 '' as fldName, 'Total' as blkName, Sum(CDbl(fldValue))" & _ But I get an get the error Data type mismatch in criteria expression... any Idea why?

Giovanni Over a year ago

instead "SELECT top 1 '' as fldName, 'Total' as blkName, fldValue " & _ works and give me an empty line as Total. it seems that the problem is the Sum but I cannot figure out why...

Gustav Over a year ago

_ I get an get the error Data type mismatch in criteria expression... any Idea why?_ Sure. You corrected the correct SQL to invalid SQL. If you have Null values, CDbl will fail; see extended answer, please.

Giovanni Over a year ago

I still have the same error: Data type mismatch in criteria expression. see extended answer

Collectives™ on Stack Overflow

add a row to query in MS-ACCESS SQL

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related