Results 1 to 7 of 7
  1. #1
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185

    Adding Dates To A Record

    Hi Guys, i am having a brain freeze here, i have a record to add a shipment date to which the Day is the same every week so the dates are added as Friday (Forms!frmMainMenu!frmIndex1!cboShipmentDateIndex1 ) is selected to Fridays date, when i choose confirm, it should add the shipment date from cboShipmentDateindex1, which works fine, i am trying to check to see if another user has already confirmed, if so, then do we want to add next friday's date or cancel, it's a one of these where it should be simple enough just side tracked and brain freeze!!,


    IT WON'T ADD THE DATE TO THE CURRENT WEEK RECORD (POSTCODE)

    Note all Dims as set mPC string dates are all set as dates

    Code:
    Dim LastDate, NewDate As DateIf Me.Confirm = True Then  ' this is a tickbox
    If Forms!frmMainMenu!frmIndex1!cboShipmentDateIndex1 <> "" Then
    If Me.Source <> "Acc" Then
    mPC = Me.PostCode
    mShipDate = Forms!frmMainMenu!frmIndex1!cboShipmentDateIndex1
    LastDate = DMax("ShipmentDate", "tblEdit", "[PostCode] = '" & mPC & "'")
    NewDate = DateAdd("d", 7, LastDate)
    If LastDate = mShipDate Then
    If MsgBox("You Already Have This Record For:" & " " & LastDate & Chr(10) & Chr(10) & _
    "Do You Want To Add For The Following Week" & " " & NewDate & " " & "?", vbQuestion + vbYesNo, "ALREADY EXISTS") = vbNo Then
    Me.ShipmentDate = mShipDate
    Else
    Me.ShipmentDate = NewDate
    End If
    Dim mday As Integer
    mday = Weekday(mShipDate, vbUseSystemDayOfWeek)
    WKDay = WeekdayName(mday)
    Me.Status = WKDay
    Me.SL = "SL-" & " " & Me.Source & " " & Format(Me.ShipmentDate, "ddmmyy")
    DoCmd.OutputTo acOutputReport, "rptPO", acFormatPDF, "T:\DMT\PDF\DMT SL's\" & Me.SL & ".pdf"
    End If
    End If
    End If
    End If

  2. #2
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    I'm not sure I follow the logic here. I've indented the code to make it more legible and altered your Dim statements that weren't explicit.
    What bit doesn't work? What error do you get ?

    Code:
     
    Dim LastDate As Date    
    Dim NewDate As Date
    Dim mday As Integer
    
        If Me.Confirm = True Then  ' this is a tickbox
            If Forms!frmMainMenu!frmIndex1!cboShipmentDateIndex1 <> "" Then
                If Me.Source <> "Acc" Then
                    mPC = Me.PostCode
                    mShipDate = Forms!frmMainMenu!frmIndex1!cboShipmentDateIndex1
                    LastDate = DMax("ShipmentDate", "tblEdit", "[PostCode] = '" & mPC & "'")
                    NewDate = DateAdd("d", 7, LastDate)
                    If LastDate = mShipDate Then
                        If MsgBox("You Already Have This Record For:" & " " & LastDate & Chr(10) & Chr(10) & _
                            "Do You Want To Add For The Following Week" & " " & NewDate & " " & "?", vbQuestion + vbYesNo, "ALREADY EXISTS") = vbNo Then
                            Me.ShipmentDate = mShipDate
                        Else
                            Me.ShipmentDate = NewDate
                        End If
                        
                        mday = Weekday(mShipDate, vbUseSystemDayOfWeek)
                        WKDay = WeekdayName(mday)
                        Me.Status = WKDay
                        Me.SL = "SL-" & " " & Me.Source & " " & Format(Me.ShipmentDate, "ddmmyy")
                        DoCmd.OutputTo acOutputReport, "rptPO", acFormatPDF, "T:\DMT\PDF\DMT SL's\" & Me.SL & ".pdf"
                    End If
                End If
            End If
        End If
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    Hi Minty, There is no Shipment Date adding to the ShipmentDate Field (mShipDate or NewDate), it will add if i take out The If statements, the message won't pop up to say it already exists as it's not putting the date in, i may well have the sequence incorrect!, i will post a copy that does add the shipment date, many thanks

  4. #4
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    The following adds the shipment date, only difference is the source but that shouldn't stop it!

    Code:
    If Me.Confirm = True Then
    If Forms!frmMainMenu!frmIndex1!cboShipmentDateIndex1 <> "" Then
    If Me.Source = "Acc" Then
    Me.ShipmentDate = mShipDate
    Dim day As Integer
    day = Weekday(mShipDate, vbUseSystemDayOfWeek)
    WKDay = WeekdayName(day)
    Me.Status = WKDay
    Else
    DoCmd.CancelEvent
    End If
    End If
    End If

  5. #5
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    I also mean't to mention there is no error comes up, it just doesnt add the shipment date based on Forms!frmMainMenu!frmIndex1!cboShipmentDateIndex1, note there is definitely a date in the combo many thanks

  6. #6
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    I thought this was brain freeze, i have used many times Dlookup("PostCode","tblJobs","[Date] = Is Not Null") or Date Is Null, usually get the results!!

  7. #7
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    I think you have solved this but you need to use

    Code:
    Not IsNull(Forms!frmMainMenu!frmIndex1!cboShipmentDateIndex1)
    In your expression. A date will never be an empty string ""
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

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

Similar Threads

  1. Adding Dates to Meeting's Invitations
    By Lou_Reed in forum Access
    Replies: 0
    Last Post: 03-24-2017, 02:30 PM
  2. Problem with adding dates
    By winterh in forum Access
    Replies: 1
    Last Post: 05-23-2012, 04:27 PM
  3. Adding Dates
    By SobJim in forum Queries
    Replies: 11
    Last Post: 04-21-2012, 01:28 PM
  4. Replies: 10
    Last Post: 11-16-2011, 10:58 AM
  5. Adding numbers to dates
    By MFS in forum Programming
    Replies: 5
    Last Post: 11-24-2010, 12:06 PM

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