DSUM with multiple variables

[FireyColin]

It's me again. I just can't seem to get this right. What's wrong with this:

strSQL = DSum("[Consurer]", "share_pct", "[policy_no] = & 'strPolicy' & AND & '[version_no]' & = strVer")

When I run it, I get the message: syntax error(missing operator) in query expression "[policy_no] = & 'strPolicy' & AND & '[version_no]' & = strVer"

[Ron Paii]

"FireyColin" <colin....@btopenworld.com> wrote in message
news:96100e21-4868-42a2...@googlegroups.com...

Your are confusing "&" which concatenates strings and logical "AND".
Assuming [Policy_no] and [version_no] are text fields. you need to compare
them to strings

strSQL = DSum("[Consurer]", "share_pct", "[policy_no] = """ & strPolicy &

""" AND [version_no] = """ & strVer & """")


To DSum, the criteria would look like the following; assuming strPolicy =
Policy1 and strVer = Version2.

[policy_no] = "Policy1" AND [version_no] = "Version2"

[FireyColin]

On Monday, October 22, 2012 3:36:45 PM UTC+1, Ron Paii wrote:
> "FireyColin" wrote in message
>
> news:96100e21-4868-42a2-a9cb-fbefade1a017

>
> > It's me again. I just can't seem to get this right. What's wrong with
>
> > this:
>
> >
>
> > strSQL = DSum("[Consurer]", "share_pct", "[policy_no] = & 'strPolicy' &
>
> > AND & '[version_no]' & = strVer")
>
> >
>
> > When I run it, I get the message: syntax error(missing operator) in query
>
> > expression "[policy_no] = & 'strPolicy' & AND & '[version_no]' & = strVer"
>
>
>
> Your are confusing "&" which concatenates strings and logical "AND".
>
> Assuming [Policy_no] and [version_no] are text fields. you need to compare
>
> them to strings
>
>
>
> strSQL = DSum("[Consurer]", "share_pct", "[policy_no] = """ & strPolicy &
>
> """ AND [version_no] = """ & strVer & """")
>
>
>
>
>
> To DSum, the criteria would look like the following; assuming strPolicy =
>
> Policy1 and strVer = Version2.
>
>
>
> [policy_no] = "Policy1" AND [version_no] = "Version2"

Thanks Ron

But still no joy. Don't understand the triple and quadruple double-inverted commas.

Colin

[Bob Quintal]

FireyColin <colin....@btopenworld.com> wrote in
news:96100e21-4868-42a2...@googlegroups.com:

Well, first of all, DSum() is not a sql statement, it is a function
that returns a value (number).

Second, the order of DSum()'s parameters are
1: the field you wish to sum
2: the table or query in which that field exists
3: the criteria to filter the table or query

I suspect you have reversed the first two parameters in your
example.

Thirdly, the criteria needs the value of your strPolicy and strVer,
not their names.

You get that by using the concatenation operator differently than
you show. A single parameter would be .
"[policy_no] = " & strPolicy & " .... which works for a number but
not a string. The string needs quotes around the variable.

To force a double-quote into the strin, it needs to be doubled,
which gives "[policy_no] = """ & strPolicy & """ ....

You then do your second variable

"[policy_no] = """ & strPolicy & """ AND [version_no] = """ & strVer
& """

You then need to finish off the string with its ending doublequote
giving you four doublequotes in a row.





--
Bob Q.
PA is y I've altered my address.

[Phil]

I I sympathise with Colin's frustration with triple quotes. Personally I
never use them. Instead I use the ASCII equivalent of Chr$(34)
So the Criteria part of the DSum would be
"Policy_No" = & Chr$(34) & StrPolicy & Chr$(34) & "AND Version_No = " &
Chr$(34) & StrVer & Chr$(34)

On one of the posts, Colin had the line

strSQL = DSum("[Consurer]", "share_pct", "[policy_no] = & 'strPolicy' & AND &
'[version_no]' & = strVer")

If this DSum function is part of an SQL statement the matter gets further
complicated as you can't have double quotes within the string. They have to
be replaced by single quotes

So I thing that his expression would be (I agree he has almost certainly got
the thing he is summing and his table the wrong way round)

strSQL = "DSum('share_pct', 'Consurer', 'Policy_No' = & Chr$(34) & StrPolicy
& Chr$(34) & 'AND Version_No = ' & Chr$(34) & StrVer & Chr$(34))"

Incidentally, you only need square brackets round field names with spaces in
them [Policy No], not single named field name Policy_No The other exception
is when you use a reserved word (bad practice) like a field name called Date
which would always have to be referred to as [Date]

Phil

[Ron Paii]

"FireyColin" <colin....@btopenworld.com> wrote in message

news:fdd29767-c4a7-432c...@googlegroups.com...

Those are not commas, they are double quotes.

As for the triple and quadruple double quotes, you are using VBA to
concatenating strings to pass a single string to a function. Those strings
need to contain quotes to identify string criteria to be used by the SQL
command submitted by DSUM. (2) double quotes within a string is interpreted
by VBA as (1) double quote. The 3rd double quote is closing the string. The
ending quadruple double quote is appending a single double quote to the end
of the string which is required by the SQL command that DSUM will submit.

If you want to practice concatenating strings in VBA, use the debugger

Example: Type the following into the debugger window and observe the output

?"( String1 )" & "( String2 )"

?"( ""String1"" )" & "( ""String2"" )"

[Ron Paii]

"Phil" <ph...@stantonfamily.co.uk> wrote in message
news:k65loo$b2u$1...@speranza.aioe.org...

To eliminate all the string issues in dLookup, Dsum ect. I modified Allen
Browne's abro...@bigpond.net.au functions to allow parameter queries.
Following is pLookup version of eLookup


Public Function pLookup(strExpr As String, strDomain As String, strCriteria
As String, _
strOrderClause As String, ParamArray vaParameters()
As Variant)

On Error GoTo Err_pLookup
'Purpose: Modify elookup
'Arguments: strExpr Column to return
' strDomain Query
' strCriteria Extra search criteria
' strOrderClause Extra order by, to return specific if more
than 1 record matches
' ParamArray List of query parameter values
'Return: Value of the strExpr if found, else Null or #Error.

'Example:
'1. To search the saved parameter query
' pLookup("[ID]", "qryContactLookup", "","", "TREEO'S SERVICES")

'2. Search with built up query,
' note: strCriteria and strOrderClause will be ignored if PARAMETERS
is at start of strDomain
' pLookup("[ID]", "PARAMETERS ContactName Text ( 255 );
' SELECT TOP 1 tblContact.ID FROM tblContact
' WHERE tblContact.Name1=[ContactName];",
vbNullString, vbNullString, "TREEO'S SERVICES")
'Note: Requires a reference to the DAO library.

Dim db As Database
Dim rs As Recordset
Dim qryMyQuery As QueryDef
Dim SQLStg As String
Dim i As Integer

If UCase(Left$(strDomain, 10)) = "PARAMETERS" Then
SQLStg = strDomain
Else
'Build the SQL string.
SQLStg = "SELECT TOP 1 " & strExpr & " FROM " & strDomain

If strCriteria <> vbNullString Then

SQLStg = SQLStg & " WHERE " & strCriteria

End If
If strOrderClause <> vbNullString Then

SQLStg = SQLStg & " ORDER BY " & strOrderClause

End If
SQLStg = SQLStg & ";"
End If

'Lookup the value.
Set db = dbLocal()
Set qryMyQuery = db.CreateQueryDef("", SQLStg) ' Create a temp query
from SQL string
For i = 0 To UBound(vaParameters())
qryMyQuery.Parameters(i) = vaParameters(i)
Next

Set rs = qryMyQuery.OpenRecordset(dbOpenForwardOnly)

If rs.RecordCount = 0 Then
pLookup = Null
Else
pLookup = rs(0)
End If
rs.Close

Exit_pLookup:
Set rs = Nothing
Set db = Nothing
Set qryMyQuery = Nothing
Exit Function

Err_pLookup:
Debug.Print Err.Description, vbExclamation, "pLookup Error " &
Err.Number
If Err.Number < 0& Or Err.Number > 65535 Then 'Valid range for
CVErr()
pLookup = Null ' CVErr(5) 'Out of range.
Else
pLookup = Null ' CVErr(Err.Number)
End If
Resume Exit_pLookup

End Function

[FireyColin]

Thank you Phil

It tried the syntax below that you suggested but get a compile error 'Expected expression'; with the single quote before the 'AND' highlighted.

strSQL = "DSum('share_pct', 'CoInsurer', 'Policy_No' = & Chr$(34) & StrPolicy

& Chr$(34) & 'AND Version_No = ' & Chr$(34) & StrVer & Chr$(34))"

You and Bob were right about my schoolboy error reversing the field and table parameters. Itied again with treble and quadruple double-quotes as per Bob's advice with no joy. I must confess I am confused about that and completely bewildered by Phil's last advice in this string.

Colin

[Douglas J Steele]

Add a space between ' and AND.

"FireyColin" wrote in message
news:cce34de8-3537-4547...@googlegroups.com...

[FireyColin]

THanks Douglas

Tred that, same result

Regards Colin

[Joan Wild]

Douglas J Steele wrote:

> Add a space between ' and AND.
>

Also the ' after Policy_No is misplaced - should be after the =

strSQL = "DSum('share_pct', 'CoInsurer', 'Policy_No = ' & Chr$(34) &
StrPolicy & Chr$(34) & ' AND Version_No = ' & Chr$(34) & StrVer &
Chr$(34))"

Joan

[FireyColin]

Thank you Joan

I tried the syntax below that you suggested but get a compile error
Expected end of statement with strpolicy highlighted.

strSQL = "DSum('share_pct', 'CoInsurer', 'Policy_No = ' & Chr$(34) & " StrPolicy & Chr$(34) & ' AND Version_No = ' & Chr$(34) & StrVer & Chr$(34))"

I don't know why this is so difficult.

Colin

[Phil]

Lets do it by stages

Past this into the immediate window and see if you get the correct answer

Print DSum(�share_pct�, �CoInsurer�, �Policy_No = � & Chr$(34) & StrPolicy &

Chr$(34) & � AND Version_No = � & Chr$(34) & StrVer & Chr$(34))

Assuming you do, convert all the double quotes to single quotes and the sql
statement becomes

strSQL = "DSum('share_pct', 'CoInsurer', 'Policy_No = ' & Chr$(34) &
StrPolicy & Chr$(34) & ' AND Version_No = ' & Chr$(34) & StrVer & Chr$(34))"

Phil

[Joan Wild]

That is not what I suggested. Mine had no " before strPolicy. Try
again.

[Bob Quintal]

FireyColin <colin....@btopenworld.com> wrote in
news:f0ddb304-d104-40c1...@googlegroups.com:

That is because you have a double-quote symbol to the left of strPolicy
that does not belong there.

[Phil]

On 23/10/2012 14:54:05, "Ron Paii" wrote:
>
>
Nice Ron, but it doesn't, as far as I can see, solve the problem of triple
quotes of Chr$(34) in straightforward D or ELookup()

Incidently has anyone recently ckeched the difference in speed between
DLookup & ELookup? I have a feeling that DLookup has improved consideably
since AK2000

Phil