passing NUll value in SQL statement

[rocco]

Hello folks,
I have an unbound form with 3 controls:
a combobox [epyear] where only values in the list can be chosen; a checkbox
(ephospital] and a textbox [eptime].
I have the line of code below which will fire up when user hit a command
button that confirm what has been entered in the 3 controls mentioned.

Code is:
If IsNull(Epyear) = False And IsNull(ephospital) = False And
IsNull(eptime) = False Then
Application.CurrentDb.Execute ("INSERT INTO TBLANAMPSICHIATRICA
(ID,EPYEAR,EPHOSPITAL,EPTIME) VALUES('" & Me.id & "'," & Epyear & "," &
ephospital & "," & eptime & ")")
End if

Eptime should be filled with data ONLY if the checkbox has been checked, so
it is possible that it has to be left blank and the checkbox unchecked.

But in this case I’m not able to have the SQL line working.
It seems that I HAVE TO provide a number for Eptime: it doesn’t allow for
Null.

Many books say that if a column is mentioned in the INSERT INTO statement
than a value HAVE TO be provided for each column. If there’s no values than
Null should be used. But my statement will pass a Null (since the control has
been left blank).
And this happen only with fields in the table that expect number datatype.
Sounds like you cannot assign a Null value through SQL to a numeric field.

Please can someone explain this messy thing?
Thanks

Rocco

 

[John Spencer]

You might try the following
Application.CurrentDb.Execute ("INSERT INTO TBLANAMPSICHIATRICA
(ID,EPYEAR,EPHOSPITAL,EPTIME) VALUES('" & Me.id & "'," & Epyear & "," &
ephospital & "," & NZ(eptime, "NULL") & ")")

Your other option is to build an alternate SQL string without EpTime when
EPTime is Null
Application.CurrentDb.Execute ("INSERT INTO TBLANAMPSICHIATRICA
(ID,EPYEAR,EPHOSPITAL) VALUES('" & Me.id & "'," & Epyear & "," &
ephospital & ")")

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You can provide a NULL as a column value ONLY WHEN the column definition
has the Required property set to No; otherwise, a non-NULL value will
have to be used. So, check the Required property of the column EPTIME.

You can pass a NULL this way (all one line):

CurrentDb.Execute ("INSERT INTO TBLANAMPSICHIATRICA
(ID,EPYEAR,EPHOSPITAL,EPTIME) VALUES('" & Me.id & "'," & Epyear & "," &
ephospital & "," & IIf(IsNull(eptime),"NULL",eptime) & ")")

If the EPTIME has a datatype of DateTime you have to delimit the value
with the # delimiter. E.g.:

CurrentDb.Execute ("INSERT INTO TBLANAMPSICHIATRICA
(ID,EPYEAR,EPHOSPITAL,EPTIME) VALUES('" & Me.id & "'," & Epyear & "," &
ephospital & ", & IIf(IsNull(eptime),"NULL","#" & eptime "#") & )")

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRIdDCoechKqOuFEgEQKdiQCfe4cPCrmU/Z/llhrgXiRfihBAf4cAoNMy
g6/S41BK1bf7dpsma3UdxUSh
=dZEk
-----END PGP SIGNATURE-----

 

[rocco]

Thanks for your reply.
Required property is fine: it is NO.
I have found on many books on SQL (I would say on any) that something like
this works perfectly:
INSERT INTO tblPlace (city, zipcode, country) VALUES ('pittsburgh',Null,'PA')

So I was supposing that since my code referes to values of form's controls
and since eptime control as been left blank it will pass Null to my code, so
its like I have wrote
CurrentDb.Execute ("INSERT INTO TBLANAMPSICHIATRICA
(ID,EPYEAR,EPHOSPITAL,EPTIME) VALUES('" & Me.id & "'," & Epyear & "," &
ephospital & "," & Null)").
Sorry to bother you, but I'm at beginning of building an application and in
my project design this situation will show up many times on many tables. So I
really need to understand if it can or cannot be done. I mean, if it
something I misunderstood of SQL rules or Access rules. Can help?
Anyway a really appreciate your reply,
Rocco

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You can provide a NULL as a column value ONLY WHEN the column definition
has the Required property set to No; otherwise, a non-NULL value will
have to be used. So, check the Required property of the column EPTIME.

You can pass a NULL this way (all one line):

CurrentDb.Execute ("INSERT INTO TBLANAMPSICHIATRICA
(ID,EPYEAR,EPHOSPITAL,EPTIME) VALUES('" & Me.id & "'," & Epyear & "," &
ephospital & "," & IIf(IsNull(eptime),"NULL",eptime) & ")")

If the EPTIME has a datatype of DateTime you have to delimit the value
with the # delimiter. E.g.:

CurrentDb.Execute ("INSERT INTO TBLANAMPSICHIATRICA
(ID,EPYEAR,EPHOSPITAL,EPTIME) VALUES('" & Me.id & "'," & Epyear & "," &
ephospital & ", & IIf(IsNull(eptime),"NULL","#" & eptime "#") & )")

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRIdDCoechKqOuFEgEQKdiQCfe4cPCrmU/Z/llhrgXiRfihBAf4cAoNMy
g6/S41BK1bf7dpsma3UdxUSh
=dZEk
-----END PGP SIGNATURE-----

Hello folks,
I have an unbound form with 3 controls:
a combobox [epyear] where only values in the list can be chosen; a checkbox
(ephospital] and a textbox [eptime].
I have the line of code below which will fire up when user hit a command
button that confirm what has been entered in the 3 controls mentioned.

Code is:
If IsNull(Epyear) = False And IsNull(ephospital) = False And
IsNull(eptime) = False Then
Application.CurrentDb.Execute ("INSERT INTO TBLANAMPSICHIATRICA
(ID,EPYEAR,EPHOSPITAL,EPTIME) VALUES('" & Me.id & "'," & Epyear & "," &
ephospital & "," & eptime & ")")
End if

Eptime should be filled with data ONLY if the checkbox has been checked, so
it is possible that it has to be left blank and the checkbox unchecked.

But in this case I’m not able to have the SQL line working.
It seems that I HAVE TO provide a number for Eptime: it doesn’t allow for
Null.

Many books say that if a column is mentioned in the INSERT INTO statement
than a value HAVE TO be provided for each column. If there’s no values than
Null should be used. But my statement will pass a Null (since the control has
been left blank).
And this happen only with fields in the table that expect number datatype.
Sounds like you cannot assign a Null value through SQL to a numeric field.

Please can someone explain this messy thing?

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I believe that using a reference to a control that is empty will return
an empty string instead of a NULL. Therefore, we need to explicitly
indicate that the word NULL should be entered in the INSERT INTO string.
John Spencer showed another method using the Nz() function to insert
the "NULL" word when the control's value is empty/null:

Nz(eptime,"NULL")

This will insert the word NULL rather than the NULL "value" into the
statement. I know this is rather confusing, but what we want to happen
is the WORD "NULL" is placed in the SQL string rather than a NULL
"value," which can't be placed in the SQL string you are building.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRIdcgIechKqOuFEgEQKCuQCg6NCSdgcE2e2FuNccZMcYDOh1mvkAoI9M
kLsUVg6vr/WZOyVTEjFbaW8M
=ly1Q
-----END PGP SIGNATURE-----

Thanks for your reply.
Required property is fine: it is NO.
I have found on many books on SQL (I would say on any) that something like
this works perfectly:
INSERT INTO tblPlace (city, zipcode, country) VALUES ('pittsburgh',Null,'PA')

So I was supposing that since my code referes to values of form's controls
and since eptime control as been left blank it will pass Null to my code, so
its like I have wrote
CurrentDb.Execute ("INSERT INTO TBLANAMPSICHIATRICA
(ID,EPYEAR,EPHOSPITAL,EPTIME) VALUES('" & Me.id & "'," & Epyear & "," &
ephospital & "," & Null)").
Sorry to bother you, but I'm at beginning of building an application and in
my project design this situation will show up many times on many tables. So I
really need to understand if it can or cannot be done. I mean, if it
something I misunderstood of SQL rules or Access rules. Can help?
Anyway a really appreciate your reply,
Rocco


:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You can provide a NULL as a column value ONLY WHEN the column definition
has the Required property set to No; otherwise, a non-NULL value will
have to be used. So, check the Required property of the column EPTIME.

You can pass a NULL this way (all one line):

CurrentDb.Execute ("INSERT INTO TBLANAMPSICHIATRICA
(ID,EPYEAR,EPHOSPITAL,EPTIME) VALUES('" & Me.id & "'," & Epyear & "," &
ephospital & "," & IIf(IsNull(eptime),"NULL",eptime) & ")")

If the EPTIME has a datatype of DateTime you have to delimit the value
with the # delimiter. E.g.:

CurrentDb.Execute ("INSERT INTO TBLANAMPSICHIATRICA
(ID,EPYEAR,EPHOSPITAL,EPTIME) VALUES('" & Me.id & "'," & Epyear & "," &
ephospital & ", & IIf(IsNull(eptime),"NULL","#" & eptime "#") & )")

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRIdDCoechKqOuFEgEQKdiQCfe4cPCrmU/Z/llhrgXiRfihBAf4cAoNMy
g6/S41BK1bf7dpsma3UdxUSh
=dZEk
-----END PGP SIGNATURE-----

Hello folks,
I have an unbound form with 3 controls:
a combobox [epyear] where only values in the list can be chosen; a checkbox
(ephospital] and a textbox [eptime].
I have the line of code below which will fire up when user hit a command
button that confirm what has been entered in the 3 controls mentioned.

Code is:
If IsNull(Epyear) = False And IsNull(ephospital) = False And
IsNull(eptime) = False Then
Application.CurrentDb.Execute ("INSERT INTO TBLANAMPSICHIATRICA
(ID,EPYEAR,EPHOSPITAL,EPTIME) VALUES('" & Me.id & "'," & Epyear & "," &
ephospital & "," & eptime & ")")
End if

Eptime should be filled with data ONLY if the checkbox has been checked, so
it is possible that it has to be left blank and the checkbox unchecked.

But in this case I’m not able to have the SQL line working.
It seems that I HAVE TO provide a number for Eptime: it doesn’t allow for
Null.

Many books say that if a column is mentioned in the INSERT INTO statement
than a value HAVE TO be provided for each column. If there’s no values than
Null should be used. But my statement will pass a Null (since the control has
been left blank).
And this happen only with fields in the table that expect number datatype.
Sounds like you cannot assign a Null value through SQL to a numeric field.

Please can someone explain this messy thing?

 

[rocco]

Ok. I *think* to have understood.
Since you and John Spencer are surely much-much better than me into these
things, I have another little question...
Is there a way to programmatically set the default value for fields in
tables instead of going field by field while designiong the table throught he
Access interface?...which can easily drive me crazy...

Thanks you and John for your help, I really appreciate it
Rocco

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I believe that using a reference to a control that is empty will return
an empty string instead of a NULL. Therefore, we need to explicitly
indicate that the word NULL should be entered in the INSERT INTO string.
John Spencer showed another method using the Nz() function to insert
the "NULL" word when the control's value is empty/null:

Nz(eptime,"NULL")

This will insert the word NULL rather than the NULL "value" into the
statement. I know this is rather confusing, but what we want to happen
is the WORD "NULL" is placed in the SQL string rather than a NULL
"value," which can't be placed in the SQL string you are building.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRIdcgIechKqOuFEgEQKCuQCg6NCSdgcE2e2FuNccZMcYDOh1mvkAoI9M
kLsUVg6vr/WZOyVTEjFbaW8M
=ly1Q
-----END PGP SIGNATURE-----

Thanks for your reply.
Required property is fine: it is NO.
I have found on many books on SQL (I would say on any) that something like
this works perfectly:
INSERT INTO tblPlace (city, zipcode, country) VALUES ('pittsburgh',Null,'PA')

So I was supposing that since my code referes to values of form's controls
and since eptime control as been left blank it will pass Null to my code, so
its like I have wrote
CurrentDb.Execute ("INSERT INTO TBLANAMPSICHIATRICA
(ID,EPYEAR,EPHOSPITAL,EPTIME) VALUES('" & Me.id & "'," & Epyear & "," &
ephospital & "," & Null)").
Sorry to bother you, but I'm at beginning of building an application and in
my project design this situation will show up many times on many tables. So I
really need to understand if it can or cannot be done. I mean, if it
something I misunderstood of SQL rules or Access rules. Can help?
Anyway a really appreciate your reply,
Rocco


:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You can provide a NULL as a column value ONLY WHEN the column definition
has the Required property set to No; otherwise, a non-NULL value will
have to be used. So, check the Required property of the column EPTIME.

You can pass a NULL this way (all one line):

CurrentDb.Execute ("INSERT INTO TBLANAMPSICHIATRICA
(ID,EPYEAR,EPHOSPITAL,EPTIME) VALUES('" & Me.id & "'," & Epyear & "," &
ephospital & "," & IIf(IsNull(eptime),"NULL",eptime) & ")")

If the EPTIME has a datatype of DateTime you have to delimit the value
with the # delimiter. E.g.:

CurrentDb.Execute ("INSERT INTO TBLANAMPSICHIATRICA
(ID,EPYEAR,EPHOSPITAL,EPTIME) VALUES('" & Me.id & "'," & Epyear & "," &
ephospital & ", & IIf(IsNull(eptime),"NULL","#" & eptime "#") & )")

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRIdDCoechKqOuFEgEQKdiQCfe4cPCrmU/Z/llhrgXiRfihBAf4cAoNMy
g6/S41BK1bf7dpsma3UdxUSh
=dZEk
-----END PGP SIGNATURE-----

rocco wrote:

Hello folks,
I have an unbound form with 3 controls:
a combobox [epyear] where only values in the list can be chosen; a checkbox
(ephospital] and a textbox [eptime].
I have the line of code below which will fire up when user hit a command
button that confirm what has been entered in the 3 controls mentioned.

Code is:
If IsNull(Epyear) = False And IsNull(ephospital) = False And
IsNull(eptime) = False Then
Application.CurrentDb.Execute ("INSERT INTO TBLANAMPSICHIATRICA
(ID,EPYEAR,EPHOSPITAL,EPTIME) VALUES('" & Me.id & "'," & Epyear & "," &
ephospital & "," & eptime & ")")
End if

Eptime should be filled with data ONLY if the checkbox has been checked, so
it is possible that it has to be left blank and the checkbox unchecked.

But in this case I’m not able to have the SQL line working.
It seems that I HAVE TO provide a number for Eptime: it doesn’t allow for
Null.

Many books say that if a column is mentioned in the INSERT INTO statement
than a value HAVE TO be provided for each column. If there’s no values than
Null should be used. But my statement will pass a Null (since the control has
been left blank).
And this happen only with fields in the table that expect number datatype.
Sounds like you cannot assign a Null value through SQL to a numeric field.

Please can someone explain this messy thing?

 

[rocco]

sorry to bother you...regarding your last reply...
The fact is that when the error shows up the bad code is highlighted in
yellow.
If you place teh cursor over the words that refer to the EPtime control, it
says Null (!!!).
Maybe this has misleading me in pretendign that Null what was going to be
passed in the SQL statement...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I believe that using a reference to a control that is empty will return
an empty string instead of a NULL. Therefore, we need to explicitly
indicate that the word NULL should be entered in the INSERT INTO string.
John Spencer showed another method using the Nz() function to insert
the "NULL" word when the control's value is empty/null:

Nz(eptime,"NULL")

This will insert the word NULL rather than the NULL "value" into the
statement. I know this is rather confusing, but what we want to happen
is the WORD "NULL" is placed in the SQL string rather than a NULL
"value," which can't be placed in the SQL string you are building.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRIdcgIechKqOuFEgEQKCuQCg6NCSdgcE2e2FuNccZMcYDOh1mvkAoI9M
kLsUVg6vr/WZOyVTEjFbaW8M
=ly1Q
-----END PGP SIGNATURE-----

Thanks for your reply.
Required property is fine: it is NO.
I have found on many books on SQL (I would say on any) that something like
this works perfectly:
INSERT INTO tblPlace (city, zipcode, country) VALUES ('pittsburgh',Null,'PA')

So I was supposing that since my code referes to values of form's controls
and since eptime control as been left blank it will pass Null to my code, so
its like I have wrote
CurrentDb.Execute ("INSERT INTO TBLANAMPSICHIATRICA
(ID,EPYEAR,EPHOSPITAL,EPTIME) VALUES('" & Me.id & "'," & Epyear & "," &
ephospital & "," & Null)").
Sorry to bother you, but I'm at beginning of building an application and in
my project design this situation will show up many times on many tables. So I
really need to understand if it can or cannot be done. I mean, if it
something I misunderstood of SQL rules or Access rules. Can help?
Anyway a really appreciate your reply,
Rocco


:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You can provide a NULL as a column value ONLY WHEN the column definition
has the Required property set to No; otherwise, a non-NULL value will
have to be used. So, check the Required property of the column EPTIME.

You can pass a NULL this way (all one line):

CurrentDb.Execute ("INSERT INTO TBLANAMPSICHIATRICA
(ID,EPYEAR,EPHOSPITAL,EPTIME) VALUES('" & Me.id & "'," & Epyear & "," &
ephospital & "," & IIf(IsNull(eptime),"NULL",eptime) & ")")

If the EPTIME has a datatype of DateTime you have to delimit the value
with the # delimiter. E.g.:

CurrentDb.Execute ("INSERT INTO TBLANAMPSICHIATRICA
(ID,EPYEAR,EPHOSPITAL,EPTIME) VALUES('" & Me.id & "'," & Epyear & "," &
ephospital & ", & IIf(IsNull(eptime),"NULL","#" & eptime "#") & )")

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRIdDCoechKqOuFEgEQKdiQCfe4cPCrmU/Z/llhrgXiRfihBAf4cAoNMy
g6/S41BK1bf7dpsma3UdxUSh
=dZEk
-----END PGP SIGNATURE-----

rocco wrote:

Hello folks,
I have an unbound form with 3 controls:
a combobox [epyear] where only values in the list can be chosen; a checkbox
(ephospital] and a textbox [eptime].
I have the line of code below which will fire up when user hit a command
button that confirm what has been entered in the 3 controls mentioned.

Code is:
If IsNull(Epyear) = False And IsNull(ephospital) = False And
IsNull(eptime) = False Then
Application.CurrentDb.Execute ("INSERT INTO TBLANAMPSICHIATRICA
(ID,EPYEAR,EPHOSPITAL,EPTIME) VALUES('" & Me.id & "'," & Epyear & "," &
ephospital & "," & eptime & ")")
End if

Eptime should be filled with data ONLY if the checkbox has been checked, so
it is possible that it has to be left blank and the checkbox unchecked.

But in this case I’m not able to have the SQL line working.
It seems that I HAVE TO provide a number for Eptime: it doesn’t allow for
Null.

Many books say that if a column is mentioned in the INSERT INTO statement
than a value HAVE TO be provided for each column. If there’s no values than
Null should be used. But my statement will pass a Null (since the control has
been left blank).
And this happen only with fields in the table that expect number datatype.
Sounds like you cannot assign a Null value through SQL to a numeric field.

Please can someone explain this messy thing?