DCount

  • Thread starter Thread starter Vsn
  • Start date Start date
V

Vsn

Hi all,

I have got a problem with DCount, I suppose its possible what I do try.

I have an textbox on a form with the control source
=DCount("*","tblPersonnel","RPExpDT < '" & Date() + 45"'")

The idea was this should give me the total number of fields (RPExpDT wich is
formated as date field) which would expire within 45 days. Unfortunatly an
error appears.

Could some one give me the solution?

Thx,
Vsn
 
Vsn said:
Hi all,

I have got a problem with DCount, I suppose its possible what I do
try.
I have an textbox on a form with the control source
=DCount("*","tblPersonnel","RPExpDT < '" & Date() + 45"'")

The idea was this should give me the total number of fields (RPExpDT
wich is formated as date field) which would expire within 45 days.
Unfortunatly an error appears.

Could some one give me the solution?
Click to expand...

Dates should be delimited with # not with '. You're also missing an &.

=DCount("*","tblPersonnel","RPExpDT < #" & Date() + 45 & "#")
 
Thx, so far.


Still the out come is not as I did expect it to be.


I have a table as below:-



fID fText fDate fAddDate

1 Hallo 25-11-2005 26-11-2005

2 Cheff 26-11-2006 26-11-2005

3 Vink 24-10-2004 26-11-2005

4 Simon 23-10-2004 26-11-2005

5 Henk 22-11-2008 26-11-2005

6 Jan 12-08-2005 26-11-2005

7 Tinus 13-09-2006 26-11-2005

8 Gert 12-12-2006 26-11-2005

9 Pieter 12-12-2005 26-11-2005

10 James 11-11-2006 26-11-2005

11 David 11-05-2005 26-11-2005

12 Robert 12-04-2003 26-11-2005

13 Willem 05-07-2003 26-11-2005



my text box control source is =DCount("fDate","tblData","fDate < #" &
Date()+45 & "#")


I do expect the number returned to be 8 however I do get 9.


Is there an explanation and can I just solve it by deducting 1 like

=DCount("fDate","tblData","fDate < #" & Date()+45 & "#")-1




Last question which keeps me busy:


Can I use a SQL query in the domain instead of the table name as:-

=DCount("fDate",[SELECT tblData.fText, tblData.fDate

FROM tblData

WHERE (((tblData.fDate)<Date()+45))

WITH OWNERACCESS OPTION;

],"fDate < #" & Date()+45 & "#")





thx for the help,

Vsn
 
Vsn said:
Thx, so far.

Still the out come is not as I did expect it to be.

I have a table as below:-

fID fText fDate fAddDate
1 Hallo 25-11-2005 26-11-2005
2 Cheff 26-11-2006 26-11-2005
3 Vink 24-10-2004 26-11-2005
4 Simon 23-10-2004 26-11-2005
5 Henk 22-11-2008 26-11-2005
6 Jan 12-08-2005 26-11-2005
7 Tinus 13-09-2006 26-11-2005
8 Gert 12-12-2006 26-11-2005
9 Pieter 12-12-2005 26-11-2005
10 James 11-11-2006 26-11-2005
11 David 11-05-2005 26-11-2005
12 Robert 12-04-2003 26-11-2005
13 Willem 05-07-2003 26-11-2005

my text box control source is =DCount("fDate","tblData","fDate < #" &
Date()+45 & "#")

I do expect the number returned to be 8 however I do get 9.
Click to expand...

Sorry, BUt I created this exact table structure and the DCount() expresson gives
me 8, not 9.

Are these actual DateTime Data Types or Text? A date comparison is not going to
be correct when applied to a text field that just happens to have dates entered
into it. The comparison will be alphabetical rather than chronological.

Last question which keeps me busy:


Can I use a SQL query in the domain instead of the table name as:-

