Dcount Question

[Imyls]

I'm trying to use dcount() as countif() from excel and I'm not having much luck.

What I want to do is to report how many times a record's respective Layer 2 occurs throughout the database in a query.

For the Frequency Column the formula I have is:
Freq: dcount("*", "Repeat Address", "Layer 2" = [Layer 2])

"Repeat Address" is a Query
"Layer 2" is a text string that looks like "123, abc, 12345"


For the life of me I can't figure out the right arrangements of quotation marks .



Any help be greatly appreciated

 

[pbaldy]

This should help:

http://www.mvps.org/access/general/gen0018.htm

 

[Imyls]

Just had a HTTP Error 503. The service is unavailable.

 

[pbaldy]

Worked for me; see attached.

 

[Imyls]

So now I have rewritten in Query Field

Frequency Check: DCount("Layer 2","Repeat Address","Layer 2 ='[Layer 2]'")

The error I have is: Syntax error (missing operator) in query expression 'Count(Layer2)'.

And then when I close that another message box appears saying: Uknown

 

[pbaldy]

You'd use the form syntax, but use your field name instead of a form reference. Also, your names will need to be bracketed due to the inadvisable spaces.

 

[Imyls]

Sorry I'm a total noob and just started using access to help a charity. (I'm an engineer who's above average with Excel so I thought I could help them with an access database)

I'm a lil confused by what you said

Am I correct in believing that only the criteria part of the expression is wrong?

 

[pbaldy]

Try

FrequencyCheck: DCount("[Layer 2]","[Repeat Address]","[Layer 2] ='" & [Layer 2] & "'")

 

[Imyls]

Perfect thanks a bunch, you deserve an award or something

 

[pbaldy]

Happy to help!

 

[Imyls]

How would you Dcount date and times that are less than records date and time?

Right now the format is General Date and the field is date and time

Expr1: DCount("[Visiting_Time_N_Date] ","[qry_Account_Pick_Ups_Order_List]","#[Visiting_Time_N_Date]# > #[Visiting_Time_N_Date]#")

 

[pbaldy]

You'd use the same format you used before, substituting # for ' (just the ', not the "). You've got the # all over the place. The link above also demonstrates it.

 

[Imyls]

The two #'s around the &'s

DCount("[Visiting_Time_N_Date]","[qry_Account_Pick_Ups_Order_List]","[Visiting_Time_N_Date] =#" & [Visiting_Time_N_Date] & "#")

What do they represent?

 

[pbaldy]

From the link:

For dates:
DLookup("FieldName" , "TableName" , "Criteria= #date#")

So

DCount("[Visiting_Time_N_Date]","[qry_Account_Pick_Ups_Order_List]","[Visiting_Time_N_Date] =#" & [Visiting_Time_N_Date] & "#")

ends up evaluating to

DCount("[Visiting_Time_N_Date]","[qry_Account_Pick_Ups_Order_List]","[Visiting_Time_N_Date] =#8/30/12#")

 

[Imyls]

I apologize I meant to say what do the &'s mean.

Also how does access know to look through the all the values in a given field versus the value in the record.

Lastly if I wanted to count how many times a1=b1 is that a dCount function or do I sum a query?

 

[pbaldy]

& is a concatenation operator. It joins things together, in this case literal strings and interpreted values.

If I'm interpreting the question correctly, that's how the function was created, plus it's how relational databases are structured. You have multiple records, not multiple fields within a record.

Either would work. In essence, a domain aggregate function like DCount is a query.

DCount("[Visiting_Time_N_Date]","[qry_Account_Pick_Ups_Order_List]","[Visiting_Time_N_Date] =#8/30/12#")

produces the same result as

SELECT Count([Visiting_Time_N_Date]) As HowMany
FROM [qry_Account_Pick_Ups_Order_List]
WHERE [Visiting_Time_N_Date] =#8/30/12#