Dcount expression help. 1

[Utracman]

A97

Unbound texbox control I'm trying to get the count of records that meet this expression
=DCount("corpBranch","tblBranch"," [LastReview] = Month(Now) & "/" & Day("*&quot And [Sales ID] = [txtSales ID]&quot

I'm trying to count the records with a date that matches the current month. So every month the code will work and the count will be right.

Any Ideas?

Many thanks for the lesson.

Dave To learn fron those who have mastered the art..... Saves many headaches!

 

[Aivars]

Hi!

You must use text qulifier (quote marks like 'Me.dteDate') or date qulifier ( like #Me.dteDate#).
Accordingly your expression may be wrote following:

=DCount("corpBranch","tblBranch"," [LastReview] like '" & CStr(Month(Now())) & "/*" & "' And [Sales ID] = " & [txtSales ID])

If type of field [Sales ID] is text, then
=DCount("corpBranch","tblBranch"," [LastReview] like '" & CStr(Month(Now())) & "/*" & "' And [Sales ID] = '" & [txtSales ID] & "'&quot

If type of field [Sales ID] is date, then
=DCount("corpBranch","tblBranch"," month([LastReview]) = " & Month(Now) & " And .....)

Aivars

 

[Utracman]

Thanks for the help!

[Sales ID] is a text field. I tried both options and in the first I get an #error and the second gives me a value of 0. What is missing? The [LastReview] field has a value like "05/09/2001 9:45:48 AM" I'm trying to show all records that match [Sales ID] and count the total for the current month. This must be close since I have a 0 from your second option.

Many thanks Again


Dave To learn fron those who have mastered the art..... Saves many headaches!

 

[Aivars]

Hallo, Dave!

Before was missed space between <And> and <[Sales ID]>.
Small advice:
1. declare variable (for example: MyCriteria);
2. copy following code into your program;
3. mark with breakpoint row ...=DCount...
4. when program will be stoped write in immediate window
? MyCriteria
press <Enter>
5. verify returned clause (you can copy/paste it into query SQL Window like this &quot;Select tblBranch.* From tblBranch Where <returned clause in immediate> ;&quot; and then to verify query).

But I think if your table name is &quot;tblBranch&quot; and field name &quot;corpBranch&quot; is true, all must be worked.

Dim lngCount As Long
Dim MyCriteria As String

MyCriteria = &quot; Month([LastReview])=&quot; & Month(Now()) & &quot; And [Sales ID] = '&quot; & Me![txtSales ID] & &quot;'&quot;
lngCount = DCount(&quot;corpBranch&quot;, &quot;tblBranch&quot;, MyCriteria) 'point this row with breakpoint

Good luck!
Aivars

P.S. Are you from? I'm from Latvia.

 

[Utracman]

Thanks Again

THis is what I did.

Copied the code into Sub MyCriteria()

Dim lngCount As Long
Dim MyCriteria As String

MyCriteria = &quot; Month([LastReview])=&quot; & Month(Now()) & &quot; And [Sales ID] = '&quot; & Me![txtSales ID] & &quot;'&quot;
lngCount = DCount(&quot;LastReview&quot;, &quot;tblBranch&quot;, MyCriteria)

For my unbound textbox I used in the control source= DCount(&quot;LastReview&quot;, &quot;tblBranch&quot;, MyCriteria)

Notice I changed the first argument from [corpBranch] to [LastReview]

I used your suggestions (Thanks learned some new things) and the result is every record that has a date instead of those in the current month being counted or that = [Sales ID].

One thing [LastReview] is a date field.... Does that change things

This is almost there but something is missing or my lack of experience misinterpreted your instructions.

Many Thanks

Dave To learn fron those who have mastered the art..... Saves many headaches!

 

[Aivars]

Hallo, Dave!

My small advice for you.
Study SQL language by using of SQL window (query constructor). Create any query with graphic constructor, then open <SQL Design> window and compare SQL text with query graphic in <Design view>.

Good luck!
Aivars
alaganovskis@hotmail.com