Replies

You need to turn off (unreference) your Active X 2.1

Can you post the SQL statement from the query that you are trying to use?

Firstly, I believe that there is an advantage in not recording the hyphen. We can use input masks or format properties to control how the number are displayed when they do not have the hypehn, but if they cotnain spaces and hypehns, then we are more limited in what we can do (or at least it becomes more difficult).

So..my suggestion would be to record only the phone numbers themselves, no hyphens and no spaces.

Okay, so how can you remove all the hyphens and spaces from a field in a table? Easy. Use the function that I have supplied below (and look how it is used in the example file attached).

To use the below function, place it in a new module in the database with the data you want to modify. Make sure you take a backup (ie. copy it and call it something different) of the data that you are going to be editing, just in case something goes wrong.

You need to pass three variables to the function, all strings

The first variable is the character that you wish to remove (ie. “-“, or ” “)
The second variable is the name of the table (ie. “tblPerson”)
The third variable is the name of the field that you want to search through (ie. “PhoneNumber”)

So calling the function would look something like this:

RemoveCharacter(“-“, “tblPerson”, “PhoneNumber”)

This would remove all hyphens from the PhoneNumber field in the table tblPerson.

Note that I haven’t included any error handling routines in this function, if you spell the table name or field name wrong. Just make sure you spell them right, or a funny error may occur (something like object not found in collection).

Hope that this helps

Public Function RemoveCharacter(strRemovalCharacter As String, strTableName As String, strFieldName As String)
‘===============================================================’
‘ Author: Jayden MacRae ‘
‘ Created: 27/10/2001 ‘
‘ Purpose: To remove any characters from a field in a table ‘
‘===============================================================’
Dim rstPerson As Recordset
Dim intHyphenPos As Integer
Dim strFieldValue As String

Set rstPerson = CurrentDb.OpenRecordset(strTableName, dbOpenTable)
With rstPerson
Do Until .EOF
Do
strFieldValue = .Fields(strFieldName)
intHyphenPos = InStr(1, strFieldValue, strRemovalCharacter)
If intHyphenPos > 0 Then
strFieldValue = Left(strFieldValue, intHyphenPos – 1) & _
Right(strFieldValue, Len(strFieldValue) – intHyphenPos)
End If
If strFieldValue .Fields(strFieldName) Then
.Edit
.Fields(strFieldName) = strFieldValue
.Update
End If
Loop Until intHyphenPos = 0
.MoveNext
Loop
.Close
End With

Set rstPerson = Nothing
End Function

It sounds to me that if you are mucking around with different platforms and spreadsheets (linked??) eeek, I would go for a nice ‘cross-platform’ option that could interact with your database and doesn’t need any software installe don the ‘client’ machines apart from a web browser ie. ASP.

Of course, this relies on the fact that you have an intranet (or at least the ability to set one up with IIS) and someone who could write the asps (or the time to do/learn it yourself).

This may be completely out of the question, but it is certainly what came to mind for me. I’ve done a couple of ‘sites’ / web applications now that interact with Access 2K databases just nicely.

jayden

Hi Arage

Cross-tab queries take data in a query and use the values in one column to create a column for each value. This effectively spins the table around.

As far as I know, you cannot enter values into a Cross-tab query (because of the way it calculates the ‘values’, they are aggregates of individual fields).

The way around this would be to create your table to ‘look’ like the structure you want to enter into and base your form on this table. Then at a later stage, use some make table and append queries to turn the column names in your table into values in a new table (or if you didn’t want to do this you could just leave the table how it was).

Hope that this helps

I think that you need to approach this problem from a slightly different angle. Instead of using criteria in your SQL (which probably won’t work the way you have it set up), use a Join between the two tables.

I would probably make the code to look something like this:

Dim strSQL as String

