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

Rabu, 08 Januari 2014

Re: [MS_AccessPros] Dates in Dcount Criteria

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