Results 1 to 11 of 11
  1. #1
    Join Date
    Nov 2003
    Posts
    104

    Unanswered: Date inserted as 12:00:08 A.M

    Hi All,
    I looked through this forum and could not, for the life of me, find anything that could help me solve this problem. I appreciate any help/feedback i could get.

    Here is the situation. I have a text box (yes, ou read it right, a TEXT BOX) on a form. The text box is used for the user to enter in a date (like for example: 3/17/2004).

    The problem is this. Whenever I try to add a r ecord into a table (into a DATE feild), I use

    DateValue(sometextbox.text)

    and in the debugger it shows a correct date. But when its added to the database it shows as

    12:00:08 A.m or 12/31/1899
    ???

    Can anyone help/

    Thanx

  2. #2
    Join Date
    Jan 2003
    Location
    Rockville, MD
    Posts
    179
    Hi shassouneh,

    The example date you gave

    3/17/2004

    is evaluated as

    3 divided by 17 divided by 2004

    which equals a really tiny number that, when translated to a date, equates to something like 12:00:08 A.m.

    Try using the CVDate() function, rather than the DateValue() function to change the text to a date.

    Let me know how it works out.
    JT

  3. #3
    Join Date
    Nov 2003
    Posts
    104

    Still No Good!

    I tried what you suggested with the CVDate. it STILL does the same thing. The screen shots below help show how it gets added. Note that Screenshot 1 shows 1899 and screenshot two shows 12 a.m (both are the same record). When you put the mouse cursor over it it switches form 1899 to 12 a.m

    Screenshot 1 A year of 1899????
    http://shassouneh.homestead.com/file...ogood-1899.jpg

    Screenshot 2 A time of 12 a.m????
    http://shassouneh.homestead.com/file...ogood-12am.jpg

    NOTE: Here is the Sub that is responsible for adding stuff to the table. it works perfectly apart from the date problems.

    Code:
    Private Sub cmdAdd_Click()
    Dim ID As Integer
    Dim Creator As String
    Dim Name As String
    Dim Desc As String
    Dim Dept As Integer
    Dim Templ As Integer
    Dim VAX As String
    Dim DateCreated As Date
    Dim DateSuspended As Date
    Dim Active As Integer
    
    Dim SQL As String
    
    If (ProblemsFound = False) Then 'if and only if no problems found
    
        lblStatus.Caption = "Watch Here For Status Messages -->" & vbCrLf & "Attempting to add or associate. Please wait..."
        
        cmbobxOpID.SetFocus
        ID = CInt(cmbobxOpID.text)
        
        txtbxName.SetFocus
        Name = txtbxName.text
        
        txtbxDescription.SetFocus
        Desc = txtbxDescription.text
        
        cmbobxDept.SetFocus
        Dept = cmbobxDept.Value
        
        cmbobxTempl.SetFocus
        Templ = cmbobxTempl.Value
        
        txtbxVAX.SetFocus
        VAX = txtbxVAX.text
        
        txtbxCreator.SetFocus
        Creator = txtbxCreator.text
        
        txtbxDateCreated.SetFocus
        DateCreated = CVDate(txtbxDateCreated.text)
            
        txtbxDateSuspended.SetFocus
        
       
        
        chkbxActive.SetFocus
        Active = chkbxActive.Value
         
        txtbxDateSuspended.SetFocus
        
        If (IsNull(txtbxDateSuspended) Or txtbxDateSuspended.text = "") Then 'If no date suspended has been entered...
            
            'attempt to add into DB without a date suspended
          
            SQL = "INSERT into OPERATOR([Operator ID],[Creator],[Name],[Description],[Department ID],[Template ID], [VAX ID],[Date Created], [Active]) values(" & ID & "," & "'" & Creator & "'" & "," & "'" & Name & "'" & "," & "'" & Desc & "'" & "," & Dept & "," & Templ & "," & "'" & VAX & "'" & "," & DateCreated & "," & Active & ");"
            
            
            
        Else
            
            DateSuspended = CVDate(txtbxDateSuspended.text)
            SQL = "INSERT into OPERATOR([Operator ID],[Creator],[Name],[Description],[Department ID],[Template ID], [VAX ID],[Date Created],[Date Suspended],[Active]) values(" & ID & "," & "'" & Creator & "'" & "," & "'" & Name & "'" & "," & "'" & Desc & "'" & "," & Dept & "," & Templ & "," & "'" & VAX & "'" & "," & DateCreated & "," & DateSuspended & "," & Active & ");"
            
        
            
        End If
      
        
        MsgBox SQL
        Call RunSQL_force_withdescription(SQL, "Are you Sure You want to make changes to the database?", "The Operation You Requested will make some changes to the database. this may or may not have undo functionality. Do you still wish to make the changes?")
        lblStatus.Caption = "Watch Here For Status Messages -->" & vbCrLf & "Add/Associate attempt done!"
        
    Else
        
        MsgBox "Note: Some Problems Have been Found. The Operator has NOT been added or associated. Check the status area for details"
        
    End If
    
    cmdAdd.Enabled = False 'revert to a default of disabled until the user selects or adds an Op ID
    
    
    End Sub

    NOTE: The ProblemsFound is a function that basically checks to make sure all items in the form have been filled out. If they have not it returns a false. Otherwise, it returns a true
    Last edited by shassouneh; 03-18-04 at 17:53.

  4. #4
    Join Date
    Jan 2003
    Location
    Rockville, MD
    Posts
    179
    Can you post the sql (if you're appending the record with a query), or the code you're using?

  5. #5
    Join Date
    Feb 2004
    Location
    Wales
    Posts
    343
    Hi just a thought - maybe youve checked already but here goes.....

    Is the text box set in the input mask to a date ( this will stop --/--/--) style entries

    is the receiving table set as a shortdate?

    if both of these are yes i suggest deleting the box and using the table listing drop in a new text box in case youve a slight corrupt there

    gareth

  6. #6
    Join Date
    Nov 2003
    Posts
    104
    garethfx --> The table's date feilds are set to SHORT DATE. The text boxes do NOT have any input masks (I doubt that should be a problem though?).

    JTRockville --> I edited my reply above. It now includes the sub that does the adding. The SQL is stored in a string called "SQL"
    Last edited by shassouneh; 03-18-04 at 17:50.

  7. #7
    Join Date
    Jan 2003
    Location
    Rockville, MD
    Posts
    179
    Try this (note the bolded part).

    Code:
        If (IsNull(txtbxDateSuspended) Or txtbxDateSuspended.text = "") Then 'If no date suspended has been entered...
            
            'attempt to add into DB without a date suspended
          
            SQL = "INSERT into OPERATOR([Operator ID],[Creator],[Name],[Description],[Department ID],[Template ID], [VAX ID],[Date Created], [Active]) values(" & ID & "," & "'" & Creator & "'" & "," & "'" & Name & "'" & "," & "'" & Desc & "'" & "," & Dept & "," & Templ & "," & "'" & VAX & "'" & ",CVDate(""" & txtbxDateCreated.text & """)," & Active & ");"
            
            
            
        Else
            
            DateSuspended = CVDate(txtbxDateSuspended.text)
            SQL = "INSERT into OPERATOR([Operator ID],[Creator],[Name],[Description],[Department ID],[Template ID], [VAX ID],[Date Created],[Date Suspended],[Active]) values(" & ID & "," & "'" & Creator & "'" & "," & "'" & Name & "'" & "," & "'" & Desc & "'" & "," & Dept & "," & Templ & "," & "'" & VAX & "'" & ",CVDate(""" & txtbxDateCreated.text & """),CVDate(""" & txtbxDateSuspended.text & """)," & Active & ");"
            
        
            
        End If

  8. #8
    Join Date
    Nov 2003
    Posts
    104
    I used Copy/Paste using the code you gave (of course after commenting out mine), and that fixed the issue of adding without a date suspended. Adding WITH a date suspended gives me problems with setFocus since in the SQL statement we use 2 CVDate() thingies

    How can I get around this?

    Again, the problem is this:


    ",CVDate(""" & txtbxDateCreated.text & """),CVDate(""" & txtbxDateSuspended.text & """),"


    We are using BOTH txtbxDateCreated and txtbxDateSuspended.

    We can only have ONE setFocus statement before this.

    Can i just use a variable like
    txtbxDateCreated.setFocus
    dc = CVDate(""" & txtbxDateCreated.text & """),
    txtbxDateSuspended.setFocus
    ds = CVDate(""" & txtbxDateSuspended.text & """),"


    And isn't that what i originally tried in my code to no avail? HELP
    Last edited by shassouneh; 03-18-04 at 18:45.

  9. #9
    Join Date
    Mar 2004
    Posts
    77
    try to change the format of the textbox to accept short date. the option is at the properties ->format tab.

  10. #10
    Join Date
    Jan 2003
    Location
    Rockville, MD
    Posts
    179
    Try it this way (using your original variables):

    Code:
        If (IsNull(txtbxDateSuspended) Or txtbxDateSuspended.text = "") Then 'If no date suspended has been entered...
            
            'attempt to add into DB without a date suspended
          
            SQL = "INSERT into OPERATOR([Operator ID],[Creator],[Name],[Description],[Department ID],[Template ID], [VAX ID],[Date Created], [Active]) values(" & ID & "," & "'" & Creator & "'" & "," & "'" & Name & "'" & "," & "'" & Desc & "'" & "," & Dept & "," & Templ & "," & "'" & VAX & "'" & ",CVDate(""" & DateCreated & """)," & Active & ");"
            
            
            
        Else
            
            DateSuspended = CVDate(txtbxDateSuspended.text)
            SQL = "INSERT into OPERATOR([Operator ID],[Creator],[Name],[Description],[Department ID],[Template ID], [VAX ID],[Date Created],[Date Suspended],[Active]) values(" & ID & "," & "'" & Creator & "'" & "," & "'" & Name & "'" & "," & "'" & Desc & "'" & "," & Dept & "," & Templ & "," & "'" & VAX & "'" & ",CVDate(""" & DateCreated & """),CVDate(""" & DateSuspended & """)," & Active & ");"
            
        
            
        End If

  11. #11
    Join Date
    Nov 2003
    Posts
    104

    10x

    Thanx. That seems to work like a charm!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •