Query can't find Year data

[G.King]

I have created a dbase to allow staff at my school to enter homework tasks for their classes. That side works fine. All I want to do now is collect the data from the tables using a select query and display it in a report that can be printed as a PDF and sent to parents as an email attachment.

One of the fields used in the query is called Year [as in Year group]; a fairly essential field in a school based system. If I set the criteria in this field to look for homework for Year 10 or above it returns the data no problem. If I set the criteria to Year 7 [or Year 8 or Year 9] it finds no records [records do exist].

I recall a message saying that Year was a reserved word and it might cause problems, but it's the title of the field used in the data source that I have imported class and staff data from [and it isn't causing a problem elsewhere in my system]. I don't understand why it will work for Year 10+ and not Year 7 to 9.

Any suggestions gratefully received.

[Rhino373]

Is this field year a data format or a text format?

[G.King]

It's Text field type

[Rhino373]

I think that's where your problem starts. Can you post an example database.

I'd look to compare using the CInt() function to convert the text to a integer. For starters if it were text your greater than or less than would be out of wack. Numerically they sort like this: 1,2,3...... 9,10 but alphabetically they sort like this: 1,10,11,12.....2,21,22....3,4,5,6,7

[G.King]

Thanks for the help.

The problem is in qryCollectRptData. As you can see the query isn't trying to sort, it's simply trying to find any records where the Year field entry is Year 9, for example. That's what I don't get: I'm just asking it to find a text string matching Year 9.

As you can see I've attempted to find which Years it does find using OR, but I actually want to create a different report for each Year group [the report is an earlier version that no longer links to anything - qryCollectRptData is the basis of a new version].

[Rhino373]

The problem is spaces. For each entry where one character represents the Year (between 1 and 9) there are two spaces so its: "Year(space)(space)9". For those years greater than 9 there is only one space. "Year(space)12"

[Rhino373]

You don't appear to have told the field to sort either. Run Design mode on that query and change the "sort" field to Ascending for the Year column. Then for criteria you can type in ">="Year 9" (in this case 2 spaces before the 9) and it will give you all of 9, 10, 11 and 12

[G.King]

Well spotted!

As I said ealier the data was extracted from our main MIS system and never occurred to me that data would have been entered as anything other than one space. I can get around that easily now I know the logic behind what it's doing.

Many thanks.