Update query to find hidden characters

[Leesha]

Hi,
I have a user that has a query that appears to have extraneous data in the results that they can’t see. They use the file for import into another software and they are getting errors re special characters not being allowed, yet they don’t see any when they look for them. It isn’t all records that are affected. The two fields that are affected are [Beneficiary Account Number] and [Beneficiary Bank ID]. The total number of numbers in [Beneficiary Account Number] vary, and may have leading zeros. The [Beneficiary Bank Id] MUST contain 9 numbers and may have leading zeros.

I’m wondering if there is an update query that can be run to be sure there are no characters after the last number in each of these fields.

My other question is what would the export code look like if a query is to be export as a .csv file vs an excel file?

Thanks,
Leesha

[HansV]

Are those two fields text fields?

Export to a CSV file looks like this in VBA:

Code: Select all

    DoCmd.TransferText TransferType:=acExportDelim, SpecificationName:="MyExportSpec", _
        TableName:="QueryName", FileName:="C:\MyFiles\Export.csv", HasFieldNames:=True

You must create an export specification by exporting the query manually, and just before the end clicking Specifications and then Save.

[Leesha]

Hi hans,
Yes both fields are text fields.

RE the export specifications.....Do I create that once and name if "MyExportSpec" and then the code above works using it or does the user have to create it each time?

Thanks,
Leesha

[HansV]

You have to create the export specification only once. You can name it whatever you want, but use the same name in the Docmd.TransferText line.
The specification will be stored in the database and travel with it, so the user doesn't even need to know it exists.

How do you check the contents of the [Beneficiary Account Number] and [Beneficiary Bank ID] fields? Do they have an input mask, or do you use the Before Update event?

[Leesha]

Thanks for the info on the export specs!

RE checking the contents of the two fields they don't use anything that I'm aware of at this point. They contacted me due the issues they are having so I'm just starting out with them. They manually enter the information into the table fields. I know they will copy and paste from other sources so I'm not sure if they are picking up extraneous info

[HansV]

You might create Before Update event procedures to check that the fields only contain digits. For example:

Code: Select all

Private Sub Beneficiary_Account_Number_BeforeUpdate(Cancel As Integer)
    Dim i As Long
    If Not IsNull(Me.[Beneficiary Account Number]) Then
        For i = 1 To Len(Me.[Beneficiary Account Number])
            If Asc(Mid(Me[Beneficiary Account Number], i, 1)) < 48 Or Asc(Mid(Me.[Beneficiary Account Number], i, 1)) > 67 Then
                MsgBox "Beneficiary Account Number must consist of digits only!", vbExclamation
                Cancel = True
                Exit For
            End If
        Next i
    End If
End Sub

Private Sub Beneficiary_Bank_Id_BeforeUpdate(Cancel As Integer)
    If Not IsNull(Me.[Beneficiary Bank Id]) Then
        If Not Me.[Beneficiary Bank Id] Like "#########" Then
            MsgBox "Beneficiary Bank Id must consist of 9 digits!", vbExclamation
            Cancel = True
        End If
    End If
End Sub

[Leesha]

Hi Hans,
Where do I put this? Would you run it against the table containing the the fields before the query is run? Right now the query is run on a form called frmACHAutoImport. The form has text boxes that the user can enter date ranges and store ID's in to filter the query.
Thanks,
Leesha

[HansV]

These event procedure would be for the text boxes on the form in which the users enter [Beneficiary Account Number] and [Beneficiary Bank ID].

To check already created records would be more complicated.

[Leesha]

Unfortunately, they already have over 2000 already entered in each text field in the table. They had been doing ACH payments manually for years and are now automating it.

[HansV]

Create the following function in a standard module:

Code: Select all

Function IsOK(v As Variant) As Boolean
    Dim re As Object
    If Not IsNull(v) Then
        Set re = CreateObject("VBScript.RegExp")
        re.Global = True
        re.Pattern = "^\d{1,}$"
        IsOK = re.Test(v)
    Else
        IsOK = True
    End If
End Function

Then create a query with SQL

SELECT [Beneficiary Account Number] FROM [NameOfTable] WHERE IsOK([Beneficiary Account Number]) = False

where NameOfTable is the name of the table containing the data. This query will return the [Beneficiary Account Number] values that are not all digits.
Also create a query with SQL

SELECT [Beneficiary Bank Id] FROM [NameOfTable] WHERE IsOK([Beneficiary Bank Id]) = False

This will do the same for [Beneficiary Bank Id]

[Leesha]

Thanks Han's! this works great. Is there way to count the total number of number of digits in [Beneficiary Bank Id] to be sure it's 9 numbers?

[HansV]

Like this:


SELECT [Beneficiary Bank Id] FROM [NameOfTable] WHERE IsOK([Beneficiary Bank Id]) = False OR Len(Nz([Beneficiary Bank Id]))<>9

[Leesha]

Thank you!!!