ACCESS FEED: Re: [MS_AccessPros] Dates in Dcount Criteria

Rabu, 08 Januari 2014

Re: [MS_AccessPros] Dates in Dcount Criteria

Hi John

I guess 'a spot of bother' is very English !!!

Thank you - that sorted out my 'between dates' problem - but, as is always the case, I have now run into another problem.

I am trying to do this in query design (rather than any VBA) by the way.

The query itself is limiting the records it finds based on criteria entered and the field I am using the dcount function in is counting the number of records that not only satisfy all the query criteria but also 1 further test (the [totbooks]<21 element).

This works fine as long as the query returns just one record - because of grouping.

However I need the query to return several records and i need the dcount field to display the correct count for each subgrouping.

Is this possible to do using this approach and if so what do I put in the dcount criteria to achieve it?

should I be doing it in a different way - probably in VBA I imagine!

at the moment it asks for div, and 2 dates and then finds all the records for div between the 2 dates and I have a field that gives the count of all the records it has found and my dcount field then gives a count of all those records with totbooks <21.

I now need to subgroup the single record that the query returns by month (regardless of year) ie one record for all matches records with a January date and another record for Feb etc. - and for each of the query rows, a correct count of those records and a count of those records with totbooks <21

Once I have worked out how to do this I need to add several more columns of a similar nature but with different ranges for totbooks

Hope I have explained it adequately - if not please ask for clarificatiion

cheers

Phil

On Wednesday, 8 January 2014, 11:10, John Viescas <JohnV@msn.com> wrote:

Phil-

"Spot of bother" implies to me that you're not on a US machine. Date in predicates in SQL (which DCount uses) must be in US format if they're Short date. If the values in [Enter Date1] and [Enter Date2] are in European format (dd/mm/yyyy), you could get wrong results. For example 1/8/2013 (August 1, 2013 outside of the US) is interpreted by the query engine as January 8!

Try this:

DCount("id","matches","[totbooks]<21 and [div]='" & [enter league code] & "' and [date] between #" & Format([enter date1], "mm/dd/yyyy") & "# and #" & Format([enter date2], "mm/dd/yyyy") & "# ")

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Jan 8, 2014, at 11:47 AM, <pdk444444@yahoo.co.uk> <pdk444444@yahoo.co.uk> wrote:

Hi Guys,

I am having a spot of bother again - help please.

I am trying to do a count of a set of records for a field in a query.

I am user parameters and all was going well until I tried to change a date bit to between 2 dates - I am now getting odd results.

I have tried the following

DCount("id","matches","[totbooks]<21 and [div]='" & [enter league code] & "' and [date] between #" & [enter date1] & "# and #" & [enter date2] & "# ")

this returns a count that is much too high

and also

DCount("id","matches","[totbooks]<21 and [div]='" & [enter league code] & "' and [date] >= #" & [enter date1] & "# and [date]<= #" & [enter date2] & "# ")

this returns 0

I must admit it took me a while to work out all the delimiters needed to accomodate parameters and I wonder if they are not correct?

cheers

Phil