DCOUNT format question

You're sure that txtregdate and txtinitials have legitimate values in them?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"Gary" wrote in message
news
Doug,

It is a date/time format as general date.

I changed the name of the controls to have a txt prefix but to no avail.
I
still get the #NAME? error.

This is my current string:

=DCount("[INCR]","tblPURD","[reqdate]=" &
Format(me.txtreqdate,"\#mm\/dd\/yyyy\#") & " AND [initials] = '" &
me.txtinitials & "'")+1

(Changed table name from PickUpReqData to tblPURD

Thanks.

"Douglas J. Steele" wrote:

Is reqdate in your table a Date/Time field, or simply a Text field
containing a date? Does the field reqdate on your form contain a valid
date?

You could try changing the names of the controls on your forms: sometimes
Access gets confused when the name of the control and the name of the
field
is the same (even though it chooses to name the controls that way...) I
always rename all of my textboxes so that they start with a txt prefix,
so
that my statement would actually be:

=DCount("[incr]","pickupreqdata","[reqdate]=" &
Format(me.txtReqDate,"\#mm\/dd\/yyyy\#") & " AND initials = '" &
me.txtInitials & "'")+1

Also, see whether replace [incr] with * makes any difference (I wouldn't
really expect it to...)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"Gary" wrote in message
...
John,

When I place this in the control I get a #NAME? error:

=DCount("[incr]","pickupreqdata","[reqdate]=" &
Format(me.reqdate,"\#mm\/dd\/yyyy\#") & " AND initials = '" &
me.initials
&
"'")+1

The only things I changed from your string was the field to count (from
Initials to INCR) and from the form name to the table name.

Gary

"John Spencer" wrote:

I think Doug must have been tired. He missed an opening quote mark
before
the date format and an ampersand between "initials =" and
"me.initials"

=DCount("[initials]","pickupreqdatafrm","[reqdate]=" &
Format(me.reqdate,"\#mm\/dd\/yyyy\#") & " AND initials = '" &
me.initials
&
"'")+1


"Gary" wrote in message
...
Douglas,

Thanks for your reply.

When I enter your string into the control on the form, I receive
this
error:

The expression you entered contains invalid syntax.
You may have entered a comma without a preceding value or
identifier.

Thanks.

"Douglas J. Steele" wrote:

Yes, your format is incorrect.

The values you're checking need to go outside of the quotes.
Additional,
dates must be delimited with # (and in mm/dd/yyyy format,
regardless
of
what
your short date format has been set to through Regional Settings)*,
and
strings with quotes.

Try the following:

