Groups | Search | Server Info | Login | Register

Groups > comp.databases.ms-access > #1241

Re: Using AllenBrowne's AppAudit routine - getting errors with nulls

Show all headers | View raw

BobAlston wrote:
> On 5/5/2011 9:00 AM, Bob Barrows wrote:
>> BobAlston wrote:
>>> On 5/2/2011 12:45 PM, BobAlston wrote:
>>> Well, I made a mistake.  The above did NOT fix my issue.
>>>
>>> Anyone have a good solution for SQL INSERT INTO with null values,
>>> when there are 245 fields in the table so listing each would be a
>>> pain.
>>>
>> Not an issue for me - I always list all the fields. Granted, I've
>> never had a table with 245 fields before - seems to be a good time
>> to be splitting that table up in my mind. It would certainly make
>> issues like this easier to track down. I think if you split up the
>> table into 3 or 4 tables, and created a view that joins them with
>> the same name as the original table, you would be able to carry on
>> with a minimum of fuss.
>> I would try to determine which field is actually causing the  error
>> to be raised. Having 4 narrower tables (remember the old maxim of
>> databases vs spreadsheets: spreadsheets should be wide and short,
>> database table should be narrow and long) would certainly make this
>> easier but I would not let the number of fields deter me. Since
>> you're getting a message about Nulls, the issue would seem to be
>> that one or more of your fields is set to Required, and with that
>> many fields, it would be easy to miss one.. A bit of VBA code would
>> make sure you have not missed any:
>>
>> set tdf=currentdb.tabledefs("tablename")
>> for each fld in tdf.fields
>>      if fld.required then debug.print fld.name
>> next
>>
>> This is air-code but it should give you an idea of how to do it.
>> Verify that the fields output as required actually have values in
>> your insert ... or set those fields to not required if they should
>> actually be allowing nulls
>>
>>
> Thanks I have double checked none of the fields are set to required.
>
Then you should not be getting a Null violation message. Period.

And actually, now that I think about the error you're receiving:
"you tried to assign the null value to a variable that is not a variant data
type"
I realize that you aren't experiencing a Null violation. This is something
else ... I think we've been chasing a red herring (getting back to Douglas's
initial response)

This is easy to verify. Try running this query that inserts a hard-coded
value into a single field of your table (after fixing the field name):
insert tablename (col1)
values (<suitable value that should be insertable>)

If you can run this without error, then it's guaranteed we've been chasing a
red herring.

Are any calculations involved?
Can you run the SELECT portion of the query (delete the initial INSERT part)
with no errors?

SELECT TOP 1
audtmp_client_Data_Additional.* from audtemp.client_data_additional
where (audtemp_client_Data_Additional.audtype = 'EditFrom')
ORDER BY audtemp_Client_Data_Additional.audDate DESC

This statement seems strange - what is "audtemp.client_data_additional"?
And where do you assign this alias: "audtmp_client_Data_Additional"? Why
wouldn't this statement generate an error all by itself? We have absolutely
been chasing the wrong problem, I think.

Note - that ORDER BY is superfluous - it does not guarantee data will be
stored in that order. Sorting is done when selecting data from the table by
means of an ORDER BY clause.

Back to comp.databases.ms-access | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

Using AllenBrowne's AppAudit routine - getting errors with nulls BobAlston <bobalston9@yahoo.com> - 2011-05-01 18:40 -0500
  Re: Using AllenBrowne's AppAudit routine - getting errors with nulls "Douglas J Steele" <NOSPAM_djsteele@NOSPAM_gmail.com> - 2011-05-01 20:01 -0400
  Re: Using AllenBrowne's AppAudit routine - getting errors with nulls BobAlston <bobalston9@yahoo.com> - 2011-05-02 11:11 -0500
    Re: Using AllenBrowne's AppAudit routine - getting errors with nulls "Bob Barrows" <reb01501@NOyahooSPAM.com> - 2011-05-02 12:52 -0400
      Re: Using AllenBrowne's AppAudit routine - getting errors with nulls BobAlston <bobalston9@yahoo.com> - 2011-05-02 12:11 -0500
        Re: Using AllenBrowne's AppAudit routine - getting errors with nulls BobAlston <bobalston9@yahoo.com> - 2011-05-02 12:17 -0500
          Re: Using AllenBrowne's AppAudit routine - getting errors with nulls BobAlston <bobalston9@yahoo.com> - 2011-05-02 12:45 -0500
            Re: Using AllenBrowne's AppAudit routine - getting errors with nulls "Bob Barrows" <reb01501@NOyahooSPAM.com> - 2011-05-02 14:53 -0400
            Re: Using AllenBrowne's AppAudit routine - getting errors with nulls BobAlston <bobalston9@yahoo.com> - 2011-05-05 08:09 -0500
              Re: Using AllenBrowne's AppAudit routine - getting errors with nulls "Bob Barrows" <reb01501@NOyahooSPAM.com> - 2011-05-05 10:00 -0400
                Re: Using AllenBrowne's AppAudit routine - getting errors with nulls BobAlston <bobalston9@yahoo.com> - 2011-05-05 09:12 -0500
                Re: Using AllenBrowne's AppAudit routine - getting errors with nulls "Bob Barrows" <reb01501@NOyahooSPAM.com> - 2011-05-05 10:51 -0400
                Re: Using AllenBrowne's AppAudit routine - getting errors with nulls BobAlston <bobalston9@yahoo.com> - 2011-05-05 14:01 -0500
                Re: Using AllenBrowne's AppAudit routine - getting errors with nulls "Bob Barrows" <reb01501@NOyahooSPAM.com> - 2011-05-05 15:58 -0400
                Re: Using AllenBrowne's AppAudit routine - getting errors with nulls BobAlston <bobalston9@yahoo.com> - 2011-05-05 15:11 -0500
                Re: Using AllenBrowne's AppAudit routine - getting errors with nulls BobAlston <bobalston9@yahoo.com> - 2011-05-05 22:16 -0500

csiph-web