Storing a date field without a value

I am working on a application that has several date fields, some of which are
optional. I know that you insert/update date fields enclosing them with #.
How can you store a date field that has no value in it. For example, a case
closed date - if the case is still open then the close date is empty, blank,
null.... I continue to get errors. Any advice ? Thanks.

If the field is blank, why are you trying to do anything? Just fill in the
fields that need entries, and don't touch the ones that don't.

We might need a better example from you.

Post the exact code you are using and tell us what the exact error is.

Rick b


"Kevin Sheardy" Kevin wrote in message
...
I am working on a application that has several date fields, some of which
are
optional. I know that you insert/update date fields enclosing them with #.
How can you store a date field that has no value in it. For example, a case
closed date - if the case is still open then the close date is empty, blank,
null.... I continue to get errors. Any advice ? Thanks.

here is part of the sql update from the VB application. I realize that I can
create separate SQL statements based on whether or not I have a date but I
would rather not.
fstrSQL = "update worklist set wl_shortname = " &
Storefld(cboShortName.Text) & ", wl_lawsuittitle = " & _
Storefld(txtLawsuit.Text) & ", wl_date_initiated = " &
StoreDate(txtDateInit.Text) & _
", wl_date_resolved = " & StoreDate(txtDateResolved.Text) & ",
wl_littypeid = " & _
Storefld(littype) & ", wl_casenumber = " &
Storefld(txtCaseNum.Text) & _
", wl_companyid = " & Storefld(company) & ", wl_attorneyid = "
& Storefld(attorney) & _
", wl_state = " & Storefld(txtState.Text) & ", wl_venue = " &
Storefld(txtVenue.Text) & _
", wl_courtcasenumber = " & Storefld(txtCourtCaseNum.Text) &
", opcon_name = " & _
Storefld(txtOppName.Text) & ", opcon_firm = " &
Storefld(txtOppFirm.Text) & _
", opcon_address = " & Storefld(txtOppAddr.Text) & ",
opcon_city = " & _
Storefld(txtOppCity.Text) & ", opcon_state = " &
Storefld(txtOppState.Text) & ", opcon_zip = " & _
Storefld(txtOppZip.Text) & ", opcon_phone = " &
Storefld(txtOppPhone.Text) & _
", wl_description = " & Storefld(txtDesc.Text) & ", wl_outcome
= " & Storefld(txtOutcome.Text) & _
", outcon_name = " & Storefld(txtOutName.Text) & ",
outcon_firm = " & Storefld(txtOutFirm.Text) & _
", outcon_address = " & Storefld(txtOutAddr.Text) & ",
outcon_city = " & Storefld(txtOutCity.Text) & _
", outcon_state = " & Storefld(txtOutState.Text) & ",
outcon_zip= " & Storefld(txtOutZip.Text) & _
", outcon_phone = " & Storefld(txtOutPhone.Text) & ",
wl_allparties = " & Storefld(txtParties.Text) & _
", wl_natureofaction = " & Storefld(txtNature.Text) & ",
wl_lobid = " & Storefld(lob) & _
", wl_policynumber = " & Storefld(txtPolicyNum.Text) & ",
wl_insuredfirst = " & _
Storefld(txtInsuredFirst.Text) & ", wl_insuredmiddle = " &
Storefld(txtInsuredMiddle.Text) & _
", wl_insuredlast = " & Storefld(txtInsuredLast.Text) & ",
wl_pending = " & Storefld(txtPending.Text) & _
", wl_summaryofmatter = " & Storefld(txtSummary.Text) & ",
wl_insuredocc = " & _
Storefld(txtOccupation.Text) & ", wl_year = " &
Storefld(caseyear) & ", wl_reinsured = " & _
Storefld(txtReinsured.Text) & ", wl_claimsanalyst = " &
Storefld(txtClaimsAnalyst.Text) & _
" where wl_caseid = " & fintCurrentCaseId

The storedate and storefld are public functions that format the data. The
storedate formats a valid date as "#" & datefield & "#". For non-valid or
empty dates I've tried "#" & null & "#" and "#" & "#"# and "#" & " " & "#"
along with some other attempts.

"Rick B" wrote:

If the field is blank, why are you trying to do anything? Just fill in the
fields that need entries, and don't touch the ones that don't.

