Find how many similar records..

[N J]

Hi,

I want my form to tell me if there are similar records based on a certain
field. For example, I currently have a record open on my form with
12*@abc.com as a data in a field. I want the form to search the table for
any other records with that same data in that field and tell me in a text
box....'This is an existing customer' if there is 2 or more records, or
'This is a new customer' if there is just one record.

TIA

Nick J
--

Kind Regards,
Customer Services,
Blue Lagoon Products
http://www.fastminimoto.co.uk
P.S. For us to answer your emails efficiently, please always include the
original message. Thank you.

[Tom van Stiphout]

On Sun, 04 Sep 2005 15:32:47 GMT, "N J"
<bl*************@blueyonder.co.uk> wrote:

You could use the DCount function:
Me.txtDuplicatesMessage = "You already have " &
DCount("EmailAddress", "tblCustomers", "EmailAddress='" &
Me.txtEmailAddress & "'") & " customers with this email address."
Note the single-quotes around the email address value.

If you don't want duplicates, you are MUCH better off putting a unique
index on the field(s) that make up a unique customer.

-Tom.

Hi,

I want my form to tell me if there are similar records based on a certain
field. For example, I currently have a record open on my form with
12*@abc.com as a data in a field. I want the form to search the table for
any other records with that same data in that field and tell me in a text
box....'This is an existing customer' if there is 2 or more records, or
'This is a new customer' if there is just one record.

TIA

Nick J

[N J]

Hi,

I have tried this and I keep getting #Name? :(

Please explain what you mean by unique customer. What I do is I import my
orders from a Tab Delimated file from Paypal, some customers have purchased
from me before.

--

Kind Regards,
Customer Services,
Blue Lagoon Products
http://www.fastminimoto.co.uk
P.S. For us to answer your emails efficiently, please always include the
original message. Thank you.
"Tom van Stiphout" <no*************@cox.net> wrote in message
news:hc********************************@4ax.com...

On Sun, 04 Sep 2005 15:32:47 GMT, "N J"
<bl*************@blueyonder.co.uk> wrote:

You could use the DCount function:
Me.txtDuplicatesMessage = "You already have " &
DCount("EmailAddress", "tblCustomers", "EmailAddress='" &
Me.txtEmailAddress & "'") & " customers with this email address."
Note the single-quotes around the email address value.

If you don't want duplicates, you are MUCH better off putting a unique
index on the field(s) that make up a unique customer.

-Tom.

Hi,

I want my form to tell me if there are similar records based on a certain
field. For example, I currently have a record open on my form with
12*@abc.com as a data in a field. I want the form to search the table for
any other records with that same data in that field and tell me in a text
box....'This is an existing customer' if there is 2 or more records, or
'This is a new customer' if there is just one record.

TIA

Nick J

[Tom van Stiphout]

On Sun, 04 Sep 2005 23:44:18 GMT, "N J" <in**@bluelagoonproducts.com>
wrote:

I was speculating about a few things:
* You have a textbox called txtDuplicatesMessage
* You have a table called tblCustomers, with a field called
EmailAddress
* You have a textbox called txtEmailAddress
You should adjust the syntax to the actual names used in your
application. Or just look up DCount in the help file.

I was also speculating you were talking about adding new records to a
Customers table. Now I understand you're dealing with an Orders table,
so duplicate email addresses can be appropriate.

-Tom.

Hi,

I have tried this and I keep getting #Name? :(

Please explain what you mean by unique customer. What I do is I import my
orders from a Tab Delimated file from Paypal, some customers have purchased
from me before.