Replies

Unfortunately, I need the cells to contain text, rather than simply formatting it as text on the fly (for reasons I won’t go into here). Luckily, I’ve found a solution that seems to work under both regional settings.

For the date, I’ve used =CONCATENATE(ReportFYear,”-“,TEXT(ReportFPeriod,”00″),”-01T00:00:00Z”).

For the number, I’ve used =FIXED(CostImpact,2,TRUE) which formats the number to 2 (or whatever) decimal places, then formats it as text.

An alternative suggestion…

If the formatting depends only on whether the number is positive, zero or negative, you can do it directly in the cell format. This is simpler than using conditional formatting, but very much more limited in what it can do.

I’ve also used the Arial font from the character map to produce the formula “[Green]▲0;[Red]▼0;[Blue]►0”. This formats positive numbers in green with a ▲, negative numbers in red with a ▼, and zeros in blue with a ►. You could also add another section at the end (separated by a semicolon) for blanks.

(Looks like I was beaten to it by Rory!)

Thanks for everyone’s advice!

One of my colleagues found another solution before I got to try any of your suggestions. In case anyone gets a similar problem, here’s what my colleague did…

You can use MFCMapi (http://mfcmapi.codeplex.com/) to move folders. Since Sent Items is a special folder, the move function is greyed out, but you can copy and paste instead, ticking the option to move instead (a bizarre workaround, but it did the trick).

I still have no idea how the folder got moved in the first place, but that’s IT for you.

Thanks for the suggestion, John, but it didn’t resolve the problem.

The Sent Items folder is still in Deleted Items, and it still refuses to move.

Does anyone have any other ideas? If not, I’ll have to archive all the email to a PST, destroy the mailbox, and create it from scratch!

Yes, that did the trick.

And you’re right, it is easier done than said!

Many thanks!

Thanks, Hans, but that’s not really what I’m after.

I would like to force the subreports to appear on the main report, even if they have no data (i.e. the report header and footer to show, although presumably not the detail section since this would be empty). If you open a data-less report directly, you do get headers and footers, but if you open the same report as a subreport, you see nothing.

One way I could achieve this is to move the headers and footers to the main report, leaving only the detail section in the subreport. The reason I’m reluctant to do this is that it would make it harder to get the controls to align nicely whenever I make changes to the subreport.

Any other ideas, anyone? If not, I’ll use Plan B.

Regards,

Mark

I’d started looking at a solution for this a while ago, albeit for ODBC connections to SQL Server. I got as far as creating an Excel spreadsheet where you could list the connection details, and some code that would put them into the Registry.

It’s been a while since I looked at it, and I can’t even remember if it works or not. If you don’t get any other answers and you’re really desperate, I’ll try to strip all the rubbish out of it, and post it on here.

Here’s another idea: could you have another table linking the various sizes to the related value? This table could be included in the query, linked by the size, to return the required value, thereby replacing the nested IIFs. You could then use the latter part of your clause to divide by [pack] if required.

Yes, it does work in Excel 97, but only if you remove the “, True” argument at the end.

I was really hoping to modify the object directly, rather than have to make a selection, but it really does look like that’s the only solution.

Thanks you, Hans!

In case it’s useful for anyone, here’s a stripped down Access 97 database with the code.

I can’t remember where I got this code (I’m certainly not clever enough to write it myself) but it does work well.

The test subroutine opens Notepad and waits until you close it before continuing to the next line of code.

Hope that helps!

Option Compare Database
Option Explicit

Const PROCESS_QUERY_INFORMATION = &H400
Const SYNCHRONIZE = &H100000
Const STILL_ACTIVE = &H103&
Const INFINITE = &HFFFFFFFF

Private Declare Function OpenProcess Lib "kernel32" _
    (ByVal dwDesiredAccess As Long, _
    ByVal bInheritHandle As Long, _
    ByVal dwProcessId As Long) As Long
    
Private Declare Function GetExitCodeProcess Lib "kernel32" _
    (ByVal hProcess As Long, lpExitCode As Long) As Long
    
Sub TestRunApp()

    apiRunAppThenWait "NOTEPAD.exe", vbNormalFocus
    MsgBox "Application has finished running.", vbInformation, "DONE"

End Sub


Public Sub apiRunAppThenWait(strCmdLine As String, intWinMode As Integer)
    On Error GoTo Err_Handler

    Dim hInstance As Long
    Dim hProcess As Long
    Dim lngRetval As Long
    Dim lngExitCode As Long

    ' Get Instance Handle & pass to OpenProcess function:
    hInstance = Shell(strCmdLine, intWinMode)
    hProcess = OpenProcess(PROCESS_QUERY_INFORMATION Or SYNCHRONIZE, _
                True, hInstance)
    
    Do
        lngRetval = GetExitCodeProcess(hProcess, lngExitCode)
        DoEvents
    Loop Until lngExitCode  STILL_ACTIVE

Exit_Sub:
    Exit Sub
Err_Handler:
    Select Case Err.Number
        Case 53 ' File not found
            Beep
            MsgBox "Invalid command line - cannot open application.", _
                    vbExclamation, "FILE NOT FOUND"
        Case Else
            Beep
            MsgBox "Error " & Err.Number & ": " & Err.Description
    End Select
    Resume Exit_Sub
    
End Sub

Thanks for your responses everyone. Very interesting!

I think everyone learns the importance of “ease of maintenance” the hard way, by getting horribly burned when an old project rears its ugly head. And it’s the same with those supposedly “one-off” projects.

To go back to my original questions, I think I’ll continue to take the second option because it’s easier to write and read. However, I think constants are more appropriate for text that can be re-used elsewhere.

Regards,

Mark

Thanks, Hans, that’s reassuring.

One of my university tutors always advocated laziness (at least in the sense of taking the most efficient route to a good end result), so I can now happily continue to follow his advice.

Well, the problem’s been resolved for now by rebooting the server (which is running Windows Server 2003, not XP).

The server has a RAID array, so it’s unlikely that a disk error could cause a recurring problem in a particular folder. Therefore I doubt that running checkdisk would sort the problem out, except for the fact that it would force a reboot.

Thanks a lot for your help and suggestions with this very strange problem. I think we’ll just have to reboot if we get any further problems.

Regards,

Mark

Yes, I wasn’t very clear on that, was I? Sorry!

They are stored on a network drive. Everything I mentioned in my first post was tried whilst logged on to the server as an administrator.

One of my colleagues has just told me that we have had this problem before with PDFs, and that it was only resolved by rebooting the server. We’ll have to wait until no-one’s using it tonight to reboot. He suspects the file has been deleted (one of users was trying to overwrite the file with an updated version), but not completely.

The problem has only occurred in this particular folder. I can’t work out what the root cause is.

Regards,

Mark