We might need a better example from you.

Post the exact code you are using and tell us what the exact error is.

Rick b


"Kevin Sheardy" Kevin wrote in message
...
I am working on a application that has several date fields, some of which
are
optional. I know that you insert/update date fields enclosing them with #.
How can you store a date field that has no value in it. For example, a case
closed date - if the case is still open then the close date is empty, blank,
null.... I continue to get errors. Any advice ? Thanks.

On Tue, 17 Aug 2004 09:43:04 -0700, "Kevin Sheardy" Kevin
wrote:

I am working on a application that has several date fields, some of which are
optional. I know that you insert/update date fields enclosing them with #.
How can you store a date field that has no value in it. For example, a case
closed date - if the case is still open then the close date is empty, blank,
null.... I continue to get errors. Any advice ? Thanks.

You can use NULL (the word NULL with no delimiters) in an INSERT or
UPDATE clause to store a blank (undefined, unspecified) date value.

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public

I would use some If IsNull(...) statements to build your SQL
fstrSQL = "...."
If Not IsNull(Me.txtDate) Then
fstrSQL = fstrSQL & ", wl_Date =#" & Me.txtDate.Text & "# "
End If


--
Duane Hookom
MS Access MVP


"Kevin Sheardy" wrote in message
...
here is part of the sql update from the VB application. I realize that I
can
create separate SQL statements based on whether or not I have a date but I
would rather not.
fstrSQL = "update worklist set wl_shortname = " &
Storefld(cboShortName.Text) & ", wl_lawsuittitle = " & _
Storefld(txtLawsuit.Text) & ", wl_date_initiated = " &
StoreDate(txtDateInit.Text) & _
", wl_date_resolved = " & StoreDate(txtDateResolved.Text) &
",
wl_littypeid = " & _
Storefld(littype) & ", wl_casenumber = " &
Storefld(txtCaseNum.Text) & _
", wl_companyid = " & Storefld(company) & ", wl_attorneyid =
"
& Storefld(attorney) & _
", wl_state = " & Storefld(txtState.Text) & ", wl_venue = "
&
Storefld(txtVenue.Text) & _
", wl_courtcasenumber = " & Storefld(txtCourtCaseNum.Text) &
", opcon_name = " & _
Storefld(txtOppName.Text) & ", opcon_firm = " &
Storefld(txtOppFirm.Text) & _
", opcon_address = " & Storefld(txtOppAddr.Text) & ",
opcon_city = " & _
Storefld(txtOppCity.Text) & ", opcon_state = " &
Storefld(txtOppState.Text) & ", opcon_zip = " & _
Storefld(txtOppZip.Text) & ", opcon_phone = " &
Storefld(txtOppPhone.Text) & _
", wl_description = " & Storefld(txtDesc.Text) & ",
wl_outcome
= " & Storefld(txtOutcome.Text) & _
", outcon_name = " & Storefld(txtOutName.Text) & ",
outcon_firm = " & Storefld(txtOutFirm.Text) & _
", outcon_address = " & Storefld(txtOutAddr.Text) & ",
outcon_city = " & Storefld(txtOutCity.Text) & _
", outcon_state = " & Storefld(txtOutState.Text) & ",
outcon_zip= " & Storefld(txtOutZip.Text) & _
", outcon_phone = " & Storefld(txtOutPhone.Text) & ",
wl_allparties = " & Storefld(txtParties.Text) & _
", wl_natureofaction = " & Storefld(txtNature.Text) & ",
wl_lobid = " & Storefld(lob) & _
", wl_policynumber = " & Storefld(txtPolicyNum.Text) & ",
wl_insuredfirst = " & _
Storefld(txtInsuredFirst.Text) & ", wl_insuredmiddle = " &
Storefld(txtInsuredMiddle.Text) & _
", wl_insuredlast = " & Storefld(txtInsuredLast.Text) & ",
wl_pending = " & Storefld(txtPending.Text) & _
", wl_summaryofmatter = " & Storefld(txtSummary.Text) & ",
wl_insuredocc = " & _
Storefld(txtOccupation.Text) & ", wl_year = " &
Storefld(caseyear) & ", wl_reinsured = " & _
Storefld(txtReinsured.Text) & ", wl_claimsanalyst = " &
Storefld(txtClaimsAnalyst.Text) & _
" where wl_caseid = " & fintCurrentCaseId

