Solved Dcoount (1 Viewer)

[sbaud2003]

I want to count the no of records in QPENSIONER_NEW TBL WHERE YR IS YEAR AND MN IN NAME OF MONTH
BUT I AM GETTING ERROR
Me.TXTCASE.value = DCount("REC_ID", "QPENSIONER_NEW", "YR = Forms!SERVICE_FORM!CYEAR " And MN = Forms!SERVICE_FORM!CMONTH)

 

[bob fitz]

Perhaps:
Me.TXTCASE.value = DCount("REC_ID", "QPENSIONER_NEW", "YR = '" & Forms!SERVICE_FORM!CYEAR & "' And MN = '" & Forms!SERVICE_FORM!CMONTH & "'")

 

[arnelgp]

YR IS YEAR AND MN IN NAME OF MONTH

Me.TXTCASE.value = DCount("REC_ID", "QPENSIONER_NEW", "YR = " & Forms!SERVICE_FORM!CYEAR & " And MN = '" & Forms!SERVICE_FORM!CMONTH & "'")

 

[ebs17]

(1) DCount("REC_ID", ...  
 versus    
(2) DCount("*", ...

(1) counts contents of REC_ID, i.e. values that are not NULL
(2) counts records per se
So there can definitely be differences.

YR IS YEAR AND MN IN NAME OF MONTH

This is not the most clever way to store and process data. A date like the first of the month could be used much more flexibly; month and year and many other formats (calendar week, quarter) can be derived from a date.

 

[sbaud2003]

Perhaps:
Me.TXTCASE.value = DCount("REC_ID", "QPENSIONER_NEW", "YR = '" & Forms!SERVICE_FORM!CYEAR & "' And MN = '" & Forms!SERVICE_FORM!CMONTH & "'")

No, Getting error as "Data Type Mismatch in criteria Expression" Both YR and MN is being extraxted from Date Field

 

[sbaud2003]

Me.TXTCASE.value = DCount("REC_ID", "QPENSIONER_NEW", "YR = " & Forms!SERVICE_FORM!CYEAR & " And MN = '" & Forms!SERVICE_FORM!CMONTH & "'")

No, Getting error as "Data Type Mismatch in criteria Expression" Both YR and MN is being extraxted from Date Field

 

[ebs17]

Data Type Mismatch

As you can imagine, the two don't go together. So what data types do JR / CJEAR and MN/CMONTH have?
Don't be surprised, just check!

Both YR and MN is being extraxted from Date Field

You can usually also work directly with the date field. It is estimated that you are making your work more complicated and more prone to making errors by taking unnecessary intermediate steps.

 

[Gasman]

No, Getting error as "Data Type Mismatch in criteria Expression" Both YR and MN is being extraxted from Date Field

I tend to put the criteria into a string variable for domain functions if not a single simple criteria.
Then I can debug.print them to see if I have the syntax correct.
Then when correct, I can use that in the function.
Plus, if you cannot see the error, you can copy and paste here or eslewhere, so someone can point out the error.?

Strings need to be surrounded by single quotes ' unless it contains a single quote, then triple double quotes works, I think?

Date literals with # and in mm/dd/yyyy or yyyy-mm-dd format

Numbers do not need anything

Also for anything other than one item of criteria, I tend to put the the criteria into a string variable and then debug.print it, until correct then also use that in the code.

Added benefit is, if you still cannot see the error, you can copy and paste back here for someone else to spot it.

 

[sbaud2003]

As you can imagine, the two don't go together. So what data types do JR / CJEAR and MN/CMONTH have?
Don't be surprised, just check!


You can usually also work directly with the date field. It is estimated that you are making your work more complicated and more prone to making errors by taking unnecessary intermediate steps.

Thanks a ton. I got the error, i was uning monthname function instead of month.

 

[sbaud2003]

As you can imagine, the two don't go together. So what data types do JR / CJEAR and MN/CMONTH have?
Don't be surprised, just check!


You can usually also work directly with the date field. It is estimated that you are making your work more complicated and more prone to making errors by taking unnecessary intermediate steps.

Thanks a ton. I got the error, i was uning monthname function instead of month.