A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Setting Up & Running Reports
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Calculating totals on a Crosstab Query



 
 
Thread Tools Display Modes
  #21  
Old February 26th, 2007, 09:15 AM posted to microsoft.public.access.reports
Blair
external usenet poster
 
Posts: 27
Default Calculating totals on a Crosstab Query

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.
Do you have any idea why the changes won't Take?
Thanks Blair

"Marshall Barton" wrote in message
...
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.
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.)

--
Marsh
MVP [MS Access]



  #22  
Old February 26th, 2007, 10:17 AM posted to microsoft.public.access.reports
Blair
external usenet poster
 
Posts: 27
Default Calculating totals on a Crosstab Query

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
"Marshall Barton" wrote in message
...
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.
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.)

--
Marsh
MVP [MS Access]



  #23  
Old February 26th, 2007, 05:24 PM posted to microsoft.public.access.reports
Marshall Barton
external usenet poster
 
Posts: 5,361
Default Calculating totals on a Crosstab Query

Blair wrote:

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.

--
Marsh
MVP [MS Access]
  #24  
Old February 26th, 2007, 05:47 PM posted to microsoft.public.access.reports
Marshall Barton
external usenet poster
 
Posts: 5,361
Default Calculating totals on a Crosstab Query

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)

--
Marsh
MVP [MS Access]
  #25  
Old February 26th, 2007, 05:53 PM posted to microsoft.public.access.reports
Marshall Barton
external usenet poster
 
Posts: 5,361
Default Calculating totals on a Crosstab Query

Blair wrote:

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.

--
Marsh
MVP [MS Access]
  #26  
Old February 26th, 2007, 10:30 PM posted to microsoft.public.access.reports
Blair
external usenet poster
 
Posts: 27
Default Calculating totals on a Crosstab Query

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" wrote in message
...
Blair wrote:

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.

--
Marsh
MVP [MS Access]



  #27  
Old February 27th, 2007, 02:34 AM posted to microsoft.public.access.reports
Marshall Barton
external usenet poster
 
Posts: 5,361
Default Calculating totals on a Crosstab Query

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]


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)

  #28  
Old February 27th, 2007, 02:38 AM posted to microsoft.public.access.reports
Blair
external usenet poster
 
Posts: 27
Default Calculating totals on a Crosstab 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!!

Thanks Blair

"Marshall Barton" wrote in message
...
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)

--
Marsh
MVP [MS Access]



  #29  
Old February 27th, 2007, 01:39 PM posted to microsoft.public.access.reports
Blair
external usenet poster
 
Posts: 27
Default Calculating totals on a Crosstab Query

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

"Marshall Barton" wrote in message
...
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]


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)



  #30  
Old February 28th, 2007, 05:55 PM posted to microsoft.public.access.reports
Marshall Barton
external usenet poster
 
Posts: 5,361
Default Calculating totals on a Crosstab Query

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]


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

"Marshall Barton" wrote
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)



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 03:21 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.