Search results

I'm pretty much lost with your last post. The query I used for the max values is: SELECT CommissionGroups.SurveyorID, DMax("CommissionGroupID","CommissionGroups") AS CommissionGroupIDMax FROM CommissionGroups GROUP BY CommissionGroups.SurveyorID; Its basically the same as the first query but...

Yep. Actually I haven't even added the join for the max query yet. I've been procrastinating lol.

I still get the same error using DMax(). And I can't use DMax/Max() on the fly because the value will always be incrementing when a new CommissionGroupID is created (CommissionGroupID acts in the same way as an invoice but for paying commissions)and I need the particular value at that...

That would be true for something like "Totals" but I'm using max() as more of a utility. That value of the max() function gets assigned to a field that is used for a join, so it is necessary.

As it turns out, the Jet 4 engine does not let an update query run if the source data is the product of an aggregated function e.g. max(). That workaround is to create a temp table to store the data, join the table in the update query, then drop the table after the query has run. Also fyi, Jet...

Hey, Can anyone see the problem with this update query. This query is supposed to update Orders.CommissionID to a calculated maximum value from another table/query if the value of Orders.CommissionID is still its default value of 1. UPDATE CommissionGroupIDGroupMax INNER JOIN Orders ON...

I ended up running an append query to the Invoices table to create a new InvoiceID before running the UpdateOrdersInvoiceID query. I imagine VBA is a lot more powerful and flexible so hopefully I'll be able to learn some of it enough to be able to to use it in the future. Thanks for your help.

Hey Penguino, Another problem has inevitably arose. I cannot seem to be able to create and save a new record using a macro embedded on a button control in a form I am using to display the records to be assigned to an invoice. Basically I need to create a new Invoices record to be able to...

I just tried your sugestion and it worked! Thank you.

I just tried an update using the value of DMax on a standard number field to update a number filed and Access still threw out a type conversion error. Why god, why?

Thanks for the advice. You are right, when I change the updade field to a hard coded value it works. Unfortunatly when I use the Dmax function it throws a conversion error. I tried to run it though the CInt fuction but it still threw out the same error. I think this is because I'm getting a...

Thanks for the link. Its a little complex for my needs though.

Hey, I'm having a problem running an UPDATE query using an expression as the value to update to. I have 2 tables called "Invoices" and "Orders". Both "Invoices" and "Orders" have a field called InvoiceID with the primary key and foreign key respectively. I have a control "Create invoice"...

I've also heard that lookups should be avoided. Non-the-less I would still like to understand them.

Thanks for the reply. I am actually working with the "Opportunities" table, not a form. You are correct though, the width for the first column is set to 0 but that only affects the information in the combo box. Once I make a selection from the combo box the information displayed in the field is...

Hello, can someone please shed some light on something for me? I am having a lot of trouble understanding the workings of a particular lookup field in the template "Sales pipeling" (downloaded from Microsoft Office online (business), when creating a new db in Access 2007). In the...