Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Delete a field from table if null using VB

I'm trying to populate a table from append queries, howver I need to delete a column/field if it is null. I've tried to mix IIf and Fields.Delete in the event but VB doesn't like this:-

IIf ([IrRelatImport].[IRAttrib],null, Fields.Delete [IrRelatImport].[IRAttrib],)

Any help greatly appreciated

Comments

  • dokken2dokken2 Member Posts: 532
    : I'm trying to populate a table from append queries, howver I need to
    : delete a column/field if it is null. I've tried to mix IIf and
    : Fields.Delete in the event but VB doesn't like this:-
    :
    : IIf ([IrRelatImport].[IRAttrib],null, Fields.Delete
    : [IrRelatImport].[IRAttrib],)
    :
    : Any help greatly appreciated
    :

    don't fully understand your question. will all records have the field as null? if you want to delete the field from the table, with ADO you could execute a sql DDL command-

    ALTER TABLE table1 DROP column NewFld

    there's probably a way to do the same with DAO or by using Jet.
  • timreadtimread Member Posts: 2
    Thanks for that, I only need that statement to run when the column in question is null. So how do I get that element in? e.g This is along the lines of what I thought would work:

    DoCmd.RunSQL "Alter Table IRRelatImport DROP COLUMN IRAttrImpID WHERE IRAttrImpID is null"

    But vb doesn't seem to like WHERE
  • dokken2dokken2 Member Posts: 532
    : Thanks for that, I only need that statement to run when the column
    : in question is null. So how do I get that element in? e.g This is
    : along the lines of what I thought would work:
    :
    : DoCmd.RunSQL "Alter Table IRRelatImport DROP COLUMN IRAttrImpID
    : WHERE IRAttrImpID is null"
    :
    : But vb doesn't seem to like WHERE
    :

    dropping the column is an all or nothing deal, don't recall you can use WHERE to test if its null. one idea, use ado/dao to loop thru all the records in the table and count how many are null in that field. if the count matches total of records, the entire column is null so then perform the docmd.runsql
Sign In or Register to comment.