Trapping/Validating Paste'd data

[David Gray]

Greetings all,

Quick newbie type question:

I would like to be able to trap non-numerical data entered into a
textbox via CTRL+C and/or Shift+Insert.

I realise that this data can be validated using the TEXTn_validate
event but I would like to stop the user before it gets that far.

Thanks in advance.

David.

[Rick Rothstein]

> >Here is an example that checks for the Shift or Ctrl keys in the KeyDown

event (before the keypress event occurs) and sets a flag (using the Tag
property) if one or other is held down. The KeyPress event then checks theflag and rejects the keypress if Ctrl or Shift were held down (thereby
preventing Ctrl V and Shift Insert). It then checks for a digit (0 to 9). Asit stands the code will reject all attempts to paste stuff in (using Ctrl Vor Shift Insert), including attempts to paste in digits. If you want to
reject "pasted" stuff and yet still accept "pasted digits" then that, too,can be easily accomplished with a little bit of extra code.

That sounds exactly what I need to do.

Actually, it won't stop the pasting of bad data by right-clicking on the
TextBox and then selecting Paste from that menu (or from clicking on a Paste
option on a regular menu I would guess). Here is a method I've posted in the
past that stops both non-digit keystrokes and invalid pastes. To see it
work, start a new project, put a TextBox on the Form and paste the following
into the Form's code window.

Rick - MVP

Dim LastPosition As Long

Private Sub Text1_Change()
Static LastText As String
Static SecondTime As Boolean
If Not SecondTime Then
With Text1
If .Text Like "*[!0-9]*" Then
Beep
SecondTime = True
.Text = LastText
.SelStart = LastPosition
Else
LastText = .Text
End If
End With
End If
SecondTime = False
End Sub

Private Sub Text1_MouseDown (Button As Integer, _
Shift As Integer, X As Single, Y As Single)
With Text1
LastPosition = .SelStart
'Place any other MouseDown event code here
End With
End Sub

Private Sub Text1_KeyPress( KeyAscii As Integer)
With Text1
LastPosition = .SelStart
'Place any other KeyPress checking code here
End With
End Sub

[Rick Rothstein]

> Given two dates I would like to calculate the difference between them

in years months and days. Datediff does not seem to do what I need.
S_DATE = 10-AUG-2002
E_DATE = 11-AUG-2002

Result = 1 year, 0 month, 1 day
That looks like only 1 day to me.<g>

Has anyone got a routine for this or do I need to code one up
taking into account leap years , days in months spanned etc etc.

This is a modification of some code I've posted in the past. I think it does
what you want. Note that is properly singularizes and/or pluralizes the text
parts.

Rick - MVP

Function YMD(StartDate As Date, EndDate As Date) As String
Dim TempDate As Date
Dim NumOfYears As Long
Dim NumOfMonths As Long
Dim NumOfWeeks As Long
Dim NumOfDays As Long
Dim NumOfHMS As Double
Dim TSerial1 As Double
Dim TSerial2 As Double
NumOfYears = DateDiff("yyyy" , StartDate, EndDate)
TSerial1 = TimeSerial(Hour (StartDate), _
Minute(StartDat e), Second(StartDat e))
TSerial2 = TimeSerial(Hour (EndDate), _
Minute(EndDate) , Second(EndDate) )
NumOfHMS = 24 * (TSerial2 - TSerial1)
If NumOfHMS < 0 Then
NumOfHMS = NumOfHMS + 24
EndDate = DateAdd("d", -1, EndDate)
End If
StartDate = DateSerial(Year (EndDate), _
Month(StartDate ), Day(StartDate))
If StartDate > EndDate Then
StartDate = DateAdd("yyyy", -1, StartDate)
NumOfYears = NumOfYears - 1
End If
NumOfMonths = DateDiff("m", StartDate, EndDate)
StartDate = DateSerial(Year (EndDate), _
Month(EndDate), Day(StartDate))
If StartDate > EndDate Then
StartDate = DateAdd("m", -1, StartDate)
NumOfMonths = NumOfMonths - 1
End If
NumOfDays = Abs(DateDiff("d ", StartDate, EndDate))
YMD = CStr(NumOfYears ) & " year" & _
IIf(NumOfYears = 1, "", "s")
YMD = YMD & ", "
YMD = YMD & CStr(NumOfMonth s) & " month" & _
IIf(NumOfMonths = 1, "", "s")
YMD = YMD & ", "
YMD = YMD & CStr(NumOfDays) & " day" & _
IIf(NumOfDays = 1, "", "s")
End Function

[David Gray]

That does the job perfectly!

Thanks a lot.

Dave.
On Wed, 27 Aug 2003 11:58:38 -0400, "Rick Rothstein"
<ri************ @NOSPAMcomcast. net> wrote:

Given two dates I would like to calculate the difference between them
in years months and days. Datediff does not seem to do what I need.
S_DATE = 10-AUG-2002
E_DATE = 11-AUG-2002

Result = 1 year, 0 month, 1 day

That looks like only 1 day to me.<g>

Has anyone got a routine for this or do I need to code one up
taking into account leap years , days in months spanned etc etc.

This is a modification of some code I've posted in the past. I think it does
what you want. Note that is properly singularizes and/or pluralizes the text
parts.

Rick - MVP

Function YMD(StartDate As Date, EndDate As Date) As String
Dim TempDate As Date
Dim NumOfYears As Long
Dim NumOfMonths As Long
Dim NumOfWeeks As Long
Dim NumOfDays As Long
Dim NumOfHMS As Double
Dim TSerial1 As Double
Dim TSerial2 As Double
NumOfYears = DateDiff("yyyy" , StartDate, EndDate)
TSerial1 = TimeSerial(Hour (StartDate), _
Minute(StartDat e), Second(StartDat e))
TSerial2 = TimeSerial(Hour (EndDate), _
Minute(EndDate) , Second(EndDate) )
NumOfHMS = 24 * (TSerial2 - TSerial1)
If NumOfHMS < 0 Then
NumOfHMS = NumOfHMS + 24
EndDate = DateAdd("d", -1, EndDate)
End If
StartDate = DateSerial(Year (EndDate), _
Month(StartDate ), Day(StartDate))
If StartDate > EndDate Then
StartDate = DateAdd("yyyy", -1, StartDate)
NumOfYears = NumOfYears - 1
End If
NumOfMonths = DateDiff("m", StartDate, EndDate)
StartDate = DateSerial(Year (EndDate), _
Month(EndDate), Day(StartDate))
If StartDate > EndDate Then
StartDate = DateAdd("m", -1, StartDate)
NumOfMonths = NumOfMonths - 1
End If
NumOfDays = Abs(DateDiff("d ", StartDate, EndDate))
YMD = CStr(NumOfYears ) & " year" & _
IIf(NumOfYears = 1, "", "s")
YMD = YMD & ", "
YMD = YMD & CStr(NumOfMonth s) & " month" & _
IIf(NumOfMonths = 1, "", "s")
YMD = YMD & ", "
YMD = YMD & CStr(NumOfDays) & " day" & _
IIf(NumOfDays = 1, "", "s")
End Function

[Jeff Brown]

try converting it to a julianized date format and then performing your
calculations. I remeber doing something similiar to that in school, or
trying evaluating each aspect individually.

difference in years ,,, then differnce in months ,... etc
"David Gray" <po****@spamcop .net> wrote in message
news:0f******** *************** *********@4ax.c om...

Greetings all,

Yep I'm failry new to VB as you can probably guess. :-)
Given two dates I would like to calculate the difference between them
in years months and days. Datediff does not seem to do what I need.
S_DATE = 10-AUG-2002
E_DATE = 11-AUG-2002

Result = 1 year, 0 month, 1 day
Has anyone got a routine for this or do I need to code one up
taking into account leap years , days in months spanned etc etc.

Thanks in advance,
Dave.