Student Progress Database Application

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.

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.

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

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?

Everything was set right, but there was some weirdness in that the query was reading the Text control for the month as a number rather than a text string.

Sounds like a good example of why parameters should always be declared, other than when testing for OR <parameter> IS NULL to make them optional.   In such cases a declared parameter can't evaluate to NULL, except where declared as DATETIME.  Date parameters are different from other data types in this respect and can be NULL, so should always be declared to avoid their inadvertently being interpreted as arithmetical expressions and thus returning the wrong values.