query from form

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

geebee - 21 Aug 2006 16:02 GMT

hi,

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

Reply to this Message

Allen Browne - 21 Aug 2006 16:41 GMT

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

Signature

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.

> I have the following:
> TRANSFORM Count(Tbl_masterpop.[Loan Acct #]) AS [CountOfLoan Acct #]SELECT
[quoted text clipped - 20 lines]
> Thanks in advance,
> geebee

Reply to this Message

geebee - 21 Aug 2006 17:16 GMT

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.

> The query that works has the literal value as criteria.
> The query that fails refers to the text box on the form.
[quoted text clipped - 35 lines]
> > Thanks in advance,
> > geebee

Reply to this Message

Allen Browne - 21 Aug 2006 17:33 GMT

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

Signature

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.

> The column type of [Status] is text. This column is indexed (duplicates
> OK).
[quoted text clipped - 53 lines]
>> > Thanks in advance,
>> > geebee

Reply to this Message

John Spencer - 21 Aug 2006 18:58 GMT

Allen,
Your last suggestion may very well solve the problem. In Access, you have
to declare the parameters in a crosstab query. Also, if you have queries in
the crosstab that use parameters, you have to declare any parameters that
the queries use.

Why it's required to do this on crosstabs and optional on other queries is a
mystery that only MS knows the answer to.

> So you verified that the control name was spelled correctly.
> And you ran the query directly (not via the report), after moving the
[quoted text clipped - 63 lines]
>>> > Thanks in advance,
>>> > geebee

Reply to this Message

Allen Browne - 22 Aug 2006 03:52 GMT

Thanks, John.

My understanding is that explicitly declaring the parameter is not required
if you list the Column Headings.

In a crosstab where the column headings are not specified, JET can't
determine the field names until it has run the query to completion, and of
course it can't run the query to completion until it knows whether the name
is a field name or an undeclared parameter. Catch 22.

But you are right of course: this particuar query statement had no IN in the
PIVOT clause.

Signature

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.

> Allen,
> Your last suggestion may very well solve the problem. In Access, you have
[quoted text clipped - 73 lines]
>>>> > Thanks in advance,
>>>> > geebee

Reply to this Message

geebee - 21 Aug 2006 21:17 GMT

Hi,

Thanks. This worked. I declared the parameter type explicitly. Now the
source is:

strSQL1 = "PARAMETERS [forms].[frm_criteria].[delinquentcriteria] Text ( 255
); " _
& "TRANSFORM " _
& "Count(Tbl_masterpop.[Loan Acct #]) AS [CountOfLoan Acct #]" _
& "SELECT Tbl_masterpop.status " _
& "FROM Tbl_masterpop " _
& "WHERE (([popenterdt] between #" & [Forms]![frm_criteria]![trend1] & "#
AND #" & [Forms]![frm_criteria]![trend2] & "#) AND
(([Status]=[Forms]![frm_criteria]![delinquentcriteria]))) " _
& "GROUP BY Tbl_masterpop.status " _
& "ORDER BY Format([popenterdt],'m/dd')" _
& "PIVOT Format([popenterdt],'m/dd');"

> So you verified that the control name was spelled correctly.
> And you ran the query directly (not via the report), after moving the focus
[quoted text clipped - 62 lines]
> >> > Thanks in advance,
> >> > geebee

Reply to this Message

geebee - 21 Aug 2006 23:09 GMT

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

Reply to this Message

John Spencer - 22 Aug 2006 13:16 GMT

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.

> hi,
>
[quoted text clipped - 108 lines]
>> >> > Thanks in advance,
>> >> > geebee

Reply to this Message