Duplicate Records Warning

[Guest]

Hi All,

I've come across a problem and I'm not that experienced in Access so can't
work it out. I have a datasheet which I enter account numbers and details in
when I refer the account to debt counsellors. Generally the account shouldn't
be referred twice in any 3 month period (but in exceptional circumstances can
be).

When I was doing this in Excel I had a "Countif" formula to calculate how
many times the account number entered appeared in the list - and then
conditional formatting to flag it up if it had appeared more than once. This
was sufficient as it allowed a manual review before sending the e-mail to
instruct the debt counsellor.

Question is? Can this be done in Access?

And a more advanced version could be.... can it flag up the account if it
appeared in the list and the date sent is newer than 3 months ago?

These are queries/expressions/formula I don't have a clue how to do... so
any help would be much appreciated!

Thanks,

Richard

 

[Marshall Barton]

I've come across a problem and I'm not that experienced in Access so can't
work it out. I have a datasheet which I enter account numbers and details in
when I refer the account to debt counsellors. Generally the account shouldn't
be referred twice in any 3 month period (but in exceptional circumstances can
be).

When I was doing this in Excel I had a "Countif" formula to calculate how
many times the account number entered appeared in the list - and then
conditional formatting to flag it up if it had appeared more than once. This
was sufficient as it allowed a manual review before sending the e-mail to
instruct the debt counsellor.

Question is? Can this be done in Access?

And a more advanced version could be.... can it flag up the account if it
appeared in the list and the date sent is newer than 3 months ago?

These are queries/expressions/formula I don't have a clue how to do... so
any help would be much appreciated!

I think this expression will calculate the desired count in
a text box on a form:

