Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


Problems exiting from a loop

Problems exiting from a loop

Problems exiting from a loop

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

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
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 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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close