Help with K Dale's Syntax!

  • Thread starter Thread starter Alan Nicoll
  • Start date Start date
A

Alan Nicoll

K Dale gave me the following code in response to a post of
mine:

Accumulated:DSum("[Amount]","TABLE1","[Date]=#"&[Date]&"#
And [TableKey]<="&[TableKey])

I need to modify this, but can't figure out the syntax. I
gather that the #s indicate a date, and the & is to
concatenate strings, but the quotes make no sense to me
and seem very erratic. Yet when I try to add more
criteria, I can't figure out where the quotes go.

Help!

Alan
 
Never mind, I sort of figured it out, though I don't
understand what I'm constructing with these concatenations.
 
Never mind, I sort of figured it out, though I don't
understand what I'm constructing with these concatenations.
-----Original Message-----
K Dale gave me the following code in response to a post of
mine:

Accumulated:DSum("[Amount]","TABLE1","[Date]=#"&[Date]&"#
And [TableKey]<="&[TableKey])

I need to modify this, but can't figure out the syntax. I
gather that the #s indicate a date, and the & is to
concatenate strings, but the quotes make no sense to me
and seem very erratic. Yet when I try to add more
criteria, I can't figure out where the quotes go.
Click to expand...
Click to expand...

What you're doing for the third argument of DSum (or any Domain
function) is building a valid SQL WHERE clause without the word WHERE.
You can even go into the Query Builder and create a query which
returns the desired records, and go to SQL view to see how the WHERE
clause should look.

To build it, you splice together string literals - stuff that's the
same for all values - and variables, such as table fields. In this
case the final value you want would be something like

[Date] = #10/21/2003# AND [TableKey] <= 3218

(assuming those values for [Date] and [TableKey] of course).

To get this, you need to string together the pieces:

"[Date] = #" - a string constant
[Date] - the value in the field [Date]
"# AND [TableKey] =" - another constant
[TableKey] - the value in the field [TableKey]

Things get really snarky with quotes when you have a Text field to
consider, since Text field criteria must be delimited by quotes -
either ' or " - and you need to have a quote character INSIDE a quoted
string. If TableKey were Text instead of numeric you'ld need either

"# AND [TableKey] = '" & [TableKey] & "'"

to delimit it with ' (so it would look like [TableKey] = 'A31BK' for
instance); or, if the text field might contain apostrophes, you need
to use TWO " characters within the string literal delimited by ":

[TableKey] = """ & [TableKey] & """"

I call this phenomenon "doublequote doubletalk" because it can make
the string look REALLY confusing! If you analyse it piece by piece,
though, it's logical.
 
Glad you figured it out, and John Vinson gives some good
info.

Look in Help under Domain Aggregate Functions for more
info. There are 3 arguments to the function and they are
all string arguments. Strings need to be in quotes if
typed literally, or can be variables like field values, or
can be a combination of the two. Field names, in Access,
are specified by square braces [].

So in my DSum function, first comes the name of the field
to sum, which is your Amount field, or [Amount]. Next
comes the table name. Those are easy. After that I am
constructing a string to tell Access the condition that
specifies which records need to be included when adding
together the Amounts. I need to say in Access terms "When
the Field called Date is equal to (insert date here) and
the field called TableKey is less than (insert current
TableKey value here)."

What I put inside the quotes is setting up the condition
to be checked (and this does not change from record to
record, so it can all be typed in literally). However,
the actual values of the Date and TableKey will change
from record to record, so I need to insert them into the
sentence I am typing. When that happens, I exit
the 'quoted' part of the string I am typing and I append
(with '&') the field name that contains the value to use
for the current record, which is inside the square braces.

And you are correct: to get Access to understand a date,
you surround it with the #'s.

A very brief lesson in syntax but I hope it clears up some
of the mysteries.

Ken Dales
-----Original Message-----
Never mind, I sort of figured it out, though I don't
understand what I'm constructing with these concatenations.
-----Original Message-----
K Dale gave me the following code in response to a post of
mine:

Accumulated:DSum("[Amount]","TABLE1","[Date]=#"&[Date] &"#
And [TableKey]<="&[TableKey])

I need to modify this, but can't figure out the syntax. I
gather that the #s indicate a date, and the & is to
concatenate strings, but the quotes make no sense to me
and seem very erratic. Yet when I try to add more
criteria, I can't figure out where the quotes go.

Help!

Alan
.
Click to expand...
.
Click to expand...
 
Back
Top