The storedate and storefld are public functions that format the data.
The
storedate formats a valid date as "#" & datefield & "#". For non-valid or
empty dates I've tried "#" & null & "#" and "#" & "#"# and "#" & " " & "#"
along with some other attempts.

"Rick B" wrote:

If the field is blank, why are you trying to do anything? Just fill in
the
fields that need entries, and don't touch the ones that don't.

We might need a better example from you.

Post the exact code you are using and tell us what the exact error is.

Rick b


"Kevin Sheardy" Kevin wrote in
message
...
I am working on a application that has several date fields, some of
which
are
optional. I know that you insert/update date fields enclosing them with
#.
How can you store a date field that has no value in it. For example, a
case
closed date - if the case is still open then the close date is empty,
blank,
null.... I continue to get errors. Any advice ? Thanks.

that could work on the update logic but on the insert logic the SQL stmt is
set up differently. For the insert, it's coded as

fstrsql = "insert into table (field1, field2, field3) values (value1,
value2, value3)
how can I create the logic to handle if field2 is the null field ?


"Duane Hookom" wrote:

I would use some If IsNull(...) statements to build your SQL
fstrSQL = "...."
If Not IsNull(Me.txtDate) Then
fstrSQL = fstrSQL & ", wl_Date =#" & Me.txtDate.Text & "# "
End If


--
Duane Hookom
MS Access MVP


"Kevin Sheardy" wrote in message
...
here is part of the sql update from the VB application. I realize that I
can
create separate SQL statements based on whether or not I have a date but I
would rather not.
fstrSQL = "update worklist set wl_shortname = " &
Storefld(cboShortName.Text) & ", wl_lawsuittitle = " & _
Storefld(txtLawsuit.Text) & ", wl_date_initiated = " &
StoreDate(txtDateInit.Text) & _
", wl_date_resolved = " & StoreDate(txtDateResolved.Text) &
",
wl_littypeid = " & _
Storefld(littype) & ", wl_casenumber = " &
Storefld(txtCaseNum.Text) & _
", wl_companyid = " & Storefld(company) & ", wl_attorneyid =
"
& Storefld(attorney) & _
", wl_state = " & Storefld(txtState.Text) & ", wl_venue = "
&
Storefld(txtVenue.Text) & _
", wl_courtcasenumber = " & Storefld(txtCourtCaseNum.Text) &
", opcon_name = " & _
Storefld(txtOppName.Text) & ", opcon_firm = " &
Storefld(txtOppFirm.Text) & _
", opcon_address = " & Storefld(txtOppAddr.Text) & ",
opcon_city = " & _
Storefld(txtOppCity.Text) & ", opcon_state = " &
Storefld(txtOppState.Text) & ", opcon_zip = " & _
Storefld(txtOppZip.Text) & ", opcon_phone = " &
Storefld(txtOppPhone.Text) & _
", wl_description = " & Storefld(txtDesc.Text) & ",
wl_outcome
= " & Storefld(txtOutcome.Text) & _
", outcon_name = " & Storefld(txtOutName.Text) & ",
outcon_firm = " & Storefld(txtOutFirm.Text) & _
", outcon_address = " & Storefld(txtOutAddr.Text) & ",
outcon_city = " & Storefld(txtOutCity.Text) & _
", outcon_state = " & Storefld(txtOutState.Text) & ",
outcon_zip= " & Storefld(txtOutZip.Text) & _
", outcon_phone = " & Storefld(txtOutPhone.Text) & ",
wl_allparties = " & Storefld(txtParties.Text) & _
", wl_natureofaction = " & Storefld(txtNature.Text) & ",
wl_lobid = " & Storefld(lob) & _
", wl_policynumber = " & Storefld(txtPolicyNum.Text) & ",
wl_insuredfirst = " & _
Storefld(txtInsuredFirst.Text) & ", wl_insuredmiddle = " &
Storefld(txtInsuredMiddle.Text) & _
", wl_insuredlast = " & Storefld(txtInsuredLast.Text) & ",
wl_pending = " & Storefld(txtPending.Text) & _
", wl_summaryofmatter = " & Storefld(txtSummary.Text) & ",
wl_insuredocc = " & _
Storefld(txtOccupation.Text) & ", wl_year = " &
Storefld(caseyear) & ", wl_reinsured = " & _
Storefld(txtReinsured.Text) & ", wl_claimsanalyst = " &
Storefld(txtClaimsAnalyst.Text) & _
" where wl_caseid = " & fintCurrentCaseId

