Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Archive Code incorrect? (2000)

    Hi,
    I have the following code in a form where I need to archive records relating to specific data:

    Private Sub cmdArchive_Click()
    Dim strSQL As String

    On Error GoTo ErrHandler

    If MsgBox("You are about to archive records where the packages have been sent. Are you sure?", _
    vbQuestion + vbYesNo) = vbNo Then
    Exit Sub
    End If

    strSQL = "UPDATE TblMain SET Archive = True WHERE Archive = False And DatePackageOut Is Not Null And Datefull Is Not Null And DateSerial(Year([DatePackageOut]),Month([DatePackageOut]),1)=DateSerial(Year(Date()),Month(Date())-1,1)"
    DoCmd.RunSQL strSQL ' or CurrentDb.Execute strSQL

    Exit Sub

    ErrHandler:
    If Err <> 2501 Then
    MsgBox Err.Description, vbExclamation
    End If

    End Sub

    Doesn't seem to work though. Can anybody who is good with code shed some light on why?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Archive Code incorrect? (2000)

    Something like "Doesn't seem to work though" is completely unhelpful. It doesn't seem to work? But does it? Please be specific:

    - Do you get an error message? If so, what does it say?
    - Does the code run, but with an undesired or unexpected result? If so, what is that result?
    - Does the code run, but without apparent effect?

  3. #3
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Archive Code incorrect? (2000)

    It displays the error;
    Data type mismatch in criteria expression.

    The code:
    strSQL = "UPDATE TblMain SET Archive = True WHERE Archive = False And DatePackageOut Is Not Null And Datefull Is Not Null And DateSerial(Year([DatePackageOut]),Month([DatePackageOut]),1)=DateSerial(Year(Date()),Month(Date())-1,1)"

    As far as I can see the above statement covers everything that it should

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Archive Code incorrect? (2000)

    Open TblArchive in design view. What is the data type of the Archive field?

  5. #5
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Archive Code incorrect? (2000)

    Archive is a Yes/No field

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Archive Code incorrect? (2000)

    Apparently the calculations in the comparison of the DateSerial values have become too complicated for the Jet Engine to resolve. Try this; it looks more complicated, but the calculations involved are easier:

    strSQL = "UPDATE TblMain SET TblMain.Archive = True WHERE (((TblMain.Archive)=False) AND ((Year([DatePackageOut]))=Year(Date())) AND ((Month([DatePackageOut]))=Month(Date())-1) AND ((TblMain.DatePackageOut) Is Not Null) AND ((TblMain.DateFull) Is Not Null)) OR (((TblMain.Archive)=False) AND ((Year([DatePackageOut]))=Year(Date())-1) AND ((Month([DatePackageOut]))=12) AND ((TblMain.DatePackageOut) Is Not Null) AND ((TblMain.DateFull) Is Not Null) AND ((Month(Date()))=1))"

  7. #7
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Archive Code incorrect? (2000)

    Thats fixed the problem, I'm quite surprised that the coding was too complicated to resolve and I never would have gotten there.
    Thanks again.

Posting Permissions

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