Criteria in a query

[Sgte]

In a query, which show a list of carnival entrants, there is a "year" field - to denote which procession year the data applies to. When in the design view I enter the criteria = 2010, save the query, and go into view. But there are no entries displayed, despite there being 6 entries to 2010. Help please

[Mark L]

In a query, which show a list of carnival entrants, there is a "year" field - to denote which procession year the data applies to. When in the design view I enter the criteria = 2010, save the query, and go into view. But there are no entries displayed, despite there being 6 entries to 2010. Help please

You say there is a "year" field in query, but is this a "year" field in the table (and is it numeric) or are you extracting the year from a date? Are there any other selection criteria? Maybe you could post the query, just in case.

[Sgte]

The year field is just the year number, viz 2010. I've attached the data

[Mark L]

The year field is just the year number, viz 2010. I've attached the data

I don't see the attachment.

[Sgte]

Ooh.... can't upload the dab "The extension mdb is not allowed" How does one upload a query then?

[HansV]

Create a zip file containing the database, and attach the zip file to your reply.

[Sgte]

Aah Haa

Carnival_Procession.zip

[HansV]

The EntYr field is a lookup field - it links to the YearID field in tblYears, which has values 1, 2, 3, not 2008, 2009, 2010. It displays the corresponding year because it uses a combo box that hides the YearID column.

x68.png

There is not much point in having the YearID field in tblYears, since Year itself is a number field. I'd delete the YearID field and simply use Year. You'll have to delete the relationship between tblYears and tblEntries before deleting the YearID field, then create a new relationship on Year vs EntYr. You'll have to update the values of EntYr.

[Mark L]

This is one of the hazards of using a Lookup field. It hides the real value of the field and displays the lookup value, which is just confusing when you want to use it in a query. My basic suggestion is "don't use them"; you can always create combobox in your forms to handle the lookup (and you shouldn't being doing dataentry directly into a table anyway).

[JohnH]

It hides the real value of the field and displays the lookup value, which is just confusing when you want to use it in a query.

I agree with you about not using lookup fields, but it does not have to hide the real value.
The real value is only hidden if you accept the default column widths, which puts a column width of 0 on the first column.

[Sgte]

Thanks all for the help and comments. Really appreciated.