Date Serial from Userform Textbox

[breynolds0431]

Hello. I have a userform that should check to see if the new entry is already logged in the data table. This duplicate check evaluates the account number, Date, and ID letter and then searches the sheet's Column A to see if it's listed. The sheet has this information concatenated in column A of each data row.

The problem I'm running into is the date piece. On the sheet, the date is formatted as a date (not text) and is a date serial in the concatenate cell. The userform views this date as a text string, so a duplicate is never found. I tried to convert the date from text to date and then attempted to use DateValue to obtain the serial (i.e.; DateValue(CDate(Me.FYEDate))), but it seems to always return the m/d/yyyy format.

Any thoughts on how I can convert the Me.FYEDate to a serial number to work with the sheet? Also, I cannot modify the sheet to convert the dates to text.

Thank you for your time.

'will check for duplicates
 
Dim lRow As Long, ws As Worksheet, c As Range
Set ws = Worksheets("log")
lRow = ws.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Row

With ws.Range(ws.Cells(3, 1), ws.Cells(lRow, 1))
    Set c = .Find(Val(Me.Acct) & Me.FYEDate.Text & Me.ID.Text, LookIn:=xlValues, LookAt:=xlWhole)
End With

If c Is Nothing Then

Else
    MsgBox Me.Acct.Text & " " & Me.FYEDate.Text & " " & Me.ID.Text & " has already been logged. " & _
        "Please verify the details and re-enter, if necessary.", vbOKOnly, "Duplicate Entry"
    Me.ID.SetFocus
End If

 

[jkpieterse]

Use CLng(CDate(Me.FYEDate.Text)) to convert that date to a serial number

 

[breynolds0431]

Use CLng(CDate(Me.FYEDate.Text)) to convert that date to a serial number

Thank you very much!