I need to make a check in a field to see if more than One number exists in the field

Access 2010  VBA

I need to make a check in a field to see if more than One number exists in the field.
Field1

' Currently i check for blank values in that field.
after that check is completed, I need to see if any number variations exists.

Field1 should contain the same number

Example:
Field1
2321
2321
2321
etc...


If this exists:
2321
2322
2321
2324

Then I need to stop the routine with a message nox !
Dim R As DAO.Recordset
' NOW LOOK TO SEE FOR NULL RECORDS?
Set R = CurrentDb.OpenRecordset("SELECT * FROM [escalation_detail] ", dbOpenDynaset, dbSeeChanges)
' any ID nulls ?
R.MoveLast
R.MoveFirst
While Not R.EOF
If IsNull(R.Fields(1).Value) Then
   MsgBox "You Have Blank Record IDS. Double Check Your Import!", vbCritical
   R.Close
   Set R = Nothing
   Exit Sub
End If
R.MoveNext
Wend

Open in new window


Thanks
fordraiders
LVL 3
FordraidersAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
You can get the list by counting:

SELECT 
    Field1
FROM 
    [escalation_detail]
WHERE
    Count(*) > 1
GROUP BY
    Field1

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mbizupCommented:
Try this:

DIM rs AS DAO.Recordset
Set rs  = "SELECT YourField FROM YourTable GroupBy YourField"
If rs.RecordCount = 0 then
   msgbox "No records"
   exit sub
end if
rs.MoveLast
if rs.RecordCount >1 then
       msgbox "You have more than one distinct value
       ' Do something
end if

Open in new window

0
FordraidersAuthor Commented:
Thanks Folks !! Either Solution will work well.
Should have thought of that.
fordraiders
0
FordraidersAuthor Commented:
Thanks Folks !
0
PatHartmanCommented:
Sounds like a design problem.  If all child records are supposed to contain the same value, then the field does NOT belong in that table.  It belongs in the parent table.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.