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

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

From	BobAlston <bobalston9@yahoo.com>
Newsgroups	comp.databases.ms-access
Subject	Re: Using AllenBrowne's AppAudit routine - getting errors with nulls
Date	2011-05-05 08:09 -0500
Organization	A noiseless patient Spider
Message-ID	<ipu7hp$2h0$1@dont-email.me> (permalink)
References	(1 earlier) <ipml3n$p84$1@dont-email.me> <ipmngs$74n$1@dont-email.me> <ipmokm$5js$1@dont-email.me> <ipmoum$959$1@dont-email.me> <ipmqk8$rm1$1@dont-email.me>

Show all headers | View raw

On 5/2/2011 12:45 PM, BobAlston wrote:
> On 5/2/2011 12:17 PM, BobAlston wrote:
>> On 5/2/2011 12:11 PM, BobAlston wrote:
>>> On 5/2/2011 11:52 AM, Bob Barrows wrote:
>>>> BobAlston wrote:
>>>>>> and sSQL is
>>>>>>
>>>>>> INSERT INTO aud_client_Data_Additional 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;
>>>>>>
>>>> My first step would be to copy the above sql into the SQL View of a
>>>> query
>>>> and attempt to run it. If it runs, you've eliminated the query being
>>>> the
>>>> problem.
>>>>>> It is writing the before image of the record, previously saved in the
>>>>>> audtemp.... table into the aud.... table.
>>>>>>
>>>>>> Clearly my table DOES have various null values in it.
>>>>
>>>> The corresponing fields in the audit table are set to Required? If so,
>>>> why
>>>> would you do that when the fields in the source table are not Required?
>>>>
>>>>>>
>>>>>> It seems to me that my best approach to resolve
>>>>
>>>> ... assuming the append query is the actual problem ...
>>>>
>>>>>> would be to use DAO
>>>>>> or ADO and update the data fields using a loop of all controls and
>>>>>> check for null values before attempting to set a value.
>>>>
>>>>
>>>> Nah - replace the * with actual field names in the query and use Nz on
>>>> the
>>>> fields that are set to be Required in the audit table.
>>>> Better yet - set Required to No on the relevant fields in the audit
>>>> table -
>>>> again, this is assuming the append query is the actual culprit.
>>>>
>>>>
>>> Excellent debugging advise. thank you.
>>>
>>> Copied the insert query to a separate query and ran it interactively. NO
>>> problem.
>>>
>>> then saved it and ran it - so the insert would happen - got the error.
>>>
>>> I looked at the fields - none are set to required. But clearly some of
>>> the fields (optional content) are null.
>>>
>>> Now????
>>>
>>> Bob
>> I may have found the solution here.
>>
>> http://www.access-programmers.co.uk/forums/showthread.php?t=157357
>>
>> Can't try until tonight when I get exclusive access to the database.
>>
>> bob
> That wasn't it. all text fields allowed zero length.
> But I found two text fields with Indexed = yes. When I changed the
> Indexed=no, running with a test copy of the production table, it worked.
> Yea!
>
> Bob
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.

Bob

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