Search results

I realise that you've already solved your issue, but as an alternative for future use, refer to the Example 1 in this link: http://support.microsoft.com/support/kb/articles/Q153/7/48.ASP This uses the Excel median function rather than 'reinventing the wheel'. Just feed the results of your...

Thanks again, MrT. It is Access that's holding Excel open, because when Access is closed the excel.exe process disappears. Unfortunately, the solution you suggest will not solve my problem as the VBA needs to completely control the Excel processes involved (without user intervention). The...

The following should work. Dim strSQL As String, db as Database, rs as Recordset strSQL = "select [First name], [last name] from [contact details] where [client reference number] = " & Me.Client_Reference_Number Set db=CurrentDb Set rs=db.OpenRecordSet(strSQL) Me.txtName = "Appointment...

I've tried what you suggested MrT, but the excel object remains open. I've also found the following on the MS-KB, but I appear to be doing everything it suggests. http://support.microsoft.com/support/kb/articles/Q210/1/29.ASP Any other suggestions! Please!!

Thanks Doc. Repairing the database worked.

Not sure if this will solve your problem, but it's worth a mention! Try using the Requery method on your main form, this should update the data underlying the form and hence the many side should allow data entry.

Many, many apologies!! It's actually an Excel function, not an Access one. Forgot I had the Excel references in my Access setup!! It is a real shame Microsoft didn't feel the need to incorporate this function in Access as well. It's extremely useful. Once again, apologies for getting your...

I have a query which contains a calculated field that utilises the Nz function to deal with NULLs. This query works fine by itself. However, when I try to call this query in VBA using the OpenRecordSet action, I get the error message: "Undefined function 'nz' in expression" Why does this...

Following on from Chris McBride's suggestion, try using the Application.OnTime function. This will enable you to schedule the exact time you want the code to start running. It also avoids using timers and hence having any code running during the hour pause. HTH [This message has been...

I've got a database that creates an Excel object, outputs data, formats and then saves the Excel file. Excel then closes. The problem is the Excel object hasn't been fully closed as it still appears within the NT Task Manager process list. This causes the code to crash if it's then run a...

Hi, You'll need to break this down into 2 queries: Query 1 (returns the latest version number of the chosen project): SELECT [Project ID], Max([Version]) AS MaxVer FROM [Project Details] GROUP BY [Project ID] HAVING [Project ID]=GetID(); Query 2 (then performs an INNER JOIN using Query 1...

Hi. The following should help: TRANSFORM Sum(VALUE) AS [The Value] SELECT Country, Sum(VALUE) AS [Total Of VALUE] FROM {TableName} GROUP BY Country PIVOT FiscalQuarter; This will add an extra RowHeading with the Total of the Quarters. HTH

Done it!! To cut a long story short. I've replaced the original Union query with an extremely more complex Select query and Access seems to like it. Wonders will never cease.

Many thanks for your help. Unfortunately the queries do have to be this complex. It is also just the Union that's causing the problem, as it works fine with just either half of the Union. Anyway, the only way I can see to eliminate some of the complexity is to use a MakeTable query based on...

Can anyone think of a reason why the following is happening? I have a Union query which runs off 2 Select queries, this works. I then have a Select query that runs from the Union query and another Select query, this also works. This Select query is then used with other Select queries to create...

I believe this can be accomplished with just a single query. If I understood your question correctly then this should work: SELECT Tbl1.tnum, Tbl1.Date1, Tbl1.field1 AS OldValue, Tbl2.field1 AS NewValue FROM Tbl1 LEFT JOIN Tbl2 ON Tbl1.tnum = Tbl2.tnum WHERE (((Tbl1.Date1)=#{DateToLookFor}#)...

If it's a .dbf file then you would want to use the DoCmd.TransferDatabase method rather than .TransferText. If you feel that there are too many arguments!!, then the alternative would be to use the TransferDatabase Action in a Macro rather than VB. But, this could be restrictive if you wish to...

As far as I am aware, if you want to detach a table from a database to send to someone else, then you have to export using a format such as text, excel, dbf etc. Then to avoid leaving it to the user to import the table, you would then need to set up an import routine on the receiving database...

Use the DIR function. If it returns a zero length string {""} then the file doesn't exist and you can then skip the problem code.

The following should sort you out. However, I can't find the VBA to un-hide it!! Let me know if you find it. Sub Main() Dim db As Database, _ td As TableDef Set db = CurrentDb Set td = db.TableDefs("{TableName}") td.Attributes = dbHiddenObject End Sub