Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2004

    Unanswered: insert N/A in a blank ROW


    I have a database that has a column called assem number. What I am trying to do is if the row is blank, insert N/A. Can I do this as a Make table query in SQL? Thanks for the help


  2. #2
    Join Date
    Mar 2003
    The Bottom of The Barrel
    Provided Answers: 1
    probably better to do that at the report/form level:

    =nz([assem number], "n/a")

    Alternately, you COULD set a default value for that column at the table level, but I wouldn't recommend it.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Jun 2005
    Richmond, Virginia USA
    Provided Answers: 19
    If you want to do this as each record is saved then this should do it:

    Private Sub Form_BeforeUpdate(Cancel As Integer)
      If IsNull(Me.[assem number]) Then
         Me.[assem number].Value = "N/A"
      End If
    End Sub
    If you already have a table full of these and you want to do a "wholesale" change of them, you need to do this with an Undate Query. Using the above code will then insure that there will be no more blank fields in the future.
    Hope this helps!

    The problem with making anything that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  4. #4
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59 back up Teddy's comment

    NULLS are a powerfull concept in databases, what you seem to be requesting is a way of presenting the data, not storing the data. If your column can have a value of unknown then NULL is almost certainly the best means of storing it.

    Keep numeric and string/text columns separate. if you make a numeric column string to store "N/A" you loose the potential power of the SQL aggregate functons on those columns, you also loose sorting / order capability. You also will probably bollux sub totals in group/page/report footers.

    you could put the nz function into a query if you preferred, but I'd guess most people on this forum would put it in the presentation layer
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts