Hi,
I'm wondering if someone could help me out a little. I'm working on a routine that detects spaces and other special characters from imported data coming from an external Excel file. Even when the user of the Excel file should use the DD/MM/YYYY format on some date cells, and even when the validation rule is implemented, we are seeing junk like DD.MMM.YYYY or '[Space]DD-MM*YYYY. This basically happens when data is being copied into cells instead of manually typed in. The problem being that when this data is imported into our DB, the whole thing stops.
I'm working on a process should clear all those special characters and other non wanted characters in order to keep the process going, but I'm a little stuck. I have a module that, in theory, should extract the characters from the [Document Expiry Date] field after the date [DD] part has been cleaned and captured.
I'm trying to extract, character by character, the text (month) from the fields (whether it be 09 for September or Sep) before the next date separator or special character is detected.
Here's the code:
Function CheckMonth() As Variant
Dim N2 As Integer
Dim NN As Integer
Dim Check2, Counter2
Dim Check
Dim NB
Dim Ctl
N2 = Nz(Len([Forms]![Host]![Document Expiry Date]), 0)
Check = True: Counter2 = 0 ' Initialize variables.
Do ' Outer loop.
Do While Counter2 < N2 ' Inner loop.
Counter2 = Counter2 + 1 ' Increment Counter.
For NB = 1 To N2
Ctl = Counter2
Next
Forms!Host!Blank_Space = Mid(Forms!Host![Document Expiry Date], 1, 1)
Select Case Asc(Mid(Forms!Host!Blank_Space, 1, 1))
Case 32 To 47, 58 To 64, 91 To 96, 123 To 255 ' all char$ except for numbers and letters
MsgBox "SPC Detected"
Forms!Host![Document Expiry Date] = Mid(Forms!Host![Document Expiry Date], 2, N2)
NN = Nz(Len([Forms]![Host]![D2]), 0)
If NN = "2" Then ' DD captured
Else
Forms!Host!D2 = DLookup("
I'm wondering if someone could help me out a little. I'm working on a routine that detects spaces and other special characters from imported data coming from an external Excel file. Even when the user of the Excel file should use the DD/MM/YYYY format on some date cells, and even when the validation rule is implemented, we are seeing junk like DD.MMM.YYYY or '[Space]DD-MM*YYYY. This basically happens when data is being copied into cells instead of manually typed in. The problem being that when this data is imported into our DB, the whole thing stops.
I'm working on a process should clear all those special characters and other non wanted characters in order to keep the process going, but I'm a little stuck. I have a module that, in theory, should extract the characters from the [Document Expiry Date] field after the date [DD] part has been cleaned and captured.
I'm trying to extract, character by character, the text (month) from the fields (whether it be 09 for September or Sep) before the next date separator or special character is detected.
Here's the code:
Function CheckMonth() As Variant
Dim N2 As Integer
Dim NN As Integer
Dim Check2, Counter2
Dim Check
Dim NB
Dim Ctl
N2 = Nz(Len([Forms]![Host]![Document Expiry Date]), 0)
Check = True: Counter2 = 0 ' Initialize variables.
Do ' Outer loop.
Do While Counter2 < N2 ' Inner loop.
Counter2 = Counter2 + 1 ' Increment Counter.
For NB = 1 To N2
Ctl = Counter2
Next
Forms!Host!Blank_Space = Mid(Forms!Host![Document Expiry Date], 1, 1)
Select Case Asc(Mid(Forms!Host!Blank_Space, 1, 1))
Case 32 To 47, 58 To 64, 91 To 96, 123 To 255 ' all char$ except for numbers and letters
MsgBox "SPC Detected"
Forms!Host![Document Expiry Date] = Mid(Forms!Host![Document Expiry Date], 2, N2)
NN = Nz(Len([Forms]![Host]![D2]), 0)
If NN = "2" Then ' DD captured
Else
Forms!Host!D2 = DLookup("
Code:
", "Month", "[Month]=" & "[Forms]![Host]![D2]" & "") ' Convert MMM to MM
End If
Case Else
End Select
MsgBox "Add D2 - Extract the next Character"
Forms!Host!D2 = Forms!Host!D2 & Mid(Forms!Host![Document Expiry Date], 1, 1)
Forms!Host![Document Expiry Date] = Mid(Forms!Host![Document Expiry Date], 2, N2)
If Counter2 = N2 Then ' If condition is True.
Check = False ' Set value of flag to False.
Exit Do ' Exit inner loop.
End If
Loop
Loop Until Check = False ' Exit outer loop immediately.
End Function
Once run, the code extracts the text, character by character from [Document Expiry Date] into the D2 field without any problems. The issue being that the process should stop after detecting the next separator or special character. But even when I get the pop up saying "SPC Detected", the code keeps going and captures the rest of the data into the D2 Field, this does not stop until the the D2 field gets all the remaining text from the [Document Expiry Date]. This leaves me with Sep2015 instead of Sep
I'v tried placing Check = False after the MsgBox "SPC Detected" but no joy!
Can someone please tell me how to jump out of the loop after detecting the last Special Character (before the YYYY)
Any help would be appreciated.
Thanks,