Excel SQL Error - Invalid use of Null INSERT INTO

[kwhite100]

Hello team,

I have a co-worker out on vacation and I was asked to step in and find the issue with this statement below. We have an excel add-in that has a excel module with the following code:

Function ImportProgressSql() As String
    ImportProgressSql = _
        "INSERT INTO DrawingProgress(DrawingId, ProgressId, Progress, ProgressDate, EntryDate) " & vbCrLf & _
        "SELECT DrawingId, ProgressTypeId, Progress, DataDate, Now() AS EntryDate " & vbCrLf & _
        "  FROM ((tmpJuniorProgress" & Environ("USERNAME") & " jp " & vbCrLf & _
        "    LEFT JOIN Drawings d " & vbCrLf & _
        "      ON jp.ContractorDrawingId = d.ContractorDrawingId) " & vbCrLf & _
        "    LEFT JOIN DrawingProgressType pt " & vbCrLf & _
        "      ON jp.ProgressTypeName = pt.ProgressTypeName)  " & vbCrLf & _
        "  WHERE CStr(DrawingId) + CStr(ProgressTypeId) + CStr(Progress) + CStr(DataDate) " & vbCrLf & _
        "    NOT IN (SELECT CStr(DrawingId) + CStr(ProgressTypeId) + CStr(Progress) + CStr(DataDate) FROM DrawingProgress)"

My first instinct is to look at syntac..syntax looks alright. Second would be to make sure the columns exists in the tables..I will list the current columns out for the tables in questions:

Table 1 - DrawingProgress(ProgressDrawingID, DrawingID, ProgressID, Progress, ProgressDate, EntryDate)
Table 2 - tmpJuniorProgress(ContractorDrawingID, ProgressTypeName, Weight, DataDate, Progress)
Table 3 - DrawingProgressType(ProgressType, ProgressTypeName)

I think there may be an invalid value type in the DrawingProgressType Table. Does anyone see anything that sticks out? Thank you again for assisting. Just trying to step in an assist someone who is out.

 

[Joe4]

Cross-posted: http://www.mrexcel.com/forum/microsoft-access/873249-sql-error-invalid-use-null-insert-into.html

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule #10 here: Forum Rules).

This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

For a more complete explanation on cross-posting, see here: Excelguru Help Site - A message to forum cross posters).

 

[mole999]

why did it stop working.presuming it had worked, whats new

I know virtually nothing about INSERT

but this strikes me

INSERT INTO DrawingProgress(DrawingId, ProgressId, Progress, ProgressDate, EntryDate)

yet there are

Table 1 - DrawingProgress(ProgressDrawingID, DrawingID, ProgressID, Progress, ProgressDate, EntryDate)

Tables I use have to be the same number of columns (I'm not au fai with INSERTS)

 

[kwhite100]

why did it stop working.presuming it had worked, whats new

I know virtually nothing about INSERT

but this strikes me

INSERT INTO DrawingProgress(DrawingId, ProgressId, Progress, ProgressDate, EntryDate)

yet there are

Table 1 - DrawingProgress(ProgressDrawingID, DrawingID, ProgressID, Progress, ProgressDate, EntryDate)

Tables I use have to be the same number of columns (I'm not au fai with INSERTS)

ProgressDrawingID is the unique identifier. Not necessary above.

When I run the sql syntax for everything but the insert the query runs fine. When I put in the insert it states a null error.

 

[Micron]

There is nothing wrong with the listed fields UNLESS ProgressDrawingID cannot be null. The error usually means that one of the query values is null but this violates the requirements of that/those fields in the target table (i.e. the field must contain a value). It could be any one of your 5 values that you are trying to insert into 5 fields. Kwhite100 is on the right track in that these counts have to match, but nothing says you must insert into every field, subject to my first statement.

When I run the sql syntax for everything but the insert the query runs fine.
Are you 100% sure about that? I don't know if you're returning one row or several with this sql, but are you sure every row has a value in every field? I suspect if you look again, you will find there are one or more values missing in one or more fields in one or more of the records. Check those out against the requirements of the table fields and see if that's the problem. If it is, you will either have to alter the target table to allow null, the source table(s) design to prevent the nulls, or supply a default value for null with the NZ function.

If not, one of the things that may be causing a problem is unexpected "end of line" errors caused by nested double quotes. You might not detect these by pasting the code into a new test query, but they often cause failures in sql constructed by code because they break up a line in unexpected ways. It usually throws an error in Access.

 "  FROM ((tmpJuniorProgress" & Environ("USERNAME") & " jp " & vbCrLf & _

should be

 "  FROM ((tmpJuniorProgress" & Environ('USERNAME') & " jp " & vbCrLf & _

Not sure about the spaces on either side of jp.

BTW, I'll suggest another way of writing sql in code that might make it easier to read:

Dim svSql as string
svSql = "INSERT INTO DrawingProgress(DrawingId, ProgressId, Progress, ProgressDate, EntryDate) " 
svSql = svSql & "SELECT DrawingId, ProgressTypeId, Progress, DataDate, Now() AS EntryDate "
svSql = svSql & "FROM ((tmpJuniorProgress" & Environ('USERNAME') & " jp " 
svSql = svSql & "LEFT JOIN Drawings d " & vbCrLf & _ ....etc, etc.

ImportProgressSql = svSql

If using this method, you adopt the habit of always putting any end of line spaces at the end OR the beginning of a line.