Calculating totals on a Crosstab Query

[Blair]

Your data sheet view of your qry is exactly what I was tiring to do.
But in print preview I would like it to stay in landscape but it reverts
back to portrait when I close it. Is there a way to keep just this one qry
data sheet view in landscape
Thanks Blair

It actually says
The Microsoft Jet database engine does not recognize
'[Forms]![FdailyWhelpingReport]![Text6]' as a valid field name or
expression.

This is the sql of the qry QDailyWhelpingReport That I am using. I guess I
should have used this from the start, but I was using the other until I
got
the results I wanted so I wouldn't have to mess with the criteria until I
got it working the way I wanted.
Sorry for the incontinence
Thanks Blair

SELECT [98MatingRecords].[SHED #], [98MatingRecords].[NEST #],
[98MatingRecords].[FEMALE #], [98MatingRecords].[Mating Year],
[98MatingRecords].[WHELPING DATE], [98MatingRecords].Dead,
[98MatingRecords].[1st MATING]
FROM 98MatingRecords
WHERE ((([98MatingRecords].[Mating
Year])=[Forms]![FDailyWhelpingReport]![Text6]) AND
(([98MatingRecords].Dead)
Like [Forms]![FDailyWhelpingReport]![Option4] Or
([98MatingRecords].Dead)=IsNull([Forms]![FDailyWhelpingReport]![Option4]))
AND (([98MatingRecords].[1st MATING]) Is Not Null));

First, make sure that you have UNCHECKed the troublesome
Name AutoCorrect feature (Tools menu - Options, General
tab). This feature seems to randomly cause strange
problems. Of more immediate importance, when you change
the name of something, it changes the name of other things
with the same name.

Then double check the names of everything in the query,
especially the name of the form. (I note that you changed
the spelling of whelp , but you also seem to have dropped
the 2 at the end.)

 

[Marshall Barton]

I can't find Name AutoCorrect feature, Im using A97 if that makes a
difference.

That feature did not exist in A97. Just keep that issue in
mind when you upgrade to a A2002 or A2003.

 

[Marshall Barton]

Found the problem, but can't fix it
Tracked it down to the name change of my form that has the controls and the
query that has the criteria. When I change them it won't work, even though
it is all spelled correctly, it won't recognize it.I change it back and it
will work.

It actually says
The Microsoft Jet database engine does not recognize
'[Forms]![FdailyWhelpingReport]![Text6]' as a valid field name or
expression.

This is the sql of the qry QDailyWhelpingReport That I am using. I guess I
should have used this from the start, but I was using the other until I
got
the results I wanted so I wouldn't have to mess with the criteria until I
got it working the way I wanted.

SELECT [98MatingRecords].[SHED #], [98MatingRecords].[NEST #],
[98MatingRecords].[FEMALE #], [98MatingRecords].[Mating Year],
[98MatingRecords].[WHELPING DATE], [98MatingRecords].Dead,
[98MatingRecords].[1st MATING]
FROM 98MatingRecords
WHERE ((([98MatingRecords].[Mating
Year])=[Forms]![FDailyWhelpingReport]![Text6]) AND
(([98MatingRecords].Dead)
Like [Forms]![FDailyWhelpingReport]![Option4] Or
([98MatingRecords].Dead)=IsNull([Forms]![FDailyWhelpingReport]![Option4]))
AND (([98MatingRecords].[1st MATING]) Is Not Null));

Keep checking the names. The Text6 and Option4 are likely
to be different if you rereated the form. I strongly
suggest that you use more meaningful names such as
txtMatingYear and that you also avoid names with spaces
and/or other non-alphanumeric characters.

Another potential issue is the criteria:
((Dead Like Forms!FDailyWhelpingReport!Option4) Or
(.Dead=IsNull(Forms!FDailyWhelpingReport!Option4))

Note the use of parenthesis to explicitly specify the
evaluation order of precedence between the AND and OR
ecpressions.

The issue is that Like is only appropriate when comparing a
Text field to a wildcard pattern. I think you can get the
intended result from:

Dead = Nz(Forms!FDailyWhelpingReport!Option4, True)

 

[Marshall Barton]

Your data sheet view of your qry is exactly what I was tiring to do.
But in print preview I would like it to stay in landscape but it reverts
back to portrait when I close it. Is there a way to keep just this one qry
data sheet view in landscape

The Page Setup menu item is not available for queries. You
should use a report to preview/print the results of the
query.

 

[Blair]

Thanks on all acounts, will keep tring to get it to work like I want, but
its not a big issue now I got the format I wanted for my qry
Thanks for all your help
Blair

 

[Marshall Barton]

I don't see where you are using Date(), Now() or whatever it
is that's dependent on the computer's date. I think I need
to be kept abreast of the changes to the form and the query.

The names I suggested that you change are the names of the
controls on the form. The control references in the query
must then be modified to agree with the names on the form.
It sounds like you are having trouble keeping them in sync,
possibly because you have too many copies of the form and
query
--
Marsh
MVP [MS Access]

Something is terribly screwy with something or my db. If I want to bring up
2006 year records I have to change my computer Date to 2006 in order for the
query to show any records. If I make a new qry it will work properly in
retrieving the records, But then the qry's you made won't work. They come
back with the error that it can't find Text6 or MatingYear, which is what I
called it in the new qry as you suggested.
Any Idea what's going on here?? Weird!!


"Marshall Barton" wrote

Found the problem, but can't fix it
Tracked it down to the name change of my form that has the controls and
the
query that has the criteria. When I change them it won't work, even
though
it is all spelled correctly, it won't recognize it.I change it back and it
will work.

Blair wrote:
It actually says
The Microsoft Jet database engine does not recognize
'[Forms]![FdailyWhelpingReport]![Text6]' as a valid field name or
expression.

This is the sql of the qry QDailyWhelpingReport That I am using. I guess
I
should have used this from the start, but I was using the other until I
got
the results I wanted so I wouldn't have to mess with the criteria until
I
got it working the way I wanted.

SELECT [98MatingRecords].[SHED #], [98MatingRecords].[NEST #],
[98MatingRecords].[FEMALE #], [98MatingRecords].[Mating Year],
[98MatingRecords].[WHELPING DATE], [98MatingRecords].Dead,
[98MatingRecords].[1st MATING]
FROM 98MatingRecords
WHERE ((([98MatingRecords].[Mating
Year])=[Forms]![FDailyWhelpingReport]![Text6]) AND
(([98MatingRecords].Dead)
Like [Forms]![FDailyWhelpingReport]![Option4] Or
([98MatingRecords].Dead)=IsNull([Forms]![FDailyWhelpingReport]![Option4]))
AND (([98MatingRecords].[1st MATING]) Is Not Null));

Keep checking the names. The Text6 and Option4 are likely
to be different if you rereated the form. I strongly
suggest that you use more meaningful names such as
txtMatingYear and that you also avoid names with spaces
and/or other non-alphanumeric characters.

Another potential issue is the criteria:
((Dead Like Forms!FDailyWhelpingReport!Option4) Or
(.Dead=IsNull(Forms!FDailyWhelpingReport!Option4))

Note the use of parenthesis to explicitly specify the
evaluation order of precedence between the AND and OR
ecpressions.

The issue is that Like is only appropriate when comparing a
Text field to a wildcard pattern. I think you can get the
intended result from:

Dead = Nz(Forms!FDailyWhelpingReport!Option4, True)

 

[Blair]

Something is terribly screwy with something or my db. If I want to bring up
2006 year records I have to change my computer Date to 2006 in order for the
query to show any records. If I make a new qry it will work properly in
retrieving the records, But then the qry's you made won't work. They come
back with the error that it can't find Text6 or MatingYear, which is what I
called it in the new qry as you suggested.
Any Idea what's going on here?? Weird!!

Thanks Blair

Found the problem, but can't fix it
Tracked it down to the name change of my form that has the controls and
the
query that has the criteria. When I change them it won't work, even
though
it is all spelled correctly, it won't recognize it.I change it back and it
will work.

Blair wrote:
It actually says
The Microsoft Jet database engine does not recognize
'[Forms]![FdailyWhelpingReport]![Text6]' as a valid field name or
expression.

This is the sql of the qry QDailyWhelpingReport That I am using. I guess
I
should have used this from the start, but I was using the other until I
got
the results I wanted so I wouldn't have to mess with the criteria until
I
got it working the way I wanted.

SELECT [98MatingRecords].[SHED #], [98MatingRecords].[NEST #],
[98MatingRecords].[FEMALE #], [98MatingRecords].[Mating Year],
[98MatingRecords].[WHELPING DATE], [98MatingRecords].Dead,
[98MatingRecords].[1st MATING]
FROM 98MatingRecords
WHERE ((([98MatingRecords].[Mating
Year])=[Forms]![FDailyWhelpingReport]![Text6]) AND
(([98MatingRecords].Dead)
Like [Forms]![FDailyWhelpingReport]![Option4] Or
([98MatingRecords].Dead)=IsNull([Forms]![FDailyWhelpingReport]![Option4]))
AND (([98MatingRecords].[1st MATING]) Is Not Null));

Keep checking the names. The Text6 and Option4 are likely
to be different if you rereated the form. I strongly
suggest that you use more meaningful names such as
txtMatingYear and that you also avoid names with spaces
and/or other non-alphanumeric characters.

Another potential issue is the criteria:
((Dead Like Forms!FDailyWhelpingReport!Option4) Or
(.Dead=IsNull(Forms!FDailyWhelpingReport!Option4))

Note the use of parenthesis to explicitly specify the
evaluation order of precedence between the AND and OR
ecpressions.

The issue is that Like is only appropriate when comparing a
Text field to a wildcard pattern. I think you can get the
intended result from:

Dead = Nz(Forms!FDailyWhelpingReport!Option4, True)

 

[Blair]

Using Year(Now())
in the form load with the option to change it if I wanted a different years
results.
Tried it in the text box default value and it did the same thing. It seems
like the qry and form has some hidden link, besides the obvious.
I do have several of copies until I get what I want.I will have to start
exporting to another db to hold them till I don't need them
Thanks Blair

I don't see where you are using Date(), Now() or whatever it
is that's dependent on the computer's date. I think I need
to be kept abreast of the changes to the form and the query.

The names I suggested that you change are the names of the
controls on the form. The control references in the query
must then be modified to agree with the names on the form.
It sounds like you are having trouble keeping them in sync,
possibly because you have too many copies of the form and
query
--
Marsh
MVP [MS Access]

Something is terribly screwy with something or my db. If I want to bring
up
2006 year records I have to change my computer Date to 2006 in order for
the
query to show any records. If I make a new qry it will work properly in
retrieving the records, But then the qry's you made won't work. They come
back with the error that it can't find Text6 or MatingYear, which is what
I
called it in the new qry as you suggested.
Any Idea what's going on here?? Weird!!


"Marshall Barton" wrote

Blair wrote:

Found the problem, but can't fix it
Tracked it down to the name change of my form that has the controls and
the
query that has the criteria. When I change them it won't work, even
though
it is all spelled correctly, it won't recognize it.I change it back and
it
will work.

Blair wrote:
It actually says
The Microsoft Jet database engine does not recognize
'[Forms]![FdailyWhelpingReport]![Text6]' as a valid field name or
expression.

This is the sql of the qry QDailyWhelpingReport That I am using. I
guess
I
should have used this from the start, but I was using the other until
I
got
the results I wanted so I wouldn't have to mess with the criteria
until
I
got it working the way I wanted.

SELECT [98MatingRecords].[SHED #], [98MatingRecords].[NEST #],
[98MatingRecords].[FEMALE #], [98MatingRecords].[Mating Year],
[98MatingRecords].[WHELPING DATE], [98MatingRecords].Dead,
[98MatingRecords].[1st MATING]
FROM 98MatingRecords
WHERE ((([98MatingRecords].[Mating
Year])=[Forms]![FDailyWhelpingReport]![Text6]) AND
(([98MatingRecords].Dead)
Like [Forms]![FDailyWhelpingReport]![Option4] Or
([98MatingRecords].Dead)=IsNull([Forms]![FDailyWhelpingReport]![Option4]))
AND (([98MatingRecords].[1st MATING]) Is Not Null));

Keep checking the names. The Text6 and Option4 are likely
to be different if you rereated the form. I strongly
suggest that you use more meaningful names such as
txtMatingYear and that you also avoid names with spaces
and/or other non-alphanumeric characters.

Another potential issue is the criteria:
((Dead Like Forms!FDailyWhelpingReport!Option4) Or
(.Dead=IsNull(Forms!FDailyWhelpingReport!Option4))

Note the use of parenthesis to explicitly specify the
evaluation order of precedence between the AND and OR
ecpressions.

The issue is that Like is only appropriate when comparing a
Text field to a wildcard pattern. I think you can get the
intended result from:

Dead = Nz(Forms!FDailyWhelpingReport!Option4, True)

 

[Marshall Barton]

Check the data type of the [Mating Year] field in the table.
If it's a Text field, the text box on the form shuld
probably be set to: CStr(Year(Date()))

I don't understand why you should need to change your
system's date. If you want to search for a different year,
can't you just type the year in the text box?

I am afraid that there is so much going on now that I have
lost track of what you are now using for the query and
what's in the form.
--
Marsh
MVP [MS Access]

Using Year(Now())
in the form load with the option to change it if I wanted a different years
results.
Tried it in the text box default value and it did the same thing. It seems
like the qry and form has some hidden link, besides the obvious.
I do have several of copies until I get what I want.I will have to start
exporting to another db to hold them till I don't need them

I don't see where you are using Date(), Now() or whatever it
is that's dependent on the computer's date. I think I need
to be kept abreast of the changes to the form and the query.

The names I suggested that you change are the names of the
controls on the form. The control references in the query
must then be modified to agree with the names on the form.
It sounds like you are having trouble keeping them in sync,
possibly because you have too many copies of the form and
query

Something is terribly screwy with something or my db. If I want to bring
up
2006 year records I have to change my computer Date to 2006 in order for
the
query to show any records. If I make a new qry it will work properly in
retrieving the records, But then the qry's you made won't work. They come
back with the error that it can't find Text6 or MatingYear, which is what
I
called it in the new qry as you suggested.
Any Idea what's going on here?? Weird!!


"Marshall Barton" wrote
Blair wrote:

Found the problem, but can't fix it
Tracked it down to the name change of my form that has the controls and
the
query that has the criteria. When I change them it won't work, even
though
it is all spelled correctly, it won't recognize it.I change it back and
it
will work.

Blair wrote:
It actually says
The Microsoft Jet database engine does not recognize
'[Forms]![FdailyWhelpingReport]![Text6]' as a valid field name or
expression.

This is the sql of the qry QDailyWhelpingReport That I am using. I
guess
I
should have used this from the start, but I was using the other until
I
got
the results I wanted so I wouldn't have to mess with the criteria
until
I
got it working the way I wanted.

SELECT [98MatingRecords].[SHED #], [98MatingRecords].[NEST #],
[98MatingRecords].[FEMALE #], [98MatingRecords].[Mating Year],
[98MatingRecords].[WHELPING DATE], [98MatingRecords].Dead,
[98MatingRecords].[1st MATING]
FROM 98MatingRecords
WHERE ((([98MatingRecords].[Mating
Year])=[Forms]![FDailyWhelpingReport]![Text6]) AND
(([98MatingRecords].Dead)
Like [Forms]![FDailyWhelpingReport]![Option4] Or
([98MatingRecords].Dead)=IsNull([Forms]![FDailyWhelpingReport]![Option4]))
AND (([98MatingRecords].[1st MATING]) Is Not Null));

Keep checking the names. The Text6 and Option4 are likely
to be different if you rereated the form. I strongly
suggest that you use more meaningful names such as
txtMatingYear and that you also avoid names with spaces
and/or other non-alphanumeric characters.

Another potential issue is the criteria:
((Dead Like Forms!FDailyWhelpingReport!Option4) Or
(.Dead=IsNull(Forms!FDailyWhelpingReport!Option4))

Note the use of parenthesis to explicitly specify the
evaluation order of precedence between the AND and OR
ecpressions.

The issue is that Like is only appropriate when comparing a
Text field to a wildcard pattern. I think you can get the
intended result from:

Dead = Nz(Forms!FDailyWhelpingReport!Option4, True)

 

[SBGFF]

IF you like I could send you a db with just qrys and records you need to try
it out
Thanks Blair
This is the first qry, the one the crosstabs are built off.
Qry name is QDailyWhelpingReport

SELECT [98MatingRecords].[SHED #], [98MatingRecords].[NEST #],
[98MatingRecords].[FEMALE #], [98MatingRecords].[Mating Year],
[98MatingRecords].[WHELPING DATE], [98MatingRecords].Dead,
[98MatingRecords].[1st MATING]
FROM 98MatingRecords
WHERE ((([98MatingRecords].[Mating
Year])=[Forms]![FDailyWhelpingReport]![MatingYear]) AND
(([98MatingRecords].Dead) Like [Forms]![FDailyWhelpingReport]![OptionDead]
Or
([98MatingRecords].Dead)=IsNull([Forms]![FDailyWhelpingReport]![OptionDead]))
AND (([98MatingRecords].[1st MATING]) Is Not Null));

Next is the first crosstab you sent me,
Qry name is QDailyWhelpingReport_Shed

TRANSFORM Count(QDailyWhelpingReport.[NEST #]) AS [The Value]
SELECT QDailyWhelpingReport.[WHELPING DATE],
Count(QDailyWhelpingReport.[NEST #]) AS [Total Of NEST #]
FROM QDailyWhelpingReport
GROUP BY QDailyWhelpingReport.[WHELPING DATE]
PIVOT QDailyWhelpingReport.[SHED #];

Next The 2nd Crosstab you sent me
Qry name is QDailyWhelpingReport_SubTotal

TRANSFORM Count(IIf([Whelping Date] Is Null,Null,[NEST #])) AS Expr1
SELECT "SubTotal" AS Expr2, Count(IIf([Whelping Date] Is Null,Null,[NEST
#])) AS Expr3
FROM QDailyWhelpingReport
GROUP BY "SubTotal"
PIVOT QDailyWhelpingReport.[SHED #];

Next The 3rd Crosstab you sent me
Qry name is QDailyWhelpingReport_Total

TRANSFORM Count(QDailyWhelpingReport.[NEST #]) AS [CountOfNEST #]
SELECT "Total" AS Expr1, Count(QDailyWhelpingReport.[NEST #]) AS
[CountOfNEST #1]
FROM QDailyWhelpingReport
GROUP BY "Total"
PIVOT QDailyWhelpingReport.[SHED #];

Next The 4th Crosstab you sent me
Qry name is QDailyWhelpingReport_UnionQry

SELECT * FROM QDailyWhelpingReport_Shed
UNION ALL
SELECT * FROM QDailyWhelpingReport_SubTotal
UNION ALL SELECT * FROM QDailyWhelpingReport_Total;

Crosstab queries generally require their parameters to be
declared, so that part is necessary. I need to see the
queries involved before I can hope to understand the rest of
what you're describing.

Sometimes you can unravel parameter issues by temporarily
repacing the parameter with a literal value. If that works,
then the problem is in the way the parameter value is
referenced or on the source of the parameter value. If the
literal value doesn't work either, then the problem is in
how the parameter is used.
--
Marsh
MVP [MS Access]

The data type in the table is set to Number.Just got your last post.
To night I deleted every thing and started from scratch.Made a new qry and
every thing works till I try to open the first crosstab you made, then it
has an error concerning the parameter. I tried declaring the parameter in
the query, it then opened but there were no results. I tried declaring
them
in the qry your qry's are based on and I don't get any results, remove
them
and the qry works. remove them from the crosstab and I get the error.
Something ain't just quite right.
In the parameters data type in the qry, number is not there, the only
option
that it will let me use is Date/time

Check the data type of the [Mating Year] field in the table.
If it's a Text field, the text box on the form shuld
probably be set to: CStr(Year(Date()))

I don't understand why you should need to change your
system's date. If you want to search for a different year,
can't you just type the year in the text box?

I am afraid that there is so much going on now that I have
lost track of what you are now using for the query and
what's in the form.


Blair wrote:
Using Year(Now())
in the form load with the option to change it if I wanted a different
years
results.
Tried it in the text box default value and it did the same thing. It
seems
like the qry and form has some hidden link, besides the obvious.
I do have several of copies until I get what I want.I will have to
start
exporting to another db to hold them till I don't need them

I don't see where you are using Date(), Now() or whatever it
is that's dependent on the computer's date. I think I need
to be kept abreast of the changes to the form and the query.

The names I suggested that you change are the names of the
controls on the form. The control references in the query
must then be modified to agree with the names on the form.
It sounds like you are having trouble keeping them in sync,
possibly because you have too many copies of the form and
query

Blair wrote:
Something is terribly screwy with something or my db. If I want to
bring
up
2006 year records I have to change my computer Date to 2006 in order
for
the
query to show any records. If I make a new qry it will work properly
in
retrieving the records, But then the qry's you made won't work. They
come
back with the error that it can't find Text6 or MatingYear, which is
what
I
called it in the new qry as you suggested.
Any Idea what's going on here?? Weird!!


"Marshall Barton" wrote
Blair wrote:

Found the problem, but can't fix it
Tracked it down to the name change of my form that has the controls
and
the
query that has the criteria. When I change them it won't work, even
though
it is all spelled correctly, it won't recognize it.I change it back
and
it
will work.

Blair wrote:
It actually says
The Microsoft Jet database engine does not recognize
'[Forms]![FdailyWhelpingReport]![Text6]' as a valid field name or
expression.

This is the sql of the qry QDailyWhelpingReport That I am using. I
guess
I
should have used this from the start, but I was using the other
until
I
got
the results I wanted so I wouldn't have to mess with the criteria
until
I
got it working the way I wanted.

SELECT [98MatingRecords].[SHED #], [98MatingRecords].[NEST #],
[98MatingRecords].[FEMALE #], [98MatingRecords].[Mating Year],
[98MatingRecords].[WHELPING DATE], [98MatingRecords].Dead,
[98MatingRecords].[1st MATING]
FROM 98MatingRecords
WHERE ((([98MatingRecords].[Mating
Year])=[Forms]![FDailyWhelpingReport]![Text6]) AND
(([98MatingRecords].Dead)
Like [Forms]![FDailyWhelpingReport]![Option4] Or
([98MatingRecords].Dead)=IsNull([Forms]![FDailyWhelpingReport]![Option4]))
AND (([98MatingRecords].[1st MATING]) Is Not Null));

Keep checking the names. The Text6 and Option4 are likely
to be different if you rereated the form. I strongly
suggest that you use more meaningful names such as
txtMatingYear and that you also avoid names with spaces
and/or other non-alphanumeric characters.

Another potential issue is the criteria:
((Dead Like Forms!FDailyWhelpingReport!Option4) Or
(.Dead=IsNull(Forms!FDailyWhelpingReport!Option4))

Note the use of parenthesis to explicitly specify the
evaluation order of precedence between the AND and OR
ecpressions.

The issue is that Like is only appropriate when comparing a
Text field to a wildcard pattern. I think you can get the
intended result from:

Dead = Nz(Forms!FDailyWhelpingReport!Option4, True)

 

[SBGFF]

The data type in the table is set to Number.Just got your last post.
To night I deleted every thing and started from scratch.Made a new qry and
every thing works till I try to open the first crosstab you made, then it
has an error concerning the parameter. I tried declaring the parameter in
the query, it then opened but there were no results. I tried declaring them
in the qry your qry's are based on and I don't get any results, remove them
and the qry works. remove them from the crosstab and I get the error.
Something ain't just quite right.
In the parameters data type in the qry, number is not there, the only option
that it will let me use is Date/time
Thanks for any help you can give me
Blair

Check the data type of the [Mating Year] field in the table.
If it's a Text field, the text box on the form shuld
probably be set to: CStr(Year(Date()))

I don't understand why you should need to change your
system's date. If you want to search for a different year,
can't you just type the year in the text box?

I am afraid that there is so much going on now that I have
lost track of what you are now using for the query and
what's in the form.
--
Marsh
MVP [MS Access]

Using Year(Now())
in the form load with the option to change it if I wanted a different
years
results.
Tried it in the text box default value and it did the same thing. It seems
like the qry and form has some hidden link, besides the obvious.
I do have several of copies until I get what I want.I will have to start
exporting to another db to hold them till I don't need them

I don't see where you are using Date(), Now() or whatever it
is that's dependent on the computer's date. I think I need
to be kept abreast of the changes to the form and the query.

The names I suggested that you change are the names of the
controls on the form. The control references in the query
must then be modified to agree with the names on the form.
It sounds like you are having trouble keeping them in sync,
possibly because you have too many copies of the form and
query

Blair wrote:
Something is terribly screwy with something or my db. If I want to bring
up
2006 year records I have to change my computer Date to 2006 in order for
the
query to show any records. If I make a new qry it will work properly in
retrieving the records, But then the qry's you made won't work. They
come
back with the error that it can't find Text6 or MatingYear, which is
what
I
called it in the new qry as you suggested.
Any Idea what's going on here?? Weird!!


"Marshall Barton" wrote
Blair wrote:

Found the problem, but can't fix it
Tracked it down to the name change of my form that has the controls
and
the
query that has the criteria. When I change them it won't work, even
though
it is all spelled correctly, it won't recognize it.I change it back
and
it
will work.

Blair wrote:
It actually says
The Microsoft Jet database engine does not recognize
'[Forms]![FdailyWhelpingReport]![Text6]' as a valid field name or
expression.

This is the sql of the qry QDailyWhelpingReport That I am using. I
guess
I
should have used this from the start, but I was using the other
until
I
got
the results I wanted so I wouldn't have to mess with the criteria
until
I
got it working the way I wanted.

SELECT [98MatingRecords].[SHED #], [98MatingRecords].[NEST #],
[98MatingRecords].[FEMALE #], [98MatingRecords].[Mating Year],
[98MatingRecords].[WHELPING DATE], [98MatingRecords].Dead,
[98MatingRecords].[1st MATING]
FROM 98MatingRecords
WHERE ((([98MatingRecords].[Mating
Year])=[Forms]![FDailyWhelpingReport]![Text6]) AND
(([98MatingRecords].Dead)
Like [Forms]![FDailyWhelpingReport]![Option4] Or
([98MatingRecords].Dead)=IsNull([Forms]![FDailyWhelpingReport]![Option4]))
AND (([98MatingRecords].[1st MATING]) Is Not Null));

Keep checking the names. The Text6 and Option4 are likely
to be different if you rereated the form. I strongly
suggest that you use more meaningful names such as
txtMatingYear and that you also avoid names with spaces
and/or other non-alphanumeric characters.

Another potential issue is the criteria:
((Dead Like Forms!FDailyWhelpingReport!Option4) Or
(.Dead=IsNull(Forms!FDailyWhelpingReport!Option4))

Note the use of parenthesis to explicitly specify the
evaluation order of precedence between the AND and OR
ecpressions.

The issue is that Like is only appropriate when comparing a
Text field to a wildcard pattern. I think you can get the
intended result from:

Dead = Nz(Forms!FDailyWhelpingReport!Option4, True)

 

[Marshall Barton]

Crosstab queries generally require their parameters to be
declared, so that part is necessary. I need to see the
queries involved before I can hope to understand the rest of
what you're describing.

Sometimes you can unravel parameter issues by temporarily
repacing the parameter with a literal value. If that works,
then the problem is in the way the parameter value is
referenced or on the source of the parameter value. If the
literal value doesn't work either, then the problem is in
how the parameter is used.
--
Marsh
MVP [MS Access]

The data type in the table is set to Number.Just got your last post.
To night I deleted every thing and started from scratch.Made a new qry and
every thing works till I try to open the first crosstab you made, then it
has an error concerning the parameter. I tried declaring the parameter in
the query, it then opened but there were no results. I tried declaring them
in the qry your qry's are based on and I don't get any results, remove them
and the qry works. remove them from the crosstab and I get the error.
Something ain't just quite right.
In the parameters data type in the qry, number is not there, the only option
that it will let me use is Date/time

Check the data type of the [Mating Year] field in the table.
If it's a Text field, the text box on the form shuld
probably be set to: CStr(Year(Date()))

I don't understand why you should need to change your
system's date. If you want to search for a different year,
can't you just type the year in the text box?

I am afraid that there is so much going on now that I have
lost track of what you are now using for the query and
what's in the form.

Using Year(Now())
in the form load with the option to change it if I wanted a different
years
results.
Tried it in the text box default value and it did the same thing. It seems
like the qry and form has some hidden link, besides the obvious.
I do have several of copies until I get what I want.I will have to start
exporting to another db to hold them till I don't need them

I don't see where you are using Date(), Now() or whatever it
is that's dependent on the computer's date. I think I need
to be kept abreast of the changes to the form and the query.

The names I suggested that you change are the names of the
controls on the form. The control references in the query
must then be modified to agree with the names on the form.
It sounds like you are having trouble keeping them in sync,
possibly because you have too many copies of the form and
query

Blair wrote:
Something is terribly screwy with something or my db. If I want to bring
up
2006 year records I have to change my computer Date to 2006 in order for
the
query to show any records. If I make a new qry it will work properly in
retrieving the records, But then the qry's you made won't work. They
come
back with the error that it can't find Text6 or MatingYear, which is
what
I
called it in the new qry as you suggested.
Any Idea what's going on here?? Weird!!


"Marshall Barton" wrote
Blair wrote:

Found the problem, but can't fix it
Tracked it down to the name change of my form that has the controls
and
the
query that has the criteria. When I change them it won't work, even
though
it is all spelled correctly, it won't recognize it.I change it back
and
it
will work.

Blair wrote:
It actually says
The Microsoft Jet database engine does not recognize
'[Forms]![FdailyWhelpingReport]![Text6]' as a valid field name or
expression.

This is the sql of the qry QDailyWhelpingReport That I am using. I
guess
I
should have used this from the start, but I was using the other
until
I
got
the results I wanted so I wouldn't have to mess with the criteria
until
I
got it working the way I wanted.

SELECT [98MatingRecords].[SHED #], [98MatingRecords].[NEST #],
[98MatingRecords].[FEMALE #], [98MatingRecords].[Mating Year],
[98MatingRecords].[WHELPING DATE], [98MatingRecords].Dead,
[98MatingRecords].[1st MATING]
FROM 98MatingRecords
WHERE ((([98MatingRecords].[Mating
Year])=[Forms]![FDailyWhelpingReport]![Text6]) AND
(([98MatingRecords].Dead)
Like [Forms]![FDailyWhelpingReport]![Option4] Or
([98MatingRecords].Dead)=IsNull([Forms]![FDailyWhelpingReport]![Option4]))
AND (([98MatingRecords].[1st MATING]) Is Not Null));

Keep checking the names. The Text6 and Option4 are likely
to be different if you rereated the form. I strongly
suggest that you use more meaningful names such as
txtMatingYear and that you also avoid names with spaces
and/or other non-alphanumeric characters.

Another potential issue is the criteria:
((Dead Like Forms!FDailyWhelpingReport!Option4) Or
(.Dead=IsNull(Forms!FDailyWhelpingReport!Option4))

Note the use of parenthesis to explicitly specify the
evaluation order of precedence between the AND and OR
ecpressions.

The issue is that Like is only appropriate when comparing a
Text field to a wildcard pattern. I think you can get the
intended result from:

Dead = Nz(Forms!FDailyWhelpingReport!Option4, True)

 

[SBGFF]

sending again cause had date set for 3/2/2007 for testing db, don't know if
it makes any difference in you getting it

IF you like I could send you a db with just qrys and records you need to try
it out
Thanks Blair
This is the first qry, the one the crosstabs are built off.
Qry name is QDailyWhelpingReport

SELECT [98MatingRecords].[SHED #], [98MatingRecords].[NEST #],
[98MatingRecords].[FEMALE #], [98MatingRecords].[Mating Year],
[98MatingRecords].[WHELPING DATE], [98MatingRecords].Dead,
[98MatingRecords].[1st MATING]
FROM 98MatingRecords
WHERE ((([98MatingRecords].[Mating
Year])=[Forms]![FDailyWhelpingReport]![MatingYear]) AND
(([98MatingRecords].Dead) Like [Forms]![FDailyWhelpingReport]![OptionDead]
Or
([98MatingRecords].Dead)=IsNull([Forms]![FDailyWhelpingReport]![OptionDead]))
AND (([98MatingRecords].[1st MATING]) Is Not Null));

Next is the first crosstab you sent me,
Qry name is QDailyWhelpingReport_Shed

TRANSFORM Count(QDailyWhelpingReport.[NEST #]) AS [The Value]
SELECT QDailyWhelpingReport.[WHELPING DATE],
Count(QDailyWhelpingReport.[NEST #]) AS [Total Of NEST #]
FROM QDailyWhelpingReport
GROUP BY QDailyWhelpingReport.[WHELPING DATE]
PIVOT QDailyWhelpingReport.[SHED #];

Next The 2nd Crosstab you sent me
Qry name is QDailyWhelpingReport_SubTotal

TRANSFORM Count(IIf([Whelping Date] Is Null,Null,[NEST #])) AS Expr1
SELECT "SubTotal" AS Expr2, Count(IIf([Whelping Date] Is Null,Null,[NEST
#])) AS Expr3
FROM QDailyWhelpingReport
GROUP BY "SubTotal"
PIVOT QDailyWhelpingReport.[SHED #];

Next The 3rd Crosstab you sent me
Qry name is QDailyWhelpingReport_Total

TRANSFORM Count(QDailyWhelpingReport.[NEST #]) AS [CountOfNEST #]
SELECT "Total" AS Expr1, Count(QDailyWhelpingReport.[NEST #]) AS
[CountOfNEST #1]
FROM QDailyWhelpingReport
GROUP BY "Total"
PIVOT QDailyWhelpingReport.[SHED #];

Next The 4th Crosstab you sent me
Qry name is QDailyWhelpingReport_UnionQry

SELECT * FROM QDailyWhelpingReport_Shed
UNION ALL
SELECT * FROM QDailyWhelpingReport_SubTotal
UNION ALL SELECT * FROM QDailyWhelpingReport_Total;

Crosstab queries generally require their parameters to be
declared, so that part is necessary. I need to see the
queries involved before I can hope to understand the rest of
what you're describing.

Sometimes you can unravel parameter issues by temporarily
repacing the parameter with a literal value. If that works,
then the problem is in the way the parameter value is
referenced or on the source of the parameter value. If the
literal value doesn't work either, then the problem is in
how the parameter is used.
--
Marsh
MVP [MS Access]

The data type in the table is set to Number.Just got your last post.
To night I deleted every thing and started from scratch.Made a new qry and
every thing works till I try to open the first crosstab you made, then it
has an error concerning the parameter. I tried declaring the parameter in
the query, it then opened but there were no results. I tried declaring
them
in the qry your qry's are based on and I don't get any results, remove
them
and the qry works. remove them from the crosstab and I get the error.
Something ain't just quite right.
In the parameters data type in the qry, number is not there, the only
option
that it will let me use is Date/time

Check the data type of the [Mating Year] field in the table.
If it's a Text field, the text box on the form shuld
probably be set to: CStr(Year(Date()))

I don't understand why you should need to change your
system's date. If you want to search for a different year,
can't you just type the year in the text box?

I am afraid that there is so much going on now that I have
lost track of what you are now using for the query and
what's in the form.


Blair wrote:
Using Year(Now())
in the form load with the option to change it if I wanted a different
years
results.
Tried it in the text box default value and it did the same thing. It
seems
like the qry and form has some hidden link, besides the obvious.
I do have several of copies until I get what I want.I will have to
start
exporting to another db to hold them till I don't need them

I don't see where you are using Date(), Now() or whatever it
is that's dependent on the computer's date. I think I need
to be kept abreast of the changes to the form and the query.

The names I suggested that you change are the names of the
controls on the form. The control references in the query
must then be modified to agree with the names on the form.
It sounds like you are having trouble keeping them in sync,
possibly because you have too many copies of the form and
query

Blair wrote:
Something is terribly screwy with something or my db. If I want to
bring
up
2006 year records I have to change my computer Date to 2006 in order
for
the
query to show any records. If I make a new qry it will work properly
in
retrieving the records, But then the qry's you made won't work. They
come
back with the error that it can't find Text6 or MatingYear, which is
what
I
called it in the new qry as you suggested.
Any Idea what's going on here?? Weird!!


"Marshall Barton" wrote
Blair wrote:

Found the problem, but can't fix it
Tracked it down to the name change of my form that has the controls
and
the
query that has the criteria. When I change them it won't work, even
though
it is all spelled correctly, it won't recognize it.I change it back
and
it
will work.

Blair wrote:
It actually says
The Microsoft Jet database engine does not recognize
'[Forms]![FdailyWhelpingReport]![Text6]' as a valid field name or
expression.

This is the sql of the qry QDailyWhelpingReport That I am using. I
guess
I
should have used this from the start, but I was using the other
until
I
got
the results I wanted so I wouldn't have to mess with the criteria
until
I
got it working the way I wanted.

SELECT [98MatingRecords].[SHED #], [98MatingRecords].[NEST #],
[98MatingRecords].[FEMALE #], [98MatingRecords].[Mating Year],
[98MatingRecords].[WHELPING DATE], [98MatingRecords].Dead,
[98MatingRecords].[1st MATING]
FROM 98MatingRecords
WHERE ((([98MatingRecords].[Mating
Year])=[Forms]![FDailyWhelpingReport]![Text6]) AND
(([98MatingRecords].Dead)
Like [Forms]![FDailyWhelpingReport]![Option4] Or
([98MatingRecords].Dead)=IsNull([Forms]![FDailyWhelpingReport]![Option4]))
AND (([98MatingRecords].[1st MATING]) Is Not Null));

Keep checking the names. The Text6 and Option4 are likely
to be different if you rereated the form. I strongly
suggest that you use more meaningful names such as
txtMatingYear and that you also avoid names with spaces
and/or other non-alphanumeric characters.

Another potential issue is the criteria:
((Dead Like Forms!FDailyWhelpingReport!Option4) Or
(.Dead=IsNull(Forms!FDailyWhelpingReport!Option4))

Note the use of parenthesis to explicitly specify the
evaluation order of precedence between the AND and OR
ecpressions.

The issue is that Like is only appropriate when comparing a
Text field to a wildcard pattern. I think you can get the
intended result from:

Dead = Nz(Forms!FDailyWhelpingReport!Option4, True)

 

[Marshall Barton]

As always, double check the names of the controls on the
form to make sure they are identical to the names you use in
the query parameters.

I don't see where you explained which error message you
received and/or which parameter was causing it.

Your first query's WHERE clause is not quite right. Don't
use Like unless you really are doing a wildcard text
comparison.

WHERE [Mating Year] = Forms!FDailyWhelpingReport!MatingYear
AND (Dead = Forms!FDailyWhelpingReport!OptionDead
Or (Dead=IsNull(Forms!FDailyWhelpingReport!OptionDead)
AND [1st MATING] Is Not Null

The Dead test looks odd, the way it is written, the query
will return only records with Dead = True when you do not
specify OptionDead on the form.

If that still gives you trouble, remove the criteria one at
a time to see if it helps you locate the one that causing
trouble.

If you can deal with it, you can avoid all those extra [ ],
( ), and table names by never switching the query from SQL
view to the QBE grid. When you save a query from SQL view,
it will come back in SQL view.
--
Marsh
MVP [MS Access]

IF you like I could send you a db with just qrys and records you need to try
it out

This is the first qry, the one the crosstabs are built off.
Qry name is QDailyWhelpingReport

SELECT [98MatingRecords].[SHED #], [98MatingRecords].[NEST #],
[98MatingRecords].[FEMALE #], [98MatingRecords].[Mating Year],
[98MatingRecords].[WHELPING DATE], [98MatingRecords].Dead,
[98MatingRecords].[1st MATING]
FROM 98MatingRecords
WHERE ((([98MatingRecords].[Mating Year])=[Forms]![FDailyWhelpingReport]![MatingYear])
AND (([98MatingRecords].Dead) Like [Forms]![FDailyWhelpingReport]![OptionDead]
Or ([98MatingRecords].Dead)=IsNull([Forms]![FDailyWhelpingReport]![OptionDead]))
AND (([98MatingRecords].[1st MATING]) Is Not Null));

Next is the first crosstab you sent me,
Qry name is QDailyWhelpingReport_Shed

TRANSFORM Count(QDailyWhelpingReport.[NEST #]) AS [The Value]
SELECT QDailyWhelpingReport.[WHELPING DATE],
Count(QDailyWhelpingReport.[NEST #]) AS [Total Of NEST #]
FROM QDailyWhelpingReport
GROUP BY QDailyWhelpingReport.[WHELPING DATE]
PIVOT QDailyWhelpingReport.[SHED #];

Next The 2nd Crosstab you sent me
Qry name is QDailyWhelpingReport_SubTotal

TRANSFORM Count(IIf([Whelping Date] Is Null,Null,[NEST #])) AS Expr1
SELECT "SubTotal" AS Expr2,
Count(IIf([Whelping Date] Is Null,Null,[NEST #])) AS Expr3
FROM QDailyWhelpingReport
GROUP BY "SubTotal"
PIVOT QDailyWhelpingReport.[SHED #];

Next The 3rd Crosstab you sent me
Qry name is QDailyWhelpingReport_Total

TRANSFORM Count(QDailyWhelpingReport.[NEST #]) AS [CountOfNEST #]
SELECT "Total" AS Expr1,
Count(QDailyWhelpingReport.[NEST #]) AS [CountOfNEST #1]
FROM QDailyWhelpingReport
GROUP BY "Total"
PIVOT QDailyWhelpingReport.[SHED #];

Next The 4th Crosstab you sent me
Qry name is QDailyWhelpingReport_UnionQry

SELECT * FROM QDailyWhelpingReport_Shed
UNION ALL
SELECT * FROM QDailyWhelpingReport_SubTotal
UNION ALL SELECT * FROM QDailyWhelpingReport_Total;

Crosstab queries generally require their parameters to be
declared, so that part is necessary. I need to see the
queries involved before I can hope to understand the rest of
what you're describing.

Sometimes you can unravel parameter issues by temporarily
repacing the parameter with a literal value. If that works,
then the problem is in the way the parameter value is
referenced or on the source of the parameter value. If the
literal value doesn't work either, then the problem is in
how the parameter is used.