Tip Multi criteria aggregate functions (1 Viewer)

[Isskint]

A little tip that may save some minutes, hours or even longer, not to mention getting a headache.


In a recent post - http://www.access-programmers.co.uk/forums/showthread.php?t=228412 -kasmax asks about a common issue that many people have encountered (and will again I am sure). When i encouter the need for multiple criteria in a Dsum, Dcount, Dlookup etc I do the following:

1. Record each criteria seperately first (to check for syntax and data return accuracy)
   1. Dcount("[Field]","[tblTableName]","[Field1]='" & [fText] & "'")
   2. Dcount("[Field]","[tblTableName]","[Field2]=" & [fValue] & "")
   3. Dcount("[Field]","[tblTableName]","[Field3]=#" & [fDate] & "#")
   4. etc
2. Write the multi criteria function as Dcount("[Field]","[tblTableName]","((A) AND (B) AND (C))") etc
3. Copy criteria for each part and paste over A, B, C etc
   1. "[Field1]='" & [fText] & "'"
   2. "[Field2]=" & [fValue] & ""
   3. "[Field3]=#" & [fDate] & "#"
4. Dcount("[Field]","[tblTableName]","(("[Field1]='" & [fText] & "'") AND ("[Field2]=" & [fValue] & "") AND ("[Field3]=#" & [fDate] & "#"))")

This way you know the syntax is correct for each part (and that they return the expected values)

 

[MarkK]

If it really matters, and you know a little VBA, you can also use a parameterized QueryDef, since then all delimiters are handled automatically, and your data can even contain delimiters. Consider...

  dim dbs as dao.database
  dim qdf as dao.querydef

  set dbs = currentdb
[COLOR="Green"]  'create a temp querydef[/COLOR]
  set qdf = dbs.createquerydef("", _
    "SELECT Count(*) " & _
    "FROM tblTableName " & _
    "WHERE Field1 = prm0 " & _
      "AND Field2 = prm1 " & _
      "AND Field3 = prm3;"

  with qdf
[COLOR="Green"]    'assign values to the parameters you defined in the query SQL, above
    'noting that all datatyping and delimiting is handled by the system[/COLOR]
    .parameters(0) = fText
    .parameters(1) = fValue
    .parameters(2) = fDate
[COLOR="Green"]    'open a recordset and retrieve the value of the first field[/COLOR]
    debug.print .openrecordset.fields(0)
  end with

Using the querydef you can have data like O'Malley or 2x8'-2"x4", with apostophes and double quotes that might break delimited solutions.

Cheers,
Mark

 

[Isskint]

hmmm excelent idea and i like the way it will handle delimited issues.