The storedate and storefld are public functions that format the data.
The
storedate formats a valid date as "#" & datefield & "#". For non-valid or
empty dates I've tried "#" & null & "#" and "#" & "#"# and "#" & " " & "#"
along with some other attempts.

"Rick B" wrote:

If the field is blank, why are you trying to do anything? Just fill in
the
fields that need entries, and don't touch the ones that don't.

We might need a better example from you.

Post the exact code you are using and tell us what the exact error is.

Rick b


"Kevin Sheardy" Kevin wrote in
message
...
I am working on a application that has several date fields, some of
which
are
optional. I know that you insert/update date fields enclosing them with
#.
How can you store a date field that has no value in it. For example, a
case
closed date - if the case is still open then the close date is empty,
blank,
null.... I continue to get errors. Any advice ? Thanks.

You could either use If Not IsNull().. in two places in your code or just
update the field with Null (no "#" delimiters).

--
Duane Hookom
MS Access MVP
--

"Kevin Sheardy" wrote in message
...
that could work on the update logic but on the insert logic the SQL stmt
is
set up differently. For the insert, it's coded as

fstrsql = "insert into table (field1, field2, field3) values (value1,
value2, value3)
how can I create the logic to handle if field2 is the null field ?


"Duane Hookom" wrote:

I would use some If IsNull(...) statements to build your SQL
fstrSQL = "...."
If Not IsNull(Me.txtDate) Then
fstrSQL = fstrSQL & ", wl_Date =#" & Me.txtDate.Text & "# "
End If


--
Duane Hookom
MS Access MVP


"Kevin Sheardy" wrote in
message
...
here is part of the sql update from the VB application. I realize
that I
can
create separate SQL statements based on whether or not I have a date
but I
would rather not.
fstrSQL = "update worklist set wl_shortname = " &
Storefld(cboShortName.Text) & ", wl_lawsuittitle = " & _
Storefld(txtLawsuit.Text) & ", wl_date_initiated = " &
StoreDate(txtDateInit.Text) & _
", wl_date_resolved = " &
StoreDate(txtDateResolved.Text) &
",
wl_littypeid = " & _
Storefld(littype) & ", wl_casenumber = " &
Storefld(txtCaseNum.Text) & _
", wl_companyid = " & Storefld(company) & ",
wl_attorneyid =
"
& Storefld(attorney) & _
", wl_state = " & Storefld(txtState.Text) & ", wl_venue
= "
&
Storefld(txtVenue.Text) & _
", wl_courtcasenumber = " &
Storefld(txtCourtCaseNum.Text) &
", opcon_name = " & _
Storefld(txtOppName.Text) & ", opcon_firm = " &
Storefld(txtOppFirm.Text) & _
", opcon_address = " & Storefld(txtOppAddr.Text) & ",
opcon_city = " & _
Storefld(txtOppCity.Text) & ", opcon_state = " &
Storefld(txtOppState.Text) & ", opcon_zip = " & _
Storefld(txtOppZip.Text) & ", opcon_phone = " &
Storefld(txtOppPhone.Text) & _
", wl_description = " & Storefld(txtDesc.Text) & ",
wl_outcome
= " & Storefld(txtOutcome.Text) & _
", outcon_name = " & Storefld(txtOutName.Text) & ",
outcon_firm = " & Storefld(txtOutFirm.Text) & _
", outcon_address = " & Storefld(txtOutAddr.Text) & ",
outcon_city = " & Storefld(txtOutCity.Text) & _
", outcon_state = " & Storefld(txtOutState.Text) & ",
outcon_zip= " & Storefld(txtOutZip.Text) & _
", outcon_phone = " & Storefld(txtOutPhone.Text) & ",
wl_allparties = " & Storefld(txtParties.Text) & _
", wl_natureofaction = " & Storefld(txtNature.Text) & ",
wl_lobid = " & Storefld(lob) & _
", wl_policynumber = " & Storefld(txtPolicyNum.Text) &
",
wl_insuredfirst = " & _
Storefld(txtInsuredFirst.Text) & ", wl_insuredmiddle = "
&
Storefld(txtInsuredMiddle.Text) & _
", wl_insuredlast = " & Storefld(txtInsuredLast.Text) &
",
wl_pending = " & Storefld(txtPending.Text) & _
", wl_summaryofmatter = " & Storefld(txtSummary.Text) &
",
wl_insuredocc = " & _
Storefld(txtOccupation.Text) & ", wl_year = " &
Storefld(caseyear) & ", wl_reinsured = " & _
Storefld(txtReinsured.Text) & ", wl_claimsanalyst = " &
Storefld(txtClaimsAnalyst.Text) & _
" where wl_caseid = " & fintCurrentCaseId

