Re: query from form

hi,

I have a question about applying to this to a query within a report. I have
a report whose recordsource is:

PARAMETERS test Long;
SELECT DISTINCT Tbl_MasterPop.Status, Count(Tbl_MasterPop.Status) AS
CountOfstatus, Sum(Tbl_MasterPop.[Principal Balance]) AS [SumOfprincipal
balance], Avg(Tbl_MasterPop.NETBAL) AS AvgOfNETBAL,
Count(Tbl_MasterPop.[Buying Center #]) AS [CountOfBuying Center #],
IIf(Forms!frm_criteria!delinquent_summary_status1 Is Null,"",[buying center
#]) AS test, Tbl_MasterPop.PopEnterDt
FROM Tbl_MasterPop
WHERE (((Tbl_MasterPop.PopEnterDt) Between [forms]![frm_criteria]![from1]
And [forms]![frm_criteria]![through1]))
GROUP BY Tbl_MasterPop.Status,
IIf(Forms!frm_criteria!delinquent_summary_status1 Is Null,"",[buying center
#]), Tbl_MasterPop.PopEnterDt
ORDER BY Tbl_MasterPop.Status;

Now in the report is a subreport. I am trying to link the subreport to the
parent report using [test] from the parent and [buying center #] from the
child. As you can see from the above query, I declared the datatype as Long.
The datatype for [buying center #] is long integer. when I invoke the
subreport field linker to specify the master fields and the child fields, the
[test] is listed, but with a datatype of text, so I cant join it to [buying
center #]. How can I solve this to allow the join?

Thanks in advance,
geebee

"Allen Browne" wrote:

So you verified that the control name was spelled correctly.
And you ran the query directly (not via the report), after moving the focus
on the form to another control (e.g. by tabbing out of the text box.)

I don't know what else to suggest. Guess you could try declaring the
parameter (Parameters on Query menu, in query design view), though I don't
see that it would solve the problem, and it might introduce another one:
http://allenbrowne.com/bug-13.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"geebee" <geebee@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:FAC097A4-0825-41B9-A405-AA2287C64821@xxxxxxxxxxxxxxxx
The column type of [Status] is text. This column is indexed (duplicates
OK).
And the focu moves to another field within the form before the report is
generated. I tested this by running the query with the form open, after
renaming the [delinquent_criteria] to [delinquentcriteria], and I am
getting
a "The Microsoft Jet database engine does not recognize
'[Forms]![frm_criteria]![delinquentcriteria]' as a valid field name or
expression." error message.



"Allen Browne" wrote:

The query that works has the literal value as criteria.
The query that fails refers to the text box on the form.

Presumably the form is open, and you typed the value pdb1-30 into the
text
box. Any chance the focus is still in that text box? If so, the Value
will
not have been accepted yet, and so the query will not work. Move the
focus
out of the text box to another control on the form, and see if that makes
a
difference.

If it still fails, JET does have a problem with fields that contain a
dash
character. They are handled inconsistently depending on whether the field
is
indexed or not. What is the data type of the Status field? And is it
indexed? More info on this issue:
http://support.microsoft.com/kb/271661/en-us

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"geebee" <geebee@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:F8D6C183-CD3C-4E93-9091-A81E689CB731@xxxxxxxxxxxxxxxx

I have the following:
TRANSFORM Count(Tbl_masterpop.[Loan Acct #]) AS [CountOfLoan Acct
#]SELECT
Tbl_masterpop.status FROM Tbl_masterpop WHERE (([popenterdt] between
#7/1/2006# AND #7/31/2006#) AND
(([Status]=[Forms]![frm_criteria]![delinquent_criteria]))) GROUP BY
Tbl_masterpop.status ORDER BY Format([popenterdt],'m/dd')PIVOT
Format([popenterdt],'m/dd');

but it returns no results. However, when I change the query to:
TRANSFORM Count(Tbl_masterpop.[Loan Acct #]) AS [CountOfLoan Acct #]
SELECT Tbl_masterpop.Status
FROM Tbl_masterpop
WHERE (((Tbl_masterpop.PopEnterDt) Between #7/1/2006# And #7/31/2006#)
AND
((Tbl_masterpop.Status)="pdb1-30"))
GROUP BY Tbl_masterpop.Status
ORDER BY Format([popenterdt],'m/dd')
PIVOT Format([popenterdt],'m/dd');

It works just fine.

Can someone tell me how I can get the first version to work?

Thanks in advance,
geebee






.

Relevant Pages

  • Re: Problem with Send Object
    ... Inserted into criteria of report query: ... How are you passing that filter string to the report, ... Allen Browne - Microsoft MVP. ...
    (microsoft.public.access.formscoding)
  • Re: Working with many-to-many relationships
    ... You can now use the function in a query, just like the built-in functions such as Trimor Left. ... Allen Browne - Microsoft MVP. ... it would be best to get it working in a query, and then base the report on ... >>> The titles are in a table, the authors are in a table ...
    (microsoft.public.access.reports)
  • Re: Reports, Subreports & VBA
    ... Then create a new query, ... Allen Browne - Microsoft MVP. ... >> it should give the right results when the main report calls it. ... >>> In the example of your SQL Statement, ...
    (microsoft.public.access.reports)
  • Re: "0.0" Value (not Null)... missing value in report
    ... When you view the query results directly, ... Now open the report in design view. ... Allen Browne - Microsoft MVP. ... 'CalculationChecker' report but produces a blank on my application report ...
    (microsoft.public.access.reports)
  • Re: Report with date range selection
    ... you do need one query. ... Allen Browne - Microsoft MVP. ... Then use this query as the record source for the report. ...
    (microsoft.public.access.reports)