Recent content by mcalex

To me your IIf looks like: IIF( -- condition IsNull([CourtRefNo]) Or Len([CourtRefNo]) = 0, -- truepart IIF( -- condition IsNull([QcatRefNo]) Or Len([QcatRefNo]) = 0, -- truepart " ", -- falsepart "QCAT - " & [QcatLocation] & " - " & [QcatRefNo]...

Well there's a name I haven't seen in years. It appears a dev before me had included a module 'modJustify' written by Stphen Lebans - back in the late 90s. It seems to have fallen over when we - finally - upgraded machines to 64bit. The module isn't actually needed anymore and removing it...

Thank you all for the o/night assistance :) Yup, that works, with that result. I have now refreshed all links to the back end. The table is there and all fields are present and accounted for. We have had machine upgrades about 18 months ago, but both FE and BE are on network drives. At...

As mentioned in OP: Compile error. in query expression (including Access grammar) Then it lists the query text. I double click the query from the object sidebar thing, it warns me about updating a large number of rows and immediately on hitting OK, I get the error dialog. Investigation shows...

Essentially, the query is only run every couple years, after a bulk external update to OrdinaryExpiry and ExtraOrdinaryExpiry. It's an inherited system. The TermExpired field was originally manually input :confused:. Thinking about it, I may be able to update the field based on the min of...

Hi all, A sanity check please? I have the following nested IIf query that populates a field depending on a couple conditions. It has worked for ages, but only gets run once a biennial. This morning I got a compile error. Has IIf syntax changed perhaps? Query: UPDATE Member SET...

As mentioned: The data relates to information in annual financial reports. There are about 200 pieces of information collected from each report, so in theory, only one table is needed to keep it at 3NF. I've now split the data into four tables, each with like data (operating revenue info...

Is the best way: create one form and then open it differently for adding new vs editing existing records create one form to insert and one form to edit, where the edit form is bound to a query, and the insert form saves data following a button's click event. some other method? I guess I need...

@jdraw. table is pretty much as described: LocalGovernment ID Name RegionID LGType Ward ID Name LocalGovernmentID Election ID WardID ElectionDate ElectionTypeID TotalTurnout LocalGovernment 1 Metropolis 1 Shire 2 Gotham City 2 City 3 Bizarria (the capital city of Bizarro world) 3 City 4...

I'm trying to do a tally of election types using count, but am getting the wrong answer. In the database, LocalGovernments have Wards and Ward have Elections. An election can be one of two ElectionTypes ('In Person' or 'Postal'). Every Ward in a LocalGovernment has the same ElectionType. I...

sounds like you need to split the database into front end & back end. Tools/Database Utilities/Database splitter (at least in Access 2003, dunno if it moved in 2007). hth mcalex

investigate Cross-Tab queries

That sounds like a logic problem in the IIf(), or maybe there's a problem with QUERY_A itself (not just the Qty1 value) Might need to do some investigating (eg using Debug.Print) with what the values actually are so you can work out how to treat them. cheers mcalex

not IS NULL; IsNull() You don't want the SQL predicate (I think that's the word), you want the vba function. For your first query, try: IIf(IsNull([QUERY_A]![QTY1]), 0, (IIf(IsNull([QUERY_B]![QTY2]), 0, ([QUERY_B]![QTY2]*[QUERY_A]![QTY1])))) hth mcalex

I haven't come across iferror in excel - maybe it's a 2007/2010 thing. In any event, they managed to get by with this omission up til 2003, by using an if() wrapped around an IsErr(). The only difference in Access is you wrap the if() around an IsError(), so: If(IsError(x*y), <do nothing...