Access 2K Form Criteria feeding Query Problem

[Emarque]

I have a unbound form that contains 6 fields on it. For example:

Field1 + Field2 = Field3
Field4 + Field5 = Field6

10 + 10 = 20
20 + 30 = 30

The form displays fine. I have a query that uses the calculated fields of field3 and field6. When I run the access query in Access 2k I get check boxes displayed in place of the values of the numbers. This problem occurs when I try to display any calculated fields from the forms in the query result set. When I run the same query or queries in Access 97 my queries behave fine.

To make things even more wierd. If I surround the fields with the function cint or str... I get get the correct values displayed in access 2k.

Is there an integration problem between Forms and Queries where you have to define the form's calculated controls explicit data type in Access 2k query? The reason I ask is I have a number of questionaire type applications that have queries driven by forms and their values. Some are quite large, so I was wonder is this just a problem in Access 2k and XP? Is this issue by design? Is there a simple work around without excessive coding?

Thanks

 

[TheAceMan1]

How are ya Emarque . . . . .

It all depends on how your referencing the fields in the query. For instance :

Forms!UnboundFormName!FieldName is a proper reference.

If you can, show the query, so we can see exactly how your fields are being referenced.

TheAceMan

 

[Emarque]

Here is a sample of my query:

SELECT [Forms]![Form1]![EmployeeID] AS Expr1, [Forms]![Form1]![LastName] AS Expr2, [Forms]![Form1]![FirstName] AS Expr3, [Forms]![Form1]![Calculated_Name] AS Expr4, [Forms]![Form1]![Text14] AS Expr5
FROM Employees;

 

[TheAceMan1]

OK Emarque . . . . .

First, there is no way this query can work! Simply because there's no reference to any table (as it is, all the data comes from an unbound form). The query is just unacceptable to the SQL engine. If there's no reference to any table, usually the SQL is not allowed to run.

SQL's have to reference tables directly, and/or formula's must reference tables, unless the formula's are criteria

I could'nt believe you got this to work in 97, so I setup the form and tried to make a query. As I suspected, the toolbar button for Datasheet View was grayed out. Tried the same in Office 2K with the same results. If you really got this SQL to run in 97, you should let Microsoft know!.

In either case if the SQL did work, it would only display the data thats already displayed by the form! . . . . . What could be the purpose of that?

Any Form Data, 99.999% of the time is used as criteria. I have proven in every that this SQL won't work, and I still would like to help you . . . .

The question you need to answer is:

Just what is your intention with this SQL?

TheAceMan