Search results

  1. T

    Delete records with Null values

    Oke Paul. Thank you. The part of '' in stead of "" I should indeed also change. And the = in stead of like is also a good suggestion. Thank you again!
  2. T

    Delete records with Null values

    I got it. This work: [CODE]CurrentDb.Execute "DELETE * FROM [FoodComposition FE1] WHERE FoodEx1_hier_cd LIKE '" & Me.List53.Column(0) & "' AND (IsNull(Ingredient) = True OR Ingredient LIKE '');"[CODE/] Thanks.
  3. T

    Delete records with Null values

    Thank you Paul. You are right, it should be Ingredient in the delete statement. It is a pity however that I get the same error... Any other ideas...? Thanks.
  4. T

    Delete records with Null values

    Hi, I would like to make a listbox that on double click will delete the concerning record. . This works fine for records with both a value for [FoodEx1_hier_cd] and for [Ingredient], but it does not work for records with a Null value for [Ingredient]. THis is the code: If...
  5. T

    Not updateable subform

    Hi, I have a form with a unbound combo box (Natcd) and a subform (Missing Proportion% subform). The form and subform are linked with Natcd (master) and [Ingredient name] (child). The record source of the subform is table Foodcomposition. The subform is a continuous form. The fields on the...
  6. T

    Data type mismatch in criteria expression

    That's it! :) Thank you all very much! Without your suggestions I would never have solved this :o Great!
  7. T

    Data type mismatch in criteria expression

    There I select all the records in the Listbox ListSelectedNatcd, so that ItemsSelected uses all records in the loop. At the end of the code I do a deselect of all the records: For i = 0 To Me.ListSelectedNatcd.ListCount - 1 Me.ListSelectedNatcd.Selected(i) = False Next i I have found this...
  8. T

    Data type mismatch in criteria expression

    This is the result in the immediate window: UPDATE Mapping SET Mapping.FoodEx1_cd = 'A.01.000001' WHERE Mapping.Nat_cd = 0 The FoodEx1_cd is correct, but the Nat_cd not. So it does not recognize the varItem, I think. The varItem is however recognized in a similar code: Dim strSQL As String Dim...
  9. T

    Data type mismatch in criteria expression

    Hi pr2-eugin, Thank you for looking at the problem. I understood that Variant could cope with all data types. When I change the Where-clause as you suggested it still does not update any rows (with message that it is updating 0 rows).
  10. T

    Data type mismatch in criteria expression

    I think it does not recognize the varItem in the Where clause, because it does work fine when I test the code with: <CODE>"WHERE Mapping.Nat_cd = '9600'"</CODE> And I also tested if varItems are present. How should I refer to the varItem? Thanks again
  11. T

    Data type mismatch in criteria expression

    Thank you very much! It does work now. However, another problem occurs :-( When I run the code it says "You are about to update 0 rows", while I am sure there are rows that should be updated. Do you have any ideas what the problem can be? Thanks again, Tep
  12. T

    Data type mismatch in criteria expression

    Hi, I have a data type mismatch in criteria expression with the following code: Dim SQL As String Dim db As DAO.Database Dim varItem As Variant Set db = CurrentDb() Dim i As Integer For i = 0 To Me.ListSelectedNatcd.ListCount - 1 Me.ListSelectedNatcd.Selected(i) = True Next i For Each...
  13. T

    Overwrite records if exists

    Hi, I have a code that will put values from several unbound textboxes and two listboxes into a table 'dbo_residunorm_nieuw' for all the selected records in the listboxes 'ListStoffen2' and 'ListKAPprodukten'. The code below works fine, but now I would like to add this: If the selected records...
  14. T

    Select all records when a field is null

    Ah, okay, Now I understand. Thank you for explaining. :)
  15. T

    Select all records when a field is null

    Great, Paul, this does it indeed! I now used Nz: WHERE (((melding.status) Like Nz([Forms]![Versie keuze]![Versiebeheer subform].[Form]![Status],"*"))) I still do not understand why "*" did not work and "Bij VWA" did work correctly (both without the like-operator). But well, with Nz-solution it...
  16. T

    Select all records when a field is null

    Hi, I have a subform and when field [Status] is Null I would like to append all records from table melding. I have some trouble with the criteria... Does anybody understand why this does not work in an append query: WHERE (((melding.status)=IIf([Forms]![Versie keuze]![Versiebeheer...
  17. T

    Question Export filtered pivot table into Excel

    Hi, I would like to export a filtered pivot table (Access 2010) into Excel 2010. I do not want to export all the data in dataview, as is done with the 'Export to Excel'-button. So, for instance, for this pivot table-selection: I would like to have this result in Excel: How should I...
  18. T

    String comparision in selection

    Great! It works! Thank you very much Kiwiman.
  19. T

    String comparision in selection

    Hello, I have a string "buildingHierarchyCode" with values as: Z0001 Z0001.0001 Z0001.0002 Z0002 Z0002.0001 Z0002.0002 In a first combobox (called Niveau1) I can make a selection from values with a lenght of 5 characters in buildingHierarchyCode and I have a second combobox (Niveau2) with...
  20. T

    GotoRecord determined by several values

    The criteria in the VBA gives the same error: "Run-time error 2498. An expression you entered is the wrong data type for one of the arguments". I hope tomorrow I will have a fresh view on this matter. Anyhow, thank you very much for your support!
Back
Top Bottom