Return union query result in number format

[NeilT123]

Help elsewhere on this forum helped me create the following union query. I have tried to link the output data to another query but am getting a "type mismatch in expression" error. I am trying to link the yr field to a number field. Can anyone tell me how to format the Yr as a number?

SELECT [OM Application Index],[Field Name],[Field Code],[Total N applied],[Total P2O5 applied],[Total K2O applied],[Total MgO applied],[Total SO3 applied], NYr0 as Effect, [OM Application Year] As Yr
FROM qry2FMA1
UNION ALL
SELECT [OM Application Index],[Field Name],[Field Code],[Total N applied],[Total P2O5 applied],[Total K2O applied],[Total MgO applied],[Total SO3 applied], NYr1, [OM Application Year]+1
FROM qry2FMA1
UNION ALL SELECT [OM Application Index],[Field Name],[Field Code],[Total N applied],[Total P2O5 applied],[Total K2O applied],[Total MgO applied],[Total SO3 applied],Nyr2, [OM Application Year]+2
FROM qry2FMA1;

 

[rac2]

What is the datatype of [OM Application Year] ?

If it is TEXT, then it must be converted to INTEGER for the addition, then back to TEXT.

It may be that Access will automatically convert it to a number for the expressions where you add 1 and 2. And that the mismatch is with the first query where there is no addition. If that is the case try adding zero in the first query.

 . . . NYr0 as Effect, [OM Application Year] + 0 As Yr

...

Access might then convert [OM Application Year] to numbers in all three queries and avoid the mismatch.