MS Access reserved error (-1517)

[sportsguy]

WTF does this mean?

ugh

nothing on google with this error message

thanks

sportsguy

 

[sportsguy]

running an append query from one database to another. . .

INSERT INTO ACEINVOICES ( YYYYMM, District, SRSA_Nbr, Category, TaskNbr, StartDate, EndDate, BillingType, [Transaction], UOM, Quantity, Rate, INVExtAmount, INVTax, INVTotalAmount, ItemNbr, ProductGroup, CustomerNbr, CustomerName, LegacyCustNbr, City, State, ZIP, CustAcctNbr, INV_CM, INV_CM_DATE, TrxType, ServiceType, NatLocal, RunDate, NatAcctNo )
SELECT [Enter YYYYMM] AS YYYYMM, SGXSR019M.District, SANbr([SR_SA_Number]) AS SA, SGXSR019M.Category, SGXSR019M.Task_Number, SGXSR019M.Start_Date, SGXSR019M.End_date, SGXSR019M.Billing_Type, SGXSR019M.Transaction_Name, SGXSR019M.UOM, SGXSR019M.QTY, SGXSR019M.Rate, SGXSR019M.INV_Ext_Amt, SGXSR019M.TAX, SGXSR019M.Total_Amt, SGXSR019M.Item_Number, SGXSR019M.Product_Group, CustNbr([Customer_Number]) AS Expr1, SGXSR019M.Customer_Name, SGXSR019M.Legacy_Customer, SGXSR019M.City, SGXSR019M.State, SGXSR019M.ZIP, SGXSR019M.Cust_Acct_Number, SGXSR019M.[INV_CM#], SGXSR019M.INV_CM_DATE, SGXSR019M.Trx_Type, SGXSR019M.Service_Type, Left([Natl_Local],1) AS Expr2, SGXSR019M.runDate, SGXSR019M.natAcctNo
FROM SGXSR019M
WHERE (((SGXSR019M.Category)<>"Service Agreement" And Not (SGXSR019M.Category) Is Null));

here are the functions in the module

Option Compare Database
Option Explicit

Function CustNbr(anyNbr As Variant) As String

If Len(anyNbr) = 1 Then
    CustNbr = "000000" & anyNbr
ElseIf Len(anyNbr) = 2 Then
    CustNbr = "00000" & anyNbr
ElseIf Len(anyNbr) = 3 Then
    CustNbr = "0000" & anyNbr
ElseIf Len(anyNbr) = 4 Then
    CustNbr = "000" & anyNbr
ElseIf Len(anyNbr) = 5 Then
    CustNbr = "00" & anyNbr
ElseIf Len(anyNbr) = 6 Then
    CustNbr = "0" & anyNbr
ElseIf Len(anyNbr) = 7 Then
    CustNbr = anyNbr
Else
    CustNbr = "0000000"
End If

End Function

Function SANbr(anyNbr As Variant) As String

If Len(anyNbr) = 1 Then
    SANbr = "00000" & anyNbr
ElseIf Len(anyNbr) = 2 Then
    SANbr = "0000" & anyNbr
ElseIf Len(anyNbr) = 3 Then
    SANbr = "000" & anyNbr
ElseIf Len(anyNbr) = 4 Then
    SANbr = "00" & anyNbr
ElseIf Len(anyNbr) = 5 Then
    SANbr = "0" & anyNbr
ElseIf Len(anyNbr) = 6 Then
    SANbr = anyNbr
Else
    SANbr = "000000"
End If

End Function

nothing spectacular

nothing come up error if i open the append query showing the data without appending. . .

thanks

sportsguy

 

[Moniker]

I think you may be hitting an ElseIf issue in those functions. Replace that with this and see if it continues.

Function CustNbr(anyNbr As Variant) As String

    Dim ctr As Byte
    Dim anyNbrLen As Byte

    anyNbrLen = Len(Nz(AnyNbr),"")
    For ctr = anyNbrLen To 6
        CustNbr = CustNbr & "0"
    Next
    CustNbr = CustNbr & Nz(anyNbr,"")

End Function

Function SANbr(anyNbr As Variant) As String

    Dim ctr As Byte
    Dim anyNbrLen As Byte

    anyNbrLen = Len(Nz(AnyNbr),"")
    For ctr = anyNbrLen To 5
        SANNbr = SANNbr & "0"
    Next
    SANNbr = SANNbr & Nz(anyNbr,"")

End Function

Note that this will also handle NULL values in CustNbr and SANNbr, which may have also been hidden culprits. The functions are cleaner as it is, so it can't hurt.

And finally, here's the first result from Googling for MS Access "reserved error -1517" (quotes and all in the search). It sounds like compacting and relinking tables will fix the issue.

 

[pbaldy]

BTW, an alternative to all that might be:

Format(FieldName, "0000000")

Which should pad with the appropriate number of zeros. If Nulls are possible, you'd probably need to wrap that in an nz(), as I'm not sure it will handle a Null properly.

 

[sportsguy]

so far, what i have found is that loading a null field to a table that has a date field formatted as short date causes the error. I changed the date field format to blank, and all is well. . .

put that one in your list of weird error messages

sportsguy

 

[Moniker]

BTW, an alternative to all that might be:

Format(FieldName, "0000000")

Which should pad with the appropriate number of zeros. If Nulls are possible, you'd probably need to wrap that in an nz(), as I'm not sure it will handle a Null properly.

Doh! Good call Baldy... I knew there was an easier way -- was just going through too many of these at once I think. Thanks for pointing out the obvious.

 

[sportsguy]

Just goes to show you that my description of Access HACK is correct!

thanks for the reminder about format, i forgot completely about it. . .

sportsguy