An SQL Insert problem with a Date (1 Viewer)

[Access2000_JS1]

Hi, I am inserting records into a table where one of the fields I want to insert is a date value from a form. When I run the query without inserting it it works fine. When I change the value into some other type (a string) it also works fine. I have tried it with a date 05/05/05 so it has nothing to do with the sequence. The destination field as well as the form has identical data types. The keys are OK as the record is inserted. Only the date is missing.
The error message displayed states:

Microsoft access set 6 fields to NULL due to a type conversion failure ...


I guess I am using the 'dateInvoiceSent' wrong or without the required format command. Any help is most appreciated.

INSERT INTO clientInvoice (InvoiceNo,clientName,dateInvoiceSent)
SELECT DISTINCT jobInvoice.InvoiceNo, job.ClientName, [Forms]![CreateInvoices].[InvoiceDate] AS dateInvoiceSent
FROM job INNER JOIN jobInvoice ON job.JobRef = jobInvoice.JobRef
WHERE (((job.JobRef)=[jobinvoice].[jobref]));

 

[Access2000_JS1]

Sorted thanks

INSERT INTO clientInvoice (InvoiceNo,clientName,dateInvoiceSent)
SELECT DISTINCT jobInvoice.InvoiceNo, job.ClientName,format( [Forms]![CreateInvoices].[InvoiceDate], "dd/mm/yy") AS dateInvoiceSent
FROM job INNER JOIN jobInvoice ON job.JobRef = jobInvoice.JobRef
WHERE (((job.JobRef)=[jobinvoice].[jobref]));