Working with dates in a query and . . .

[AccessNubee]

I'm so new to Access that I don't know how to purpose the question but here
goes.1 - To obtain the number of new members since April 2008, would that be
done through a query? If so, what would the expression be. 2 - I've created a
report to capture the grand total dollar amount of vouchers and quanity. How
do I only display the clients that have purchased the vouchers and not the
entire database entries? And last 3 - I have created a table the tells me
which clients have received or not received repairs which is indicated by
check boxes. How do i get a count of who received repairs and who hasn't?

 

[Michel Walsh]

DCount("*", "TableNameHere", "StartingDateFieldName>= #4/1/2008#)


or, with a query:

SELECT COUNT**)
FROM tableNameHere
WHERE startingDateFieldName >= #04/01/2008#



Vanderghast, Access MVP

 

[AccessNubee]

Michael,
Don't know enough about queries. Where would these expressions go???

 

[Golfinray]

In A new field in your query.

Michael,
Don't know enough about queries. Where would these expressions go???

 

[Michel Walsh]

Indeed, the DCount can be a new field in a query, or in VBA code, under the
'right' place (such as under a button click event handling subroutine, or in
the after change of a text control :


Me.SomeControl = DCount( "*", "TableNameHere",
"YourDateTimeFieldNameHere=#04/01/2008#" )



In fact, you can even type it directly in the debug immediate window
(Ctrl_G) :

? DCount( "*", "TableNameHere", "YourDateTimeFieldNameHere=#04/01/2008#" )



I made a typo for the query, it should be:



SELECT COUNT(*)
FROM tableNameHere
WHERE startingDateFieldName >= #04/01/2008#



And you can use that query almost anywhere a query retrieving data can be
used.



Vanderghast, Access MVP

 

[AccessNubee]

Michael,

Do I put the expression as criteria in the query or in Field as an
expression? And I don't know anything about VBA code.

 

[Michel Walsh]

You use

DCount( "*", "TableNameHere",
"YourDateTimeFieldNameHere=#04/01/2008#" )



as an expression. You can put is as a criteria if this is what you want to,
such as wanting some field value EQUAL to that count, but that hardly make
sense, in general. In fact, you can use that expression almost anywhere a
value will be accepted. So, definitively, you can place it as an expression,
in a query, first line of the grid. You have not tried it?


Vanderghast, Access MVP

 

[AccessNubee]

When I put it in the first line of the grid (Field, I get "Enter Parameter
Value". Am I supposed to enter data here? I have a column "MembershipDate"
with various sign up dates, some of which joined in 2007. How would I get my
"New Members" query to get number of new members since April 2008 so I can do
a report?

 

[Michel Walsh]

You get an "Enter Parameter Value" if the field name is not typed correctly.
Sounds it could be:

DCount("*", "TableNameHere", "MembershipDate >= #04/01/2008# " )

where you replace the TableNameHere with the real table name. If the table
has space in it, you have to use [ ] around it:

DCount( "*", " [Table name with spaces in it-and some other * strange +
characters #] ", " [Membership Date] >= #04/01/2008# " )


Note that the date is in the US format, mm/dd/yyyy, and that should be like
this independently of your regional setting.



Vanderghast, Access MVP

 

[AccessNubee]

Thank you Michel it worked in query. Now can I use the same expression in a
report to read: " New Members as of Apr. '08 with the result in a box and
have it update as new members are added? Or will this take a different type
of expression. I tried rewording the expression you gave me but that didn't
work.

You get an "Enter Parameter Value" if the field name is not typed correctly.
Sounds it could be:

DCount("*", "TableNameHere", "MembershipDate >= #04/01/2008# " )

where you replace the TableNameHere with the real table name. If the table
has space in it, you have to use [ ] around it:

DCount( "*", " [Table name with spaces in it-and some other * strange +
characters #] ", " [Membership Date] >= #04/01/2008# " )


Note that the date is in the US format, mm/dd/yyyy, and that should be like
this independently of your regional setting.



Vanderghast, Access MVP

When I put it in the first line of the grid (Field, I get "Enter
Parameter
Value". Am I supposed to enter data here? I have a column
"MembershipDate"
with various sign up dates, some of which joined in 2007. How would I get
my
"New Members" query to get number of new members since April 2008 so I can
do
a report?

 

[Michel Walsh]

You can use the same expression, but precede it with an equal sign, in the
text box control, or in the property sheet, for the property "Control
Source".

Vanderghast, Access MVP

Thank you Michel it worked in query. Now can I use the same expression in
a
report to read: " New Members as of Apr. '08 with the result in a box and
have it update as new members are added? Or will this take a different
type
of expression. I tried rewording the expression you gave me but that
didn't
work.

You get an "Enter Parameter Value" if the field name is not typed
correctly.
Sounds it could be:

DCount("*", "TableNameHere", "MembershipDate >= #04/01/2008# " )

where you replace the TableNameHere with the real table name. If the
table
has space in it, you have to use [ ] around it:

DCount( "*", " [Table name with spaces in it-and some other * strange +
characters #] ", " [Membership Date] >= #04/01/2008# " )


Note that the date is in the US format, mm/dd/yyyy, and that should be
like
this independently of your regional setting.



Vanderghast, Access MVP

When I put it in the first line of the grid (Field, I get "Enter
Parameter
Value". Am I supposed to enter data here? I have a column
"MembershipDate"
with various sign up dates, some of which joined in 2007. How would I
get
my
"New Members" query to get number of new members since April 2008 so I
can
do
a report?

:

You use

DCount( "*", "TableNameHere",
"YourDateTimeFieldNameHere=#04/01/2008#" )



as an expression. You can put is as a criteria if this is what you
want
to,
such as wanting some field value EQUAL to that count, but that hardly
make
sense, in general. In fact, you can use that expression almost
anywhere a
value will be accepted. So, definitively, you can place it as an
expression,
in a query, first line of the grid. You have not tried it?


Vanderghast, Access MVP


Michael,

Do I put the expression as criteria in the query or in Field as an
expression? And I don't know anything about VBA code.



:

Indeed, the DCount can be a new field in a query, or in VBA code,
under
the
'right' place (such as under a button click event handling
subroutine,
or
in
the after change of a text control :


Me.SomeControl = DCount( "*", "TableNameHere",
"YourDateTimeFieldNameHere=#04/01/2008#" )



In fact, you can even type it directly in the debug immediate
window
(Ctrl_G) :

? DCount( "*", "TableNameHere",
"YourDateTimeFieldNameHere=#04/01/2008#" )



I made a typo for the query, it should be:



SELECT COUNT(*)
FROM tableNameHere
WHERE startingDateFieldName >= #04/01/2008#



And you can use that query almost anywhere a query retrieving data
can
be
used.



Vanderghast, Access MVP



In A new field in your query.

:

Michael,
Don't know enough about queries. Where would these expressions
go???

:

DCount("*", "TableNameHere", "StartingDateFieldName>=
#4/1/2008#)


or, with a query:

SELECT COUNT**)
FROM tableNameHere
WHERE startingDateFieldName >= #04/01/2008#



Vanderghast, Access MVP


message
I'm so new to Access that I don't know how to purpose the
question
but
here
goes.1 - To obtain the number of new members since April
2008,
would
that
be
done through a query? If so, what would the expression be.
2 -
I've
created a
report to capture the grand total dollar amount of vouchers
and
quanity.
How
do I only display the clients that have purchased the
vouchers
and
not the
entire database entries? And last 3 - I have created a table
the
tells me
which clients have received or not received repairs which is
indicated by
check boxes. How do i get a count of who received repairs
and
who
hasn't?

 

[AccessNubee]

We're getting there Michel. Now how do I get rid of the / / (forward slashes)
in the box? It appears to be displaying the result in date format. I just
want the number to appear

You can use the same expression, but precede it with an equal sign, in the
text box control, or in the property sheet, for the property "Control
Source".

Vanderghast, Access MVP

Thank you Michel it worked in query. Now can I use the same expression in
a
report to read: " New Members as of Apr. '08 with the result in a box and
have it update as new members are added? Or will this take a different
type
of expression. I tried rewording the expression you gave me but that
didn't
work.

You get an "Enter Parameter Value" if the field name is not typed
correctly.
Sounds it could be:

DCount("*", "TableNameHere", "MembershipDate >= #04/01/2008# " )

where you replace the TableNameHere with the real table name. If the
table
has space in it, you have to use [ ] around it:

DCount( "*", " [Table name with spaces in it-and some other * strange +
characters #] ", " [Membership Date] >= #04/01/2008# " )


Note that the date is in the US format, mm/dd/yyyy, and that should be
like
this independently of your regional setting.



Vanderghast, Access MVP


When I put it in the first line of the grid (Field, I get "Enter
Parameter
Value". Am I supposed to enter data here? I have a column
"MembershipDate"
with various sign up dates, some of which joined in 2007. How would I
get
my
"New Members" query to get number of new members since April 2008 so I
can
do
a report?

:

You use

DCount( "*", "TableNameHere",
"YourDateTimeFieldNameHere=#04/01/2008#" )



as an expression. You can put is as a criteria if this is what you
want
to,
such as wanting some field value EQUAL to that count, but that hardly
make
sense, in general. In fact, you can use that expression almost
anywhere a
value will be accepted. So, definitively, you can place it as an
expression,
in a query, first line of the grid. You have not tried it?


Vanderghast, Access MVP


Michael,

Do I put the expression as criteria in the query or in Field as an
expression? And I don't know anything about VBA code.



:

Indeed, the DCount can be a new field in a query, or in VBA code,
under
the
'right' place (such as under a button click event handling
subroutine,
or
in
the after change of a text control :


Me.SomeControl = DCount( "*", "TableNameHere",
"YourDateTimeFieldNameHere=#04/01/2008#" )



In fact, you can even type it directly in the debug immediate
window
(Ctrl_G) :

? DCount( "*", "TableNameHere",
"YourDateTimeFieldNameHere=#04/01/2008#" )



I made a typo for the query, it should be:



SELECT COUNT(*)
FROM tableNameHere
WHERE startingDateFieldName >= #04/01/2008#



And you can use that query almost anywhere a query retrieving data
can
be
used.



Vanderghast, Access MVP



In A new field in your query.

:

Michael,
Don't know enough about queries. Where would these expressions
go???

:

DCount("*", "TableNameHere", "StartingDateFieldName>=
#4/1/2008#)


or, with a query:

SELECT COUNT**)
FROM tableNameHere
WHERE startingDateFieldName >= #04/01/2008#



Vanderghast, Access MVP


message
I'm so new to Access that I don't know how to purpose the
question
but
here
goes.1 - To obtain the number of new members since April
2008,
would
that
be
done through a query? If so, what would the expression be.
2 -
I've
created a
report to capture the grand total dollar amount of vouchers
and
quanity.
How
do I only display the clients that have purchased the
vouchers
and
not the
entire database entries? And last 3 - I have created a table
the
tells me
which clients have received or not received repairs which is
indicated by
check boxes. How do i get a count of who received repairs
and
who
hasn't?

 

[Michel Walsh]

You got me on that one, a count should not be formatted as a date... Anyhow,
the textbox control owns a format property, under the format tab, of the
properties sheet. Leave that property in blank.


Also, be sure you didn't type:


= "sometext " & DCount( ...) & " something"


unless you want to add the extra text before and after the result of the
count.


Vanderghast, Access MVP

 

[AccessNubee]

Solved the problem. The input mask was set up as date format. Thanks again
for all your help.