Re: query from form
- From: "John Spencer" <spencer@xxxxxxxxx>
- Date: Tue, 22 Aug 2006 08:16:15 -0400
The following forces the type of test to be a string.
IIf(Forms!frm_criteria!delinquent_summary_status1 Is Null,"",[buying
center #]) AS test
You might try
IIf(Forms!frm_criteria!delinquent_summary_status1 Is Null,Null,[buying
center #]) AS test
If that fails, try forcing a number that is known to never exist for buying
center #. I don't like "magic" numbers like this, but sometimes it is the
best available solution.
Another alternative is to make Buying Center # in the master a text field by
appending a zero-length string to it or by using one of the functions CStr
to force its type to string.
"geebee" <geebee@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:1CD66021-5C44-43D6-BF75-73F0E44A25DC@xxxxxxxxxxxxxxxx
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
.
- References:
- Re: query from form
- From: Allen Browne
- Re: query from form
- From: geebee
- Re: query from form
- From: Allen Browne
- Re: query from form
- From: geebee
- Re: query from form
- Prev by Date: Re: Formatting when working with phone numbers in queries
- Next by Date: Re: Rounding to nearest 1000
- Previous by thread: Re: query from form
- Next by thread: Re: Union 2 fields where values are counted?
- Index(es):
Relevant Pages
|
