• Status: Solved
  • Priority: High
  • Security: Public
  • Views: 27
  • Last Modified:

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
0
Fordraiders
Asked:
Fordraiders
2 Solutions
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now