The storedate and storefld are public functions that format the data.
The
storedate formats a valid date as "#" & datefield & "#". For
non-valid or
empty dates I've tried "#" & null & "#" and "#" & "#"# and "#" & " " &
"#"
along with some other attempts.

"Rick B" wrote:

If the field is blank, why are you trying to do anything? Just fill
in
the
fields that need entries, and don't touch the ones that don't.

We might need a better example from you.

Post the exact code you are using and tell us what the exact error
is.

Rick b


"Kevin Sheardy" Kevin wrote in
message
...
I am working on a application that has several date fields, some of
which
are
optional. I know that you insert/update date fields enclosing them
with
#.
How can you store a date field that has no value in it. For
example, a
case
closed date - if the case is still open then the close date is
empty,
blank,
null.... I continue to get errors. Any advice ? Thanks.

The format given in http://www.mvps.org/access/datetime/date0005.htm at "The
Access Web" is really good in this case.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"Duane Hookom" wrote in message
...
You could either use If Not IsNull().. in two places in your code or just
update the field with Null (no "#" delimiters).

--
Duane Hookom
MS Access MVP
--

"Kevin Sheardy" wrote in message
...
that could work on the update logic but on the insert logic the SQL stmt
is
set up differently. For the insert, it's coded as

fstrsql = "insert into table (field1, field2, field3) values (value1,
value2, value3)
how can I create the logic to handle if field2 is the null field ?


"Duane Hookom" wrote:

I would use some If IsNull(...) statements to build your SQL
fstrSQL = "...."
If Not IsNull(Me.txtDate) Then
fstrSQL = fstrSQL & ", wl_Date =#" & Me.txtDate.Text & "# "
End If


--
Duane Hookom
MS Access MVP


"Kevin Sheardy" wrote in
message
...
here is part of the sql update from the VB application. I realize
that I
can
create separate SQL statements based on whether or not I have a date
but I
would rather not.
fstrSQL = "update worklist set wl_shortname = " &
Storefld(cboShortName.Text) & ", wl_lawsuittitle = " & _
Storefld(txtLawsuit.Text) & ", wl_date_initiated = " &
StoreDate(txtDateInit.Text) & _
", wl_date_resolved = " &
StoreDate(txtDateResolved.Text) &
",
wl_littypeid = " & _
Storefld(littype) & ", wl_casenumber = " &
Storefld(txtCaseNum.Text) & _
", wl_companyid = " & Storefld(company) & ",
wl_attorneyid =
"
& Storefld(attorney) & _
", wl_state = " & Storefld(txtState.Text) & ",
wl_venue
= "
&
Storefld(txtVenue.Text) & _
", wl_courtcasenumber = " &
Storefld(txtCourtCaseNum.Text) &
", opcon_name = " & _
Storefld(txtOppName.Text) & ", opcon_firm = " &
Storefld(txtOppFirm.Text) & _
", opcon_address = " & Storefld(txtOppAddr.Text) & ",
opcon_city = " & _
Storefld(txtOppCity.Text) & ", opcon_state = " &
Storefld(txtOppState.Text) & ", opcon_zip = " & _
Storefld(txtOppZip.Text) & ", opcon_phone = " &
Storefld(txtOppPhone.Text) & _
", wl_description = " & Storefld(txtDesc.Text) & ",
wl_outcome
= " & Storefld(txtOutcome.Text) & _
", outcon_name = " & Storefld(txtOutName.Text) & ",
outcon_firm = " & Storefld(txtOutFirm.Text) & _
", outcon_address = " & Storefld(txtOutAddr.Text) & ",
outcon_city = " & Storefld(txtOutCity.Text) & _
", outcon_state = " & Storefld(txtOutState.Text) & ",
outcon_zip= " & Storefld(txtOutZip.Text) & _
", outcon_phone = " & Storefld(txtOutPhone.Text) & ",
wl_allparties = " & Storefld(txtParties.Text) & _
", wl_natureofaction = " & Storefld(txtNature.Text) &
",
wl_lobid = " & Storefld(lob) & _
", wl_policynumber = " & Storefld(txtPolicyNum.Text) &
",
wl_insuredfirst = " & _
Storefld(txtInsuredFirst.Text) & ", wl_insuredmiddle =
"
&
Storefld(txtInsuredMiddle.Text) & _
", wl_insuredlast = " & Storefld(txtInsuredLast.Text)
&
",
wl_pending = " & Storefld(txtPending.Text) & _
", wl_summaryofmatter = " & Storefld(txtSummary.Text)
&
",
wl_insuredocc = " & _
Storefld(txtOccupation.Text) & ", wl_year = " &
Storefld(caseyear) & ", wl_reinsured = " & _
Storefld(txtReinsured.Text) & ", wl_claimsanalyst = "
&
Storefld(txtClaimsAnalyst.Text) & _
" where wl_caseid = " & fintCurrentCaseId

