Trouble In Access Forms : Identifying Criteria And DCOUNT

[pilky01]

Hi,

I'm having difficulties in identifying criteria from a text box in a form. I have a text box named "txtCompanyID" and I cannot find a way to identify what is within it.

I have used the following formula, which is about half way to what I want, but all it manages to get me is the total number of reports (the form creates a kind of visual report maker) that I am creating:

=DCount("[Report Number]","tblReport",[Date]>(Date()-31) And [Company ID]=14)

OK, so I don't know a) how to get the companyID to come up automatically from what I have chosen in the form, as I feel that [Company ID] = [Company ID] would probably not work, as both entities would result in the same.

Furthermore, although I have defined the date must be less than 31 days ago, this doesn't seem to work either, as when the date for reports where the date is greater than 31 days, nothing shows [great, that much works], but when it is less, it counts ALL of my records, rather than just the ones in which the date is more recent than 31 days.
Its driving me nuts, and brought my entire Computing coursework to a stall.

When done with this I hope that I can make it only shows up the ones for the same problem ID, but I think that I could do this using the groundwork from this problem.

Overall I want the reports of:

The same Company ID as shown in the form
AND the same Problem ID as shown in the form
AND the date less than 31 days

All of my data is stored in the same table [tblReport] and it would be a great help if you could assist me.

Thank you in advance

 

[Remou]

It is to do with the position of your quotes.

[tt]=DCount("[Report Number]","tblReport",[Date]>(Date()-31) And [Company ID]=" & Me.CompanyID[/tt])

If CompanyID is text, you will need single quotes. In the example you gave, you said Date > (greater than) Date-31 is that whay you want?

This thread explains how to add variables to Dcount:
D Count Problem
thread705-1207225