Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2009
    Posts
    11

    Unanswered: Problem with INSERT query and DATES in Visual Basic

    I have a query where I am appending rows into an access table from another access table dependant on the start and end dates a user imputs on a front end application, they then click a command button to trigger the sql qry. Seems to work fine with no errors except upon testing, it only appends 1 row!

    for example, I know entering the dates 28/10/2008 and 01/11/2008 should append 3 rows because there are 3 records with date stamps between those dates. But the query only appends 1 row, a record dated 28/10/2008, i.e. the start date.

    Here is the sql:

    "INSERT INTO tbl_final_status (Incident_Ref, Date_Time, Station_Area, Send_Time, Arrive_Time, Expr1, Expr2) SELECT Incidents.Incident_Ref, Incidents.Date_Time, Incidents.Station_Area, Incidents.Send_Time, Incidents.Arrive_Time, ([Arrive_Time]-[Send_Time]) AS Expr1, IIf(Expr1>0.00486111111111111,'FAIL','PASS') AS Expr2 FROM Incidents WHERE Incidents.Date_Time Between #" & txtStart.Text & "# AND #" & txtEnd.Text & "#"

    The fields in both columns of the dates are in Date/Time dd/mm/yyyy hh:mm:ss format.

    Is it the sql code that isn't working? Triggering the code through access itself, it works fine, and appends all rows applicable!

  2. #2
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    To begin with, post the actual SQL that is used built and used...

    Add a string, and build the SQL within the string. That way, you can print the contents of the string before you send it to ADO.

    Code:
    Dim strSQL as string
    Dim lCount as Long
    
    strSQL = "INSERT INTO tbl_final_status (Incident_Ref, Date_Time, Station_Area, Send_Time, Arrive_Time, Expr1, Expr2) " _
          & SELECT I.Incident_Ref, I.Date_Time, I.Station_Area, I.Send_Time, I.Arrive_Time, " _
          & "([Arrive_Time]-[Send_Time]) AS Expr1, IIf(Expr1>0.00486111111111111,'FAIL','PASS') AS Expr2 " _
          & "FROM Incidents I WHERE I.Date_Time Between #" & txtStart.Text & "# AND #" & txtEnd.Text & "#"
    debug.Print strSql
    
    cn.Execute strSQL, lCount
    debug.Print lCount & " Records inserted"
    if cn.errors.count > 0 then
      ' loop through the errors in the cn.errors collection here.  Connection 'errors' 
      ' can include multiple warnings and errors, of which, only the most recent is 
      ' passed to the VB err object.
    End If
    Note that one of the approaches that I've had to take in regards to VB, Access & dates is to avoid the use of the BETWEEN in the Where clause. Rather than use TheDate BETWEEN Date1 and Date2, I end up using TheDate >= Date1 AND TheDate <= Date2
    Last edited by loquin; 03-07-09 at 03:49.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  3. #3
    Join Date
    Feb 2009
    Posts
    11
    I think the issue could be something to do with date formats. Sql wants the date format in mm/dd/yyyy but the dates in my access tables are in dd/mm/yyyy

    If I type dates in the textbox in mm/dd/yyyy format everything works fine. But in the uk format, dd/mm/yyyy, it all goes wrong.

    How would I go about sorting this out?

  4. #4
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    in access, all dates must be stored in the U.S. date format. You can format them for presentation any way you like.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  5. #5
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    I believe the one of the date functions (cdate?) converts dates in the local format. So, in your code, convert the text to a date, then use format to format it in US mm/dd/yyyy format.

    Or, use separate text boxes for Month, Day, and year, or the calendar control, or the date-time picker. Or, if you want to avoid any potential licensing issues with the calendar control, use CCRP's MonthCal control (below)
    Attached Thumbnails Attached Thumbnails ccrp MonthCal control.JPG  
    Attached Files Attached Files
    Last edited by loquin; 03-08-09 at 05:00.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  6. #6
    Join Date
    Feb 2009
    Posts
    11
    Thanks for the advice

    I've solved it by using seperate combo boxes for the dd mm and yyyy, then in the sql INSERT query I've concatenated the values in the where clause in the format mm/dd/yyyy

    This is working perfectly, although it does seem like a bit of a lame workaround. I'd like to use the date/time picker ideally, but at least this way I can continue working on the rest of the application now the dates are sorted!

  7. #7
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    The CCRP calendar viewer is a beta version, and it probably won't ever be a released version. (It doesn't display the date selected in the format you spocify, for instance. Other than that (You can disable the date selected display) it works well. I've also used the BCG date time controls. They're available here or at CodeGuru.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You can try this one if you want. It's a bit ugly but it's free and you can customize it.

    Have a nice day!
    Attached Files Attached Files

Posting Permissions

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