Search results

  1. M

    Nested IIF's

    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]...
  2. M

    Solved IIf query stopped working

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

    Solved IIf query stopped working

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

    Solved IIf query stopped working

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

    Solved IIf query stopped working

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

    Solved IIf query stopped working

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

    Best practice - Adding vs Editing

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

    Best practice - Adding vs Editing

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

    using Count()

    @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...
  10. M

    using Count()

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

    Multi user environment

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

    Combine duplicate records into one.

    investigate Cross-Tab queries
  13. M

    'iferror' equivalent in access

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

    'iferror' equivalent in access

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

    'iferror' equivalent in access

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

    Copying Value of One field to the other

    I think you missed the
  17. M

    Copying Value of One field to the other

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

    Pulling data from one column based on the data from another column

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

    Chart problem!

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

    The 'one record in a subform' blues

    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...
Back
Top Bottom