Help with query

[jak82]

Hi All,

I have a query which checks a linked table for any records older than a year that havnt had a service.

I presume in the criteria field for org name I would put Not [dbo_flowitems].[name].["serviced"]

Is this correct? Also the line above does not work

What would the correct syntax be?

The sql is

SELECT dbo_flowitems.name, dbo_orgs.orgtype, dbo_orgs.orgname, dbo_people.firstname, dbo_people.lastname, dbo_people.role, dbo_people.email, [Year Reminder].[Emailed?], dbo_flows.flowdate, dbo_flows.flowtype


FROM [Year Reminder], (((dbo_orgs INNER JOIN dbo_calibrations ON dbo_orgs.id = dbo_calibrations.id) INNER JOIN dbo_people ON dbo_calibrations.id = dbo_people.id) INNER JOIN dbo_flows ON dbo_orgs.id = dbo_flows.orgid) INNER JOIN dbo_flowitems ON dbo_flows.id = dbo_flowitems.flowid
WHERE (((dbo_flows.flowtype)="Accounts Invoice") AND ((CDate(Format([flowdate],"dd/mm/yyyy")))<Date()-365));


The error message I get is

ODBC call failed
[sqlserver] The datatypes text and varchar are incompatible in the equal to operator


Thanks

[June7]

You have a field actually named 'name'? Better would be OrgName as Name is a reserved word.

That syntax definitely makes no sense whatsoever.

If the criteria is whether or not service is done, then is there a field called 'Serviced'? Is it a Yes/No? Apply critieria to that field.

[jak82]

You have a field actually named 'name'? Better would be OrgName as Name is a reserved word.

That syntax definitely makes no sense whatsoever.

If the criteria is whether or not service is done, then is there a field called 'Serviced'? Is it a Yes/No? Apply critieria to that field.

No unfortunatly I am using a legacy sqlserver database which has other programs attached to it, the 'routine service' is in a text field called name.

I need the criteria to check the name 'routine service' against the company and remove all instances of the company from the query.

HTH

[June7]

So there is a value of 'routine service' under field 'Name'? You want to exclude all records with 'routine service' in 'Name' field? Your original example has value 'serviced'. Which is it - 'serviced' or 'routine service'?

Try:
[dbo_flowitems].[name]<>"routine service"