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. |
|
|
Thread Tools | Display Modes |
#21
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|