Repost:DSum w/multiple criteria (Invalid Syntax)

[Robin]

Hello,

I'm trying to write a DSum formula with 4 criteria. I've tried several
variations but with no luck. I read Allen Brown's web page on use of quotes
but can't quite apply it. Any help would be appreciatied.

My fifth and latest attempt was (all criteria fields are text):
DSum ("[CSActualHrs]" , "ClientSegment",
" [ClientSegment]![ClientID] = """ & Forms![fmCS]![ClientID] """ AND
[ClientSegment]![EngagementID] = """ & Forms![fmCS]![EngagementID] & """
AND [ClientSegment]![EngagementYr] = """ & Forms![fmCS]![EngagementYr] & """
AND [ClientSegment]![AssignmentID] = """ & Forms![fmCS]![AssignmentID] & """")

I've used the 3-quote and 4-quote combos on some DLookups with fewer
criteria that work.

The error is: "The expression you entered contains invalid syntax" "You
may have entered a comma without a preceding value or identifier"
And I cannot leave the control with the formula still in it.

-The criteria form fields are all bound, text boxes. (No combos or other
types) No criteria fields are null and all are part of the Primary Key for
the Client Segment table.

-The CSActualHrs is a bound numeric field, Single/Fixed 2

Thank you for any help,
Robin

 

[John W. Vinson]

Hello,

I'm trying to write a DSum formula with 4 criteria. I've tried several
variations but with no luck. I read Allen Brown's web page on use of quotes
but can't quite apply it. Any help would be appreciatied.

My fifth and latest attempt was (all criteria fields are text):
DSum ("[CSActualHrs]" , "ClientSegment",
" [ClientSegment]![ClientID] = """ & Forms![fmCS]![ClientID] """ AND
[ClientSegment]![EngagementID] = """ & Forms![fmCS]![EngagementID] & """
AND [ClientSegment]![EngagementYr] = """ & Forms![fmCS]![EngagementYr] & """
AND [ClientSegment]![AssignmentID] = """ & Forms![fmCS]![AssignmentID] & """")

I've used the 3-quote and 4-quote combos on some DLookups with fewer
criteria that work.

The error is: "The expression you entered contains invalid syntax" "You
may have entered a comma without a preceding value or identifier"
And I cannot leave the control with the formula still in it.

-The criteria form fields are all bound, text boxes. (No combos or other
types) No criteria fields are null and all are part of the Primary Key for
the Client Segment table.

-The CSActualHrs is a bound numeric field, Single/Fixed 2

Thank you for any help,
Robin

Wgat are the datatypes of ClientID, EngagementID, EngagementYr and
AssignmentID? I'm guessing they're all numbers, which would require that there
be NO delimiters. You also don't need the [ClientSegment]! qualifier in the
field since it's unambiguous.

Try

DSum ("[CSActualHrs]" , "ClientSegment",
"[ClientID] = " & Forms![fmCS]![ClientID] &
" AND [EngagementID] = " & Forms![fmCS]![EngagementID] &
" AND [ClientSegment]![EngagementYr] = " & Forms![fmCS]![EngagementYr] &
" AND [ClientSegment]![AssignmentID] = " & Forms![fmCS]![AssignmentID])

 

[Robin]

John,

Thanks for that, I believe I'm a little closer. The formula is now
acceptable to Access (no more syntax error!) but does not return the
appropriate sum. When I run the query I get a message that says "Cannot find
the name ADAMCL you entered in your expression." ADAMCL is the ClientID.
ALL FIELDS IN THE CRITERIA ARE TEXT, NOT NUMERIC.

I'll keep trying using your example as a starting point since Access likes
yours more than mine!

Thank you for your help.
Robin

Hello,

I'm trying to write a DSum formula with 4 criteria. I've tried several
variations but with no luck. I read Allen Brown's web page on use of quotes
but can't quite apply it. Any help would be appreciatied.

My fifth and latest attempt was (all criteria fields are text):
DSum ("[CSActualHrs]" , "ClientSegment",
" [ClientSegment]![ClientID] = """ & Forms![fmCS]![ClientID] """ AND
[ClientSegment]![EngagementID] = """ & Forms![fmCS]![EngagementID] & """
AND [ClientSegment]![EngagementYr] = """ & Forms![fmCS]![EngagementYr] & """
AND [ClientSegment]![AssignmentID] = """ & Forms![fmCS]![AssignmentID] & """")

I've used the 3-quote and 4-quote combos on some DLookups with fewer
criteria that work.

The error is: "The expression you entered contains invalid syntax" "You
may have entered a comma without a preceding value or identifier"
And I cannot leave the control with the formula still in it.

-The criteria form fields are all bound, text boxes. (No combos or other
types) No criteria fields are null and all are part of the Primary Key for
the Client Segment table.

-The CSActualHrs is a bound numeric field, Single/Fixed 2

Thank you for any help,
Robin

Wgat are the datatypes of ClientID, EngagementID, EngagementYr and
AssignmentID? I'm guessing they're all numbers, which would require that there
be NO delimiters. You also don't need the [ClientSegment]! qualifier in the
field since it's unambiguous.

Try

DSum ("[CSActualHrs]" , "ClientSegment",
"[ClientID] = " & Forms![fmCS]![ClientID] &
" AND [EngagementID] = " & Forms![fmCS]![EngagementID] &
" AND [ClientSegment]![EngagementYr] = " & Forms![fmCS]![EngagementYr] &
" AND [ClientSegment]![AssignmentID] = " & Forms![fmCS]![AssignmentID])

 

[John W. Vinson]

John,

Thanks for that, I believe I'm a little closer. The formula is now
acceptable to Access (no more syntax error!) but does not return the
appropriate sum. When I run the query I get a message that says "Cannot find
the name ADAMCL you entered in your expression." ADAMCL is the ClientID.
ALL FIELDS IN THE CRITERIA ARE TEXT, NOT NUMERIC.

Are they really text? Or are they Lookup Fields (which are concealed numbers
which just appear to be text)?

If they ARE text then you do need the extra quotemarks; and if there is in
fact a field named ADAMCL in the table, there's something really wrong. Please
post the actual fieldnames and datatypes of your table and copy and paste the
entire VBA subroutine that you're actually using - I'm worried that there
might be transcription errors in the process.

 

[Robin]

Hello John,

No field named ADAMCL, that's a value in the field! Seemed odd it would
appear in an error message.

Yes, I'm sure they are all text fields as follows:

FieldName ClientID
Datatype TEXT Lenght 8
Example of values: ADAMCL, ANDEJT, ALLEMM...

FieldName EngagementID
Datatype TEXT Lenght 8
Example of values: AUDIT, COMP, REVW...

Fieldname EngagementYr
Datatype TEXT Lenght 4
Example of values: 2007, 2008 (But Text, not Numeric)

FieldName AssignmentID
Datatype TEXT Lenght 8
Example of values: INTRV, PREP, REV1, REV2...

I thought I needed the extra quotes, I'm just not able to get them in the
right place and in the right number.

Thanks,
Robin

 

[John W. Vinson]

Hello John,

No field named ADAMCL, that's a value in the field! Seemed odd it would
appear in an error message.

Yes, I'm sure they are all text fields as follows:

FieldName ClientID
Datatype TEXT Lenght 8
Example of values: ADAMCL, ANDEJT, ALLEMM...

FieldName EngagementID
Datatype TEXT Lenght 8
Example of values: AUDIT, COMP, REVW...

Fieldname EngagementYr
Datatype TEXT Lenght 4
Example of values: 2007, 2008 (But Text, not Numeric)

FieldName AssignmentID
Datatype TEXT Lenght 8
Example of values: INTRV, PREP, REV1, REV2...

I thought I needed the extra quotes, I'm just not able to get them in the
right place and in the right number.

OK, since these field values don't appear to contain any names which might
contain an apostrophe, let's simplify things by using ' as a text criterion
delimiter rather than ":

Dim strCrit As String
strCrit = "[ClientID] = '" & Forms![fmCS]![ClientID] & _
"' AND [EngagementID] = '" & Forms![fmCS]![EngagementID] & _
"' AND [EngagementYr] = '" & Forms![fmCS]![EngagementYr] & _
'" AND [AssignmentID] = '" & Forms![fmCS]![AssignmentID] & "'"
DSum ("[CSActualHrs]" , "ClientSegment", strCrit)

You can step through the code and verify that strCrit is being set to
something like

[ClientID] = 'ADAMCL' AND [EngagementID] = 'COMP' AND [EngagementYr] = '2007'
AND [AssignmentID] = 'REV1'

which should work. If it doesn't, post back with the actual result.

 

[Robin]

John,

That works! (Well, after I flipped one single/double quote combo that was
backwards.) Thank you very much.

You taught me something else...I never quite understood why some people used
single/double quote combos and some always used double quote combos. You're
statement about no field values using apostrophes clued me in. (And you were
right in that the values in those fields will never contain apostrophes.)

Thanks again,
Robin

Hello John,

No field named ADAMCL, that's a value in the field! Seemed odd it would
appear in an error message.

Yes, I'm sure they are all text fields as follows:

FieldName ClientID
Datatype TEXT Lenght 8
Example of values: ADAMCL, ANDEJT, ALLEMM...

FieldName EngagementID
Datatype TEXT Lenght 8
Example of values: AUDIT, COMP, REVW...

Fieldname EngagementYr
Datatype TEXT Lenght 4
Example of values: 2007, 2008 (But Text, not Numeric)

FieldName AssignmentID
Datatype TEXT Lenght 8
Example of values: INTRV, PREP, REV1, REV2...

I thought I needed the extra quotes, I'm just not able to get them in the
right place and in the right number.

OK, since these field values don't appear to contain any names which might
contain an apostrophe, let's simplify things by using ' as a text criterion
delimiter rather than ":

Dim strCrit As String
strCrit = "[ClientID] = '" & Forms![fmCS]![ClientID] & _
"' AND [EngagementID] = '" & Forms![fmCS]![EngagementID] & _
"' AND [EngagementYr] = '" & Forms![fmCS]![EngagementYr] & _
'" AND [AssignmentID] = '" & Forms![fmCS]![AssignmentID] & "'"
DSum ("[CSActualHrs]" , "ClientSegment", strCrit)

You can step through the code and verify that strCrit is being set to
something like

[ClientID] = 'ADAMCL' AND [EngagementID] = 'COMP' AND [EngagementYr] = '2007'
AND [AssignmentID] = 'REV1'

which should work. If it doesn't, post back with the actual result.

 

[John W. Vinson]

That works! (Well, after I flipped one single/double quote combo that was
backwards.) Thank you very much.

You're welcome. Ploughing through mazes of quoted quotes is indeed a pain...
and my example was of course untested, sorry about the typo!

You taught me something else...I never quite understood why some people used
single/double quote combos and some always used double quote combos. You're
statement about no field values using apostrophes clued me in. (And you were
right in that the values in those fields will never contain apostrophes.)

I'll use the singlequote method whenever possible, as it is just simpler to
read and count quotes.