Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Nov 2001
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Criteria for months do not function (Access 2000)

    From the comand button of my form called Form1 i try to open a report restricted
    to invoice dates only for the month July.However it seems that my command does
    not perform the criteria and i open the report with all the records, from January till
    Novemver,not onjly for July
    Both the form and the report have a control [invoicedate]
    How can i make my code work with the criteria for the months?
    And why my code doesnt work?
    Grateful for any reply

    My code in question is :
    Dim strDocName As String
    Dim strCriteria As String
    Dim invoicedate As Control
    Dim july As String
    Dim month As Control
    Set invoicedate = Forms!Form1![invoicedate]
    july = "invoicedate between #01-07-2001# and #31-07-2001# "
    strCriteria = july
    strDocName = " Rmonths"
    DoCmd.OpenReport strDocName, acPreview, , strCriteria

  2. #2
    Lounger
    Join Date
    Nov 2001
    Location
    Buckinghamshire, UK
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Criteria for months do not function (Access 2000)

    I think the problem with your code lies in your july string. However I can't be positive because I don't know what the expression in your criteria of your underlying query is. What are your referencing on your form in the query? One immediate problem I see is that you are passing the value "Invoicedate" to the query, all it wants is the syntax "between #01-07-2001# and #31-07-2001#" .

    Here's an easier way to do this. Put two text boxes on your form and name the first control TBOXdateFrom and the second TBOXdateTo. For ease of entering data install an input mask on each control in short date format.

    Next in your query in the criteria section of the InvoiceDate field put in the following:
    between forms![Form1]![TBOXDateFrom] and forms![Form1]![TBOXDateTo]

    When you run it make sure the form is open with valid dates in the two text boxes

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Criteria for months do not function (Access 2000)

    You seem to be doing an awful lot of populating variables in order to use them to populate other variables when it really isn't necessary. Your problem appears to be in the criteria string you're passing to the report. A report's filter is based on fields in its recordset, not on controls, so be sure that the name of the field is what you're passing. You don't need to deal with the invoicedate control at all, and you don't need the july string at all. Try this instead:

    Dim strDocName As String
    Dim strCriteria As String

    strCriteria = "[InvoiceDate] Between #01-07-2001# and #31-07-2001#"
    strDocName = "Rmonths"

    DoCmd.OpenReport strDocName, acPreview, , strCritiera

    You may be having a problem with the date format. SQL statements must use the US date format of mm-dd-yyyy no matter what format the system date uses, so you might try passing the dates in that format in the criteria if you can't get it to work otherwise.
    Charlotte

Posting Permissions

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