=DCount("fDate",[SELECT tblData.fText, tblData.fDate
Click to expand...

No. A Domain function's second argument has to be the name of a saved table or
query.
 
This DCount() turns me mad..........

I realy get 9 ????????????? And this was just a tryout because on the real
table where I did expect 63 I got 92. A regular query with criterea
<Date()+45 gives me 63.

I have tried it on 2 computers?

FYI I do use Office Access 2003 SP2 on a XP SP2 Os.

Below the table structure
Name Type
Size

fID
Long Integer 4

AllowZeroLength: False

Attributes: Fixed Size,
Auto-Increment

CollatingOrder: General

ColumnHidden: False

ColumnOrder: Default

ColumnWidth: Default

DataUpdatable: False

GUID: {guid
{38722836-7890-4941-93CA-5C4B6B0042AC}}

OrdinalPosition: 0

Required: False

SourceField: fID

SourceTable: tblData

fText
Text 20

AllowZeroLength: True

Attributes: Variable Length

CollatingOrder: General

ColumnHidden: False

ColumnOrder: Default

ColumnWidth: Default

DataUpdatable: False

DisplayControl: Text Box

GUID: {guid
{224D61B7-51DD-4BEF-A10D-C66B46CB2685}}

IMEMode: 0

IMESentenceMode: 3

OrdinalPosition: 1

Required: False

SourceField: fText

SourceTable: tblData

UnicodeCompression: True

fDate
Date/Time 8

AllowZeroLength: False

Attributes: Fixed Size

CollatingOrder: General

ColumnHidden: False

ColumnOrder: Default

ColumnWidth: Default

DataUpdatable: False

Format: dd/mm/yyyy

GUID: {guid
{FAD7DF3D-E1BC-4F23-B32F-75A6F1CC2C13}}

IMEMode: 0

IMESentenceMode: 3

OrdinalPosition: 2

Required: False

SourceField: fDate

SourceTable: tblData

fAddDate
Date/Time 8

AllowZeroLength: False

Attributes: Fixed Size

CollatingOrder: General

ColumnHidden: False

ColumnOrder: Default

ColumnWidth: Default

DataUpdatable: False

DefaultValue: =Now()

Format: dd/mm/yyyy

GUID: {guid
{9A75F3A0-4CDE-4695-8BEC-E42A23CD237A}}

IMEMode: 0

IMESentenceMode: 3

OrdinalPosition: 3

Required: False

SourceField: fAddDate

SourceTable: tblData






What I need to achieve it a form where in just one single number (Dcount)
the user can see, how many certificates expired (like Medical, Passport, Sea
Survival training, etc.) from an employee. This should than trigger further
action.

Would there be a better method?











Rick Brandt said:
Vsn said:
Thx, so far.

Still the out come is not as I did expect it to be.

I have a table as below:-

fID fText fDate fAddDate
1 Hallo 25-11-2005 26-11-2005
2 Cheff 26-11-2006 26-11-2005
3 Vink 24-10-2004 26-11-2005
4 Simon 23-10-2004 26-11-2005
5 Henk 22-11-2008 26-11-2005
6 Jan 12-08-2005 26-11-2005
7 Tinus 13-09-2006 26-11-2005
8 Gert 12-12-2006 26-11-2005
9 Pieter 12-12-2005 26-11-2005
10 James 11-11-2006 26-11-2005
11 David 11-05-2005 26-11-2005
12 Robert 12-04-2003 26-11-2005
13 Willem 05-07-2003 26-11-2005

my text box control source is =DCount("fDate","tblData","fDate < #" &
Date()+45 & "#")

I do expect the number returned to be 8 however I do get 9.
Click to expand...

Sorry, BUt I created this exact table structure and the DCount() expresson
gives me 8, not 9.

Are these actual DateTime Data Types or Text? A date comparison is not
going to be correct when applied to a text field that just happens to have
dates entered into it. The comparison will be alphabetical rather than
chronological.

Last question which keeps me busy:


Can I use a SQL query in the domain instead of the table name as:-

=DCount("fDate",[SELECT tblData.fText, tblData.fDate
Click to expand...

No. A Domain function's second argument has to be the name of a saved
table or query.
Click to expand...
 
Vsn said:
This DCount() turns me mad..........

I realy get 9 ????????????? And this was just a tryout because on
the real table where I did expect 63 I got 92. A regular query with
criterea <Date()+45 gives me 63.

I have tried it on 2 computers?

FYI I do use Office Access 2003 SP2 on a XP SP2 Os.
Click to expand...
[snip]

Simplify the expression to isolate the problem.

If you use a date literal that is 45 days in the future is the DCount() value
still wrong? How about if you use other (smaller) durations?

Are there additional rows that were not included in your sample because they had
some null values? When you use * as the first DCount argument Nulls are
included in the count whereas if you use a field name for the first argument
then only rows where that field is not null are counted.
 
Back
Top