The storedate and storefld are public functions that format the
data.
The
storedate formats a valid date as "#" & datefield & "#". For
non-valid or
empty dates I've tried "#" & null & "#" and "#" & "#"# and "#" & " "
&
"#"
along with some other attempts.

"Rick B" wrote:

If the field is blank, why are you trying to do anything? Just
fill
in
the
fields that need entries, and don't touch the ones that don't.

We might need a better example from you.

Post the exact code you are using and tell us what the exact error
is.

Rick b


"Kevin Sheardy" Kevin wrote in
message
...
I am working on a application that has several date fields, some
of
which
are
optional. I know that you insert/update date fields enclosing
them
with
#.
How can you store a date field that has no value in it. For
example, a
case
closed date - if the case is still open then the close date is
empty,
blank,
null.... I continue to get errors. Any advice ? Thanks.

I think that should work as well but I seem to continue to get errors. I use
a public function that is as follows:

Public Function StoreDate(fld As Variant)
If Len(Trim(fld)) = 10 Then
StoreDate = "#" & Trim(fld) & "#"
Else
' StoreDate = Null
'StoreDate = "#" & nullDate & "#"
'StoreDate = "#" & "#"
End If
End Function

this is executed after the field has been validated so if the length = 10
then it's a good date, otherwise no date exists. The field should be set to
null or blank. Is it possible to call you so we can discuss this ?

"John Vinson" wrote:

On Tue, 17 Aug 2004 09:43:04 -0700, "Kevin Sheardy" Kevin
wrote:

I am working on a application that has several date fields, some of which are
optional. I know that you insert/update date fields enclosing them with #.
How can you store a date field that has no value in it. For example, a case
closed date - if the case is still open then the close date is empty, blank,
null.... I continue to get errors. Any advice ? Thanks.

You can use NULL (the word NULL with no delimiters) in an INSERT or
UPDATE clause to store a blank (undefined, unspecified) date value.

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public

I think that the problem is in the public function that is being used. The
code is below:

Public Function StoreDate(fld As Variant)
If Len(Trim(fld)) = 10 Then
StoreDate = "#" & Trim(fld) & "#"
Else
' StoreDate = ""
' StoreDate = Null
'StoreDate = "#" & nullDate & "#"
'StoreDate = "#" & "#"
End If
End Function

This is executed after validation so by this time it's either a good date or
no date. You can see what I've tried after the else stmt but it's an error
everytime.

"Duane Hookom" wrote:

You could either use If Not IsNull().. in two places in your code or just
update the field with Null (no "#" delimiters).

--
Duane Hookom
MS Access MVP
--

"Kevin Sheardy" wrote in message
...
that could work on the update logic but on the insert logic the SQL stmt
is
set up differently. For the insert, it's coded as

fstrsql = "insert into table (field1, field2, field3) values (value1,
value2, value3)
how can I create the logic to handle if field2 is the null field ?


"Duane Hookom" wrote:

