Use of DCount

[Tony Williams]

I am using this code in the before update event on a control to check
whether the value already exists
Cancel = DCount("*", "tblmonth", [txtMonth] = "" & Me.txtMonth & """")
If Cancel Then MsgBox "This Quarter already exists."
The control is based on a field txtmonth which is a date/time field in the
table tblmonth, however if I enter a date that already exists I don't get my
error message. Can anyone see anything wrong with my code?
TIA
Tony Williams

 

[Rick Brandt]

I am using this code in the before update event on a control to check
whether the value already exists
Cancel = DCount("*", "tblmonth", [txtMonth] = "" & Me.txtMonth & """")
If Cancel Then MsgBox "This Quarter already exists."
The control is based on a field txtmonth which is a date/time field in the
table tblmonth, however if I enter a date that already exists I don't get my
error message. Can anyone see anything wrong with my code?

Is it _really_ a DateTime field? If so then you need to delimit with #, not
single quotes. Also, are you sure the values in the table all have exactly
midnight for the time? Regardless of how you format them for display, any test
has to take the time component into account.

 

[Tony Williams]

Thanks Rick I've tried this
Cancel = DCount(#*#, "tblmonth", [txtMonth] = ## & Me.txtMonth & ##)
If Cancel Then MsgBox "This Quarter already exists."
but I get a syntax error. Could you point me in the right direction?
Yes this is a date field, but it doesn't have any time, the format is Short
Date?
Thanks again
Tony

I am using this code in the before update event on a control to check
whether the value already exists
Cancel = DCount("*", "tblmonth", [txtMonth] = "" & Me.txtMonth & """")
If Cancel Then MsgBox "This Quarter already exists."
The control is based on a field txtmonth which is a date/time field in the
table tblmonth, however if I enter a date that already exists I don't get my
error message. Can anyone see anything wrong with my code?

Is it _really_ a DateTime field? If so then you need to delimit with #, not
single quotes. Also, are you sure the values in the table all have exactly
midnight for the time? Regardless of how you format them for display, any test
has to take the time component into account.

 

[Ken Snell [MVP]]

Close:

Cancel = DCount("*", "tblmonth", "[txtMonth] = #" & Me.txtMonth & "#")

--

Ken Snell
<MS ACCESS MVP>

Thanks Rick I've tried this
Cancel = DCount(#*#, "tblmonth", [txtMonth] = ## & Me.txtMonth & ##)
If Cancel Then MsgBox "This Quarter already exists."
but I get a syntax error. Could you point me in the right direction?
Yes this is a date field, but it doesn't have any time, the format is Short
Date?
Thanks again
Tony

I am using this code in the before update event on a control to check
whether the value already exists
Cancel = DCount("*", "tblmonth", [txtMonth] = "" & Me.txtMonth & """")
If Cancel Then MsgBox "This Quarter already exists."
The control is based on a field txtmonth which is a date/time field in the
table tblmonth, however if I enter a date that already exists I don't get my
error message. Can anyone see anything wrong with my code?

Is it _really_ a DateTime field? If so then you need to delimit with #, not
single quotes. Also, are you sure the values in the table all have exactly
midnight for the time? Regardless of how you format them for display,

any

test
has to take the time component into account.

 

[Tony Williams]

Thanks Ken used your code but still don't get message

Close:

Cancel = DCount("*", "tblmonth", "[txtMonth] = #" & Me.txtMonth & "#")

--

Ken Snell
<MS ACCESS MVP>

Thanks Rick I've tried this
Cancel = DCount(#*#, "tblmonth", [txtMonth] = ## & Me.txtMonth & ##)
If Cancel Then MsgBox "This Quarter already exists."
but I get a syntax error. Could you point me in the right direction?
Yes this is a date field, but it doesn't have any time, the format is Short
Date?
Thanks again
Tony

I am using this code in the before update event on a control to check
whether the value already exists
Cancel = DCount("*", "tblmonth", [txtMonth] = "" & Me.txtMonth & """")
If Cancel Then MsgBox "This Quarter already exists."
The control is based on a field txtmonth which is a date/time field in the
table tblmonth, however if I enter a date that already exists I don't get my
error message. Can anyone see anything wrong with my code?

Is it _really_ a DateTime field? If so then you need to delimit with #, not
single quotes. Also, are you sure the values in the table all have exactly
midnight for the time? Regardless of how you format them for display,

any

test
has to take the time component into account.

 

[Ken Snell [MVP]]

That's because you want to test for Cancel = False to show the message, not
if it's True, the way your expression is set up.

However, assuming that you want to have Cancel be True when there are no
records, use this expression instead:

Cancel = (DCount("*", "tblmonth", "[txtMonth] = #" & Me.txtMonth & "#") = 0)

--

Ken Snell
<MS ACCESS MVP>

Thanks Ken used your code but still don't get message

Close:

Cancel = DCount("*", "tblmonth", "[txtMonth] = #" & Me.txtMonth & "#")

--

Ken Snell
<MS ACCESS MVP>

Thanks Rick I've tried this
Cancel = DCount(#*#, "tblmonth", [txtMonth] = ## & Me.txtMonth & ##)
If Cancel Then MsgBox "This Quarter already exists."
but I get a syntax error. Could you point me in the right direction?
Yes this is a date field, but it doesn't have any time, the format is Short
Date?
Thanks again
Tony
I am using this code in the before update event on a control to check
whether the value already exists
Cancel = DCount("*", "tblmonth", [txtMonth] = "" & Me.txtMonth & """")
If Cancel Then MsgBox "This Quarter already exists."
The control is based on a field txtmonth which is a date/time field in
the
table tblmonth, however if I enter a date that already exists I don't
get my
error message. Can anyone see anything wrong with my code?

Is it _really_ a DateTime field? If so then you need to delimit with #,
not
single quotes. Also, are you sure the values in the table all have
exactly
midnight for the time? Regardless of how you format them for

display,
any

test
has to take the time component into account.

 

[Tony Williams]

Thanks Ken that worked EXCEPT I now get an error message that says the value
in the field violates the validation rule for the field but I haven't got
any validation rules? Can you help?
It's 6.30pm here in the UK and I'm going "screen blind" so if I don't
respond to any further answer until tomorrow please excuse me, I'll just
have to call it a day!
Thanks again
Tony Williams

That's because you want to test for Cancel = False to show the message, not
if it's True, the way your expression is set up.

However, assuming that you want to have Cancel be True when there are no
records, use this expression instead:

Cancel = (DCount("*", "tblmonth", "[txtMonth] = #" & Me.txtMonth & "#") = 0)

--

Ken Snell
<MS ACCESS MVP>

Thanks Ken used your code but still don't get message

Close:

Cancel = DCount("*", "tblmonth", "[txtMonth] = #" & Me.txtMonth & "#")

--

Ken Snell
<MS ACCESS MVP>


Thanks Rick I've tried this
Cancel = DCount(#*#, "tblmonth", [txtMonth] = ## & Me.txtMonth & ##)
If Cancel Then MsgBox "This Quarter already exists."
but I get a syntax error. Could you point me in the right direction?
Yes this is a date field, but it doesn't have any time, the format is
Short
Date?
Thanks again
Tony
I am using this code in the before update event on a control to check
whether the value already exists
Cancel = DCount("*", "tblmonth", [txtMonth] = "" & Me.txtMonth & """")
If Cancel Then MsgBox "This Quarter already exists."
The control is based on a field txtmonth which is a date/time field in
the
table tblmonth, however if I enter a date that already exists I don't
get my
error message. Can anyone see anything wrong with my code?

Is it _really_ a DateTime field? If so then you need to delimit with #,
not
single quotes. Also, are you sure the values in the table all have
exactly
midnight for the time? Regardless of how you format them for display,
any
test
has to take the time component into account.

 

[Ken Snell [MVP]]

Validation rule possibley can mean
(1) validation for the field in the table's design view;
(2) validation for the control on the form in the form's design view;
(3) required property is set to yes;
(4) allow zero length string property is set to no;
(5) wrong data type for the field's data type (e.g., trying to store a
letter in a numeric field)
(6) nonunique value being entered into a field that is required to be
unique;
and perhaps others.

However, I have no idea which step of the code is causing this error, which
control or field is getting a value, etc. Can you give more details?
--

Ken Snell
<MS ACCESS MVP>

Thanks Ken that worked EXCEPT I now get an error message that says the value
in the field violates the validation rule for the field but I haven't got
any validation rules? Can you help?
It's 6.30pm here in the UK and I'm going "screen blind" so if I don't
respond to any further answer until tomorrow please excuse me, I'll just
have to call it a day!
Thanks again
Tony Williams

That's because you want to test for Cancel = False to show the message, not
if it's True, the way your expression is set up.

However, assuming that you want to have Cancel be True when there are no
records, use this expression instead:

Cancel = (DCount("*", "tblmonth", "[txtMonth] = #" & Me.txtMonth & "#")

=

0)

--

Ken Snell
<MS ACCESS MVP>

Thanks Ken used your code but still don't get message

:

Close:

Cancel = DCount("*", "tblmonth", "[txtMonth] = #" & Me.txtMonth & "#")

--

Ken Snell
<MS ACCESS MVP>


Thanks Rick I've tried this
Cancel = DCount(#*#, "tblmonth", [txtMonth] = ## & Me.txtMonth & ##)
If Cancel Then MsgBox "This Quarter already exists."
but I get a syntax error. Could you point me in the right direction?
Yes this is a date field, but it doesn't have any time, the format is
Short
Date?
Thanks again
Tony
I am using this code in the before update event on a control to check
whether the value already exists
Cancel = DCount("*", "tblmonth", [txtMonth] = "" & Me.txtMonth

&
"""")

If Cancel Then MsgBox "This Quarter already exists."
The control is based on a field txtmonth which is a date/time field in
the
table tblmonth, however if I enter a date that already exists

I
don't

get my
error message. Can anyone see anything wrong with my code?

Is it _really_ a DateTime field? If so then you need to delimit with #,
not
single quotes. Also, are you sure the values in the table all have
exactly
midnight for the time? Regardless of how you format them for display,
any
test
has to take the time component into account.

 

[Tony Williams]

Ken I've checked through all the validation controls and there are none.
Required property is set to No. Data Type is Short Date and I am entering
say 30/06/04.
When I enter a value in the txtmonth control on my form as say 30/06/04 I
want to check that that date has not been input before. The txtmonth field
in my table tblmonth is a key field and indexed with no duplicates. Does
that help?
Thanks for your patience
Tony

Validation rule possibley can mean
(1) validation for the field in the table's design view;
(2) validation for the control on the form in the form's design view;
(3) required property is set to yes;
(4) allow zero length string property is set to no;
(5) wrong data type for the field's data type (e.g., trying to store a
letter in a numeric field)
(6) nonunique value being entered into a field that is required to be
unique;
and perhaps others.

However, I have no idea which step of the code is causing this error, which
control or field is getting a value, etc. Can you give more details?
--

Ken Snell
<MS ACCESS MVP>

Thanks Ken that worked EXCEPT I now get an error message that says the value
in the field violates the validation rule for the field but I haven't got
any validation rules? Can you help?
It's 6.30pm here in the UK and I'm going "screen blind" so if I don't
respond to any further answer until tomorrow please excuse me, I'll just
have to call it a day!
Thanks again
Tony Williams

That's because you want to test for Cancel = False to show the

message,
not

if it's True, the way your expression is set up.

However, assuming that you want to have Cancel be True when there are no
records, use this expression instead:

Cancel = (DCount("*", "tblmonth", "[txtMonth] = #" & Me.txtMonth &

"#")

=

0)

--

Ken Snell
<MS ACCESS MVP>

Thanks Ken used your code but still don't get message

:

Close:

Cancel = DCount("*", "tblmonth", "[txtMonth] = #" & Me.txtMonth & "#")

--

Ken Snell
<MS ACCESS MVP>


Thanks Rick I've tried this
Cancel = DCount(#*#, "tblmonth", [txtMonth] = ## & Me.txtMonth & ##)
If Cancel Then MsgBox "This Quarter already exists."
but I get a syntax error. Could you point me in the right direction?
Yes this is a date field, but it doesn't have any time, the

format
is

Short
Date?
Thanks again
Tony
I am using this code in the before update event on a control to
check
whether the value already exists
Cancel = DCount("*", "tblmonth", [txtMonth] = "" &

Me.txtMonth

&

exists

I

 

[Ken Snell [MVP]]

Is the error occurring in the code or by just entering the "date" into the
control?

Is the control bound to a field? What is the data type format of that field?
What are your regional settings for date formats?

--

Ken Snell
<MS ACCESS MVP>

Ken I've checked through all the validation controls and there are none.
Required property is set to No. Data Type is Short Date and I am entering
say 30/06/04.
When I enter a value in the txtmonth control on my form as say 30/06/04 I
want to check that that date has not been input before. The txtmonth field
in my table tblmonth is a key field and indexed with no duplicates. Does
that help?
Thanks for your patience
Tony

Validation rule possibley can mean
(1) validation for the field in the table's design view;
(2) validation for the control on the form in the form's design view;
(3) required property is set to yes;
(4) allow zero length string property is set to no;
(5) wrong data type for the field's data type (e.g., trying to store a
letter in a numeric field)
(6) nonunique value being entered into a field that is required to be
unique;
and perhaps others.

However, I have no idea which step of the code is causing this error, which
control or field is getting a value, etc. Can you give more details?

are

no

records, use this expression instead:

Cancel = (DCount("*", "tblmonth", "[txtMonth] = #" & Me.txtMonth &

"#")

=
0)

--

Ken Snell
<MS ACCESS MVP>

Thanks Ken used your code but still don't get message

:

Close:

Cancel = DCount("*", "tblmonth", "[txtMonth] = #" & Me.txtMonth

&
"#")

--

Ken Snell
<MS ACCESS MVP>


Thanks Rick I've tried this
Cancel = DCount(#*#, "tblmonth", [txtMonth] = ## & Me.txtMonth

&
##)

If Cancel Then MsgBox "This Quarter already exists."
but I get a syntax error. Could you point me in the right direction?
Yes this is a date field, but it doesn't have any time, the format
is
Short
Date?
Thanks again
Tony
I am using this code in the before update event on a

control

to

check
whether the value already exists
Cancel = DCount("*", "tblmonth", [txtMonth] = "" &

Me.txtMonth

&
"""")
If Cancel Then MsgBox "This Quarter already exists."
The control is based on a field txtmonth which is a date/time
field in
the
table tblmonth, however if I enter a date that already

exists

I
don't
get my
error message. Can anyone see anything wrong with my code?

Is it _really_ a DateTime field? If so then you need to delimit
with #,
not
single quotes. Also, are you sure the values in the table all
have
exactly
midnight for the time? Regardless of how you format them for
display,
any
test
has to take the time component into account.

 

[Tony Williams]

Ken, the error occurs just by entering a date and then tabbing to the next
field. The control is bound to a field txtmonth (same name could that cause
a problem?)
Regional settings for short date is dd/mm/yy (UK style)
Any help? This is really beginning to bug me it must be something simple but
I just can't see it!
Thanks again
Tony

Is the error occurring in the code or by just entering the "date" into the
control?

Is the control bound to a field? What is the data type format of that field?
What are your regional settings for date formats?

--

Ken Snell
<MS ACCESS MVP>

Ken I've checked through all the validation controls and there are none.
Required property is set to No. Data Type is Short Date and I am entering
say 30/06/04.
When I enter a value in the txtmonth control on my form as say 30/06/04 I
want to check that that date has not been input before. The txtmonth field
in my table tblmonth is a key field and indexed with no duplicates. Does
that help?
Thanks for your patience
Tony

store

a

letter in a numeric field)
(6) nonunique value being entered into a field that is required to be
unique;
and perhaps others.

However, I have no idea which step of the code is causing this error, which
control or field is getting a value, etc. Can you give more details?
--

Ken Snell
<MS ACCESS MVP>


Thanks Ken that worked EXCEPT I now get an error message that says the
value
in the field violates the validation rule for the field but I

haven't
got

any validation rules? Can you help?
It's 6.30pm here in the UK and I'm going "screen blind" so if I don't
respond to any further answer until tomorrow please excuse me, I'll just
have to call it a day!
Thanks again
Tony Williams

That's because you want to test for Cancel = False to show the message,
not
if it's True, the way your expression is set up.

However, assuming that you want to have Cancel be True when there

are

no
records, use this expression instead:

Cancel = (DCount("*", "tblmonth", "[txtMonth] = #" & Me.txtMonth & "#")
=
0)

--

Ken Snell
<MS ACCESS MVP>

message
Thanks Ken used your code but still don't get message

:

Close:

Cancel = DCount("*", "tblmonth", "[txtMonth] = #" &

Me.txtMonth

&

"#")

--

Ken Snell
<MS ACCESS MVP>


Thanks Rick I've tried this
Cancel = DCount(#*#, "tblmonth", [txtMonth] = ## &

Me.txtMonth

&

##)
If Cancel Then MsgBox "This Quarter already exists."
but I get a syntax error. Could you point me in the right
direction?
Yes this is a date field, but it doesn't have any time, the format
is
Short
Date?
Thanks again
Tony
I am using this code in the before update event on a

control

to
check
whether the value already exists
Cancel = DCount("*", "tblmonth", [txtMonth] = "" & Me.txtMonth
&
"""")
If Cancel Then MsgBox "This Quarter already exists."
The control is based on a field txtmonth which is a date/time
field in
the
table tblmonth, however if I enter a date that already exists
I
don't
get my
error message. Can anyone see anything wrong with my code?

Is it _really_ a DateTime field? If so then you need to delimit
with #,
not
single quotes. Also, are you sure the values in the table all
have
exactly
midnight for the time? Regardless of how you format them for
display,
any
test
has to take the time component into account.

 

[Dirk Goldgar]

Ken I've checked through all the validation controls and there are
none. Required property is set to No. Data Type is Short Date and I
am entering say 30/06/04.
When I enter a value in the txtmonth control on my form as say
30/06/04 I want to check that that date has not been input before.
The txtmonth field in my table tblmonth is a key field and indexed
with no duplicates. Does that help?

I don't know if this affects what's going on or not, but if you are
entering your dates in dd/mm/yy format, you may want to change the code
to

Cancel = (DCount("*", "tblmonth", "txtMonth = " & _
Format(Me.txtMonth, "\#mm/dd/yyyy\#") = 0)

But I'm confused, because this looks to me like it will set Cancel to
True if the date in Me.txtMonth is *not* found in the table, where your
MsgBox seemed to imply your intention to cancel if it *is* found.

 

[Ken Snell [MVP]]

So long as the control is bound to that field, having the field and control
with the same name is not why I'd expect this error to be occurring. There
are circumstances where the control should be named differently from the
field, and it's not bad practice to do that any way.

Is there any code running on any event of the txtmonth control or on any
event of the form?

Is the txtmonth field formatted as date/time type?
--

Ken Snell
<MS ACCESS MVP>

Ken, the error occurs just by entering a date and then tabbing to the next
field. The control is bound to a field txtmonth (same name could that cause
a problem?)
Regional settings for short date is dd/mm/yy (UK style)
Any help? This is really beginning to bug me it must be something simple but
I just can't see it!
Thanks again
Tony

Is the error occurring in the code or by just entering the "date" into the
control?

Is the control bound to a field? What is the data type format of that field?
What are your regional settings for date formats?

30/06/04

I

want to check that that date has not been input before. The txtmonth field
in my table tblmonth is a key field and indexed with no duplicates. Does
that help?
Thanks for your patience
Tony
Validation rule possibley can mean
(1) validation for the field in the table's design view;
(2) validation for the control on the form in the form's design view;
(3) required property is set to yes;
(4) allow zero length string property is set to no;
(5) wrong data type for the field's data type (e.g., trying to

store

a
letter in a numeric field)
(6) nonunique value being entered into a field that is required

to
be

unique;
and perhaps others.

However, I have no idea which step of the code is causing this error,
which
control or field is getting a value, etc. Can you give more details?
--

Ken Snell
<MS ACCESS MVP>


Thanks Ken that worked EXCEPT I now get an error message that says the
value
in the field violates the validation rule for the field but I haven't
got
any validation rules? Can you help?
It's 6.30pm here in the UK and I'm going "screen blind" so if I don't
respond to any further answer until tomorrow please excuse me,

I'll
just

have to call it a day!
Thanks again
Tony Williams

That's because you want to test for Cancel = False to show the
message,
not
if it's True, the way your expression is set up.

However, assuming that you want to have Cancel be True when

there
are

no
records, use this expression instead:

Cancel = (DCount("*", "tblmonth", "[txtMonth] = #" & Me.txtMonth &
"#")
=
0)

--

Ken Snell
<MS ACCESS MVP>

message
Thanks Ken used your code but still don't get message

:

Close:

Cancel = DCount("*", "tblmonth", "[txtMonth] = #" &

Me.txtMonth

&
"#")

--

Ken Snell
<MS ACCESS MVP>


Thanks Rick I've tried this
Cancel = DCount(#*#, "tblmonth", [txtMonth] = ## &

Me.txtMonth

&
##)
If Cancel Then MsgBox "This Quarter already exists."
but I get a syntax error. Could you point me in the right
direction?
Yes this is a date field, but it doesn't have any time, the
format
is
Short
Date?
Thanks again
Tony
I am using this code in the before update event on a control
to
check
whether the value already exists
Cancel = DCount("*", "tblmonth", [txtMonth] = "" &
Me.txtMonth
&
"""")
If Cancel Then MsgBox "This Quarter already exists."
The control is based on a field txtmonth which is a
date/time
field in
the
table tblmonth, however if I enter a date that already
exists
I
don't
get my
error message. Can anyone see anything wrong with my code?

Is it _really_ a DateTime field? If so then you need to
delimit
with #,
not
single quotes. Also, are you sure the values in the

table
all

have
exactly
midnight for the time? Regardless of how you format

them
for

display,
any
test
has to take the time component into account.

 

[Ken Snell [MVP]]

You're right, Dirk...my error when rewriting the code. This is what the code
should be:

Cancel = (DCount("*", "tblmonth", "txtMonth = " & _
Format(Me.txtMonth, "\#mm/dd/yyyy\#") <> 0)

 

[Tony Williams]

Hi Dirk I've used your code and got no msgbox so I changed it to
Cancel = DCount("*", "tblmonth", "txtMonth = " & Format(Me.txtMonth,
"\#dd/mm/yy\#")) and
'Cancel = (DCount("*", "tblmonth", "txtMonth = " & Format(Me.txtMonth,
"\#dd/mm/yy\#") = 0)) and this time the message box didn't popup but the
standard Access message about creating duplicate records did and no
validation error . I've tried mm/dd/yy and mm/dd/yyy and dd/mm/yy and
dd/mm/yy
Yes I want the message box if there IS a duplicate record.
Thanks for your help, this is driving me nuts!
Tony

 

[Tony Williams]

Ken the txtmonth field is formatted as date/time type short date. Ther is no
other code running on the form. After tabbing from the txtmonth control the
user goes to a calculated control called txtmonthlabel with the formula
=Format([txtMonth],"mmmm yyyy"), is that the problem? The change of format
in that calculation. I want txtmonthlabel to be just the month and year of
txtmonth.
Tony

So long as the control is bound to that field, having the field and control
with the same name is not why I'd expect this error to be occurring. There
are circumstances where the control should be named differently from the
field, and it's not bad practice to do that any way.

Is there any code running on any event of the txtmonth control or on any
event of the form?

Is the txtmonth field formatted as date/time type?
--

Ken Snell
<MS ACCESS MVP>

Ken, the error occurs just by entering a date and then tabbing to the next
field. The control is bound to a field txtmonth (same name could that cause
a problem?)
Regional settings for short date is dd/mm/yy (UK style)
Any help? This is really beginning to bug me it must be something simple but
I just can't see it!
Thanks again
Tony
30/06/04

required

to

be
unique;
and perhaps others.

However, I have no idea which step of the code is causing this error,
which
control or field is getting a value, etc. Can you give more details?
--

Ken Snell
<MS ACCESS MVP>


Thanks Ken that worked EXCEPT I now get an error message that

says
the

value
in the field violates the validation rule for the field but I haven't
got
any validation rules? Can you help?
It's 6.30pm here in the UK and I'm going "screen blind" so if I don't
respond to any further answer until tomorrow please excuse me, I'll
just
have to call it a day!
Thanks again
Tony Williams

That's because you want to test for Cancel = False to show the
message,
not
if it's True, the way your expression is set up.

However, assuming that you want to have Cancel be True when there
are
no
records, use this expression instead:

Cancel = (DCount("*", "tblmonth", "[txtMonth] = #" &

Me.txtMonth

&

"#")
=
0)

--

Ken Snell
<MS ACCESS MVP>

message
Thanks Ken used your code but still don't get message

:

Close:

Cancel = DCount("*", "tblmonth", "[txtMonth] = #" & Me.txtMonth
&
"#")

--

Ken Snell
<MS ACCESS MVP>


Thanks Rick I've tried this
Cancel = DCount(#*#, "tblmonth", [txtMonth] = ## & Me.txtMonth
&
##)
If Cancel Then MsgBox "This Quarter already exists."
but I get a syntax error. Could you point me in the right
direction?
Yes this is a date field, but it doesn't have any time, the
format
is
Short
Date?
Thanks again
Tony
I am using this code in the before update event on a
control
to
check
whether the value already exists
Cancel = DCount("*", "tblmonth", [txtMonth] = "" &
Me.txtMonth
&
"""")
If Cancel Then MsgBox "This Quarter already exists."
The control is based on a field txtmonth which is a
date/time
field in
the
table tblmonth, however if I enter a date that already
exists
I
don't
get my
error message. Can anyone see anything wrong with my code?

Is it _really_ a DateTime field? If so then you need to
delimit
with #,
not
single quotes. Also, are you sure the values in the table
all
have
exactly
midnight for the time? Regardless of how you format them
for
display,
any
test
has to take the time component into account.

 

[Tony Williams]

Ken tried that with all combinations of mm/dd/yyyy and still get error
message!!!
I'm beginning to think it would be easier to leave the users to battle with
the standard Access duplicate error message! But that wouldn't be fair would
it? Would It?
Tony

 

[Ken Snell [MVP]]

Tony - see my reply to Dirk's post for the corrected code that you should
use.

 

[Ken Snell [MVP]]

Check the data type of the txtMonth field in the table. Is it what you think
it is?

 

[Tony Williams]

ken I've tried that and still get the error message. Can you look at my
earlier reply to you which read
"Ken the txtmonth field is formatted as date/time type short date. There is
no
other code running on the form. After tabbing from the txtmonth control the
user goes to a calculated control called txtmonthlabel with the formula
=Format([txtMonth],"mmmm yyyy"), is that the problem? The change of format
in that calculation. I want txtmonthlabel to be just the month and year of
txtmonth"
Any fresh ideas?
Tony