=DCount("*", "yourtable", "[datefield] > " &
Format(datetextbox, "\#m\/d\/yyyy\#") & " And
[accountfield] = " & account textbox)

Then use Conditional Formatting (View menu) to specif the
formatting when the count value is greater then one.

 

[Guest]

Hi Marshall,

This didn't do the trick, all its doing is counting the amount of accounts
in the database after the relevant date. I need it to count all accounts
after the specified date that equal the same as the account number that is
entered on that specific record on the form.

Has anyone got any ideas on how this can be done?

Thanks,

Richard

I've come across a problem and I'm not that experienced in Access so can't
work it out. I have a datasheet which I enter account numbers and details in
when I refer the account to debt counsellors. Generally the account shouldn't
be referred twice in any 3 month period (but in exceptional circumstances can
be).

When I was doing this in Excel I had a "Countif" formula to calculate how
many times the account number entered appeared in the list - and then
conditional formatting to flag it up if it had appeared more than once. This
was sufficient as it allowed a manual review before sending the e-mail to
instruct the debt counsellor.

Question is? Can this be done in Access?

And a more advanced version could be.... can it flag up the account if it
appeared in the list and the date sent is newer than 3 months ago?

These are queries/expressions/formula I don't have a clue how to do... so
any help would be much appreciated!

I think this expression will calculate the desired count in
a text box on a form:

=DCount("*", "yourtable", "[datefield] > " &
Format(datetextbox, "\#m\/d\/yyyy\#") & " And
[accountfield] = " & account textbox)

Then use Conditional Formatting (View menu) to specif the
formatting when the count value is greater then one.

 

[Marshall Barton]

It sounds like you didn't translate my suggested code
correctly. How about posting a Copy/Paste of the actual
code that you used so we can what might be wrong with it.
--
Marsh
MVP [MS Access]

This didn't do the trick, all its doing is counting the amount of accounts
in the database after the relevant date. I need it to count all accounts
after the specified date that equal the same as the account number that is
entered on that specific record on the form.

Has anyone got any ideas on how this can be done?

I think this expression will calculate the desired count in
a text box on a form:

=DCount("*", "yourtable", "[datefield] > " &
Format(datetextbox, "\#m\/d\/yyyy\#") & " And
[accountfield] = " & account textbox)

Then use Conditional Formatting (View menu) to specif the
formatting when the count value is greater then one.

 

[Guest]

Hi Marshall,

Could you give me the code without the date aspect - it was that that was
confusing me so I removed it.

Thanks,

Richard

It sounds like you didn't translate my suggested code
correctly. How about posting a Copy/Paste of the actual
code that you used so we can what might be wrong with it.
--
Marsh
MVP [MS Access]

This didn't do the trick, all its doing is counting the amount of accounts
in the database after the relevant date. I need it to count all accounts
after the specified date that equal the same as the account number that is
entered on that specific record on the form.

Has anyone got any ideas on how this can be done?

r1c_sm1th wrote:
I've come across a problem and I'm not that experienced in Access so can't
work it out. I have a datasheet which I enter account numbers and details in
when I refer the account to debt counsellors. Generally the account shouldn't
be referred twice in any 3 month period (but in exceptional circumstances can
be).

When I was doing this in Excel I had a "Countif" formula to calculate how
many times the account number entered appeared in the list - and then
conditional formatting to flag it up if it had appeared more than once. This
was sufficient as it allowed a manual review before sending the e-mail to
instruct the debt counsellor.

Question is? Can this be done in Access?

And a more advanced version could be.... can it flag up the account if it
appeared in the list and the date sent is newer than 3 months ago?

These are queries/expressions/formula I don't have a clue how to do... so
any help would be much appreciated!

I think this expression will calculate the desired count in
a text box on a form:

=DCount("*", "yourtable", "[datefield] > " &
Format(datetextbox, "\#m\/d\/yyyy\#") & " And
[accountfield] = " & account textbox)

Then use Conditional Formatting (View menu) to specif the
formatting when the count value is greater then one.

 

[Marshall Barton]

Here you go:

=DCount("*", "yourtable", "[accountfield] = " &
accounttextbox)

What did you find confusing about the date criteria
expression?
--
Marsh
MVP [MS Access]

Could you give me the code without the date aspect - it was that that was
confusing me so I removed it.

It sounds like you didn't translate my suggested code
correctly. How about posting a Copy/Paste of the actual
code that you used so we can what might be wrong with it.

This didn't do the trick, all its doing is counting the amount of accounts
in the database after the relevant date. I need it to count all accounts
after the specified date that equal the same as the account number that is
entered on that specific record on the form.

Has anyone got any ideas on how this can be done?


r1c_sm1th wrote:
I've come across a problem and I'm not that experienced in Access so can't
work it out. I have a datasheet which I enter account numbers and details in
when I refer the account to debt counsellors. Generally the account shouldn't
be referred twice in any 3 month period (but in exceptional circumstances can
be).

When I was doing this in Excel I had a "Countif" formula to calculate how
many times the account number entered appeared in the list - and then
conditional formatting to flag it up if it had appeared more than once. This
was sufficient as it allowed a manual review before sending the e-mail to
instruct the debt counsellor.

Question is? Can this be done in Access?

And a more advanced version could be.... can it flag up the account if it
appeared in the list and the date sent is newer than 3 months ago?

These are queries/expressions/formula I don't have a clue how to do... so
any help would be much appreciated!


:
I think this expression will calculate the desired count in
a text box on a form:

=DCount("*", "yourtable", "[datefield] > " &
Format(datetextbox, "\#m\/d\/yyyy\#") & " And
[accountfield] = " & account textbox)

Then use Conditional Formatting (View menu) to specif the
formatting when the count value is greater then one.

 

[Guest]

Hi Marshall,

I'm really sorry - just can't seem to get it to work.

I currently have:

=DCount("*","[tblCounsellors]",[AcctNum]="& AcctNum)

where tblCounsellors is my table, AcctNum is the field containing the
account numbers in the table, and AcctNum is the relevant field on the form
where the user enters the account number.

This code at the moment gets an "invalid string" error message.

The bit about the date that confused me is how to get it to work out the
past 3 months bit - its not going to be a hard coded date, but something that
moves.

Thanks,

Richard

Here you go:

=DCount("*", "yourtable", "[accountfield] = " &
accounttextbox)

What did you find confusing about the date criteria
expression?
--
Marsh
MVP [MS Access]

Could you give me the code without the date aspect - it was that that was
confusing me so I removed it.

It sounds like you didn't translate my suggested code
correctly. How about posting a Copy/Paste of the actual
code that you used so we can what might be wrong with it.


r1c_sm1th wrote:
This didn't do the trick, all its doing is counting the amount of accounts
in the database after the relevant date. I need it to count all accounts
after the specified date that equal the same as the account number that is
entered on that specific record on the form.

Has anyone got any ideas on how this can be done?


r1c_sm1th wrote:
I've come across a problem and I'm not that experienced in Access so can't
work it out. I have a datasheet which I enter account numbers and details in
when I refer the account to debt counsellors. Generally the account shouldn't
be referred twice in any 3 month period (but in exceptional circumstances can
be).

When I was doing this in Excel I had a "Countif" formula to calculate how
many times the account number entered appeared in the list - and then
conditional formatting to flag it up if it had appeared more than once. This
was sufficient as it allowed a manual review before sending the e-mail to
instruct the debt counsellor.

Question is? Can this be done in Access?

And a more advanced version could be.... can it flag up the account if it
appeared in the list and the date sent is newer than 3 months ago?

These are queries/expressions/formula I don't have a clue how to do... so
any help would be much appreciated!


:
I think this expression will calculate the desired count in
a text box on a form:

=DCount("*", "yourtable", "[datefield] > " &
Format(datetextbox, "\#m\/d\/yyyy\#") & " And
[accountfield] = " & account textbox)

Then use Conditional Formatting (View menu) to specif the
formatting when the count value is greater then one.

 

[Marshall Barton]

You are missing a quote:

=DCount("*","tblCounsellors","AcctNum=" & AcctNum)

Note that you would need to modify that if the AcctNum field
is a TEXT type field:
=DCount("*","tblCounsellors","AcctNum=""" & AcctNum & """")

For the date issue, give some details about what you need.
It's really not all that complicated, probably just a matter
of using some variation of DateAdd("m", -3, datefield)
--
Marsh
MVP [MS Access]

I currently have:

=DCount("*","[tblCounsellors]",[AcctNum]="& AcctNum)

where tblCounsellors is my table, AcctNum is the field containing the
account numbers in the table, and AcctNum is the relevant field on the form
where the user enters the account number.

This code at the moment gets an "invalid string" error message.

The bit about the date that confused me is how to get it to work out the
past 3 months bit - its not going to be a hard coded date, but something that
moves.

=DCount("*", "yourtable", "[accountfield] = " &
accounttextbox)

What did you find confusing about the date criteria
expression?

Could you give me the code without the date aspect - it was that that was
confusing me so I removed it.


:
It sounds like you didn't translate my suggested code
correctly. How about posting a Copy/Paste of the actual
code that you used so we can what might be wrong with it.


r1c_sm1th wrote:
This didn't do the trick, all its doing is counting the amount of accounts
in the database after the relevant date. I need it to count all accounts
after the specified date that equal the same as the account number that is
entered on that specific record on the form.

Has anyone got any ideas on how this can be done?


r1c_sm1th wrote:
I've come across a problem and I'm not that experienced in Access so can't
work it out. I have a datasheet which I enter account numbers and details in
when I refer the account to debt counsellors. Generally the account shouldn't
be referred twice in any 3 month period (but in exceptional circumstances can
be).

When I was doing this in Excel I had a "Countif" formula to calculate how
many times the account number entered appeared in the list - and then
conditional formatting to flag it up if it had appeared more than once. This
was sufficient as it allowed a manual review before sending the e-mail to
instruct the debt counsellor.

Question is? Can this be done in Access?

And a more advanced version could be.... can it flag up the account if it
appeared in the list and the date sent is newer than 3 months ago?

These are queries/expressions/formula I don't have a clue how to do... so
any help would be much appreciated!


:
I think this expression will calculate the desired count in
a text box on a form:

=DCount("*", "yourtable", "[datefield] > " &
Format(datetextbox, "\#m\/d\/yyyy\#") & " And
[accountfield] = " & account textbox)

Then use Conditional Formatting (View menu) to specif the
formatting when the count value is greater then one.

 

[Guest]

Hi Marshall,

That worked brilliantly, thanks very much!

As for the date matter - I'm wanting it to count only those records if the
[Date] field is newer than 3 months ago.

Its "=DCount("*","tblCounsellors","AcctNum=""" & AcctNum & """")" thats
there at the moment.

Thanks,

Richard

You are missing a quote:

=DCount("*","tblCounsellors","AcctNum=" & AcctNum)

Note that you would need to modify that if the AcctNum field
is a TEXT type field:
=DCount("*","tblCounsellors","AcctNum=""" & AcctNum & """")

For the date issue, give some details about what you need.
It's really not all that complicated, probably just a matter
of using some variation of DateAdd("m", -3, datefield)
--
Marsh
MVP [MS Access]

I currently have:

=DCount("*","[tblCounsellors]",[AcctNum]="& AcctNum)

where tblCounsellors is my table, AcctNum is the field containing the
account numbers in the table, and AcctNum is the relevant field on the form
where the user enters the account number.

This code at the moment gets an "invalid string" error message.

The bit about the date that confused me is how to get it to work out the
past 3 months bit - its not going to be a hard coded date, but something that
moves.

=DCount("*", "yourtable", "[accountfield] = " &
accounttextbox)

What did you find confusing about the date criteria
expression?


r1c_sm1th wrote:
Could you give me the code without the date aspect - it was that that was
confusing me so I removed it.


:
It sounds like you didn't translate my suggested code
correctly. How about posting a Copy/Paste of the actual
code that you used so we can what might be wrong with it.


r1c_sm1th wrote:
This didn't do the trick, all its doing is counting the amount of accounts
in the database after the relevant date. I need it to count all accounts
after the specified date that equal the same as the account number that is
entered on that specific record on the form.

Has anyone got any ideas on how this can be done?


r1c_sm1th wrote:
I've come across a problem and I'm not that experienced in Access so can't
work it out. I have a datasheet which I enter account numbers and details in
when I refer the account to debt counsellors. Generally the account shouldn't
be referred twice in any 3 month period (but in exceptional circumstances can
be).

When I was doing this in Excel I had a "Countif" formula to calculate how
many times the account number entered appeared in the list - and then
conditional formatting to flag it up if it had appeared more than once. This
was sufficient as it allowed a manual review before sending the e-mail to
instruct the debt counsellor.

Question is? Can this be done in Access?

And a more advanced version could be.... can it flag up the account if it
appeared in the list and the date sent is newer than 3 months ago?

These are queries/expressions/formula I don't have a clue how to do... so
any help would be much appreciated!


:
I think this expression will calculate the desired count in
a text box on a form:

=DCount("*", "yourtable", "[datefield] > " &
Format(datetextbox, "\#m\/d\/yyyy\#") & " And
[accountfield] = " & account textbox)

Then use Conditional Formatting (View menu) to specif the
formatting when the count value is greater then one.

 

[Marshall Barton]

If by "3 months ago" you mean 3 months before the current
data, then this should work:

=DCount("*","tblCounsellors","AcctNum=""" & AcctNum & """
AND [Date] > DateAdd(""m"", -3, Date())")
--
Marsh
MVP [MS Access]

That worked brilliantly, thanks very much!

As for the date matter - I'm wanting it to count only those records if the
[Date] field is newer than 3 months ago.

Its "=DCount("*","tblCounsellors","AcctNum=""" & AcctNum & """")" thats
there at the moment.

You are missing a quote:

=DCount("*","tblCounsellors","AcctNum=" & AcctNum)

Note that you would need to modify that if the AcctNum field
is a TEXT type field:
=DCount("*","tblCounsellors","AcctNum=""" & AcctNum & """")

For the date issue, give some details about what you need.
It's really not all that complicated, probably just a matter
of using some variation of DateAdd("m", -3, datefield)

I currently have:

=DCount("*","[tblCounsellors]",[AcctNum]="& AcctNum)

where tblCounsellors is my table, AcctNum is the field containing the
account numbers in the table, and AcctNum is the relevant field on the form
where the user enters the account number.

This code at the moment gets an "invalid string" error message.

The bit about the date that confused me is how to get it to work out the
past 3 months bit - its not going to be a hard coded date, but something that
moves.


:
=DCount("*", "yourtable", "[accountfield] = " &
accounttextbox)

What did you find confusing about the date criteria
expression?


r1c_sm1th wrote:
Could you give me the code without the date aspect - it was that that was
confusing me so I removed it.


:
It sounds like you didn't translate my suggested code
correctly. How about posting a Copy/Paste of the actual
code that you used so we can what might be wrong with it.


r1c_sm1th wrote:
This didn't do the trick, all its doing is counting the amount of accounts
in the database after the relevant date. I need it to count all accounts
after the specified date that equal the same as the account number that is
entered on that specific record on the form.

Has anyone got any ideas on how this can be done?


r1c_sm1th wrote:
I've come across a problem and I'm not that experienced in Access so can't
work it out. I have a datasheet which I enter account numbers and details in
when I refer the account to debt counsellors. Generally the account shouldn't
be referred twice in any 3 month period (but in exceptional circumstances can
be).

When I was doing this in Excel I had a "Countif" formula to calculate how
many times the account number entered appeared in the list - and then
conditional formatting to flag it up if it had appeared more than once. This
was sufficient as it allowed a manual review before sending the e-mail to
instruct the debt counsellor.

Question is? Can this be done in Access?

And a more advanced version could be.... can it flag up the account if it
appeared in the list and the date sent is newer than 3 months ago?

These are queries/expressions/formula I don't have a clue how to do... so
any help would be much appreciated!


:
I think this expression will calculate the desired count in
a text box on a form:

=DCount("*", "yourtable", "[datefield] > " &
Format(datetextbox, "\#m\/d\/yyyy\#") & " And
[accountfield] = " & account textbox)

Then use Conditional Formatting (View menu) to specif the
formatting when the count value is greater then one.

 

[Guest]

That works brilliantly.. Thank you so much for your help.

Richard

If by "3 months ago" you mean 3 months before the current
data, then this should work:

=DCount("*","tblCounsellors","AcctNum=""" & AcctNum & """
AND [Date] > DateAdd(""m"", -3, Date())")
--
Marsh
MVP [MS Access]

That worked brilliantly, thanks very much!

As for the date matter - I'm wanting it to count only those records if the
[Date] field is newer than 3 months ago.

Its "=DCount("*","tblCounsellors","AcctNum=""" & AcctNum & """")" thats
there at the moment.

You are missing a quote:

=DCount("*","tblCounsellors","AcctNum=" & AcctNum)

Note that you would need to modify that if the AcctNum field
is a TEXT type field:
=DCount("*","tblCounsellors","AcctNum=""" & AcctNum & """")

For the date issue, give some details about what you need.
It's really not all that complicated, probably just a matter
of using some variation of DateAdd("m", -3, datefield)


r1c_sm1th wrote:
I currently have:

=DCount("*","[tblCounsellors]",[AcctNum]="& AcctNum)

where tblCounsellors is my table, AcctNum is the field containing the
account numbers in the table, and AcctNum is the relevant field on the form
where the user enters the account number.

This code at the moment gets an "invalid string" error message.

The bit about the date that confused me is how to get it to work out the
past 3 months bit - its not going to be a hard coded date, but something that
moves.


:
=DCount("*", "yourtable", "[accountfield] = " &
accounttextbox)

What did you find confusing about the date criteria
expression?


r1c_sm1th wrote:
Could you give me the code without the date aspect - it was that that was
confusing me so I removed it.


:
It sounds like you didn't translate my suggested code
correctly. How about posting a Copy/Paste of the actual
code that you used so we can what might be wrong with it.


r1c_sm1th wrote:
This didn't do the trick, all its doing is counting the amount of accounts
in the database after the relevant date. I need it to count all accounts
after the specified date that equal the same as the account number that is
entered on that specific record on the form.

Has anyone got any ideas on how this can be done?


r1c_sm1th wrote:
I've come across a problem and I'm not that experienced in Access so can't
work it out. I have a datasheet which I enter account numbers and details in
when I refer the account to debt counsellors. Generally the account shouldn't
be referred twice in any 3 month period (but in exceptional circumstances can
be).

When I was doing this in Excel I had a "Countif" formula to calculate how
many times the account number entered appeared in the list - and then
conditional formatting to flag it up if it had appeared more than once. This
was sufficient as it allowed a manual review before sending the e-mail to
instruct the debt counsellor.

Question is? Can this be done in Access?

And a more advanced version could be.... can it flag up the account if it
appeared in the list and the date sent is newer than 3 months ago?

These are queries/expressions/formula I don't have a clue how to do... so
any help would be much appreciated!


:
I think this expression will calculate the desired count in
a text box on a form:

=DCount("*", "yourtable", "[datefield] > " &
Format(datetextbox, "\#m\/d\/yyyy\#") & " And
[accountfield] = " & account textbox)

Then use Conditional Formatting (View menu) to specif the
formatting when the count value is greater then one.