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

    Query advice (2000)

    Hi,
    Just a quick query.
    I have a form (FrmMain) that I would like to filter. There is a Date field in the related table (named DateOut).
    I also have a query that filters all the entries in relation to the DateOut using DateSerial(Nz([DateOut]),1999)Nz(Month([DateOut]),1),1) so that they relate to the previous month. I'll be compiling reports based on these dates.
    I would like to filter the FrmMain so that they relate to entries for the current month. I presume that you would use something similar to: [DateOut]<DateSerial(Year(Date()),Month(Date())-1,1) in the Filter properties. And base the Form on the query?
    Could someone advise?

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

    Re: Query advice (2000)

    If you want to create a query that selects records for which DateOut is in the current calendar month, you would enter this in the Criteria line under DateOut:

    Between DateSerial(Year(Date()),Month(Date()),1) And DateSerial(Year(Date()),Month(Date())+1,0)

    If you want to create a query that selects records for which DateOut is within the last month from today, you would enter this in the Criteria line under DateOut:

    Between DateAdd("m",-1,Date())+1 And Date()

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

    Re: Query advice (2000)

    Hi Hans,
    I would need the first one. To show all records, in this case DateOut within May.
    I have entered the formula:
    Between DateSerial(Year(Date()),Month(Date()),1) And DateSerial(Year(Date()),Month(Date())+1,0)
    but it is still returning records relating to April.
    Have I done something wrong?

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

    Re: Query advice (2000)

    If you entered this in the criteria line under DateOut, you should only get records for which DateOut is in May, 2004, UNLESS the query also contains other criteria in another line. Criteria in different lines act as OR criteria.

    If you can't find out what's wrong, open the query in design view, then select View | SQL. Copy the complete SQL text to the clipboard and paste it into a reply. That way we can inspect the SQL statement.

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

    Re: Query advice (2000)

    Hi Hans,
    Heres the SQL:
    SELECT TblJPM.*
    FROM TblJPM
    WHERE (((TblJPM.DateReceived) Between DateSerial(Year(Date()),Month(Date()),1) And DateSerial(Year(Date()),Month(Date())+1,0)))
    WITH OWNERACCESS OPTION;

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

    Re: Query advice (2000)

    This query will select all records whose DateReceived is in the current month, but DateOut could be in any month, since the criteria don't specify a condition for DateOut.

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

    Re: Query advice (2000)

    Presumably this should work for DateReceived as essentially this is the date that is DateOut in a physical sense.
    From a work point of view, the record is finalised when a record is entered as all the information is inputted on that day. What I will need to do is keep the current months data on the form and "archive" the previous months data. I was therefore hoping that by recognizing the date in DateReceived as being in the current month that the previous month/s data would be hidden from view, and only the current months data (although obsolete) would be visible. Then the following month, this data would be collated into a report and become hidden from view........
    I think that makes sense...................
    Currently the criteria shows all records.

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

    Re: Query advice (2000)

    If you open the query in datasheet view,, do you see all records, or just those with DateReceived in the current month?

    If you see all records, I don't understand what's happening.

    If you see the correct records in the query, are you sure that the Record Source of the form has been set to this query?

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

    Re: Query advice (2000)

    Thanks Hans,
    I've worked the problem out, the field type was incorrectly text instead of Date/Time..................ho hum.
    Yes, it now works perfectly, thanks for your patience!

Posting Permissions

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