Results 1 to 6 of 6

How to compare date in table with current date

  1. #1
    ismith is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Posts
    39

    How to compare date in table with current date

    Hi,

    im making a form which calculates the total of a till at the end of the day.
    Once this is saved i enter it in a database called 'tblEndofDayTotal'.

    What i want to do is:

    Ive got a button which links to this form.
    So when the button is pressed, I want it to check the tblEndofdayTotal table
    and compare the date with todays current date. If this matches I want the fields
    to auto populate with the data of the matched field. If it doesnt, I want it to leave the fields empty.

    In my tblEndofDayTotal I have the following fields:

    Date = Date/Time
    DateTotal = Currency
    TillTotal = Currency
    Difference = Currency

    This is what ive tried

    Code:
    Set saveTotal = db.OpenRecordset("SELECT * FROM tblEndofDayTotal WHERE Date = 
    " & Date)
    
    If saveTotal.RecordCount = 0 Then
    
    pre-fill all txt boxes
    
    Else
    
    Leave empty
    
    End If
    This code is not workin and its not matching the dates in the table.

    How can i do this?

    Thanks.

  2. #2
    ismith is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Posts
    39
    any ideas? need help urgently plz

  3. #3
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,896
    Where is the data coming from that you want to use to prefill the textboxes?

    With respect to your table, the word date is a reserved word in Access, so I would recommend changing that field name to something else. Also, the field called difference is not necessary; calculated values are generally not stored in the database but calculated on the fly in forms (using expressions), queries or reports.

  4. #4
    ismith is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Posts
    39
    Hi. Im pre populating it from the same table. this is my code:

    The table tblEndofDayTotal contains:
    MyDate - Date/Time
    DayTotal - Currency
    TillTotal - Currency
    Difference - Currency

    Code:
    Set db = CurrentDb
    Set saveTotal = db.OpenRecordset("SELECT * FROM tblEndofDayTotal WHERE MyDate = " & Date)
    Set endTotal = db.OpenRecordset("EndofDayTotal")
    
    If saveTotal.RecordCount = 0 Then
    
    txtTotal.Value = endTotal("SumOfPaid")
    
    ElseIf saveTotal.RecordCount = 1 Then
    
    txtTotal.Value = saveTotal("DateTotal")
    txtTillTotal.Value = saveTotal("TillTotal")
    txtDifference.Value = saveTotal("Difference")
    
    txtTotal.Locked = True
    txtTillTotal.Locked = True
    txtDifference.Locked = True
    btnCalculate.Enabled = False
    btnSave.Enabled = False
    
    End If
    Any help?
    Need to sort urgently and been stuck for quite a while??

    Thanks..

  5. #5
    orange's Avatar
    orange is offline VIP
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    5,938
    Further to jzwp11 comment re reserved words, you should post all of the related code when showing code. Where are your Dim statements? Do you use Option Explicit in modules -- that will eliminate spelling errors etc.
    What error do you get, if any?
    When you use recordCount, you should move to the last record to ensure you are getting a correct reading.

    eg,
    Rset.MoveLast
    Rset.MoveFirst
    Debug.Print "RecordCount is " & Rset.RecordCount

    Could you describe your business process generally to help us understand the TotalTill at End of Day?

    Have you considered a Transaction table where money in (received +) and money out(paid -) are recorded as Transaction records. At the end of day (or anytime) you can run a query to Sum the transactions by Date(period) to get the TotalTally? Just a thought.

  6. #6
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,896
    You have to delimit the date field with # signs (shown in red below). Also, to get the count of records in a recordset you have to move to the last record to get the full count

    What is "EndofDayTotal"? Is it a query?

    You will also have to move to the correct record in each recordset if they contain more than 1 record. Also to reference a field, I think the proper syntax is recordsetname!fieldname, so saveTotal!DateTotal

    Also, I would use the me. shortcut to reference the current form for the controls.

    Again, you should not store the difference, you would just use an expression to calculate it.


    The following is air code (not tested):

    Dim db As DAO.Database
    Set db = CurrentDb

    Dim savetotal As DAO.Recordset
    Dim endTotal AS DAO.Recordset
    Dim FindRecordCount as long

    Set saveTotal = db.OpenRecordset("SELECT * FROM tblEndofDayTotal WHERE MyDate =#" & Date() & "#")
    Set endTotal = db.OpenRecordset("EndofDayTotal")


    If saveTotal.EOF Then
    FindRecordCount = 0
    Else
    saveTotal.MoveLast
    FindRecordCount = saveTotal.RecordCount
    End If


    If FindRecordCount = 0 Then

    me.txtTotal = endTotal!SumOfPaid

    ElseIf FindRecordCount = 1 Then

    me.txtTotal = saveTotal!DateTotal
    me.txtTillTotal = saveTotal!TillTotal

    me.txtTotal.Locked = True
    me.txtTillTotal.Locked = True
    me.btnCalculate.Enabled = False
    me.btnSave.Enabled = False

    End If


    saveTotal.close
    endTotal.close
    db.close

    Set saveTotal= Nothing
    Set endTotal = Nothing
    Set db=Nothing

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

Similar Threads

  1. Replies: 2
    Last Post: 03-31-2012, 06:53 AM
  2. Replies: 1
    Last Post: 12-07-2011, 11:02 AM
  3. SQL Inner Join where date = current date
    By Tyork in forum Queries
    Replies: 2
    Last Post: 11-07-2010, 12:07 PM
  4. Comparing Date with current Date
    By ds_8805 in forum Forms
    Replies: 7
    Last Post: 03-31-2010, 08:31 PM
  5. Replies: 9
    Last Post: 03-19-2010, 09:37 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
  •  
Tech Forums: Microsoft Office Forums