Page 1 of 2 12 LastLast
Results 1 to 15 of 16

Thread: Compare text

  1. #1
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I have a database where foundry casting is recorded. One control on the form [Alloy_Melt_No], is retrieved from a linked table. Another control [Alloy_Melt_No2], is input by the user. If say, the the Alloy Melt No is MMS 1K, the Alloy Melt No to would begin with K, eg K265. Likewise, MMS 1MT, would begin MT eg. MT148. We have had instances, where the user has typed in the wrong Alloy Code No2, and not noticed it. How can I compare the text to check if the first letter of Alloy Melt No2, is contained in Alloy Melt No, and warn the user if they don't match?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    What is the exact structure of Alloy_Melt_No?
    Does it always end in a space, the digit 1 and one or more letters?
    Or in a space, a single digit and one or more letters?
    Or a space, one or more digits followed by one or more letters?
    Or something else (if so, what)?

  3. #3
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Forget the space. I've just discovered that's an error in the list. They all begin with MMS1 followed by the alloy letters, which unfortunately, could be K, MT, H/2. Some are even H2, which they'll have to decide on some sort of standard format. But in most cases there will be 1, 2 or 3 digits after the 1.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You could use the Before_Update event of the form:

    Code:
    Private Sub Form_BeforeUpdate(Cancel As Integer)
      Dim strText As String
      If Not IsNull(Me.Alloy_Melt_No) And Not IsNull(Me.Alloy_Melt_No2) Then
    	strText = Mid(Me.Alloy_Melt_No, 5)
    	If Not InStr(Me.Alloy_Melt_No2, strText) = 1 Then
    	  Me.Alloy_Melt_No2.SetFocus
    	  MsgBox "Alloy_Melt_No2 should begin with " & strText
    	  Cancel = True
    	End If
      End If
    End Sub

  5. #5
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks Hans. I've changed the text a little, but basically it informs them when there's a typo. Thanks again.

  6. #6
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I was too hasty. It's informing me of the error, but won't let me correct it.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Setting Cancel = True in the Before Update event means that the record won't be saved, and you should be able to edit Alloy_Melt_No2 or any other editable control on the form.

  8. #8
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I've just entered some data in. The alloy_melt_code was MMS1MT (BS7252 PT4), so I put in IAlloy_Melt_No2 I024, from a drop down list, and got the error message when I tried to update the form. I then entered the correct Alloy_Melt_No2 from the drop down list which is MT148, and still got the error message.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You specified that everything after "MMS1" in the first code should be the beginning of the second code.

    So if the first code is "MMS1MT (BS7252 PT4)", the second one should begin with "MT (BS7252 PT4)".

  10. #10
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I did state in my first post.
    If say, the the Alloy Melt No is MMS 1K, the Alloy Melt No to would begin with K, eg K265. Likewise, MMS 1MT, would begin MT eg. MT148
    It's only first, or first two letters that need to match. Obviously, without the spaces, as stated.I apologise for any confusion.

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Please provide the EXACT specifications.

  12. #12
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts
    The Alloy_Master_Melt_No, will always begin MMS1. It is the first letter after the 1, that the Alloy_Melt_No2, must start with. eg

    Alloy_Melt_No is MMS1MT (ANC4B)
    Alloy_Melt_No2 is MT148

    Alloy_Melt_No is MMS1k (ANC4B)
    Alloy_Melt_No2 is k258

    Alloy_Melt_No is MMS1H/2 (ANC3A)
    Alloy_Melt_No2 is H2305

    There are some instances were the Alloy_Melt_No is MMS1MTHC, but that's the longest.

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Will there always, without exception, be a space after the letter(s) that you want to use?

  14. #14
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Yes.

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Try this version:

    Code:
    Private Sub Form_BeforeUpdate(Cancel As Integer)
      Dim lngPos As Long
      Dim strText As String
      If Not IsNull(Me.Alloy_Melt_No) And Not IsNull(Me.Alloy_Melt_No2) Then
    	' Find position of space
    	lngPos = InStr(Me.Alloy_Melt_No, " ")
    	' Extract the part between MMS1 and the space
    	strText = Mid(Me.Alloy_Melt_No, 5, lngPos - 5)
    	If Not InStr(Me.Alloy_Melt_No2, strText) = 1 Then
    	  Me.Alloy_Melt_No2.SetFocus
    	  MsgBox "Alloy_Melt_No2 should begin with " & strText
    	  Cancel = True
    	End If
      End If
    End Sub

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •