Search results for query: *

yOU COULD ALSO DO A QUERY for company 6 and multiply each column by the exchange rate. Then do a separate query rolling up the USD companies and then a union query over those 2 queries to give you an amalgamation of all the USD figures.

Thanks for the clarification Steve101

I think the issue here might be a default in Excel but I am really not sure. You could of course record a macro to convert everything in your excel sheet to the number format.

Ok somewhere you have more than one control called JobNo maybe on one of your forms? You need to call each control something different. However, I am surprised that if you only added the grouping it did not occur before. Put the query in Design mode and check JobNo is not replicated somewhere...

I have now setup a FE database on the administrator users machine which imports the tables and rexpotrs them after the upadtes have occurred this has reduced processing from 21 hours to 35 mins. Thanks for all your help! I just have 1 question regarding this now. The new FE database that I...

Its worth a crack, I will look into it, as you say my main concern is having other users using that table at the time so I would have to kick all users out before the process starts.

I believe developer version of Access allows you to build runtime exe's which you can run without the user needing access on their machines.

I haven'y seen the whole post but this formula does seem right. Nz(DLookup("[countyID]","locations","location ID]='" & Form!ReceiptEntry.[County ID] & "'"),"") 1st you are missing [ from Location ID and as I can't see the rest of the code...

Are you exporting to a new or existing sheet? What are the properties of the data on the tables and queries?

Omega36, I used your code and got an answer of -008576.64 if I translate it out it comes to around -143 mins or 2hrs 23mins. From the formula it looks like it was 2hr and 23 mins short of 24 hrs. Am I reading that correctly in that the process took 21 hours and 38mins? Cheers

Have you tried building a Macro using the outputto command to output it to an excel file? Otherwise you can output it to a .txt file and open it in Excell. It may be that the file is too large for excel to open.

On your listbox query that you have posted above there is NO grouping only sorting. List Box: SELECT joblog.JobNo, joblog.City, joblog.Status, qryunion.Name FROM joblog INNER JOIN qryunion ON joblog.JobNo = qryunion.jobno WHERE (((qryunion.Name) Like [forms]![frmCalendar]![cmbAppraiser]) AND...

Thanks foolio12 and Omega36, I have used that code, kicked it of last night at 4:44pm it is now 10:52am the next day and the process is still running!

You could add grouping to your query. As these are saved queries you can open the query that is the source of the list box then goto the menu View/Totals and in the totals row in the design grid if the query make sure they are set to group by.

Thanks Foolio I have all the combined fields indexed. I have now concatenated all those fields into a field named ID, I now have a join only between the 2 new ID fields, and performance is still very slow 30mins so far and counting. This is really doing my head in. If I add a field and...

I am guessing you haven't added the requery command. You need to add this to the OnChange event of your combo boxes so that when they change the listbox is requeried and displays the relevant results, see my previous post.

They are both saved queries kicked off within a macro. I will try adding the concatenated field and see how if that makes a difference.

No that is the unique key string. The only way around it I imagine is to concatenate all the fields into 1 field and do the join on that field. As long as I trim every field before concatenation it should work. Can you see anything wrong with that approach?

I have a few posts about this issue but I don't seem to get iit sorted I have an update query that runs in about 40 secs on the local machine but so far I have killed it after 2 hours over the network. I have tried many things including turning autoname off, adding and deleteing indexes...

Right so you are submitting this SQL from a form? Is there any validation attached to the submit button on the form? What is the message you get when it does not commit the record? What are the primary index keys on your table?