strSQL = “DELETE IssuedTo.* ” & _
“FROM IssuedTo INNER JOIN ImportTemptbl ” & _
“ON IssuedTo.[Voucher#] = ImportTemptbl.[Voucher#];”

DoCmd.RunSQL strSQL

What this code does is it creates the SQL in the variable strSQL that joins the two tables by voucher# and where any fields match in the two tables, it deletes the record from the IssuedTo table.

I haven’t tested this, but give it a try and see what happens (make a back-up of your tables first though just in case).

Incidentally the main problem that you were having in the code above was that you had the RST variable inclosed with the quotes of the SQL string you were trying to run.

If you changed the line to:

DoCmd.RunSQL “Delete * from IssuedTo where [voucher#] = ” & RST

It would use the variable, but you will still find that it probably generates an error, because the variable doesn’t contain a value, but rather contains a reference to a field in a table (but for which record?). This is why I would do it the way that I have presented it above.

One other question? Why do it in code? You could just create a DELETE query and just run that at the appropriate time?

Cheers

Jayden

Your main problem to me sounds like you need to modify your ‘join’ properties in your query.

At present, it sounds as though your join is an ‘INNER JOIN’ (solid line without any arrows between each table.

An INNER JOIN will only show records where identical values exist in both tables. This is clearly not the case between the customer table and your hours and mileage and weight tables (because sometimes, customer orders don’t have records in one table).

You need to make the join between the Cusomter/Order table and the Hours table so that the query will return ‘All records in the Customers/Order table and those that match in the Hours table’.

You need to make the joing between the Customer/Order table and the Mileagage/Weight table so that the query will return ‘All records in the Customers/Order table and those that match in the Mileage/Weight table’.

To do this, in your query, right click on the each join, select ‘Join Properties’ and then select the appropriate options.

When you have done this, your query should have arrowed joins, moving out from the Customer/Order table, pointing towards the Hours and the Mileage/Weight table.

I would also note that I would have give Hours it’s own table, Mileage it’s own table and Weight it’s own table (not agregated two together). I would have given Customers their own table and Orders a seperate table.

Access comes with an example database called Northwind. This is an inventory and order database. Have a look at it and it might give you some ideas. You may need to learn more about database normalisation too.

You could use

expression:=Round(Avg([Turnarounddays],NumberOfDecimalsToRoundTo)

Use this and replace NumberOfDecimalsToRoundTo with a number (default =0).

I vauguely remember reading something on MSDN that demonstrated a very deliberate method of ‘corrupting’ an Access database file. The details are very fuzzy, and I’ve tried looking through MSDN again, but can’t find the article (as I never can) (incidentally why is it that you only ever run into useful articles when you are looking for something entirely unrelated?).

Anyway, this might jog someone elses memory who has seen the ‘method’ for corrupting Access on MSDN ?

I have a sneaking suspicion that it was in amongst some ‘record locking’ stuff.

Anyway, my 2 cents worth

J

At the risk of sounding cheeky?

How about putting the button to undo the subform on the subform? (I know you said you couldn’t because of design changes, but hey… , sometimes it’s just easier???)

Jayden

Hi

Firstly I think the (main)problem lies in the fact that you have the button that you want to click on the main form. This is a problem, because as soon as the subform loses the focus (which it does for you to be able to click on the button) it will perform a ‘save’ of the record, promptly followed by your undo command. It will be undoing any ‘unsaved’ changes, but there are none (as you have just saved the changes to the record by clicking out of the sub form).

Secondly, I would tend to use the undo method of the form (or subform) instead of using the docmd object.

For example to undo changes in a form by clicking a button (not a subform) you would use

Private Sub cmdUndoChanges_Click()
me.undo
End Sub

If you wanted to reference a sub form from your main form you would use

Private Sub cmdUndoChanges_Click()
me.subformname.form.undo
End Sub

Of course, this second one won’t work as you want it because of the ‘save’ that happens when you lose the focus from the sub form.

Hopefully someone else knows a ‘work-around’. At least hopefully I have helped you further clarify the problem.

Cheers

Jayden

I’ve just realised as an alternative, you can do it without the module level variable.

In this example, I’ve used the same technique, but instead, just checked to see if the current colour of the detail section is white. If it is white, then the colour is set to grey. If it isn’t white, then it is set to white.

Option Compare Database
Option Explicit

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
‘===========================================================’
‘ Author: Jayden MacRae ‘
‘ Created: 26/10/2001 ‘
‘ Purpose: set the background colour appropriately or not. ‘ ‘
‘===========================================================’

‘Determine if the current line number is a odd or even
‘ number
If Me.Detail.BackColor = 16777215 Then
Me.Detail.BackColor = 12632256
Else
Me.Detail.BackColor = 16777215
End If
End Sub

Hi

Yes this can be done!

You can use a little bit of VBA to do this.

I have included a sample access 2K file, with a table and report that shades every second line.

You use two events of the Report:
-the On Open event of the report
-the On Format of the detail section of the report

In the On Open event of the report, set a module level variable to 0

The detail section of a report is ‘formatted’ once for each line of the report. This means that for every line of the report, we can make a bit of VBA run by using the ‘On Format’ event of the detail section of the report.

In the On Format event of the detail section, you need to:
-increment the Line Number variable by 1
-determine if the Line Number is odd or event

To determine if the line number is odd or even the mod operator is used. The mod operator returns any remainder of the difference between the division of two numbers. For example, 3 mod 2 = 1 (because 3 / 2 = 1 with 1 remaining). If we divide by 2 and there are no remainders (ie. line number divide by 2 has no remainders) then the line is even, else it must be odd.

We then use a if..then..else..end if statement to change the background colour to grey if the number is odd, or white if the line number is even.

There you have it, a report with alternate grey and white lines!

Hope this helps. Unzip the file and run the Acc 2K database, run the report and you will see the result.

Here is the VBA from the Report Module

Option Compare Database
Option Explicit
‘Declare the line number variable and make it available to all
‘ withing the report
Private lngLineNumber As Long

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
‘===========================================================’
‘ Author: Jayden MacRae ‘
‘ Created: 26/10/2001 ‘
‘ Purpose: To increment the line number by one each time ‘
‘ and then to determine if the line number is ‘
‘ odd or even and set the background colour ‘
‘ appropriately or not. ‘
‘===========================================================’

‘Increment line number by one
lngLineNumber = lngLineNumber + 1

‘Determine if the current line number is a odd or even
‘ number
If lngLineNumber Mod 2 > 0 Then
Me.Detail.BackColor = 12632256
Else
Me.Detail.BackColor = 16777215
End If
End Sub

Private Sub Report_Open(Cancel As Integer)
‘Initially Set the Line Number Variable
lngLineNumber = 0
End Sub

Cheers

Jayden

We are running a SQL Server 7, on a Win 2K Server, I have written a query using the query analyser and used ‘save as’ to save the sql to a directory. It seemed to save fine. I am using the query analyser from a dektop running win 2K pro?

Can you be more specific? It doesn’t seem to have affected me.

Cheers

Jayden

Hi

I’ve attached a zip file, with two databases (identible, except that one is a access97 version).

The contain a mock up of how I would go about this:

There are two report, one which shows ‘correct’ results for all graphs. Another which shows results from a table where the results are over 100% for one day, so the graph is not shown.

Personally, I would concentrate on the reason why your graphs are not adding up to 100% – this may be an indication that there are queries that are not correct or dodgey data somewhere.

Hope these files illustrate it better

Cheers

Jayden