Recent content by AOB

Sorry, final comment on this... Turns out Excel does provision the option for custom conditional number formatting natively. You can prefix each format with a condition enclosed in square brackets. In doing so, though, the standard layout of custom number formats (i.e...

Apologies for the delay in acknowledging these replies, there seems to be a weird firewall / extension block on the site from my work computer that wasn't there previously; I can log in and view threads but the requisite textbox controls for replying (or creating new threads) are obscured. Have...

I have a textbox control that displays the number of workdays between two dates. I am using a custom format to suffix " days" after the number for clarity : #,##0" days";[Red]#,##0" days" I'd like to modify this so that when the value is 1, it displays "1 day" rather than "1 days" Tried using...

Figured it out... The subform property Recordset Type was set to Snapshot. Changing this to Dynaset somehow made the results in the subform, match the results in the actual query. Working perfectly now. I'm not entirely sure why this is relevant here, given that the data is static. I can't...

Thanks @arnelgp , very interesting! However... I have followed the steps you suggested and modified the query thusly (bearing in mind the suggestions by @ebs17 above to remove the INNER JOIN) SELECT qC.CategoryID, qC.SubCategoryID, Val(Nz(jE.Selectable, qC.SubCategoryActive)) AS IsSelectable...

Thanks @ebs17 I do take your point on the superfluous INNER JOIN and have moved those details into the initial "combo" query thus : SELECT C.CategoryID, SC.SubCategoryID, SC.Active AS SubCategoryActive FROM tblCategories C, tblSubCategories SC However, to your two other observations, I suspect...

I'm getting really strange behaviour trying to apply a (relatively simple) query as a data source for a continuous subform. The query works when I run it directly, but when I use it as the data source for the subform, some of the values are not the same. Trying to figure it out before I try a...

It's not, my sort field - [SortOrder] in the Phases table - is a numerical field? And ordering prior to the pivot makes no difference; the pivot is still going to apply those transposed columns alphabetically as it sees them. But my previous post looks like it will do the trick - a tad messy...

Okay this seems to work... TRANSFORM First(tblHistory.StartDate) AS FirstOfStartDate SELECT tblHistory.ProjectID FROM tblHistory INNER JOIN tblPhases ON tblHistory.PhaseID = tblPhases.PhaseID GROUP BY tblHistory.ProjectID PIVOT Format(tblPhases.SortOrder,"0000") & " - " & tblPhases.Phase; Only...

I tried this : TRANSFORM First(tblHistory.StartDate) AS FirstOfStartDate SELECT tblHistory.ProjectID FROM tblHistory INNER JOIN tblPhases ON tblHistory.PhaseID = tblPhases.PhaseID GROUP BY tblHistory.ProjectID PIVOT tblPhases.SortOrder & " - " & tblPhases.Phase; But that doesn't work properly...

Thanks @Gasman but you've lost me there? How can I alias a pivot field and how would that get around the sorting issue?

I have two tables with a one-to-many relationship which I want to be able to query in order to "flatten" the data for reporting purposes. I have a table of "phases" and a table of "phase histories" which records the start and end dates for each parent project for any of the phases through which...

Thanks @arnelgp that is extremely kind! However, I just checked in with the owners and they reckon the user base are entirely on 64-bit M365 now so rather than trying to make it compatible with both versions, I've simply gone through all the variables (compile > debug > redim > repeat) and...

This would 100% be the correct and proper thing to do in this scenario, and normally that is precisely what I would do, but a) it's not my database, b) it's not even my responsibility, really (I am just helping out), c) i have no idea why these API's are used, or where, or for what purpose and...

Hi guys, Hoping somebody can save me some heartache here... I've inherited a very old database (MDB) format. It references a tonne of Windows API's (rough count of between 70-100 declarations) and obviously 64-bit Office wasn't on the horizon back then as none of them use conditional compiling...