Displaying Month&Year

  • Thread starter Thread starter NotGood@All
  • Start date Start date
N

NotGood@All

I’m using the following code to extract the number of things that happened 4
months ago. I would also like to display the month & year, i.e. Apr 08 –
“10 occurrences†can someone help me with this??

=DCount("DateAccept","qryStatistics","DateDiff('m', DateAccept, Now)=4")
 
=Format(DateAccept, "mmm yy") & " - " &
DCount("DateAccept","qryStatistics","DateDiff('m', DateAccept, Now)=4") & "
occurrence" & IIf(DCount("DateAccept","qryStatistics","DateDiff('m',
DateAccept, Now)=4")=1,"","s")
 
Ken, thank you very much. I don't understand the iff statement and what is "
,"s")" for?
 
The IIf function is a way to do an If...Then...Else type of logic in one
expression. I added it to the expression so that the word "occurrence" is
not made plural ("occurrences") when there is just one occurrence. It's a
small thing, but in my opinion it makes your database "look smarter" when
you don't say "1 occurrences".

If you don't want that extra "touch", then the expression can be just this:

=Format(DateAccept, "mmm yy") & " - " &
DCount("DateAccept","qryStatistics","DateDiff('m', DateAccept, Now)=4") & "
occurrences"
 
I thought that was what you were doing but I couldn’t see it. I tried using
both sets of code, both give me the “#name?†error. I compared the spelling
of everything and found no errors, so now I am completely confused. Help
said that that error means a source is missing or something is misspelled, I
can't find any of that, what else should I look for??

Thank You Again!
 
Sorry, my error:. I forgot that the DateAccept field is not on the form, but
is in that query. This should work, assuming that you'll always have at
least one record in the query that meets the criterion expression
"DateDiff('m', DateAccept, Now)=4":

=Format(DLookup("DateAccept","qryStatistics", "DateDiff('m', DateAccept,
Now)=4"), "mmm yy") & " - " &
DCount("DateAccept","qryStatistics","DateDiff('m', DateAccept, Now)=4") & "
occurrence" & IIf(DCount("DateAccept","qryStatistics","DateDiff('m',
DateAccept, Now)=4")=1,"","s")
 
Back
Top