Results 1 to 4 of 4
  1. #1
    paddybear is offline Novice
    Windows XP Access 2007
    Join Date
    Jan 2012
    Posts
    2

    Having problem in date format


    I am a newbie in Ms Access, i need help to solve this problem.

    I am creating a database to store the customer info, then now, i need to create a report which allow me to printout the data created on certain date. Before that, i need to select some fields from my master table and stored into a temp table. However, i am having problem in date format when i write the SQL code.

    I stored the create date in medium date (dd-mmm-yyyy).
    Then I have a form which allow me to keyin the report start date and report end date (dd-mmm-yyyy). But the date show as (dd/mm/yyyy). Can someone please help me to check on the code on below? TQ!

    Private Sub runReport()

    Dim rs1 As DAO.Recordset
    Dim sqlSelectGL1 As String
    Dim sqlInsert As String

    Dim startDate As String
    Dim endDate As String

    Dim Branch1 As String
    Dim UserName As String
    Dim AgentName As String
    Dim DateCreate As String

    endDate = Format(CDate(txtEnd), "dd-mmm-yy")
    startDate = Format(CDate(txtBegin), "dd-mmm-yy")

    MsgBox endDate
    sqlSelectGL1 = "SELECT Branch, UserName, IntermediaryName, CreateDate FROM tblMaster WHERE CreateDate >= '" & startDate & "' and CreateDate <= '" & endDate & "'"

    Set rs1 = CurrentDb.OpenRecordset(sqlSelectGL1)

    Do While Not rs1.EOF
    Branch1 = rs1("Branch")
    UserName = rs1("UserName")
    AgentName = rs1("IntermediaryName")
    DateCreate = rs1("CreateDate")

    sqlInsert = "INSERT INTO tblISOReport (Branch, Type, UserName, AgentName, Status, DateReceive, DateRaise, DateCreate, DateDespatch, User)" & _
    "VALUES ('" & Branch1 & "', 'GL', '" & UserName & "', '" & AgentName & "', 'N', ' ', '" & DateCreate & "','" & DateCreate & "','" & DateCreate & "', 'I')"

    DoCmd.RunSQL sqlInsert

    rs1.MoveNext
    Loop
    rs1.Close

    End Sub

    Please help~~~ my date format seems like got problem.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Use # as date delimiter instead of apostrophe. At least this is the requirement for Access data. If you are connected to SQL database, not sure. The VBA/Access query engine might still want the #.

    You want to save a space or an empty string into DateReceive? What you show looks like a space. A date/time datatype field cannot have a space or empty string. Use Null. I don't allow empty strings in any fields. I prefer Null.

    Better, don't even include the field in the INSERT. Then don't have to worry about what value to pass.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    paddybear is offline Novice
    Windows XP Access 2007
    Join Date
    Jan 2012
    Posts
    2
    Thanks for June7's reply...

    I tried to replace the ' with #
    sqlSelectGL1 = "SELECT Branch, UserName, IntermediaryName, CreateDate FROM tblMaster WHERE CreateDate >= #" & startDate & "# and CreateDate <= #" & endDate & "#"

    but the query stop at Do While Not rs1.EOF
    i wonder the date format doesn't match, so no result come out.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Declare the date variables as Date instead of String type.

    I don't know why the code would stop on that line. Step debug. If you need to become more familiar with debug techniques, review the link at bottom of my post for a tutorial.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Date field not in date format
    By Lorlai in forum Access
    Replies: 11
    Last Post: 06-27-2011, 05:14 PM
  2. Date Format
    By lonewolfwfk in forum Programming
    Replies: 4
    Last Post: 11-25-2010, 09:00 PM
  3. date format
    By dollygg in forum Access
    Replies: 2
    Last Post: 01-19-2010, 10:14 AM
  4. Date Format
    By wasim_sono in forum Forms
    Replies: 2
    Last Post: 08-15-2006, 01:20 AM
  5. Date Format
    By St3ph3n in forum Access
    Replies: 4
    Last Post: 03-09-2006, 11:23 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums