FormFilter with SQL

  • Thread starter Thread starter Junior
  • Start date Start date
J

Junior

Using a Form (continuous rows) to edit tlkpBUSCode the recordsource for the
form.

However i want to filter the data to include for editing only the BUS codes
found by the SQL below.
I copied the SQL into the Filter property but got a "syntax error - missing
operator" - what am i doingf wrong?

SELECT QDBEXProf.BUS
FROM QDBEXProf LEFT JOIN tlkpBUSCode ON QDBEXProf.BUS = tlkpBUSCode.BUS
GROUP BY QDBEXProf.BUS
HAVING (((QDBEXProf.BUS)<>"7777" And (QDBEXProf.BUS)<>"8888"));
 
Junior said:
Using a Form (continuous rows) to edit tlkpBUSCode the recordsource for the
form.

However i want to filter the data to include for editing only the BUS codes
found by the SQL below.
I copied the SQL into the Filter property but got a "syntax error - missing
operator" - what am i doingf wrong?

SELECT QDBEXProf.BUS
FROM QDBEXProf LEFT JOIN tlkpBUSCode ON QDBEXProf.BUS = tlkpBUSCode.BUS
GROUP BY QDBEXProf.BUS
HAVING (((QDBEXProf.BUS)<>"7777" And (QDBEXProf.BUS)<>"8888"));
Click to expand...


The Filter property doesn't take an SQL statement, it only
allows a Where clause (without the word WHERE).

I suggest that your query should be using WHERE instead of
HAVING. You could then use the Where clause in the Filter
property.

Personally, since I have had no end of trouble with the
Filter Property, I prefer to set the entire SQL statement in
to the form's RecordSource property and forget about using
the Filter property.

In looking at your query, I don't see any reason for Joining
to another table. Maybe you only posted part of your real
query???
 
Marshall thanks for the response-
what i'm tryin to do is use a form to edit a field in a lookup table - the
lookup table tlkpBUSCode has many records (i.e BUSCodes) but, i only want to
display those BUSCodes from tlkpBUSCode that match BUScodes found in
tblEmployee. - tblEmployee has over 3K records and many employees have the
same BUScode i used 'having' in my query because i was grouping on the
BUScodes in tblEmployee to get a list of the BUScodes that should match
records in tlkpBusCode.
tried a where statemnt in filter but another syntax error. also tried a
query as the record source but the query is not updateable therefore not
functional for a form used for editing...
i know there must be a fairly simple solution - but it escapes me
appreciate any guidance
 
I don't think your query is doing what you think it is. The
Group By clause is only performing a DISTINCT operation
(since there is no aggragation going on). So the HAVING
clause is only droping the special Bus codes, which could
have been dropped (more efficiently) before the grouping by
using WHERE instead.

Also, you're using a LEFT JOIN that accomplishes nothing
because you do not use any values from the joined table.
Maybe you intended to use an INNER JOIN to only get the bus
codes that are in both tables??

Further, you said that you want to edit a bus code in the
lookup table, but the query is returning a field in the
employee table.

I still don't have a good understanding of what you need
here, but I suspect that the form should use the record
source query:

SELECT BUS
FROM tlkpBUSCode
WHERE BUS IN (SELECT BUS FROM QDBEXProf)
 
Back
Top