Run Time error 3075 in Insert query (1 Viewer)

atrium

Registered User.
Local time
Today, 23:04
Joined
May 13, 2014
Messages
348
The full error message is - Runtime error 3075
Invalid use of '.','!', or '0', in query expression 'Forms.TrustReconAll BranchesFrm.[AcctNameFld'.

Below is the code
Code:
INSERT INTO TrustReconRptTemp ( BranchName, AcctName, BSBandAccountNo, DayEnded, LedgerAmount, UnprocessedDeposits, UnprocessedWithdrawals, UnpresentedCheques, UnpresentedDeposits, ExpectedLedgerAmount, BankStatementAmount, Discrepancy, Adjustments, AdjustedTrustLedgerAmount, CompletedBy, CompletedDate )
VALUES (Forms.TrustReconAllBranchesFrm.[BranchNameFld], Forms.TrustReconAllBranchesFrm. [AcctNameFld], Forms.TrustReconAllBranchesFrm.[BSBandAccountNoFld], Forms.TrustReconAllBranchesFrm.[DayEndedFld], Forms.TrustReconAllBranchesFrm.[LedgerAmountFld], Forms.TrustReconAllBranchesFrm.[UnprocessedDepositsFld], Forms.TrustReconAllBranchesFrm.[UnprocessedWithdrawalsFld], Forms.TrustReconAllBranchesFrm.[UnpresentedChequesFld], Forms.TrustReconAllBranchesFrm.[UnpresentedDepositsFld], Forms.TrustReconAllBranchesFrm.[ExpectedLedgerAmountFld], Forms.TrustReconAllBranchesFrm.[BankStatementAmountFld], Forms.TrustReconAllBranchesFrm.[DiscrepancyFld], Forms.TrustReconAllBranchesFrm.[AdjustmentsFld], Forms.TrustReconAllBranchesFrm.[AdjustedTrustLedgerAmountFld], Forms.TrustReconAllBranchesFrm.[CompletedByFld], Forms.TrustReconAllBranchesFrm.[CompletedDateFld]);

I have checked the code a 100s times and still cant see where my problem is.

I need new eyes and an open mind
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 12:04
Joined
Jul 9, 2003
Messages
16,245
Well, it looks like you've left out the delimiters. There could be something else wrong with the code.

I'm not sure why you are referencing the form in the way you do, unless it's somewhere else?

I need to see a sample file before I could be sure my comments are valid.

See my Blog for more information on the "Notorious insert into SQL Statement" which I observe, causes most people problems.


I demonstrate a simple technique for improving your insert into SQL statement so that you can get it right every time!

The download is available for free, just contact me for details of how to get a free copy....
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:04
Joined
May 7, 2009
Messages
19,175
Code:
With CurrentDb.CreateQueryDef("", _
"INSERT INTO TrustReconRptTemp ( BranchName, AcctName, BSBandAccountNo, DayEnded, LedgerAmount, UnprocessedDeposits, UnprocessedWithdrawals, UnpresentedCheques, UnpresentedDeposits, ExpectedLedgerAmount, BankStatementAmount, Discrepancy, Adjustments, AdjustedTrustLedgerAmount, CompletedBy, CompletedDate ) " & _
"VALUES (p1, p2, p3, p4, p5, p6, p7, p8, p9, p10, p11, p12, p13, P14, P15, P16);")
    .Parameters("p1") = Forms!TrustReconAllBranchesFrm![BranchNameFld]
    .Parameters("p2") = Forms!TrustReconAllBranchesFrm![AcctNameFld]
    .Parameters("p3") = Forms!TrustReconAllBranchesFrm![BSBandAccountNoFld]
    .Parameters("p4") = Forms!TrustReconAllBranchesFrm![DayEndedFld]
    .Parameters("p5") = Forms!TrustReconAllBranchesFrm![LedgerAmountFld]
    .Parameters("p6") = Forms!TrustReconAllBranchesFrm![UnprocessedDepositsFld]
    .Parameters("p7") = Forms!TrustReconAllBranchesFrm![UnprocessedWithdrawalsFld]
    .Parameters("p8") = Forms!TrustReconAllBranchesFrm![UnpresentedChequesFld]
    .Parameters("p9") = Forms!TrustReconAllBranchesFrm![UnpresentedDepositsFld]
    .Parameters("p10") = Forms!TrustReconAllBranchesFrm![ExpectedLedgerAmountFld]
    .Parameters("p11") = Forms!TrustReconAllBranchesFrm![BankStatementAmountFld]
    .Parameters("p12") = Forms!TrustReconAllBranchesFrm![DiscrepancyFld]
    .Parameters("p13") = Forms!TrustReconAllBranchesFrm![AdjustmentsFld]
    .Parameters("p14") = Forms!TrustReconAllBranchesFrm![AdjustedTrustLedgerAmountFld]
    .Parameters("p15") = Forms!TrustReconAllBranchesFrm![CompletedByFld]
    .Parameters("p16") = Forms!TrustReconAllBranchesFrm![CompletedDateFld]
    .Execute
End With
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 07:04
Joined
Feb 28, 2001
Messages
27,001
Looks to me like there is a space between the form name and the field name:

Code:
Forms.TrustReconAllBranchesFrm. [AcctNameFld]

Was that a copy/paste of the actual code or a retype? If copy/paste, then that space got you.
 

atrium

Registered User.
Local time
Today, 23:04
Joined
May 13, 2014
Messages
348
Thank you The_Doc_Man So simple it beat me

Thanks again(y)
 

atrium

Registered User.
Local time
Today, 23:04
Joined
May 13, 2014
Messages
348
Also thanks for your ideas Uncle Gizmo and arnelgp - I appreciate your input
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 12:04
Joined
Jul 9, 2003
Messages
16,245
Looks to me like there is a space between the form name and the field name:
I assumed that was a reoccurance of the forum software error of occasionally adding spurious spaces!
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 07:04
Joined
Feb 28, 2001
Messages
27,001
And it could have been - but I had to ask.
 

Users who are viewing this thread

Top Bottom