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. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|
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 |