Access date range quary

[Shaima]

Hi guys..
I have a problem in Dcount function.. when i enter a date it didn't count correctly.. it take all the records of all the years.. and i want only the records that mentioned.. before that it was work correctly..
What is the wrong in this code
Dcount ("[Bill_no_BDM]","BillDataMov","[Isu_Kind_BDM]='not taken' And [zone_BDM]='" & [zone_BDM] & "'")

[pbaldy]

If the second field is a date field you need # as the delimiter:

http://www.theaccessweb.com/general/gen0018.htm

[Shaima]

Hi..
No the second field is not date it is a zone name... I make a quary for the date... And i create a report to count the number of records a according to the bill that the items didn't token and the zone name.. write this code in the builder..
This code was working before... But now i don't know where is the problem.. when i run this code it appear the number of records correctly but the result number is wrong... It appear to me the number of all records for all yeas it didn't filter the year correctly..

[pbaldy]

The query didn't, or the DCount()? Is BillDataMov the query?

[Welshgasman]

Well the problem is with your query not the DCount() ?

[Shaima]

The Dcount didn't get the correct result...
I have a report and i have a quary..
The quary specified the range of the date and the zone name and also the token items from the store.. The items that appear in the report using the quary is correct.. the quary is working correctly in the report..

I used Dcount function to count the number of the records that appear in the report but the total items in the report is getting wrong.. i want to count the number of items that appear in the report.. but it count the number of records wrong.. it get the result of all items in all years that in the table.. it didn't count the result of the year that spcified in the quary..

BillDataMov is the name of the table...

[Welshgasman]

If you want to count the number of items in the report, just add a count() in the report.

If Access is giving you the wrong count, then your criteria is wrong. Plain and simple.

Can you upload the DB with enough data to show the problem?

Use the name of the query for the domain in the DCount() if you insist on using it?

How can you expect the table to provide the same as your query when your query has criteria for a certain year? The DCount() would need the same criteria?

Like comparing apples and oranges?

[Shaima]

How can i upload a DB or even photo to explain the issue?! I don't know from where can i upload data

[Welshgasman]

How can i upload a DB or even photo to explain the issue?! I don't know from where can i upload data

See the top of the screen.
However I think I understand your problem now. The query is restricted by year, the Dcount() is not. Make them have the same criteria.

[Shaima]

pic. 1. is the report that contain the items in details, the total number of item, zone name and date range.. see the total number of items is not match with the records.. the items details is correct with the date range.. but the total result is not match.. when i check the table and do filter by (zone name) and (item not token form the store) it get all the records for all years in the table and appear the result (116) records.. it didn't take the correct date range that i specified on top witch is 8 records only... so i recognize that it take all the records for all years that in the table.. it didn't filter by date range..

pic. 2. is the query for date range, zone name and item not token from store..

pic. 3. is code builder of Dcount to count the total number of records..

where is the problem?

[Welshgasman]

The problem is the criteria is NOT THE SAME!!!! I do not know how else to say it?

The Dcount() does NOT take into account *ANY* date whatsoever? What part of that do you not understand.?

The easiest way out of this fiasco is to use your query for the domain in DCount(). Then it looks like you would not need any criteria at all? The criteria is all in the query?

Test it out in the immediate window. That would be what I would do.

If you just want a count in the report then I would just use the report Count(). Even easier.

[Shaima]

You mean that i use Dcount criteria in the query... So where i write Dcount criteria in which column... If i use criteria in the query is the total result of records will shown in the report or not..
Thank you..

[Shaima]

Hi everyone...
I tried to write the criteria in the query... but the result is same.. where in the report.. Dcount result is same 116.. it seems that Dcount function is wrong.. any one can help please..

[CarlettoFed]

In the control at the bottom of the report, where there is the formula = DCount (...), you have to write = Count (Autonumber)

[Welshgasman]

DCount() function is never wrong.
You are just using it incorrectly.

Just change the table name in the function to your quey name. Do not put the function as a new field. That is not going to change anything. Why would you think it would?
Report count is still the way I would go.