Hi guys. I've created this query. which is to extract the records according to the dates. but some how when i double click it, a data type mismatch error comes out.

y is this so ?

i've tried checking around and i noticed that,

the data type for the JOIN_DATE is date/time
which the data type for the opty created date and opty closed date is text.

is this what is affecting the problem ?


Also the format of the date in the two fields is different.
For the JOIN_DATE, its in MM/DD/YY form
but for the opty created date and opty closed date, its in YY/MM/DD form

is this also another affecting factor ?


my query is shown below. thanks so much.


SELECT test1.[Opty Id], test1.[Opty Name], SJ_CIS.NEWCINSFX, test1.[Contact CIN], test1.[Contact CIN SFX], test1.[Create By (Name)], test1.[Created By (Login)], test1.[Opty Created Date], SJ_CIS.JOIN_DATE, test1.[Opty Closed Date], test1.[Closed By (Emp #)], test1.[Referral - Employee #], test1.[Referral - Employee], test1.[Sales Rep], test1.Product


FROM test1 INNER JOIN SJ_CIS ON [test1].[Contact CIN] & [test1].[Contact CIN SFX]=[SJ_CIS].[NEWCINSFX]


WHERE (((test1.[Sales Rep Position]) Like '*INTL*')
AND ([test1].[Opty Created Date] < [SJ_CIS].[JOIN_DATE])
AND ([SJ_CIS].[JOIN_DATE] < [test1].[Opty Closed Date]));

You're probably right that date conflicts with text.

Try wrapping the text field with CDate(columnName) during the query.

omg. thank you so much ! its worked like a charm.