I have an ADP with a 2008 SQL database and experiencing problems with the following code trying to pass the Admission Date from one table to populate another. It is correct in mm/dd/yyyy format in my source table and when it is passed it shows at 12:00:00 AM. The source field is formatted as DateTime in SQL and the form that I am pulling it from shows it correctly. I have even gone as far as formatting the text box as shortdate to see if that would remedy the situation. I have attempted a Convert Function with the same results. Suggestions?
strSQL = " INSERT INTO MedHistoryPhysicalAdmission ( FAMILY, AdmissionDate )"
strSQL = strSQL & " SELECT Convert(int," & [Forms]![MedicalHistoryPhysicalAdmissionLookupForm]![Family] & "), "
strSQL = strSQL & "" & [Forms]![MedicalHistoryPhysicalAdmissionLookupForm]![AdmissionDate] & ";"