extract serial number of a date from a textbox input

**rsbuslon** · 03-20-2013, 12:26 AM

Hi, I have these codes as a part of my project.

Dim xdate As Long
Dim queryX As Long

xdate = Format(CDate(textboxDate.Value), "mm/dd/yyyy")
queryX = Application.WorksheetFunction.CountIfs(ws.Range("H1:H50000"), xdate, ws.Range("B1:B50000"), comboCategory.Value, ws.Range("D1:D50000"), comboTerm.Value, ws.Range("E1:E50000"), textboxAmount.Value)

Basically, what I am trying to do is compare if a new record is a duplicate value. But when I try to run debug, CDATE can convert the textboxDate.value (say 3/31) to 3/31/2013 but xdate is 0. I need a way to extract the serial number of a date so I can compare it to range H1:H50000 which has the corresponding serial numbers of the previously entered records. I am not even sure if the other parts of the codes above are correct or would work, I just want to learn how to extract the serials. Please help.

TIA,
Steve

**:) Sixthsense :)** · 03-20-2013, 12:36 AM

Two Questions:-

Whether "H1:H50000" is having Text Date's instead of real dates? since Format converts real date to text.
If "H1:H50000" is text date then change this Dim xdate As Long to Dim xdate As string

**rsbuslon** · 03-20-2013, 12:59 AM

Originally Posted by :) Sixthsense :)

Two Questions:-

Whether "H1:H50000" is having Text Date's instead of real dates? since Format converts real date to text.
If "H1:H50000" is text date then change this Dim xdate As Long to Dim xdate As string

Sorry, I mentioned that H1:H50000 contains the serial numbers of previously entered dates, is that text date? The sheet that I am working on is actually an old project. I just want to add some VBA codes to it since I plan to use a userform as an input option. So there are records already in it.

**:) Sixthsense :)** · 03-20-2013, 01:14 AM

I am not sure what do you mean by the word serial numbers

if the entered date is real date then the each date value will show in the right side of the cell (Check it by removing the Center, Left, Right alignments).

Please check and confirm

**rsbuslon** · 03-20-2013, 01:20 AM

Sorry, I'm not even sure if I am using the right term. But other people from other forums are calling it as such so I thought,,,, you know.

Anyway, say A1 has a date, 3/31/2013. And in B1, we put =VALUE(A1), which will give us the serial number of the date as 41364. Again I don't know the correct term for that. LOL. This also appears if instead of Date, a cell is formatted as Text instead with date in it.

I'm trying to figure out how to extract that numerical value (serial number in my world) and use it to the above expression.

I tried Dim xdate as string also, but it returned xdate = "" in debug.

I'm desperate,

**:) Sixthsense :)** · 03-20-2013, 01:27 AM

You have mis understood my question.

I need to know whether the dates are in H1:H50000 is real date or text date? or if you are having the =value(TextDate) converted date's in separate column then what is the column reference?
Because If the SOURCE date is in Text Format and comparing the (form Date - Real Date) with Text date in Countif will not get the expected result.

**rsbuslon** · 03-20-2013, 02:09 AM

Got it working finally with the below code. I don't know that I was already doing what is right. I then used this to a Select statement and got the desired results.

Dim xdate As String
Dim queryX As Long

xdate = Format(CDate(textboxDate.Value), "mm/dd/yyyy")
queryX = Application.WorksheetFunction.CountIfs(ws.Range("H1:H50000"), xdate, ws.Range("B1:B50000"), comboCategory.Value, ws.Range("D1:D50000"), comboTerm.Value, ws.Range("E1:E50000"), textboxAmount.Value, ws.Range("F1:F50000"), comboDoctor.Value)

Select Case queryX
    Case Is > 0
        MsgBox "This is a possible duplicate entry! Please verify!", vbOKOnly + vbCritical + vbDefaultButton1, "Duplicate record found!"
        Me.textboxDate.SetFocus
    Case Else
        Select Case Trim(Me.comboDoctor.Value)
            Case Is = ""
                Me.comboDoctor.SetFocus
                MsgBox "Please select a Doctor.", vbOKOnly + vbCritical + vbDefaultButton1, "Alert!"
            Case Else
                ws.Cells(iRow, 1).Value = xdate
                ws.Cells(iRow, 2).Value = Me.comboCategory.Value
                ws.Cells(iRow, 3).Value = Me.textboxDetails.Value
                ws.Cells(iRow, 4).Value = Me.comboTerm.Value
                ws.Cells(iRow, 5).Value = Me.textboxAmount.Value
                ws.Cells(iRow, 6).Value = Me.comboDoctor.Value
                ws.Cells(iRow, 7).Value = Me.textboxAssistant.Value
                
                MsgBox "Entry added to Outflows database", vbOKOnly + vbInformation, "Data Added"
                
                'clear the data
                Me.textboxDate.Value = ""
                Me.comboCategory.Value = ""
                Me.textboxDetails.Value = ""
                Me.comboTerm.Value = ""
                Me.textboxAmount.Value = ""
                Me.comboDoctor.Value = ""
                Me.textboxAssistant.Value = ""
                Me.textboxDate.SetFocus
        End Select
End Select

something like that. thanks for the help

Sixthsense

**:) Sixthsense :)** · 03-20-2013, 02:19 AM

Glad you solved it

extract serial number of a date from a textbox input

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

extract serial number of a date from a textbox input

Re: extract serial number of a date from a textbox input

Re: extract serial number of a date from a textbox input

Re: extract serial number of a date from a textbox input

Re: extract serial number of a date from a textbox input

Re: extract serial number of a date from a textbox input

Re: extract serial number of a date from a textbox input

Re: extract serial number of a date from a textbox input

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions