Replies

(Edited by HansV to make URLs clickable – see Help 19)

have a look at these replacement functions for the Dlookup and other domain aggregate functions.

I have not used them but they may help.

http://easyweb.easynet.co.uk/~trevor/AccFA…ules.htm#doamin%5B/url%5D

also some tips to make access faster can be found at

http://www.fmsinc.com/tpapers/faster/index.html%5B/url%5D

Hans,

The source agency are using Office 2k and we are currently negotiating with them to replace their template with a new clean one.

It is interesting that you had different results with the macro security settings. I tried high medium and low and all 3 crashed the document to a greater or lesser degree. The best result was getting the document open and being able to print it.

Thanks for the help.

Stewart

Hans,

The source agency are using Office 2k and we are currently negotiating with them to replace their template with a new clean one.

It is interesting that you had different results with the macro security settings. I tried high medium and low and all 3 crashed the document to a greater or lesser degree. The best result was getting the document open and being able to print it.

Thanks for the help.

Stewart

Jefferson,

Thanks for the tip on the “Recover Text from Any File (*.*)” filter. It did make for interesting viewing. I’m not sure of the history of the template that the document is created from but we are in the process of trying to resolve this.

Thnaks for taking the time to look at my problem.

Stewart

Jefferson,

Thanks for the tip on the “Recover Text from Any File (*.*)” filter. It did make for interesting viewing. I’m not sure of the history of the template that the document is created from but we are in the process of trying to resolve this.

Thnaks for taking the time to look at my problem.

Stewart

Andrew,

It’s not my document I’m just trying to get the user going again . As to crashing Open Office, well there is always a first time I guess but I’m sure that I could live without the honor.

To be honest I don’t care about the Macros, the user can print the documents and do whatever else she then needs to do with the information but as she receives numerous documents every day I was looking to reduce the pain factor for her.

We are negotiating with the source agency to recreate their template(s) and solve our problem.

Thanks for your help.

Stewart

Andrew,

It’s not my document I’m just trying to get the user going again . As to crashing Open Office, well there is always a first time I guess but I’m sure that I could live without the honor.

To be honest I don’t care about the Macros, the user can print the documents and do whatever else she then needs to do with the information but as she receives numerous documents every day I was looking to reduce the pain factor for her.

We are negotiating with the source agency to recreate their template(s) and solve our problem.

Thanks for your help.

Stewart

here is a nice real world example

http://groups.google.com.au/groups?dq=&amp…s&start=100

I changed the url and tested it and it worked for me but failing that the main parts are as follows.

Problem
I have a list of 50,000 records of which two records need
to be removed. The olny thing I know about the two is that
their sum = 1160.09. How can I get Access to evaluate the
sum of any two of these records and determine which two
sumed together provides this result

A suggested Solution.

I suggest you can create a new query, select SQL view and paste the
following text:

SELECT [MyTable].[RecordID] AS Record1, [MyTable_1].[RecordID] AS Record2,
[MyTable].[NumberToSum] AS Value1, [MyTable_1].[NumberToSum] AS Value2,
[MyTable].[NumberToSum]+[MyTable_1].[NumberToSum] AS SumOfValues
FROM MyTable, MyTable AS MyTable_1
WHERE ((([MyTable].[NumberToSum]+[MyTable_1].[NumberToSum])=1160.09));

Then edit the text just to do the next changes:
Change “MyTable” to the actual name of your list of records, (and also
MyTable_1 to ActualName_1 )
Change “RecordID” to the name of the field you used to identify your records
(primary key)
Change “NumberToSum” to the name of the field containing the number to sum

I’m affraid because the size of your table, and probably the query is gonna
perform quite slow. I think it would be better if you try it first by
creating a little test table. Then you can decide if results satisfy your
needs.

The query will provide you a complete list of all pair of records that sum
1160.09

here is a nice real world example

http://groups.google.com.au/groups?dq=&amp…s&start=100

I changed the url and tested it and it worked for me but failing that the main parts are as follows.

Problem
I have a list of 50,000 records of which two records need
to be removed. The olny thing I know about the two is that
their sum = 1160.09. How can I get Access to evaluate the
sum of any two of these records and determine which two
sumed together provides this result

A suggested Solution.

I suggest you can create a new query, select SQL view and paste the
following text:

SELECT [MyTable].[RecordID] AS Record1, [MyTable_1].[RecordID] AS Record2,
[MyTable].[NumberToSum] AS Value1, [MyTable_1].[NumberToSum] AS Value2,
[MyTable].[NumberToSum]+[MyTable_1].[NumberToSum] AS SumOfValues
FROM MyTable, MyTable AS MyTable_1
WHERE ((([MyTable].[NumberToSum]+[MyTable_1].[NumberToSum])=1160.09));

Then edit the text just to do the next changes:
Change “MyTable” to the actual name of your list of records, (and also
MyTable_1 to ActualName_1 )
Change “RecordID” to the name of the field you used to identify your records
(primary key)
Change “NumberToSum” to the name of the field containing the number to sum

I’m affraid because the size of your table, and probably the query is gonna
perform quite slow. I think it would be better if you try it first by
creating a little test table. Then you can decide if results satisfy your
needs.

The query will provide you a complete list of all pair of records that sum
1160.09

To be honest Don,

I’m a regular VB neophyte, I come from the land of access where dealing with files isn’t really a big part of it all.

I was amazed at the simplicity of the code and honestly don’t see where it could be optimised further.

Is there any reason that you would expect a class to be faster?
I’m guessing here but is it because you don’t need to re initalise all the variables, just delcare a new instance of a filesearch object and keep throwing folders at it or would you throw file specs?

Stewart

To be honest Don,

I’m a regular VB neophyte, I come from the land of access where dealing with files isn’t really a big part of it all.

I was amazed at the simplicity of the code and honestly don’t see where it could be optimised further.

Is there any reason that you would expect a class to be faster?
I’m guessing here but is it because you don’t need to re initalise all the variables, just delcare a new instance of a filesearch object and keep throwing folders at it or would you throw file specs?

Stewart

Subject edited to correct spelling error, this will assist future lounge searches. StuartR
Hi all,

I’m using the filesearch object to build a list of files in a specified location.

I discovered today that if there there are shortcuts to file types that are included in the filespec, the shortcut targets are included in the search results despite the shortcut having lnk extension. To me this doesn’t make sense.

Does anyone know how to exclude shortcuts or identify when looking at the foundfiles collection that it was a link not a physical file?

Thanks

Stewart

    With appAccess.FileSearch
        .NewSearch
        .LookIn = Me.txtSelectLocation
        If chkSubFolders = 1 Then
            .SearchSubFolders = True
        Else
            .SearchSubFolders = False
        End If
        If Me.chkWord = 1 Then
            strFileSelected = strFileSelected & "*.DOC;*.DOT;"
        End If
        If Me.ChkExcel = 1 Then
            strFileSelected = strFileSelected & "*.XLS;*.XLA;*.XLT;*.XLL;"
        End If
        If Me.ChkAccess = 1 Then
            strFileSelected = strFileSelected & "*.MDB;*.MDE;*.MDW;*.MDA;*.MDZ;*.MDT;"
        End If
        If Not strFileSelected = "" Then
            .FileName = strFileSelected
        Else
            MsgBox "You Must Select File Types to Scan" & " - " & "No FileSpec Selected"
            Me.lblFiles.Caption = 0
            GoTo Proc_Exit
        End If
        If .Execute  0 Then
  etc etc.......

Subject edited to correct spelling error, this will assist future lounge searches. StuartR
Hi all,

I’m using the filesearch object to build a list of files in a specified location.

I discovered today that if there there are shortcuts to file types that are included in the filespec, the shortcut targets are included in the search results despite the shortcut having lnk extension. To me this doesn’t make sense.

Does anyone know how to exclude shortcuts or identify when looking at the foundfiles collection that it was a link not a physical file?

Thanks

Stewart

    With appAccess.FileSearch
        .NewSearch
        .LookIn = Me.txtSelectLocation
        If chkSubFolders = 1 Then
            .SearchSubFolders = True
        Else
            .SearchSubFolders = False
        End If
        If Me.chkWord = 1 Then
            strFileSelected = strFileSelected & "*.DOC;*.DOT;"
        End If
        If Me.ChkExcel = 1 Then
            strFileSelected = strFileSelected & "*.XLS;*.XLA;*.XLT;*.XLL;"
        End If
        If Me.ChkAccess = 1 Then
            strFileSelected = strFileSelected & "*.MDB;*.MDE;*.MDW;*.MDA;*.MDZ;*.MDT;"
        End If
        If Not strFileSelected = "" Then
            .FileName = strFileSelected
        Else
            MsgBox "You Must Select File Types to Scan" & " - " & "No FileSpec Selected"
            Me.lblFiles.Caption = 0
            GoTo Proc_Exit
        End If
        If .Execute  0 Then
  etc etc.......

Don,

That code absolutely screams. It had a bug that stoped it from returning any results but once I fixed that it took 5.71 seconds to run over my hard drive where filesearch took over 40seconds.

It still returned the lnk files but I added a second excluded filespec check and in 7.46 seconds it returns exactly what I needed.

Apart from solving my immediate problem you have provided me with a wonderful resource.

Thank you.

Stewart

Don,

That code absolutely screams. It had a bug that stoped it from returning any results but once I fixed that it took 5.71 seconds to run over my hard drive where filesearch took over 40seconds.

It still returned the lnk files but I added a second excluded filespec check and in 7.46 seconds it returns exactly what I needed.

Apart from solving my immediate problem you have provided me with a wonderful resource.

Thank you.

Stewart