Search results

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...

I think you missed the

Sorry, shoulda been clearer there. My bad. Nice save, thanks linq :-) @khwaja: that depends on how the data is put into the combo, but generally, the bound column is 0 and the shown column is 1, so replace: ("LocNo") with (0) and so on hth mcalex

hi robruhr, cheers ;-) I've reattached the spreadsheet with how i see the ordergroup & prices tables see if that makes sense, fill in the rest of the data, and then try to get your query going. mcalex PS: I've added keys (the ID column) to show where one table points to another. You...

I think you need to change the table so it's only a 'single value field', and then use extra rows for clients with multiple letters. You should then be able to identify not only which clients have multiple letters, but also, tally how much of each letter is being used individually. hth mcalex

Hi Uncle G, thanks for responding. A couple of reasons, extra data from associated tables is being brought into the mix, I have a combo box to select the jobs (in the single sheet subform) rather than the navi buttons (coz i hate 'em), and I prefer to use sql queries rather than the table...