Remove everything except date

[rbtm2006]

We import data from an external program into this DB. The external program does not have a place for DOB, so we use the email field. The problem is, on certain records, we use the email field for the email (go figure right...)

I do not need any email addresses in the DB. Only the DOB

My problem:

How do I filter out only the DOB from a field that has emails and other DOB info. Some examples include:

DOB:00/00/00
dob 00/00/00
DOB: 00/00/0000
email@email.com
dob:
<<Blank Record>>
wk (503) 000-0000
group#: 0000000000

just to name a few...

I need something that can look at the data, and extract only dates and dump everything else.

Any help would be appreciated...

Thanks
:banghead:

 

[John Big Booty]

Given that your dates appear to be prefixed with the string dob, you could use the criteria;

Like "dob*"

Based on your sample you might want to read up on the subject of Data Normalisation and possibly work through a tutorial. From there you might want to rethink your table structure.

 

[rbtm2006]

that does work partially... just cant figure out how to only keep the date and not the "DOB"

 

[jdraw]

I'm returning your database with 2 queries and a function called GetDate.
The Getdate function and the test procedure TestGetDate are located in Module1

Query
1: Records with Emails lists the records containing email address in the Email field

[COLOR="Blue"]SELECT QBCustomers.FullName, QBCustomers.IsActive, QBCustomers.Email
FROM QBCustomers
WHERE (((QBCustomers.Email) Like "*@*"));[/COLOR]

2: DOBs_AS_Required lists the DOB found in the Email field (this uses the GetDate function)

[COLOR="Blue"]SELECT QBCustomers.FullName, Getdate([Email]) AS DOB
FROM QBCustomers
WHERE (((QBCustomers.Email) Not Like "*@*"));[/COLOR]

The function GetDate is listed below

'---------------------------------------------------------------------------------------
' Procedure : GetDate
' Author    : Jack
' Date      : 16/10/2012
' Purpose   : Function to parse a string and out put a valid date
' if a valid date format is contained in the string
'---------------------------------------------------------------------------------------
'
Function GetDate(strInput As String) As Date
          Dim i As Integer
          Dim strOut As String
10        On Error GoTo GetDate_Error

20        For i = 1 To Len(Trim(strInput))
30            If Mid(Trim(strInput), i, 1) Like "[@ a-z:.]" Then
                  'char is alpha so ignore it
40            Else
50                strOut = strOut & Mid(Trim(strInput), i, 1)
60            End If
70        Next i
80        If Len(strOut & "") = 0 Then
90            Exit Function
100       Else
110           GetDate = CDate(strOut)
120       End If
130       On Error GoTo 0
140       Exit Function

GetDate_Error:

150       MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure GetDate of Module Module1"
End Function

This procedure is for testing the Getdate function

'---------------------------------------------------------------------------------------
' Procedure : testGetDate
' Author    : Jack
' Date      : 16/10/2012
' Purpose   : test procedure to test the GetDate function
'---------------------------------------------------------------------------------------
'
Sub testGetDate()
      Dim i As Integer
      Dim x(3) As String
10       On Error GoTo testGetDate_Error
   ' sample data for testing
20    x(0) = "bob@gmail.com"
30    x(1) = " dob 03/07/68"
40    x(2) = "dob:11/12/90"
50    x(3) = "4/6/99"

60    For i = 0 To 3 Step 1
70    If GetDate(x(i)) = #12:00:00 AM# Then
80    MsgBox x(i) & " does not contain a valid date"
90    End If
100   Next i

110      On Error GoTo 0
120      Exit Sub

testGetDate_Error:

130       MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure testGetDate of Module Module1"
End Sub

Good luck with your project.