SQL INSERT INTO Statment

[valheru]

Hi All

I have the following SQL string:

strSql = "INSERT INTO t_ClientDemographics(client_FirstHCTVisitDate, client_ClientCode, client_FirstName, " & _
"client_LastName, fk_EmploymentStatusID, client_ClientEmployee, client_JobTitle, " & _
"fk_IdentificationTypeID, client_IDPassportNumber, fk_CountryID, fk_GenderID, client_DateOfBirth, " & _
"client_Age) SELECT #" & CDate(Format(client_FirstHCTVisitDate.Value, "dd/mm/yyyy")) & "#, '" & _
UCase(strHCTClientCode) & "', '" & LCase(client_FirstName.Value) & "', '" & LCase(client_LastName.Value) & "', " & _
CLng(fk_EmploymentStatusID.Value) & ", '" & LCase(client_ClientEmployee.Value) & "', '" & _
LCase(client_JobTitle.Value) & "', " & CLng(fk_IdentificationTypeID.Value) & ", '" & _
client_IDPassportNumber.Value & "', " & CLng(fk_CountryID.Value) & ", " & CLng(fk_GenderID.Value) & _
", #" & CDate(Format(client_DateOfBirth.Value, "dd/mm/yyyy")) & "#, 35;"

When i run it, it gives me an error stating "Invalid Use Of Null"

I have a form which i use to enter data, some of the fields for example client_IDPassportNumber are not required to be completed and thus can be left out by the user. I want the user to be able to enter nothing and save the value as nothing ( null ) in the table. Required is set to no on the table.

Any help would be appreciated.

 

[highandwild]

Your SQL seems not to make any reference to a form or controls on a form so just treat it as an append query for now.

Build it up from scratch, field by field, and run it each time you add a field. You should be able to ascertain which field is causing the problem.

I notice that you hold the persons age. This will not affect the query but this could be calculated as and when you need it as it will change each year.

Do you know about the Nz function?
http://www.techonthenet.com/access/functions/advanced/nz.php

It may be what you need in this case.

 

[namliam]

You cannot insert NULL into a SQL statement like this....
Way worse, in my oppinion, is the total lack of formatting of the SQL and the WRONG date formats.

Dates HAVE TO BE MM/DD/YYYY or use dateserial or somethng to convert them.

Try this:

strSql = ""
strSql = strSql & "INSERT INTO t_ClientDemographics(
strSql = strSql & "      client_FirstHCTVisitDate"
strSql = strSql & "    , client_ClientCode"
strSql = strSql & "    , client_FirstName"
strSql = strSql & "    , client_LastName"
strSql = strSql & "    , fk_EmploymentStatusID"
strSql = strSql & "    , client_ClientEmployee"
strSql = strSql & "    , client_JobTitle"
strSql = strSql & "    , fk_IdentificationTypeID"
strSql = strSql & "    , client_IDPassportNumber"
strSql = strSql & "    , fk_CountryID"
strSql = strSql & "    , fk_GenderID"
strSql = strSql & "    , client_DateOfBirth"
strSql = strSql & "    , client_Age) "
strSql = strSql & "SELECT #" & [B][U]Format([/U][/B]CDate(Format(client_FirstHCTVisitDate.Value, "dd/mm/yyyy")),"dd/mm/YYYY")[B][U],"MM/DD/YYYY")[/U][/B] & "#"
strSql = strSql & "     , '" & UCase(strHCTClientCode) & "' "
strSql = strSql & "     , '" & LCase(client_FirstName.Value) & "'"
strSql = strSql & "     , '" & LCase(client_LastName.Value) & "'"
strSql = strSql & "     ,  " & CLng(fk_EmploymentStatusID.Value) & " "
strSql = strSql & "     , '" & LCase(client_ClientEmployee.Value) & "'"
strSql = strSql & "     , '" & LCase(client_JobTitle.Value) & "'"
strSql = strSql & "     ,  " & CLng(fk_IdentificationTypeID.Value) & " "
If isnull (client_IDPassportNumber.Value) then 
    strSql = strSql & " , NULL"
else
    strSql = strSql & " , '" & client_IDPassportNumber.Value & "'"
endif
strSql = strSql & "     ,  " & CLng(fk_CountryID.Value) & " " 
strSql = strSql & "     ,  " & CLng(fk_GenderID.Value) & " "
strSql = strSql & "     , #" & [B][U]Format([/U][/B]CDate(Format(client_DateOfBirth.Value, "dd/mm/yyyy"))[B][U], "MM/DD/YYYY")[/U][/B]  & "#"
strSql = strSql & "     ,   35;"

 

[LPurvis]

Just to address your requirement for inserting Nulls - it's entirely doable, perfectly reasonable but needs to be catered for specifically.

For example in the date field

strSql = strSql & "SELECT " & Format(Nz(client_FirstHCTVisitDate, "Null"), "\#yyyy-mm-dd\#")

And in a text field

strSql = strSql & " , " & Nz("'" + client_LastName + "'", "Null")

So that a null value parses "Null" into your SQL string (without delimiters!)
Otherwise your values are delimited as required into literal values.

Get the idea?

Cheers.