Pleas can anyone help?
I have a form that for various reasons I can not connect directly to a
datasource. I have a button where the OnClick event performs various
functions including the one below. It all seems to work fine apart for the
'CustomerReceiptDate'.

The event procedure runs without any problems but the date inserted into the
table is not the date on the form. It appears to be a time and shows in the
table as 31/12/1899.

I have checked the format in the form and table but am getting frustrated.

Thanks in advance

Marcus Armand

dbs.Execute "INSERT INTO tblSalesLedgerreceipts(BankAccount,
CustomerReceiptDate, CustomerReceiptRef, CustomerID, Description, Amount,
Reconciled, AmountPaid, Posted) SELECT " & stBankAccount & ", " &
CDate(stSalesPaymentDate) & ", " & stSalesPaymentRef & ", " & stCustomerID &
", 'Sales Receipt', " & stAmounta & ", 0, " & stAmountPaid & ", 0;",
dbFailOnError

You need to delimit the date value with # signs and it needs to be in US format
of MM/DD/YYYY.  Try the following:

dbs.Execute "INSERT INTO tblSalesLedgerreceipts(BankAccount,
CustomerReceiptDate, CustomerReceiptRef, CustomerID, Description, Amount,
Reconciled, AmountPaid, Posted) SELECT " & stBankAccount & ", " &
Format(CDate(stSalesPaymentDate),"\#mm\/dd\/yyyy\#") &
", " & stSalesPaymentRef & ", " & stCustomerID &
", 'Sales Receipt', " & stAmounta & ", 0, " & stAmountPaid & ", 0;",
dbFailOnError