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 |
#1
|
|||
|
|||
fields are missing when I try to select them as a Group Level
I am new to Microsoft Access, I have built a query for a list of products
that I want to print out in a price list, the query contains the model number, page number in catalog, price and product name. I want the group header to be the page number but it will only let me group by model number and price. Any help would be appreciated. Ian |
#2
|
|||
|
|||
fields are missing when I try to select them as a Group Level
1. What is the Record Source for this report?
If it is a query, you may need to modify the query too after adding fields to your table, so the query has the new fields, and they are then availalbe in the report. 2. Uncheck the boxes under: Tools | Options | General | Name AutoCorrect In Access 2007, it's: Office Button | Access Options | Current Database | Name AutoCorrect Then compact the database: Tools | Database Utilities | Compact/Repair or in Access 2007: Office Button | Manage | Compact/Repair Explanation of why: http://allenbrowne.com/bug-03.html 3. What is the name of the problem field? You could run ito problems if it is called Page (a report property) or Number (a reserved word.) For a list of the field names to avoid, see: http://allenbrowne.com/Ap****ueBadWord.html -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Widdy" wrote in message ... I am new to Microsoft Access, I have built a query for a list of products that I want to print out in a price list, the query contains the model number, page number in catalog, price and product name. I want the group header to be the page number but it will only let me group by model number and price. Any help would be appreciated. Ian |
#3
|
|||
|
|||
fields are missing when I try to select them as a Group Level
Thankyou,
The data source is from a query, I have not added any fields to the database since creating the query. The Name auto correct was already unchecked, I tried compacting the database anyway and no luck, I also checked the field name against your list but it was ok, the field name is Svalue. Any more ideas? Thanks Ian |
#4
|
|||
|
|||
fields are missing when I try to select them as a Group Level
Post the SQL statement of the query (by switching the query to SQL View.)
-- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Widdy" wrote in message ... Thankyou, The data source is from a query, I have not added any fields to the database since creating the query. The Name auto correct was already unchecked, I tried compacting the database anyway and no luck, I also checked the field name against your list but it was ok, the field name is Svalue. Any more ideas? Thanks Ian |
#5
|
|||
|
|||
fields are missing when I try to select them as a Group Level
HI,
Thanks a lot for your help, query is below; SELECT Product.[Product Reference], UserDefinedProperties.sValue, Product.[Short description], Product.Price FROM UserDefinedProperties INNER JOIN Product ON UserDefinedProperties.sContentID = Product.[Product Reference] WHERE (((UserDefinedProperties.nVariableID)=65879) AND ((([UserDefinedProperties].[sValue])"2")"32")); regards Ian |
#6
|
|||
|
|||
fields are missing when I try to select them as a Group Level
Okay, the sValue field is clearly there in the SELECT clause of the query. I
can see no valid reason why you cannot group by that field in your report. In report design view, open the Sorting And Grouping dialog (View menu.) You should be able to select the sValue field in the dialog, and then set Group Header and Group Footer to Yes in the lower pane of the dialog. It's not related to the problem you posted about, but I don't think the last part of the WHERE clause is doing anything. For any value in the sValue field, the expression: (([UserDefinedProperties].[sValue])"2") will yield either true (-1) or false (0.) Both values (-1 or 0) are less than "32". Therefore the expression: ((([UserDefinedProperties].[sValue])"2")"32")) will be True for all non-Null values of sValue. Is it possible you intended: WHERE (UserDefinedProperties.nVariableID = 65879) AND ([UserDefinedProperties].[sValue] "2") AND ([UserDefinedProperties].[sValue] "32") Since sValue appears to be a Text field, this will perform a text comparision, and include every value where the first digit is a 2, or anything where the first digit is a 3 and the next digit is less than 2. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Widdy" wrote in message ... HI, Thanks a lot for your help, query is below; SELECT Product.[Product Reference], UserDefinedProperties.sValue, Product.[Short description], Product.Price FROM UserDefinedProperties INNER JOIN Product ON UserDefinedProperties.sContentID = Product.[Product Reference] WHERE (((UserDefinedProperties.nVariableID)=65879) AND ((([UserDefinedProperties].[sValue])"2")"32")); |
#7
|
|||
|
|||
fields are missing when I try to select them as a Group Level
Hello,
I tried to select the Svalue in the dialog but could not, I also could not select the ShortDescription. I have removed the last bit to leave just SELECT Product.[Product Reference], Product.[Short description], Product.Price, UserDefinedProperties.sValue FROM UserDefinedProperties INNER JOIN Product ON UserDefinedProperties.sContentID = Product.[Product Reference] WHERE (((UserDefinedProperties.nVariableID)=65879)); but that has not helped, I then removed a bit more which left the below, it did not help either. SELECT Product.[Product Reference], Product.[Short description], Product.Price, UserDefinedProperties.sValue FROM UserDefinedProperties INNER JOIN Product ON UserDefinedProperties.sContentID = Product.[Product Reference]; After checking my database, both the missing fields' data type is MEMO, could that be something to do with the problem? Ian |
#8
|
|||
|
|||
fields are missing when I try to select them as a Group Level
You're trying to group on a memo field?
Not a good idea. Even if you can get it work, it will probably truncate to 255-characters. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Widdy" wrote in message ... Hello, I tried to select the Svalue in the dialog but could not, I also could not select the ShortDescription. I have removed the last bit to leave just SELECT Product.[Product Reference], Product.[Short description], Product.Price, UserDefinedProperties.sValue FROM UserDefinedProperties INNER JOIN Product ON UserDefinedProperties.sContentID = Product.[Product Reference] WHERE (((UserDefinedProperties.nVariableID)=65879)); but that has not helped, I then removed a bit more which left the below, it did not help either. SELECT Product.[Product Reference], Product.[Short description], Product.Price, UserDefinedProperties.sValue FROM UserDefinedProperties INNER JOIN Product ON UserDefinedProperties.sContentID = Product.[Product Reference]; After checking my database, both the missing fields' data type is MEMO, could that be something to do with the problem? Ian |
#9
|
|||
|
|||
fields are missing when I try to select them as a Group Level
Hi,
I am trying to group on a memo field, but it is a memo field because that field contains all of the user defined variables in our database and they can be anything, but the only ones I am using to group by are the page numbers of our catalog which will be from page 4 to page 31, so truncating is not a problem. Can it be done? Ian "Allen Browne" wrote: You're trying to group on a memo field? Not a good idea. Even if you can get it work, it will probably truncate to 255-characters. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Widdy" wrote in message ... Hello, I tried to select the Svalue in the dialog but could not, I also could not select the ShortDescription. I have removed the last bit to leave just SELECT Product.[Product Reference], Product.[Short description], Product.Price, UserDefinedProperties.sValue FROM UserDefinedProperties INNER JOIN Product ON UserDefinedProperties.sContentID = Product.[Product Reference] WHERE (((UserDefinedProperties.nVariableID)=65879)); but that has not helped, I then removed a bit more which left the below, it did not help either. SELECT Product.[Product Reference], Product.[Short description], Product.Price, UserDefinedProperties.sValue FROM UserDefinedProperties INNER JOIN Product ON UserDefinedProperties.sContentID = Product.[Product Reference]; After checking my database, both the missing fields' data type is MEMO, could that be something to do with the problem? Ian |
#10
|
|||
|
|||
fields are missing when I try to select them as a Group Level
Try changing it to a Text field (size = 255.)
-- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Widdy" wrote in message ... Hi, I am trying to group on a memo field, but it is a memo field because that field contains all of the user defined variables in our database and they can be anything, but the only ones I am using to group by are the page numbers of our catalog which will be from page 4 to page 31, so truncating is not a problem. Can it be done? Ian "Allen Browne" wrote: You're trying to group on a memo field? Not a good idea. Even if you can get it work, it will probably truncate to 255-characters. "Widdy" wrote in message ... Hello, I tried to select the Svalue in the dialog but could not, I also could not select the ShortDescription. I have removed the last bit to leave just SELECT Product.[Product Reference], Product.[Short description], Product.Price, UserDefinedProperties.sValue FROM UserDefinedProperties INNER JOIN Product ON UserDefinedProperties.sContentID = Product.[Product Reference] WHERE (((UserDefinedProperties.nVariableID)=65879)); but that has not helped, I then removed a bit more which left the below, it did not help either. SELECT Product.[Product Reference], Product.[Short description], Product.Price, UserDefinedProperties.sValue FROM UserDefinedProperties INNER JOIN Product ON UserDefinedProperties.sContentID = Product.[Product Reference]; After checking my database, both the missing fields' data type is MEMO, could that be something to do with the problem? |
Thread Tools | |
Display Modes | |
|
|