query from form

[Guest]

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

 

[Allen Browne]

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

 

[Guest]

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.

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.

Reply to group, rather than allenbrowne at mvps dot org.

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

 

[Allen Browne]

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.

Reply to group, rather than allenbrowne at mvps dot org.

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.

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.

Reply to group, rather than allenbrowne at mvps dot org.

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

 

[John Spencer]

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
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.

Reply to group, rather than allenbrowne at mvps dot org.

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.

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.

Reply to group, rather than allenbrowne at mvps dot org.


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

 

[Guest]

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
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.

Reply to group, rather than allenbrowne at mvps dot org.

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.

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.

Reply to group, rather than allenbrowne at mvps dot org.


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

 

[Guest]

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

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.

Reply to group, rather than allenbrowne at mvps dot org.

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.

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.

Reply to group, rather than allenbrowne at mvps dot org.


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

 

[Allen Browne]

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

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
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

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.

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.

Reply to group, rather than allenbrowne at mvps dot org.


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

 

[John Spencer]

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,

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

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.

Reply to group, rather than allenbrowne at mvps dot org.

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.

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.

Reply to group, rather than allenbrowne at mvps dot org.


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