type mismatch date 1

[din2005]

Hi all i have 2 tables which are linked.

tblTreatment and tblTExternal

and the two fields i am trying to link are:

treatmentDate(dateField) and trtDate(TextField)

i know the reason why i get mismatch because one is date and the other is string/text.

Is there some how i can overcome this without changing the treatmentDate to a string?

the tblTExternal.trtDate has a format date like this:

trtDate
14Feb2004
20Jun2000

However with this i code with the help on this website it was changed to this format dd/mm/yyyy

the code:
TreatDate: CDate(Left([trtDate],2) & "/" & Mid([trtDate],3,Len([trtDate])-6) & "/" & Right([trtDate],4))

Many thanks

 

[Craig0201]

But it will join.....

What's the problem?

The other thing you could do is format the date in tblTreatment to a string in the same format as the string in tblTExternal.

 

[din2005]

thanks for replying Craig0201

i get a type mismatch

the sql is

SELECT tExternal.HospNo, tExternal.trtDate, CDate(Left([trtDate],2) & "/" & Mid([trtDate],3,Len([trtDate])-6) & "/" & Right([trtDate],4)) AS TreatDate
FROM tExternal INNER JOIN TREATMENT ON tExternal.trtDate = TREATMENT.TREATDATE;

do you see any problems?

I can't convert the tblTreatment to text/string its needed by other queries.

 

[PHV]

And what about this ?
SELECT tExternal.HospNo, tExternal.trtDate, TREATMENT.TREATDATE
FROM tExternal, TREATMENT
WHERE TREATMENT.TREATDATE = CDate(Left([trtDate],2) & "/" & Mid([trtDate],3,Len([trtDate])-6) & "/" & Right([trtDate],4));

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Craig0201]

Ah....

I misunderstood what you had so far achieved.

You don't have to join on fields. You can also join on calculated columns. The nice query viewer thingy doesn't like it but it is possible.

SELECT tExternal.HospNo, tExternal.trtDate, CDate(Left([trtDate],2) & "/" & Mid([trtDate],3,Len([trtDate])-6) & "/" & Right([trtDate],4)) AS TreatDate
FROM tExternal INNER JOIN TREATMENT ON CDate(Left(tExternal.[trtDate],2) & "/" & Mid(tExternal.[trtDate],3,Len(tExternal.[trtDate])-6) & "/" & Right(tExternal.[trtDate],4)) = TREATMENT.TREATDATE

should work.

Or if not, use a SQL-89 join syntax as shown by PHV.

 

[din2005]

hi phv,

Well trying that and running the query i still get type mismatch any other suggestions?

is it possible to change the text trtDate field to date in the table but have a format of 11May2005. could that be a way?

 

[din2005]

i tried both your solutions when i run the query it displays but after a second or so i get type mismatch.
As soon as i click ok on msgbox all field data turns to

HospNo trtDate TreatDate
#Name? #Name? #Name?
#Name? #Name? #Name?
#Name? #Name? #Name?
#Name? #Name? #Name?

 

[PHV]

And what about this ?
SELECT tExternal.HospNo, tExternal.trtDate, TREATMENT.TREATDATE
FROM tExternal, TREATMENT
WHERE TREATMENT.TREATDATE Is Not Null AND trtDate Like '##???*####'
AND TREATMENT.TREATDATE = CDate(Left([trtDate],2) & "/" & Mid([trtDate],3,Len([trtDate])-6) & "/" & Right([trtDate],4));

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[din2005]

PHV your a superstar that worked really well.

Many thanks mate and heres a star.