fields are missing when I try to select them as a Group Level

[Widdy]

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

 

[Allen Browne]

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/AppIssueBadWord.html

 

[Widdy]

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

 

[Allen Browne]

Post the SQL statement of the query (by switching the query to SQL View.)

 

[Widdy]

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

 

[Allen Browne]

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.

 

[Widdy]

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

 

[Allen Browne]

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]

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

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

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

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

 

[Allen Browne]

Try changing it to a Text field (size = 255.)

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

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

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

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.

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?