Student Progress Database Application

Hi Ken, An interesting point. I'll have to check it when I get home.

In this case, the query was referencing a text box on a form to default the records being added. The data being entered was a month name, a text string. That's why it baffled me at first. The field type being written to was also Text. My first clue was the message when I ran the query that x values were not updated due to a type violation. The second clue was that the month name was right justified when I viewed the query. So I changed the expression to: Mth: CStr(Forms!formname!controlname) and everything worked as I envisioned. Are you saying that if I define the Mth column as Text in the Parameters statement I wouldn't have had that problem?

Are you saying that if I define the Mth column as Text in the Parameters statement I wouldn't have had that problem?

Possibly, but the behaviour being experienced is very strange.  I'd never have expected a string expression of the month name to be interpreted as a number.  Are you sure the control which is being referenced is really a text box, not  a combo box with the RowSource property's first (numeric) column being hidden?  I would usually set up a combo box of months like this, giving it a value list as the RowSource:

1;January;2;February;3;March;4;April;5;May;6;June;7;July;8;August;9;September;10;October;11;November;12;December

One of the rare cases where the use of a value list is appropriate as a RowSource.  That way you can reference it along these lines:

WHERE Year(MyDate) = Forms!frmMyForm!cboYears

AND Month(MyDate) = Forms!frmMyForm!cboMonths

That's why I was so baffled by this. My instructions were to use a textbox and that's what is on the form. If you look back over the screenshots you can see that.

At some point, I may have had him change it. But I wanted to get this part working. The only explanation was the Month name was somehow being interpreted as a month and being stored as the month #. Maybe Access was being smart, but it caused a problem.

I did some testing and found that if a query which returns a column which gets its value by referencing a control containing text as an undeclared parameter is made into a 'make table' query it creates a column of Binary data type in the new table.  If the parameter is declared as TEXT, on the other hand, it creates a column of Short Text data type, so I suspect declaring the parameter in this case might well have solved the problem.

Hi Scott,

This works now. Thank you. But I'm having a problem with the Student names in the second sub form not being aligned with the student names in the first sub form. I tried ascending both student names in the SELECT queries on the record sources but did'nt help either. I can't understand this behavior why both the Student names are not getting aligned by their names but by their StudentID's ? That's strange. I also cannot get the Course names in the order Hindi, English, Math and Science. Here too the table shows them in ascending order of the CourseID's 1, 2, 3 and 4 but they should be in the order like I listed above.

And can we not have a combo box for month names instead of the text box since someone can make typos for the names of the months and then it would be hard to pickup all the students belonging to that particular month in the report which follows after all this mess is sorted out?