=DCount("[initials]","pickupreqdatafrm","[reqdate]=" &
Format(me.reqdate,
\#mm\/dd\/yyyy\#") & " AND initials = '" me.initials & "'")+1

Note that, exagerated for clarity, that last bit is:

& " AND initials = ' " me.initials & " ' "

* Okay, so it's not absolutely necessary to use mm/dd/yyyy format.
You
can
use any unambiguous format, such as yyyy-mm-dd or dd mmm yyyy. The
point
is,
you can't use dd/mm/yyyy, even if that's what your short date
format
has
been set to.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"Gary" wrote in message
...
I can get this to work in a totals query but not on a form.

I have this for the control source:

=DCount("[initials]","pickupreqdatafrm","[reqdate]=me.reqdate AND
initials
=me.initials")+1

Do I have the format wrong? I'm looking to return the count value
and
then
concatenate the 3 parts of the Ref#.


I want my db to generate our reference # automatically based on
input
from
other fields. The number is the date record entered, the users
initials,
and
an incremental number. Ex. 120505GMM02

My form is based on a query and I have the query generating the
first
two
components from the date entered in reqdate and the initials
entered.
How
can
I get an incremental number generated to go with the rest.

The number should start with 01 for each day and also for each
user.
Therefore when GMM is entering his third record on the 5th it
should
generate
120505GMM03 even if there were 10 other records entered on the
fifth
by
other
users. Each users numbers would start at 01 for each day.

Thanks.

Yes.

txtreqdate is general date. When I input it I enter, i.e., 12/12 and the
result displayed 12/12/2005

txtInitials is a list box with four choices bound to the field INITIALs in
table tblPURD. Would that affect the result?

When I run a totals query I am able to get the result. Is there any way to
use the results of that query in the control for that field?

Thanks.

Gary
"Douglas J. Steele" wrote:

You're sure that txtregdate and txtinitials have legitimate values in them?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"Gary" wrote in message
news
Doug,

It is a date/time format as general date.

I changed the name of the controls to have a txt prefix but to no avail.
I
still get the #NAME? error.

This is my current string:

=DCount("[INCR]","tblPURD","[reqdate]=" &
Format(me.txtreqdate,"\#mm\/dd\/yyyy\#") & " AND [initials] = '" &
me.txtinitials & "'")+1

(Changed table name from PickUpReqData to tblPURD

Thanks.

"Douglas J. Steele" wrote:

Is reqdate in your table a Date/Time field, or simply a Text field
containing a date? Does the field reqdate on your form contain a valid
date?

You could try changing the names of the controls on your forms: sometimes
Access gets confused when the name of the control and the name of the
field
is the same (even though it chooses to name the controls that way...) I
always rename all of my textboxes so that they start with a txt prefix,
so
that my statement would actually be:

=DCount("[incr]","pickupreqdata","[reqdate]=" &
Format(me.txtReqDate,"\#mm\/dd\/yyyy\#") & " AND initials = '" &
me.txtInitials & "'")+1

Also, see whether replace [incr] with * makes any difference (I wouldn't
really expect it to...)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"Gary" wrote in message
...
John,

When I place this in the control I get a #NAME? error:

=DCount("[incr]","pickupreqdata","[reqdate]=" &
Format(me.reqdate,"\#mm\/dd\/yyyy\#") & " AND initials = '" &
me.initials
&
"'")+1

The only things I changed from your string was the field to count (from
Initials to INCR) and from the form name to the table name.

Gary

"John Spencer" wrote:

I think Doug must have been tired. He missed an opening quote mark
before
the date format and an ampersand between "initials =" and
"me.initials"

=DCount("[initials]","pickupreqdatafrm","[reqdate]=" &
Format(me.reqdate,"\#mm\/dd\/yyyy\#") & " AND initials = '" &
me.initials
&
"'")+1


"Gary" wrote in message
...
Douglas,

Thanks for your reply.

When I enter your string into the control on the form, I receive
this
error:

The expression you entered contains invalid syntax.
You may have entered a comma without a preceding value or
identifier.

Thanks.

"Douglas J. Steele" wrote:

Yes, your format is incorrect.

The values you're checking need to go outside of the quotes.
Additional,
dates must be delimited with # (and in mm/dd/yyyy format,
regardless
of
what
your short date format has been set to through Regional Settings)*,
and
strings with quotes.

Try the following:

=DCount("[initials]","pickupreqdatafrm","[reqdate]=" &
Format(me.reqdate,
\#mm\/dd\/yyyy\#") & " AND initials = '" me.initials & "'")+1

Note that, exagerated for clarity, that last bit is:

& " AND initials = ' " me.initials & " ' "

* Okay, so it's not absolutely necessary to use mm/dd/yyyy format.
You
can
use any unambiguous format, such as yyyy-mm-dd or dd mmm yyyy. The
point
is,
you can't use dd/mm/yyyy, even if that's what your short date
format
has
been set to.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"Gary" wrote in message
...
I can get this to work in a totals query but not on a form.

I have this for the control source:

=DCount("[initials]","pickupreqdatafrm","[reqdate]=me.reqdate AND
initials
=me.initials")+1

Do I have the format wrong? I'm looking to return the count value
and
then
concatenate the 3 parts of the Ref#.


I want my db to generate our reference # automatically based on
input
from
other fields. The number is the date record entered, the users
initials,
and
an incremental number. Ex. 120505GMM02

My form is based on a query and I have the query generating the
first
two
components from the date entered in reqdate and the initials
entered.
How
can
I get an incremental number generated to go with the rest.

The number should start with 01 for each day and also for each
user.
Therefore when GMM is entering his third record on the 5th it
should
generate
120505GMM03 even if there were 10 other records entered on the
fifth
by
other
users. Each users numbers would start at 01 for each day.

Thanks.

What happens when you type that DCount into the Immediate Window (Ctrl-G)?
Do you get an error, or do you get the correct value?

If you've got a Totals query, you can write a routine to retrieve that value
for you.

For instance, something like the following should work:

Function GetNextValue( _
ReqDt As Date, _
Inits As String _
) As Long

Dim dbCurr As DAO.Database
Dim rstCurr As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT Count(*) As RowCount " _
"FROM tblPURD WHERE " _
"[reqdate]=" & Format(ReqDt,"\#mm\/dd\/yyyy\#") & _
" AND [initials] = '" Inits & "'"
Set dbCurr = CurrentDb()
Set rstCurr = dbCurr.OpenRecordset(strSQL)
GetNextValue = rstCurr!RowCount + 1
rstCurr.Close
Set rstCurr = Nothing
Set dbCurr = Nothing

Exit Function



--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"Gary" wrote in message
...
Yes.

txtreqdate is general date. When I input it I enter, i.e., 12/12 and the
result displayed 12/12/2005

txtInitials is a list box with four choices bound to the field INITIALs in
table tblPURD. Would that affect the result?

When I run a totals query I am able to get the result. Is there any way
to
use the results of that query in the control for that field?

Thanks.

Gary
"Douglas J. Steele" wrote:

You're sure that txtregdate and txtinitials have legitimate values in
them?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"Gary" wrote in message
news
Doug,

It is a date/time format as general date.

I changed the name of the controls to have a txt prefix but to no
avail.
I
still get the #NAME? error.

This is my current string:

=DCount("[INCR]","tblPURD","[reqdate]=" &
Format(me.txtreqdate,"\#mm\/dd\/yyyy\#") & " AND [initials] = '" &
me.txtinitials & "'")+1

(Changed table name from PickUpReqData to tblPURD

Thanks.

"Douglas J. Steele" wrote:

Is reqdate in your table a Date/Time field, or simply a Text field
containing a date? Does the field reqdate on your form contain a valid
date?

You could try changing the names of the controls on your forms:
sometimes
Access gets confused when the name of the control and the name of the
field
is the same (even though it chooses to name the controls that way...)
I
always rename all of my textboxes so that they start with a txt
prefix,
so
that my statement would actually be:

=DCount("[incr]","pickupreqdata","[reqdate]=" &
Format(me.txtReqDate,"\#mm\/dd\/yyyy\#") & " AND initials = '" &
me.txtInitials & "'")+1

Also, see whether replace [incr] with * makes any difference (I
wouldn't
really expect it to...)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"Gary" wrote in message
...
John,

When I place this in the control I get a #NAME? error:

=DCount("[incr]","pickupreqdata","[reqdate]=" &
Format(me.reqdate,"\#mm\/dd\/yyyy\#") & " AND initials = '" &
me.initials
&
"'")+1

The only things I changed from your string was the field to count
(from
Initials to INCR) and from the form name to the table name.

Gary

"John Spencer" wrote:

I think Doug must have been tired. He missed an opening quote mark
before
the date format and an ampersand between "initials =" and
"me.initials"

=DCount("[initials]","pickupreqdatafrm","[reqdate]=" &
Format(me.reqdate,"\#mm\/dd\/yyyy\#") & " AND initials = '" &
me.initials
&
"'")+1


"Gary" wrote in message
...
Douglas,

Thanks for your reply.

When I enter your string into the control on the form, I receive
this
error:

The expression you entered contains invalid syntax.
You may have entered a comma without a preceding value or
identifier.

Thanks.

"Douglas J. Steele" wrote:

Yes, your format is incorrect.

The values you're checking need to go outside of the quotes.
Additional,
dates must be delimited with # (and in mm/dd/yyyy format,
regardless
of
what
your short date format has been set to through Regional
Settings)*,
and
strings with quotes.

Try the following:

=DCount("[initials]","pickupreqdatafrm","[reqdate]=" &
Format(me.reqdate,
\#mm\/dd\/yyyy\#") & " AND initials = '" me.initials & "'")+1

Note that, exagerated for clarity, that last bit is:

& " AND initials = ' " me.initials & " ' "

* Okay, so it's not absolutely necessary to use mm/dd/yyyy
format.
You
can
use any unambiguous format, such as yyyy-mm-dd or dd mmm yyyy.
The
point
is,
you can't use dd/mm/yyyy, even if that's what your short date
format
has
been set to.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"Gary" wrote in message
...
I can get this to work in a totals query but not on a form.

I have this for the control source:

=DCount("[initials]","pickupreqdatafrm","[reqdate]=me.reqdate
AND
initials
=me.initials")+1

Do I have the format wrong? I'm looking to return the count
value
and
then
concatenate the 3 parts of the Ref#.


I want my db to generate our reference # automatically based
on
input
from
other fields. The number is the date record entered, the users
initials,
and
an incremental number. Ex. 120505GMM02

My form is based on a query and I have the query generating
the
first
two
components from the date entered in reqdate and the initials
entered.
How
can
I get an incremental number generated to go with the rest.

The number should start with 01 for each day and also for each
user.
Therefore when GMM is entering his third record on the 5th it
should
generate
120505GMM03 even if there were 10 other records entered on the
fifth
by
other
users. Each users numbers would start at 01 for each day.

Thanks.

From the immediate window I get the following error:

Compile Error:
Expected: Line number or Label or Statement or End of Statement



"Douglas J. Steele" wrote:

What happens when you type that DCount into the Immediate Window (Ctrl-G)?
Do you get an error, or do you get the correct value?

If you've got a Totals query, you can write a routine to retrieve that value
for you.

For instance, something like the following should work:

Function GetNextValue( _
ReqDt As Date, _
Inits As String _
) As Long

Dim dbCurr As DAO.Database
Dim rstCurr As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT Count(*) As RowCount " _
"FROM tblPURD WHERE " _
"[reqdate]=" & Format(ReqDt,"\#mm\/dd\/yyyy\#") & _
" AND [initials] = '" Inits & "'"
Set dbCurr = CurrentDb()
Set rstCurr = dbCurr.OpenRecordset(strSQL)
GetNextValue = rstCurr!RowCount + 1
rstCurr.Close
Set rstCurr = Nothing
Set dbCurr = Nothing

Exit Function



--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"Gary" wrote in message
...
Yes.

txtreqdate is general date. When I input it I enter, i.e., 12/12 and the
result displayed 12/12/2005

txtInitials is a list box with four choices bound to the field INITIALs in
table tblPURD. Would that affect the result?

When I run a totals query I am able to get the result. Is there any way
to
use the results of that query in the control for that field?

Thanks.

Gary
"Douglas J. Steele" wrote:

You're sure that txtregdate and txtinitials have legitimate values in
them?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"Gary" wrote in message
news Doug,

It is a date/time format as general date.

I changed the name of the controls to have a txt prefix but to no
avail.
I
still get the #NAME? error.

This is my current string:

=DCount("[INCR]","tblPURD","[reqdate]=" &
Format(me.txtreqdate,"\#mm\/dd\/yyyy\#") & " AND [initials] = '" &
me.txtinitials & "'")+1

(Changed table name from PickUpReqData to tblPURD

Thanks.

"Douglas J. Steele" wrote:

Is reqdate in your table a Date/Time field, or simply a Text field
containing a date? Does the field reqdate on your form contain a valid
date?

You could try changing the names of the controls on your forms:
sometimes
Access gets confused when the name of the control and the name of the
field
is the same (even though it chooses to name the controls that way...)
I
always rename all of my textboxes so that they start with a txt
prefix,
so
that my statement would actually be:

=DCount("[incr]","pickupreqdata","[reqdate]=" &
Format(me.txtReqDate,"\#mm\/dd\/yyyy\#") & " AND initials = '" &
me.txtInitials & "'")+1

Also, see whether replace [incr] with * makes any difference (I
wouldn't
really expect it to...)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"Gary" wrote in message
...
John,

When I place this in the control I get a #NAME? error:

=DCount("[incr]","pickupreqdata","[reqdate]=" &
Format(me.reqdate,"\#mm\/dd\/yyyy\#") & " AND initials = '" &
me.initials
&
"'")+1

The only things I changed from your string was the field to count
(from
Initials to INCR) and from the form name to the table name.

Gary

"John Spencer" wrote:

I think Doug must have been tired. He missed an opening quote mark
before
the date format and an ampersand between "initials =" and
"me.initials"

=DCount("[initials]","pickupreqdatafrm","[reqdate]=" &
Format(me.reqdate,"\#mm\/dd\/yyyy\#") & " AND initials = '" &
me.initials
&
"'")+1


"Gary" wrote in message
...
Douglas,

Thanks for your reply.

When I enter your string into the control on the form, I receive
this
error:

The expression you entered contains invalid syntax.
You may have entered a comma without a preceding value or
identifier.

Thanks.

"Douglas J. Steele" wrote:

Yes, your format is incorrect.

The values you're checking need to go outside of the quotes.
Additional,
dates must be delimited with # (and in mm/dd/yyyy format,
regardless
of
what
your short date format has been set to through Regional
Settings)*,
and
strings with quotes.

Try the following:

=DCount("[initials]","pickupreqdatafrm","[reqdate]=" &
Format(me.reqdate,
\#mm\/dd\/yyyy\#") & " AND initials = '" me.initials & "'")+1

Note that, exagerated for clarity, that last bit is:

& " AND initials = ' " me.initials & " ' "

* Okay, so it's not absolutely necessary to use mm/dd/yyyy
format.
You
can
use any unambiguous format, such as yyyy-mm-dd or dd mmm yyyy.
The
point
is,
you can't use dd/mm/yyyy, even if that's what your short date
format
has
been set to.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"Gary" wrote in message
...
I can get this to work in a totals query but not on a form.

I have this for the control source:

=DCount("[initials]","pickupreqdatafrm","[reqdate]=me.reqdate
AND
initials
=me.initials")+1

Do I have the format wrong? I'm looking to return the count
value
and
then
concatenate the 3 parts of the Ref#.


I want my db to generate our reference # automatically based
on
input
from
other fields. The number is the date record entered, the users
initials,
and
an incremental number. Ex. 120505GMM02

My form is based on a query and I have the query generating
the
first
two
components from the date entered in reqdate and the initials
entered.
How
can
I get an incremental number generated to go with the rest.

The number should start with 01 for each day and also for each
user.
Therefore when GMM is entering his third record on the 5th it
should
generate
120505GMM03 even if there were 10 other records entered on the
fifth
by
other
users. Each users numbers would start at 01 for each day.

Thanks.

Exactly what did you type in the immediate window?

As long as your form is open, with values in the appropriate text boxes, you
should be typing

?DCount("[INCR]","tblPURD","[reqdate]=" &
Format(me.txtreqdate,"\#mm\/dd\/yyyy\#") & " AND [initials] = '" &
me.txtinitials & "'")+1

and hitting Enter.


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"Gary" wrote in message
...
From the immediate window I get the following error:

Compile Error:
Expected: Line number or Label or Statement or End of Statement



"Douglas J. Steele" wrote:

What happens when you type that DCount into the Immediate Window
(Ctrl-G)?
Do you get an error, or do you get the correct value?

If you've got a Totals query, you can write a routine to retrieve that
value
for you.

For instance, something like the following should work:

Function GetNextValue( _
ReqDt As Date, _
Inits As String _
) As Long

Dim dbCurr As DAO.Database
Dim rstCurr As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT Count(*) As RowCount " _
"FROM tblPURD WHERE " _
"[reqdate]=" & Format(ReqDt,"\#mm\/dd\/yyyy\#") & _
" AND [initials] = '" Inits & "'"
Set dbCurr = CurrentDb()
Set rstCurr = dbCurr.OpenRecordset(strSQL)
GetNextValue = rstCurr!RowCount + 1
rstCurr.Close
Set rstCurr = Nothing
Set dbCurr = Nothing

Exit Function



--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"Gary" wrote in message
...
Yes.

txtreqdate is general date. When I input it I enter, i.e., 12/12 and
the
result displayed 12/12/2005

txtInitials is a list box with four choices bound to the field INITIALs
in
table tblPURD. Would that affect the result?

When I run a totals query I am able to get the result. Is there any
way
to
use the results of that query in the control for that field?

Thanks.

Gary
"Douglas J. Steele" wrote:

You're sure that txtregdate and txtinitials have legitimate values in
them?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"Gary" wrote in message
news Doug,

It is a date/time format as general date.

I changed the name of the controls to have a txt prefix but to no
avail.
I
still get the #NAME? error.

This is my current string:

=DCount("[INCR]","tblPURD","[reqdate]=" &
Format(me.txtreqdate,"\#mm\/dd\/yyyy\#") & " AND [initials] = '" &
me.txtinitials & "'")+1

(Changed table name from PickUpReqData to tblPURD

Thanks.

"Douglas J. Steele" wrote:

Is reqdate in your table a Date/Time field, or simply a Text field
containing a date? Does the field reqdate on your form contain a
valid
date?

You could try changing the names of the controls on your forms:
sometimes
Access gets confused when the name of the control and the name of
the
field
is the same (even though it chooses to name the controls that
way...)
I
always rename all of my textboxes so that they start with a txt
prefix,
so
that my statement would actually be:

=DCount("[incr]","pickupreqdata","[reqdate]=" &
Format(me.txtReqDate,"\#mm\/dd\/yyyy\#") & " AND initials = '" &
me.txtInitials & "'")+1

Also, see whether replace [incr] with * makes any difference (I
wouldn't
really expect it to...)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"Gary" wrote in message
...
John,

When I place this in the control I get a #NAME? error:

=DCount("[incr]","pickupreqdata","[reqdate]=" &
Format(me.reqdate,"\#mm\/dd\/yyyy\#") & " AND initials = '" &
me.initials
&
"'")+1

The only things I changed from your string was the field to count
(from
Initials to INCR) and from the form name to the table name.

Gary

"John Spencer" wrote:

I think Doug must have been tired. He missed an opening quote
mark
before
the date format and an ampersand between "initials =" and
"me.initials"

=DCount("[initials]","pickupreqdatafrm","[reqdate]=" &
Format(me.reqdate,"\#mm\/dd\/yyyy\#") & " AND initials = '" &
me.initials
&
"'")+1


"Gary" wrote in message
...
Douglas,

Thanks for your reply.

When I enter your string into the control on the form, I
receive
this
error:

The expression you entered contains invalid syntax.
You may have entered a comma without a preceding value or
identifier.

Thanks.

"Douglas J. Steele" wrote:

Yes, your format is incorrect.

The values you're checking need to go outside of the quotes.
Additional,
dates must be delimited with # (and in mm/dd/yyyy format,
regardless
of
what
your short date format has been set to through Regional
Settings)*,
and
strings with quotes.

Try the following:

=DCount("[initials]","pickupreqdatafrm","[reqdate]=" &
Format(me.reqdate,
\#mm\/dd\/yyyy\#") & " AND initials = '" me.initials & "'")+1

Note that, exagerated for clarity, that last bit is:

& " AND initials = ' " me.initials & " ' "

* Okay, so it's not absolutely necessary to use mm/dd/yyyy
format.
You
can
use any unambiguous format, such as yyyy-mm-dd or dd mmm
yyyy.
The
point
is,
you can't use dd/mm/yyyy, even if that's what your short date
format
has
been set to.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"Gary" wrote in message
...
I can get this to work in a totals query but not on a form.

I have this for the control source:

=DCount("[initials]","pickupreqdatafrm","[reqdate]=me.reqdate
AND
initials
=me.initials")+1

Do I have the format wrong? I'm looking to return the count
value
and
then
concatenate the 3 parts of the Ref#.


I want my db to generate our reference # automatically
based
on
input
from
other fields. The number is the date record entered, the
users
initials,
and
an incremental number. Ex. 120505GMM02

My form is based on a query and I have the query generating
the
first
two
components from the date entered in reqdate and the
initials
entered.
How
can
I get an incremental number generated to go with the rest.

The number should start with 01 for each day and also for
each
user.
Therefore when GMM is entering his third record on the 5th
it
should
generate
120505GMM03 even if there were 10 other records entered on
the
fifth
by
other
users. Each users numbers would start at 01 for each day.

Thanks.

I had entered it with the = sign.

When I use the ? I receive the error: Compile error - variable not yet
created in this context.

"Douglas J. Steele" wrote:

Exactly what did you type in the immediate window?

As long as your form is open, with values in the appropriate text boxes, you
should be typing

?DCount("[INCR]","tblPURD","[reqdate]=" &
Format(me.txtreqdate,"\#mm\/dd\/yyyy\#") & " AND [initials] = '" &
me.txtinitials & "'")+1

and hitting Enter.


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"Gary" wrote in message
...
From the immediate window I get the following error:

Compile Error:
Expected: Line number or Label or Statement or End of Statement



"Douglas J. Steele" wrote:

What happens when you type that DCount into the Immediate Window
(Ctrl-G)?
Do you get an error, or do you get the correct value?

If you've got a Totals query, you can write a routine to retrieve that
value
for you.

For instance, something like the following should work:

Function GetNextValue( _
ReqDt As Date, _
Inits As String _
) As Long

Dim dbCurr As DAO.Database
Dim rstCurr As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT Count(*) As RowCount " _
"FROM tblPURD WHERE " _
"[reqdate]=" & Format(ReqDt,"\#mm\/dd\/yyyy\#") & _
" AND [initials] = '" Inits & "'"
Set dbCurr = CurrentDb()
Set rstCurr = dbCurr.OpenRecordset(strSQL)
GetNextValue = rstCurr!RowCount + 1
rstCurr.Close
Set rstCurr = Nothing
Set dbCurr = Nothing

Exit Function



--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"Gary" wrote in message
...
Yes.

txtreqdate is general date. When I input it I enter, i.e., 12/12 and
the
result displayed 12/12/2005

txtInitials is a list box with four choices bound to the field INITIALs
in
table tblPURD. Would that affect the result?

When I run a totals query I am able to get the result. Is there any
way
to
use the results of that query in the control for that field?

Thanks.

Gary
"Douglas J. Steele" wrote:

You're sure that txtregdate and txtinitials have legitimate values in
them?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"Gary" wrote in message
news Doug,

It is a date/time format as general date.

I changed the name of the controls to have a txt prefix but to no
avail.
I
still get the #NAME? error.

This is my current string:

=DCount("[INCR]","tblPURD","[reqdate]=" &
Format(me.txtreqdate,"\#mm\/dd\/yyyy\#") & " AND [initials] = '" &
me.txtinitials & "'")+1

(Changed table name from PickUpReqData to tblPURD

Thanks.

"Douglas J. Steele" wrote:

Is reqdate in your table a Date/Time field, or simply a Text field
containing a date? Does the field reqdate on your form contain a
valid
date?

You could try changing the names of the controls on your forms:
sometimes
Access gets confused when the name of the control and the name of
the
field
is the same (even though it chooses to name the controls that
way...)
I
always rename all of my textboxes so that they start with a txt
prefix,
so
that my statement would actually be:

=DCount("[incr]","pickupreqdata","[reqdate]=" &
Format(me.txtReqDate,"\#mm\/dd\/yyyy\#") & " AND initials = '" &
me.txtInitials & "'")+1

Also, see whether replace [incr] with * makes any difference (I
wouldn't
really expect it to...)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"Gary" wrote in message
...
John,

When I place this in the control I get a #NAME? error:

=DCount("[incr]","pickupreqdata","[reqdate]=" &
Format(me.reqdate,"\#mm\/dd\/yyyy\#") & " AND initials = '" &
me.initials
&
"'")+1

The only things I changed from your string was the field to count
(from
Initials to INCR) and from the form name to the table name.

Gary

"John Spencer" wrote:

I think Doug must have been tired. He missed an opening quote
mark
before
the date format and an ampersand between "initials =" and
"me.initials"

=DCount("[initials]","pickupreqdatafrm","[reqdate]=" &
Format(me.reqdate,"\#mm\/dd\/yyyy\#") & " AND initials = '" &
me.initials
&
"'")+1


"Gary" wrote in message
...
Douglas,

Thanks for your reply.

When I enter your string into the control on the form, I
receive
this
error:

The expression you entered contains invalid syntax.
You may have entered a comma without a preceding value or
identifier.

Thanks.

"Douglas J. Steele" wrote:

Yes, your format is incorrect.

The values you're checking need to go outside of the quotes.
Additional,
dates must be delimited with # (and in mm/dd/yyyy format,
regardless
of
what
your short date format has been set to through Regional
Settings)*,
and
strings with quotes.

Try the following:

=DCount("[initials]","pickupreqdatafrm","[reqdate]=" &
Format(me.reqdate,
\#mm\/dd\/yyyy\#") & " AND initials = '" me.initials & "'")+1

Note that, exagerated for clarity, that last bit is:

& " AND initials = ' " me.initials & " ' "

* Okay, so it's not absolutely necessary to use mm/dd/yyyy
format.
You
can
use any unambiguous format, such as yyyy-mm-dd or dd mmm
yyyy.
The
point
is,
you can't use dd/mm/yyyy, even if that's what your short date
format
has
been set to.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"Gary" wrote in message
...
I can get this to work in a totals query but not on a form.

I have this for the control source:

=DCount("[initials]","pickupreqdatafrm","[reqdate]=me.reqdate
AND
initials
=me.initials")+1

Do I have the format wrong? I'm looking to return the count
value
and
then
concatenate the 3 parts of the Ref#.


I want my db to generate our reference # automatically
based
on
input
from
other fields. The number is the date record entered, the
users
initials,
and
an incremental number. Ex. 120505GMM02

My form is based on a query and I have the query generating
the
first
two
components from the date entered in reqdate and the
initials
entered.
How
can
I get an incremental number generated to go with the rest.

The number should start with 01 for each day and also for
each
user.
Therefore when GMM is entering his third record on the 5th
it

Darn. Just realized that it probably won't work, as the Immediate Window
won't be able to figure out the references to Me.

I just read your post a little closer. You mention that txtInitials is a
list box. It definitely won't work if the list box is set to multiselect
(even if you're only choosing a single value from the list). In your code,
before your call to DCount, check that the reference to Me.txtInitials is
returning what you think it should.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"Gary" wrote in message
...
I had entered it with the = sign.

When I use the ? I receive the error: Compile error - variable not yet
created in this context.

"Douglas J. Steele" wrote:

Exactly what did you type in the immediate window?

As long as your form is open, with values in the appropriate text boxes,
you
should be typing

?DCount("[INCR]","tblPURD","[reqdate]=" &
Format(me.txtreqdate,"\#mm\/dd\/yyyy\#") & " AND [initials] = '" &
me.txtinitials & "'")+1

and hitting Enter.


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"Gary" wrote in message
...
From the immediate window I get the following error:

Compile Error:
Expected: Line number or Label or Statement or End of Statement



"Douglas J. Steele" wrote:

What happens when you type that DCount into the Immediate Window
(Ctrl-G)?
Do you get an error, or do you get the correct value?

If you've got a Totals query, you can write a routine to retrieve that
value
for you.

For instance, something like the following should work:

Function GetNextValue( _
ReqDt As Date, _
Inits As String _
) As Long

Dim dbCurr As DAO.Database
Dim rstCurr As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT Count(*) As RowCount " _
"FROM tblPURD WHERE " _
"[reqdate]=" & Format(ReqDt,"\#mm\/dd\/yyyy\#") & _
" AND [initials] = '" Inits & "'"
Set dbCurr = CurrentDb()
Set rstCurr = dbCurr.OpenRecordset(strSQL)
GetNextValue = rstCurr!RowCount + 1
rstCurr.Close
Set rstCurr = Nothing
Set dbCurr = Nothing

Exit Function



--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"Gary" wrote in message
...
Yes.

txtreqdate is general date. When I input it I enter, i.e., 12/12
and
the
result displayed 12/12/2005

txtInitials is a list box with four choices bound to the field
INITIALs
in
table tblPURD. Would that affect the result?

When I run a totals query I am able to get the result. Is there any
way
to
use the results of that query in the control for that field?

Thanks.

Gary
"Douglas J. Steele" wrote:

You're sure that txtregdate and txtinitials have legitimate values
in
them?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"Gary" wrote in message
news Doug,

It is a date/time format as general date.

I changed the name of the controls to have a txt prefix but to no
avail.
I
still get the #NAME? error.

This is my current string:

=DCount("[INCR]","tblPURD","[reqdate]=" &
Format(me.txtreqdate,"\#mm\/dd\/yyyy\#") & " AND [initials] = '"
&
me.txtinitials & "'")+1

(Changed table name from PickUpReqData to tblPURD

Thanks.

"Douglas J. Steele" wrote:

Is reqdate in your table a Date/Time field, or simply a Text
field
containing a date? Does the field reqdate on your form contain a
valid
date?

You could try changing the names of the controls on your forms:
sometimes
Access gets confused when the name of the control and the name
of
the
field
is the same (even though it chooses to name the controls that
way...)
I
always rename all of my textboxes so that they start with a txt
prefix,
so
that my statement would actually be:

=DCount("[incr]","pickupreqdata","[reqdate]=" &
Format(me.txtReqDate,"\#mm\/dd\/yyyy\#") & " AND initials = '" &
me.txtInitials & "'")+1

Also, see whether replace [incr] with * makes any difference (I
wouldn't
really expect it to...)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"Gary" wrote in message
...
John,

When I place this in the control I get a #NAME? error:

=DCount("[incr]","pickupreqdata","[reqdate]=" &
Format(me.reqdate,"\#mm\/dd\/yyyy\#") & " AND initials = '" &
me.initials
&
"'")+1

The only things I changed from your string was the field to
count
(from
Initials to INCR) and from the form name to the table name.

Gary

"John Spencer" wrote:

I think Doug must have been tired. He missed an opening quote
mark
before
the date format and an ampersand between "initials =" and
"me.initials"

=DCount("[initials]","pickupreqdatafrm","[reqdate]=" &
Format(me.reqdate,"\#mm\/dd\/yyyy\#") & " AND initials = '" &
me.initials
&
"'")+1


"Gary" wrote in message
...
Douglas,

Thanks for your reply.

When I enter your string into the control on the form, I
receive
this
error:

The expression you entered contains invalid syntax.
You may have entered a comma without a preceding value or
identifier.

Thanks.

"Douglas J. Steele" wrote:

Yes, your format is incorrect.

The values you're checking need to go outside of the
quotes.
Additional,
dates must be delimited with # (and in mm/dd/yyyy format,
regardless
of
what
your short date format has been set to through Regional
Settings)*,
and
strings with quotes.

Try the following:

=DCount("[initials]","pickupreqdatafrm","[reqdate]=" &
Format(me.reqdate,
\#mm\/dd\/yyyy\#") & " AND initials = '" me.initials &
"'")+1

Note that, exagerated for clarity, that last bit is:

& " AND initials = ' " me.initials & " ' "

* Okay, so it's not absolutely necessary to use mm/dd/yyyy
format.
You
can
use any unambiguous format, such as yyyy-mm-dd or dd mmm
yyyy.
The
point
is,
you can't use dd/mm/yyyy, even if that's what your short
date
format
has
been set to.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"Gary" wrote in message
...
I can get this to work in a totals query but not on a
form.

I have this for the control source:

=DCount("[initials]","pickupreqdatafrm","[reqdate]=me.reqdate
AND
initials
=me.initials")+1

Do I have the format wrong? I'm looking to return the
count
value
and
then
concatenate the 3 parts of the Ref#.


I want my db to generate our reference # automatically
based
on
input
from
other fields. The number is the date record entered, the
users
initials,
and
an incremental number. Ex. 120505GMM02

My form is based on a query and I have the query
generating
the
first
two
components from the date entered in reqdate and the
initials
entered.
How
can
I get an incremental number generated to go with the
rest.

The number should start with 01 for each day and also
for
each
user.
Therefore when GMM is entering his third record on the
5th
it