A long time ago I reported the problem with MS Access ADP Reports/Forms with subforms/subreports where Link
Master/Child fields are numeric but not integer (i.e scale greater than zero).
I had the join field defined as numeric(28.2).
However Access doesn't recognize scale and generate incorrect SQL
statements, eg
exec sp_executesql N'SELECT * FROM "dbo"."PickSlipOverdues" WHERE
((@P1 = "PatronID"))', N'@P1 numeric(28)', 7
instead of correct , N'@P1 numeric(28.2)', 7.01
As a result join doesn't work and subform is not populated.
The parent form is bound to the table with primary field defined as
numeric(28.2), child subforms are bound to the view or select
statements that has the same link field definition -numeric(28.2)
Today I had to fix the report with subreports where again Master/Child fields link fields are numeric (e.g . [BookID] [numeric](28, 2))
The workaround is to add to RecordSource of each report/subreport string version of the key , e.g. (,cast(BookID as nvarchar(25)) as sBookID )
After this it is possible to specify sBookID as Master/Child fields and Access correctly generates string joins.
Note: This post originally appeared on an thespoke web site, but site is down at the moment and the text was recovered from G o o g l e's cache
posted @ Wednesday, September 27, 2006 4:08 PM