Data Type Expression Error

[CarlRostron]

I have two queries below, they are exactly the same except that I am getting a data Type expression Error on the second one. Simply, in the 'Critical Task' field the value is written in text either "TRUE" or "FALSE". I have starred at this for sometime but I just can't get it to work. All the field heading in the table match those in the expression fine.

strQueryDB(50) = "SELECT TTask.TaskID, TTask.TaskDescription, TTask.TaskPhase, TTask.CriticalPath, TTaskAccountability.Who AS AssignedTo, TTask.LeadIn AS LeadInWeeks, TTask.WorkPackage FROM TTaskAccountability INNER JOIN TTask ON TTaskAccountability.AccountabilityID = TTask.AssignedTo ORDER BY TTask.TaskID;"

strQueryDB(50) = "SELECT TTask.TaskID, TTask.TaskDescription, TTask.TaskPhase, TTask.CriticalPath, TTaskAccountability.Who AS AssignedTo, TTask.LeadIn AS LeadInWeeks, TTask.WorkPackage FROM TTaskAccountability INNER JOIN TTask ON TTaskAccountability.AccountabilityID = TTask.AssignedTo WHERE (((TTask.CriticalPath)=" & comma & "TRUE" & comma & "));"

 

[nanscombe]

strQueryDB(50) = "SELECT TTask.TaskID, TTask.TaskDescription, TTask.TaskPhase, TTask.CriticalPath, TTaskAccountability.Who AS AssignedTo, TTask.LeadIn AS LeadInWeeks, TTask.WorkPackage FROM TTaskAccountability INNER JOIN TTask ON TTaskAccountability.AccountabilityID = TTask.AssignedTo WHERE (((TTask.CriticalPath)=" & comma & "TRUE" & comma & "));"

If CriticalPath is a text value then the criteria needs to be in single quotes, 'TRUE', try this instead.

strQueryDB(50) = "SELECT TTask.TaskID, TTask.TaskDescription, TTask.TaskPhase, TTask.CriticalPath, TTaskAccountability.Who AS AssignedTo, TTask.LeadIn AS LeadInWeeks, TTask.WorkPackage FROM TTaskAccountability INNER JOIN TTask ON TTaskAccountability.AccountabilityID = TTask.AssignedTo WHERE TTask.CriticalPath='TRUE';"

 

[CarlRostron]

Thanks for your email tried that:

SELECT TTask.TaskID, TTask.TaskDescription, TTask.TaskPhase, TTask.CriticalPath, TTaskAccountability.Who AS AssignedTo, TTask.LeadIn AS LeadInWeeks, TTask.WorkPackage FROM TTaskAccountability INNER JOIN TTask ON TTaskAccountability.AccountabilityID = TTask.AssignedTo WHERE (((TTask.CriticalPath)='TRUE'));

but is still gives me the same error.

Attached is the error and the schema for the table. I very much doubt it matters but I am querying the Database from Excel (however the first query in the previous post works fine as do all my other queries just this one with TRUE and FALSE)

 

[nanscombe]

Ah, the data is in Excel.

I've just recreated your scenario and linked an Excel spreadsheet to an Access database and despite the field CriticalPath showing the words TRUE and FALSE it is actually a Yes/No fieldtype, so you don't need any quotes at all.

It'll just be WHERE TTask.CriticalPath=TRUE or WHERE TTask.CriticalPath=FALSE.

SELECT TTask.TaskID, TTask.TaskDescription, TTask.TaskPhase, TTask.CriticalPath, TTaskAccountability.Who AS AssignedTo, TTask.LeadIn AS LeadInWeeks, TTask.WorkPackage FROM TTaskAccountability INNER JOIN TTask ON TTaskAccountability.AccountabilityID = TTask.AssignedTo WHERE TTask.CriticalPath=TRUE;

I hope this helps.

 

[CarlRostron]

Nigel you say it like it is so easy I'm envious ;-)

Your fix worked and i appreciate your help on that (you wouldn't beleive how long I have spent trying...)

Cheers buddy.

 

[nanscombe]

I'm glad it helped.

It's only practice. More than 10 years of it.