Query Help

[kevmeister123]

Hi again.

In my sheep management db, I have setup a new query. The query brings all relavent data up. I then want to filter the records for example, all lines have 4 possible options in the "Status" field:- Null, Sold, Lost, Died.

However when i set the filter for example to Sold I get the following error:

Type Mismatch in Expression

I'm guessing this is because the Status field is actually just a lookup field to another table.

Does that sound to be the problem? If so, is there a workaround?

Many Thanks

Kev

:O)

 

[Remou]

Create a join to the status table:

[tt]SELECT tblSheep.ID, tblStatus.Status
FROM tblSheep INNER JOIN tblStatus ON tblSheep.StatusID = tblStatus.StatusID
WHERE tblStatus.Status="Sold"[/tt]

Be aware that Nulls have to be specifically selected. The above type of join will not include records with no status.

 

[kevmeister123]

:~/

Sorry, but that means nothing to me, I can join the sheep "status" field and the status "status" field in the relationship window (if that's what you mean). But other than that.....

:~/

 

[Remou]

Ok. You can do this in the Query Design Window. Add the two tables to the design window and drag the status ID from the status table to the sheep table to create the join. If you have already set the relationship in the Relationship Window, Access will probably create the join for you. You can edit the join by right-clicking on the relationship line or by choosing Join properties from the View menu. Add a few fields to the grid by double-clicking or dragging and set criteria on the Criteria line. There are three views for queries:

- Design View
Used for building queries.

- SQL View
Something that looks like my first post. Very useful for coding and for showing people in Tek-Tips the set-up of a query. SQL view is also essential for some of the fancier queries that cannot be built in design view.

- Datasheet View
The results for the query.

 

[IGPCS]

do you get this error in the form based on a table or in a query

 

[kevmeister123]

IGPCS, The error was appearing when i was in table view, then right clicking and selecting filter, then typing in the filter box.

However, Thanks to the brilliant Remou, It is now working. I tried doing it in the design view like you said, but couldn't get it working, then i right clicked and found the secretive SQL view. I pasted your text in, and it didn't work, then i re-named the table references in SQL view and it didn't work, then I did the StatusID drag to SheepID and hey presto it works brilliant.

You people are stars and deserve some brownie points.

Many many thanks again.

Kev