Testing Data for Specific Criteria

[shank]

In the attached, I'm testing [ShipToAdd1] for any in string occurrences of what's in table [Address1CheckData]. If there are any, frmCheckData will load them and I can move those strings to the [ShipToAdd2] field. All works great.

Problem: I want to test the Ship To fields for character lengths and I cannot get accurate results. When I try to use the LEN formula in query [qry_Check_Field_Lengths] in the query [qry_CheckData], I get high numbers because, I assume, of no direct link to table [Address1CheckData].

Simply, how can I check for field lengths and string occurrences in one query so there's an accurate count and they show in the sub form as well?



Thanks!

[davegri]

Address Check Test-davegri-v01.zip

Basically changed the recordsource for the subform when Command7 is clicked.

Option Compare Database
Option Explicit


Private Sub Command7_Click()
    On Error GoTo Error_Handler
    Me![Check Data Sub].Form.RecordSource = "qry_Check_Field_Lengths"
    
Error_Handler_Exit:
    On Error Resume Next
    Exit Sub
Error_Handler:
    Select Case Err
        Case Else
            MsgBox "Error " & Err.Number & " (" & Err.Description & ")", vbExclamation, "Error in Sub Command7_Click of Form_frmCheckData"
    End Select
    Resume Error_Handler_Exit
    Resume
End Sub

[shank]

I see what you did and thanks. But that's not what I was hoping to resolve. The forms shouldn't be edited. I'm trying to get the results I need at the query level. i.e. Somehow getting a query to search for the strings as well as the field lengths.

Thanks!

[CJ_London]

presumably your problem is when you have BLD and BLDG you don't want to find BLDG when looking for BLD?

and similarly you don't want to find Steve when looking for STE?

if this is not the case, don't bother reading further but provide some examples of the results you want to see from the sample data you provided since it otherwise does not make sense to me.

But if it is, the only way I know to do this is you need a 'terminator' character such as a space

so look for 'BLD '

you may also need a prefix character, again perhaps a space ' ST'

and to complicate things further the character may not be a space, it may be a comma, a full stop, a semi colon a carriage return and the like

and if it is at the beginning of the the field, there won't be a prefix - but you can overcome that by adding a space to the beginning of the target field

And then you have context - DR might mean Doctor or it might mean Drive.

[ssanfu]

Maybe something like this??

I created a new form "frmCheckDataForm".
I added a module to add a function to count the number of addresses that need correcting and a sub to do the actual changes.

Maybe this might get you moving in the right direction......


Do LOTS of testing in a COPY of your dB.

[shank]

Wow! This is interesting! I have to review further, but it looks really good.

Thanks!

[ssanfu]

Apologies, I should have put in a lot of comments. As Ajax said, I had to prepend a space to be able to search for whole words.
And I made the assumption that the CheckData (location?) would be at the end of "ShipToAdd1" field, which is why I used the InStrRev function.



Good luck with your project..... It was a fun problem. It got my mind off of work stuff.

[shank]

Thank "you".. !!!

Much appreciated!