Results 1 to 8 of 8
  1. #1
    Join Date
    May 2006
    Location
    Alabama
    Posts
    37

    Unanswered: Need help with - format(now(),"MM") -1

    Greetings:

    I have an MsAccess 2000 application that was created in a flat file format; it is linked to a backend .mdb file. What I need to do is to move all the records except for the last month to an identical table for permanent archiving. So far I am approaching this problem by activating an append query and a delete query using code below. I have separate fields for the Month, Day and Year. I have been manually setting the criteria for the query in the “Month” field by specifying the month i.e. “03”or”04” so that the all that remain are the May records. So my question is two fold; is there a way to write criteria for the “Month” field on each query (poss example: (format(now(),”MM”)-1) so that I don’t have to do it manually, or is there a better way to approach the problem.

    Private Sub Command0_Click()
    On Error GoTo Err_Command0_Click

    Dim stDocName As String
    ‘Append query name= append1
    stDocName = "append1"
    DoCmd.OpenQuery stDocName, acNormal, acEdit

    ‘Delete query name= deletequery1
    Dim stDocName1 As String
    stDocName = "deletequery1"
    DoCmd.OpenQuery stDocName, acNormal, acEdit


    Exit_Command0_Click:
    Exit Sub

    Err_Command0_Click:
    MsgBox Err.Description
    Resume Exit_Command0_Click

    End Sub

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Your query can probably be modified, but we need to see your append query's SQL in order to know for sure. (The delete query mod will probably be identical.) Please copy-and-paste it.

    Sam

  3. #3
    Join Date
    May 2006
    Location
    Alabama
    Posts
    37
    This is the append query:

    thanks in advance.

    Carbo


    INSERT INTO COMMUNICATIONS1 ( Item, [Date], WeekDay, Unit, Location, [Activity Code], [10-28], Tag, State, [Case #], Suffix, [Month], [Day], [Year], Received, Dispatched, Arrived, Custody, Completed, Method, [Reported By], Address, City, Phone, Subject, Race, Sex, DOB, OLN, SSN, Status, AGENCY, [Duty Officers], Weather, Temp, [Enter Info], Remarks, NRC, [False Alarm], [Data Back-Up], Recorder, Visitor, Flag_DIR, Flag_ADIR, Flag_Sup, Flag_Sec, VIEW1, VIEW2, VIEW3, VIEW4, VIEW5, TEST, [DV CALL], PW, [STARTING MILEAGE], [ENDING MILEAGE], [TOTAL DAILY MILES], [DAY WORD], [MONTH WORD], [YEAR WORD], UPDATES, [Count] )
    SELECT COMMUNICATIONS.Item, COMMUNICATIONS.Date, COMMUNICATIONS.WeekDay, COMMUNICATIONS.Unit, COMMUNICATIONS.Location, COMMUNICATIONS.[Activity Code], COMMUNICATIONS.[10-28], COMMUNICATIONS.Tag, COMMUNICATIONS.State, COMMUNICATIONS.[Case #], COMMUNICATIONS.Suffix, COMMUNICATIONS.Month, COMMUNICATIONS.Day, COMMUNICATIONS.Year, COMMUNICATIONS.Received, COMMUNICATIONS.Dispatched, COMMUNICATIONS.Arrived, COMMUNICATIONS.Custody, COMMUNICATIONS.Completed, COMMUNICATIONS.Method, COMMUNICATIONS.[Reported By], COMMUNICATIONS.Address, COMMUNICATIONS.City, COMMUNICATIONS.Phone, COMMUNICATIONS.Subject, COMMUNICATIONS.Race, COMMUNICATIONS.Sex, COMMUNICATIONS.DOB, COMMUNICATIONS.OLN, COMMUNICATIONS.SSN, COMMUNICATIONS.Status, COMMUNICATIONS.AGENCY, COMMUNICATIONS.[Duty Officers], COMMUNICATIONS.Weather, COMMUNICATIONS.Temp, COMMUNICATIONS.[Enter Info], COMMUNICATIONS.Remarks, COMMUNICATIONS.NRC, COMMUNICATIONS.[False Alarm], COMMUNICATIONS.[Data Back-Up], COMMUNICATIONS.Recorder, COMMUNICATIONS.Visitor, COMMUNICATIONS.Flag_DIR, COMMUNICATIONS.Flag_ADIR, COMMUNICATIONS.Flag_Sup, COMMUNICATIONS.Flag_Sec, COMMUNICATIONS.VIEW1, COMMUNICATIONS.VIEW2, COMMUNICATIONS.VIEW3, COMMUNICATIONS.VIEW4, COMMUNICATIONS.VIEW5, COMMUNICATIONS.TEST, COMMUNICATIONS.[DV CALL], COMMUNICATIONS.PW, COMMUNICATIONS.[STARTING MILEAGE], COMMUNICATIONS.[ENDING MILEAGE], COMMUNICATIONS.[TOTAL DAILY MILES], COMMUNICATIONS.[DAY WORD], COMMUNICATIONS.[MONTH WORD], COMMUNICATIONS.[YEAR WORD], COMMUNICATIONS.UPDATES, COMMUNICATIONS.Count
    FROM COMMUNICATIONS
    WHERE (((COMMUNICATIONS.Month)="01" Or (COMMUNICATIONS.Month)="02" Or (COMMUNICATIONS.Month)="03"));

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    How about Month(Now()) - 1
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Code:
    WHERE (((COMMUNICATIONS.Month)="01" Or (COMMUNICATIONS.Month)="02" Or (COMMUNICATIONS.Month)="03"));
    Change the above to
    Code:
    WHERE CInt(COMMUNICATIONS.Month) <> Month(DateAdd("m",-1,Date()));
    What that does is filter out last month. Of couse you can modify it if you need only a 3-month window. I used CInt() because I notice that the Month field is "01", etc., which is type string, and I'm comparing it to the results of the Month() function which is type numeric.

    Izy, Month(Now()) - 1 will produce "0" if the current month is January.

  6. #6
    Join Date
    May 2006
    Location
    Alabama
    Posts
    37
    INSERT INTO COMMUNICATIONS1 ( Item, [Date], WeekDay, Unit, Location, [Activity Code], [10-28], Tag, State, [Case #], Suffix, [Month], [Day], [Year], Received, Dispatched, Arrived, Custody, Completed, Method, [Reported By], Address, City, Phone, Subject, Race, Sex, DOB, OLN, SSN, Status, AGENCY, [Duty Officers], Weather, Temp, [Enter Info], Remarks, NRC, [False Alarm], [Data Back-Up], Recorder, Visitor, Flag_DIR, Flag_ADIR, Flag_Sup, Flag_Sec, VIEW1, VIEW2, VIEW3, VIEW4, VIEW5, TEST, [DV CALL], PW, [STARTING MILEAGE], [ENDING MILEAGE], [TOTAL DAILY MILES], [DAY WORD], [MONTH WORD], [YEAR WORD], UPDATES, [Count] )
    SELECT COMMUNICATIONS.Item, COMMUNICATIONS.Date, COMMUNICATIONS.WeekDay, COMMUNICATIONS.Unit, COMMUNICATIONS.Location, COMMUNICATIONS.[Activity Code], COMMUNICATIONS.[10-28], COMMUNICATIONS.Tag, COMMUNICATIONS.State, COMMUNICATIONS.[Case #], COMMUNICATIONS.Suffix, COMMUNICATIONS.Month, COMMUNICATIONS.Day, COMMUNICATIONS.Year, COMMUNICATIONS.Received, COMMUNICATIONS.Dispatched, COMMUNICATIONS.Arrived, COMMUNICATIONS.Custody, COMMUNICATIONS.Completed, COMMUNICATIONS.Method, COMMUNICATIONS.[Reported By], COMMUNICATIONS.Address, COMMUNICATIONS.City, COMMUNICATIONS.Phone, COMMUNICATIONS.Subject, COMMUNICATIONS.Race, COMMUNICATIONS.Sex, COMMUNICATIONS.DOB, COMMUNICATIONS.OLN, COMMUNICATIONS.SSN, COMMUNICATIONS.Status, COMMUNICATIONS.AGENCY, COMMUNICATIONS.[Duty Officers], COMMUNICATIONS.Weather, COMMUNICATIONS.Temp, COMMUNICATIONS.[Enter Info], COMMUNICATIONS.Remarks, COMMUNICATIONS.NRC, COMMUNICATIONS.[False Alarm], COMMUNICATIONS.[Data Back-Up], COMMUNICATIONS.Recorder, COMMUNICATIONS.Visitor, COMMUNICATIONS.Flag_DIR, COMMUNICATIONS.Flag_ADIR, COMMUNICATIONS.Flag_Sup, COMMUNICATIONS.Flag_Sec, COMMUNICATIONS.VIEW1, COMMUNICATIONS.VIEW2, COMMUNICATIONS.VIEW3, COMMUNICATIONS.VIEW4, COMMUNICATIONS.VIEW5, COMMUNICATIONS.TEST, COMMUNICATIONS.[DV CALL], COMMUNICATIONS.PW, COMMUNICATIONS.[STARTING MILEAGE], COMMUNICATIONS.[ENDING MILEAGE], COMMUNICATIONS.[TOTAL DAILY MILES], COMMUNICATIONS.[DAY WORD], COMMUNICATIONS.[MONTH WORD], COMMUNICATIONS.[YEAR WORD], COMMUNICATIONS.UPDATES, COMMUNICATIONS.Count
    FROM COMMUNICATIONS
    WHERE CInt(COMMUNICATIONS.Month) <> Month(DateAdd("m",-1,Date()));

    I received Error: Invalid use of NULL
    Did I make a mistake?

  7. #7
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    more likely one or more of your data values is null - the CInt function doesn't accept null values. Add the Nz function to your expression.

  8. #8
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Yes; tcase is referring to your Month field. One or more values might be null. (I wouldn't use Nz(), however, because any number you assign as the value will be wrong at some point.) In that case, if you know, for example, that the COMMUNICATIONS.Date field is always populated, use Month(COMMUNICATIONS.Date) instead of COMMUNICATIONS.Month. This assumes that COMMUNICATIONS.Date is a Date data type. If it is string, as is the COMMUNICATIONS.Month field, use Month(CDate(COMMUNICATIONS.Date)), which analyzes the Date field as a date, and extracts the month information.

    Sam
    Last edited by Sam Landy; 05-11-06 at 09:52.

Posting Permissions

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