A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Storing a date field without a value



 
 
Thread Tools Display Modes
  #11  
Old August 19th, 2004, 01:14 AM
John Vinson
external usenet poster
 
Posts: n/a
Default

On Wed, 18 Aug 2004 07:09:03 -0700, "Kevin Sheardy"
wrote:

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


Try instead:

Public Function StoreDate(fld as Variant) As String
If IsDate(Trim(fld)) Then
StoreDate = Format(CDate(fld, "\#mm\/dd\/yyyy\#"))
Else
StoreDate = "NULL"
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 ?


Sure... at my current consulting rates. Email me if you want to
explore this option (jvinson *at* wysardofinfo *dot* com).


John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public
  #12  
Old August 19th, 2004, 02:14 AM
Douglas J. Steele
external usenet poster
 
Posts: n/a
Default

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

However, that's not a particularly sound function, since it doesn't do any
validation that the contents of fld is a valid date in mm/dd/yyyy format.

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



"Kevin Sheardy" wrote in message
...
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.











  #13  
Old August 27th, 2004, 08:25 PM
Kevin Sheardy
external usenet poster
 
Posts: n/a
Default

I would have never thought it would have worked because it looks like your
setting the field to a character string of NULL but it works. Thanks a
million.

How did you know this ?


"John Vinson" wrote:

On Wed, 18 Aug 2004 07:09:03 -0700, "Kevin Sheardy"
wrote:

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


Try instead:

Public Function StoreDate(fld as Variant) As String
If IsDate(Trim(fld)) Then
StoreDate = Format(CDate(fld, "\#mm\/dd\/yyyy\#"))
Else
StoreDate = "NULL"
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 ?


Sure... at my current consulting rates. Email me if you want to
explore this option (jvinson *at* wysardofinfo *dot* com).


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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Repost: Calculation problem. Someone help! Victor Running & Setting Up Queries 13 August 6th, 2004 05:21 PM
Form field with default date Christina General Discussion 5 July 27th, 2004 10:04 PM
Calculated Field Problem: Picking a date based on value in another field Dkline Database Design 3 June 23rd, 2004 04:05 PM
Calendar Object Steve Setting Up & Running Reports 1 May 18th, 2004 04:44 PM
Pivot Table - Format of Date Field Michelle Worksheet Functions 1 February 3rd, 2004 08:56 PM


All times are GMT +1. The time now is 10:37 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.