Order datasheet by field containing expression?

[nicksource]

I have a list of products with stock, the stock is calculated by an expression subtracting the goods that have gone out with those that have come in.

How do I order a datasheet with this? As it seems because it isn't a field in a table, it won't let me.

Thanks in advance.

 

[boblarson]

How are you trying to do it now?

 

[ajetrumpet]

How are you trying to do it now?

that's EXACTLY what I was wondering.

 

[nicksource]

In the Form, under the Data tab I have Order By as '[Field]'. Which when I view the form, it prompts me with a box to enter data into, however if I use another field that is actually in the table, like Order By '[ID]' it works fine.

Picture:

 

[ajetrumpet]

Picture:

You need to order it by the same expression that you used to generate the field. Use that instead of the actual field name.

 

[nicksource]

If I do place:

=Nz(DSum('[Qty]','GoodsIn','[ProdID]=[IDa]'),0)-Nz(DSum('[Qty]','GoodsOutProd','[ProdID]=[IDa]'),0)

In the Order By field I get an Unknown Error?

 

[boblarson]

I think you would need to remove the = sign. But, I would just create that field in the query and then you can just sort in the query by that field.

 

[nicksource]

I think you would need to remove the = sign. But, I would just create that field in the query and then you can just sort in the query by that field.

Tried with no = but still no luck, how do you mean create the field in the query?

Thanks.

 

[ajetrumpet]

I think he means: Put an ORDER BY clause in your SQL statement that serves as the RS of the form.

That should do this trick.

Example Query:

SELECT table.field1, table.field2, table.field2 & ", " & table.field1 AS newfield
   FROM table
      ORDER BY table.field2 & ", " & table.field1;

 

[nicksource]

The field isn't in a table, it's just an expression in a textbox on a form, so I've tried this with no luck...

ORDER BY Forms![FormName]![Text12];


Thanks for your help so far.

 

[ajetrumpet]

The field isn't in a table, it's just an expression in a textbox on a form, so I've tried this with no luck...

ORDER BY Forms![FormName]![Text12];

You're out of luck then. Expressions in form controls are just meant to manipulate data associated with the current record. They are not bound to the source recordset itself.

You should not have a problem just writing this in the SQL statement. Just bind the text box to the created field.

Or, better yet, since the form is based on SQL anyway, just create a query with that statement, then source the form from the query. That will automatically bind the textbox to the field.

Bottom line is (I'm pretty sure), you can't do it with the current setup.

 

[boblarson]

Your original formula has nothing to do with a control and uses fields in the query so you CAN include it as a field in the query:

MyNewFieldName:Nz(DSum('[Qty]','GoodsIn','[ProdID]=[IDa]'),0)-Nz(DSum('[Qty]','GoodsOutProd','[ProdID]=[IDa]'),0)


That is what I meant - you can create your own fields in queries. All you need to do is go into the QBE grid and start typing that in to a blank column area. You give a field a name like
MyNewFieldNameHere

and then you add a colon after it and then type in the formula.

 

[nicksource]

I think I understand, I put the expression in the SQL (QBE view) as you have said, then added an existing field to my form, which was this expression, however I get this error:

"The expression you entered as a query parameter produced this error: 'Microsoft Office Access can't find the name 'IDa' you entered in the expression"

The IDa field is in the form, the expression works fine anywhere else, just not through the SQL so it seems.

I appreciate your help so far guys.

 

[boblarson]

Can you post your db? See here for how: http://www.access-programmers.co.uk/forums/showthread.php?t=140587

 

[ajetrumpet]

I think I understand, I put the expression in the SQL (QBE view) as you have said, then added an existing field to my form, which was this expression, however I get this error:
The IDa field is in the form,

Unbelievable...this should be very simple (you would think).

 

[nicksource]

Unbelievable...this should be very simple (you would think).

If you have something that would help me I'd appreciate it.

 

[ajetrumpet]

If you have something that would help me I'd appreciate it.

Yes, I do.

Upload the file, because I think Bob and I have exhausted all of our energies without sourcing the actual issue (if it is different from what we are discussing).