I would use some If IsNull(...) statements to build your SQL
fstrSQL = "...."
If Not IsNull(Me.txtDate) Then
fstrSQL = fstrSQL & ", wl_Date =#" & Me.txtDate.Text & "# "
End If


--
Duane Hookom
MS Access MVP


"Kevin Sheardy" wrote in
message
...
here is part of the sql update from the VB application. I realize
that I
can
create separate SQL statements based on whether or not I have a date
but I
would rather not.
fstrSQL = "update worklist set wl_shortname = " &
Storefld(cboShortName.Text) & ", wl_lawsuittitle = " & _
Storefld(txtLawsuit.Text) & ", wl_date_initiated = " &
StoreDate(txtDateInit.Text) & _
", wl_date_resolved = " &
StoreDate(txtDateResolved.Text) &
",
wl_littypeid = " & _
Storefld(littype) & ", wl_casenumber = " &
Storefld(txtCaseNum.Text) & _
", wl_companyid = " & Storefld(company) & ",
wl_attorneyid =
"
& Storefld(attorney) & _
", wl_state = " & Storefld(txtState.Text) & ", wl_venue
= "
&
Storefld(txtVenue.Text) & _
", wl_courtcasenumber = " &
Storefld(txtCourtCaseNum.Text) &
", opcon_name = " & _
Storefld(txtOppName.Text) & ", opcon_firm = " &
Storefld(txtOppFirm.Text) & _
", opcon_address = " & Storefld(txtOppAddr.Text) & ",
opcon_city = " & _
Storefld(txtOppCity.Text) & ", opcon_state = " &
Storefld(txtOppState.Text) & ", opcon_zip = " & _
Storefld(txtOppZip.Text) & ", opcon_phone = " &
Storefld(txtOppPhone.Text) & _
", wl_description = " & Storefld(txtDesc.Text) & ",
wl_outcome
= " & Storefld(txtOutcome.Text) & _
", outcon_name = " & Storefld(txtOutName.Text) & ",
outcon_firm = " & Storefld(txtOutFirm.Text) & _
", outcon_address = " & Storefld(txtOutAddr.Text) & ",
outcon_city = " & Storefld(txtOutCity.Text) & _
", outcon_state = " & Storefld(txtOutState.Text) & ",
outcon_zip= " & Storefld(txtOutZip.Text) & _
", outcon_phone = " & Storefld(txtOutPhone.Text) & ",
wl_allparties = " & Storefld(txtParties.Text) & _
", wl_natureofaction = " & Storefld(txtNature.Text) & ",
wl_lobid = " & Storefld(lob) & _
", wl_policynumber = " & Storefld(txtPolicyNum.Text) &
",
wl_insuredfirst = " & _
Storefld(txtInsuredFirst.Text) & ", wl_insuredmiddle = "
&
Storefld(txtInsuredMiddle.Text) & _
", wl_insuredlast = " & Storefld(txtInsuredLast.Text) &
",
wl_pending = " & Storefld(txtPending.Text) & _
", wl_summaryofmatter = " & Storefld(txtSummary.Text) &
",
wl_insuredocc = " & _
Storefld(txtOccupation.Text) & ", wl_year = " &
Storefld(caseyear) & ", wl_reinsured = " & _
Storefld(txtReinsured.Text) & ", wl_claimsanalyst = " &
Storefld(txtClaimsAnalyst.Text) & _
" where wl_caseid = " & fintCurrentCaseId

The storedate and storefld are public functions that format the data.
The
storedate formats a valid date as "#" & datefield & "#". For
non-valid or
empty dates I've tried "#" & null & "#" and "#" & "#"# and "#" & " " &
"#"
along with some other attempts.

"Rick B" wrote:

If the field is blank, why are you trying to do anything? Just fill
in
the
fields that need entries, and don't touch the ones that don't.

We might need a better example from you.

Post the exact code you are using and tell us what the exact error
is.

Rick b


"Kevin Sheardy" Kevin wrote in
message
...
I am working on a application that has several date fields, some of
which
are
optional. I know that you insert/update date fields enclosing them
with
#.
How can you store a date field that has no value in it. For
example, a
case
closed date - if the case is still open then the close date is
empty,
blank,
null.... I continue to get errors. Any advice ? Thanks.