Posts by A9192Shark

Re: Finding Next 1 In A List Of 0 And 1

Rich,

Thanks for your suggestion.

I am analysisng 15 years of offshore wave data in 3 hourly intervals.

I thought of writing a time series analysis tool that processed it sequentially, however there are several things that I am analysing for and Excel has done very well so far. This one may be the one that makes me go back to a time series analysis tool and output the results to Excel.

However if I can avoid this I would like to...

I have written the udf and I am running it over 6 columns of data at the moment. It appears to be working but will still take a long time and therefore I may as well do a time series analysis tool...

Anyone else have an idea?

A.

Hello All,

I have a list of 48 000 zeros and ones (0 and 1s) (and 12 separate lists).

If there is a 1 then I will return a zero.
If there is a zero then I need to find the next 1 and return the 'distance' to that 1.

For example
1
1
1
1
0
0
0
0
1

The first zero will have to return 4, the second 3 etc.

I have tried using Match(1,[range],0) but this takes for ever to run.
I could write a udf to do this but figure that it would have to be based on a loop. Can anyone think of a clever way of doing this?

In the mean time I will implement the udf.

Thanks,

Alan.

Re: Demographics of Our Members

Location: Woking, England
Age: Just reached 37
Profession: Analyst- I analyse data and problems that no one else want to touch!

Background Oceanography with Maths, numerical modelling of the coastline (sediments, waves and tides) I am slowly getting back into this!

A.

Re: Is the selection a chart?

Thanks,

I will give this a go later.

A.

Hello,

Is there an easy way of testing to see if the selection is a single chart?

or

If a selection includes multiple objects then to pass through them and check which ones are charts?

Thanks,

Alan.

Re: maxif function in vba - additional functions

MisterBates,

The code does not deal with areas but simply checks that the two ranges have the same number of columns and rows, that is they are identically sized. If a range has multiple areas then the function will only check the first area of each range as this is teh default area returned.

My use of the functions was always on contiguous cells- ie ranges with only 1 area.

It might be worth trying to write a generic function that has a parameter passed that selects the actual function and comparison to be made. This would use a select statement based on that parameter.

Note that the code proposed by Andy Pope would be much more efficient and if I get time I will write a generic one based on his approach.

Cheers,

Alan.

Re: Multi-User Forms

If your form is simply returning information and you set up the database to have a front and back end then in theory the limit will be that of your network response and ability of the machine hosting the backend database to respond to fresh data requests.

If you have a single database file on the server and everyone is opening that file then the limit is probably about 3 or 4 before the performance will become so poor no one will want to keep doing it.

A.

Re: ADO Class Writer for Access Tables

Carl,

Attached is an Excel workbook that will build classes based on information you type in. Somewhere I have code that broke the table down into the required format. I did it as a two stage process so that I could add extra fields or exclude certain fields from the class as I wished.

Note that the code is writen for arrays but it does not work. I could not pass both the index and the value. I think I have to pass a variable that is itself a class and has the two properties index and value. I will work on that at some point!

Thanks to Andy Pope for helping with the development a long time ago. One of the key things he helped with was the Properties Property to allow dynamic selection of the property that you wish to assign a value to.

Thanks and Good Luck,

A.

Re: ADO Class Writer for Access Tables

Andy and Carl,

I have not gone through teh code in or posts in detail but thought I would drop one use in for information purposes.

I built a class builder using Excel to generate a text file based on the fields in a table that I imported to the VBE so that I could build collections of class objects to rapidly find data in a table. That is I reproduced a table as a collection. When you are doing lots of searches (50+) on lots of tables (30+) to follow a structured calculation for 30k+records it was much quicker to load all of the lookup tables into memory as collections than to keep executing find statements on a recordset.

Alan.
PS Think I posted the code to this site a while ago???

Re: Blank rows inserted after import of actual records

Qwirky,

There is something in the Excel file that is referencing a cell on the 64999th row. This could be your macro, a named range or a formula. Access is then seeing that there is 'data' on these rows.

Open the workbook in Excel and open the VBE and in the debug window type

?activesheet.usedrange.address

Does the address match the area you expected?

A.

Re: VBA code can't find Access Form

The use of the Me keyword is only valid inside a form module. If you get an error message then you are not inside the form module.

If you are trying to reference a field in a subform from a normal module then you will need something like:

Forms("MainFormName").[frmSubFrmName].Form.[Fieldname]

A.

Re: Clear table contents before importing new data

Mede,

1. Speed
Unless you are using 1000s of records the linked table will be fast enough that you will not notice the difference. Aldo, as Norrie points out, the data exchange may be quicker with ADO, but the processing will probably be far quicker using a query.

2. Functionality of Excel and Access.
If you are running a calculation in Excel and not using a specific tool such as the solver or another specialised add-in then it is likely that the calculations can be done in Access. As Norie points out though, without knowing what calculation you are doing in Excel we can not advise.

Can you post the excel sheet with a bit of explanation?

A.

Re: Clear table contents before importing new data

Is there a reason that you can not link the table in the Database Window and run queries? This would be far easier.

You state that there are always the same number of records. If this is the case why are you adding and not simply updating the data? This gets more complicated doing it programatically, if you can simply link the excel worksheet and run queries it will be much easier.

A.

Re: Clear table contents before importing new data

Mede,

If you are importing the same number of records then use an Update query and not an append query.

If you are importing a different number (more or less) of records then you will need to delete the records using a delete query and then append the records.

Note that if you are doing this often you should compact the database between the delete and append queries. Alternatively, if acceptable to your dataset, you can delete the records when the database is closed and compact the database on close. Then when you open the database force it to append the new records. I will give more info on compaction if you need it. It is often necessary and better practice) to split your database into a front and backend.

HTH,

A.

Re: worksheet copying error

I have not opened Excel to check, but try

wb2.Sheets("Template").Copy After:=wb.sheets(wb.sheets.count)

I can not remember if teh sheets collection is zero or 1 based. If it is zero based you will need to use .count-1.

HTH,

A.

Re: SQL error

OK.

Create a new query in design view and select the tables that you want with the correct join.
Right click on the grid and select Totals.
A new row appears in teh grid with a selection of functions.
Add the Batch number field and select Group By
Add Invoice number and select count.

This will return a query that counts the number of invoices per batch.

However, I have just re-read your original post. If you want to know how many unique invoice numbers there are then create a select query that returns the invoice number only and then edit the SQL by adding the word Distinct after the Select keyword. The number of records returned will be the number of invoices.

I think that with these two methods you should be able to find the invoice numbers.

Good luck,

I am off home now so will check tomorrow!

A.

Re: worksheet copying error

Try

wb2.Sheets("Template").Copy After:=wb.Sheets(Worksheets("signoffs").Range("A2"))

Note that the sheet specified in A2 must exist in WB2.

A.

Re: SQL error

In the query design window you need to change the aggregate function to a Where clause.

It is probably empty at the moment.

A.

Re: Macro not working to refresh field

Can you post the code?

I think that you are trying to select an employee using a combo box and want the employees details to be displayed in a sub form. It is likely that you need to refresh the main form and that will requery the subform.

Post your code and a picture of the form (use print screen to grab the screen image whilst the form is open).

A.

Re: Change form link & refresh Autonumber

1. What do you mean- the form is linked to output to table A?

A form is a view on a table, or are you filling in an unbound form that then places data into a table on a command from a user?

2. You can't reset an autonumber field and keep it as an autonumber. The point of the autonumber is that a unique reference is created and can not be recreated. You would have to copy the data to an empty table with the same structure and then link all of your queries etc to that new table.

A.