I have a query that converts a date in text format to a short date prior to appending it into another table where the destination field is in Date/Time format. The criteria in this field is: ">Date()-45" When I view the results, everything is in place. I have all the desired results -- the text string is converted to a short date and has data from the previous 45 days. But when I run the query, I receive the following error message: "Data Type Mismatch in Criteria"
The "dteRegularStyle" in the code below is a VBA module that converts a date string in text format (YYYYMMDD) to a short date format (MM/DD/YYYY).
I've checked the properties of both the source and destination -- and both line up. Not sure what to do from here.
Query SQL:
INSERT INTO tblPending ( receipt, cob, a_file, MailDate, SortCode )
SELECT dbo_NSC_PetApp.Receipt_Number, dbo_NSC_PetApp.Ben_Country_Of_Birth, dbo_NSC_PetApp.Ben_A_Number, dteRegularStyle([RECEIVED_DATE]) AS Expr2, fSortcode([form_number],[part_2_1],[part_2_2],[class_preference],[ben_country_of_birth],[ben_current_class]) AS SortCode
FROM dbo_NSC_PetApp INNER JOIN dbo_NSC_DateIn ON dbo_NSC_PetApp.Receipt_Number = dbo_NSC_DateIn.Receipt_Number
WHERE (((dbo_NSC_PetApp.Receipt_Number)<"z") AND ((dteRegularStyle([RECEIVED_DATE]))>Date()-45));