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

    second criteria in opening report (Access 2000)

    Second criteria in opening a report

    I have a function that works well with the first criteria called strCriteria. Is it possible to put second criteria called StrBerlin?

    StrBerlin is obtained by the control field called Site.I want to restrict the criteria with site number between 7000 and 8000 but i cannot do it. My aim with the second criteria is to open a report only for those sites that have a number between 7000 and 8000.If i succeed then i could add on other cities as well.

    Below is my function.The seocnd criteria strBErlin give me an error.
    I will be grateful for any help
    Below is my code:


    Dim strDocName As String
    Dim strCriteria As String
    Dim OrderDate As Control
    'set the months
    Dim jan As String
    'set the months
    Dim month As Control
    'naming of variables
    Set OrderDate = Forms!Orders![OrderDate]
    Set month = Forms!Orders![month]
    Set month = Forms!Orders![month]
    jan = "Month(Orderdate)= 1 and year(Orderdate) = " & CnstYear
    Select Case month
    Case 1
    strCriteria = jan
    End Select


    Dim Berlin As String
    Set Berlin = Forms!Orders![Berlin] < 7000 And Forms!Orders![Berlin] > 8000


    strDocName = " rptSalespersite"
    DoCmd.OpenReport strDocName, acPreview, , strCriteria, strBerlin

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

    Re: second criteria in opening report (Access 2000)

    If you look up the OpenReport method in the online help or the Object Browser, you will find that it has four arguments. The fourth and last argument is the WhereCondition.

    If you want multiple conditions, concatenate them into one string:

    DoCmd.OpenReport strDocName, acPreview, , strCriteria & " AND " & strBerlin

    (This opens the report with all records from the specified month AND the specified city. If you wanted to open the report with all records for the specified month OR the specified city, you would concatenate the conditions with ... & " OR " & ...)

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

    Re: second criteria in opening report (Access 2000)

    Thank you very much for your reply. Could you help me further? I receive an error "missing operator in query expression "when
    i use the concatenated strBerlin.I cannot find out the origin of my error.I do have a control named site in my form, also in my report.
    Without the strBerlin the function works.
    Bedlow is my function :

    Public Function FncSites()

    ' Defines the StrCriteria on opening the report.
    Dim f As Form
    Set f = Forms!Orders
    f.Visible = False
    Dim strDocName As String
    Dim strCriteria As String
    Dim OrderDate As Control
    'set the months
    Dim jan As String
    'set the months
    Dim month As Control
    'naming of variables
    Set OrderDate = Forms!Orders![OrderDate]
    Set month = Forms!Orders![month]
    Set month = Forms!Orders![month]
    jan = "Month(Orderdate)= 1 and year(Orderdate) = " & CnstYear
    Select Case month
    Case 1
    strCriteria = jan
    End Select
    strDocName = " rptSalespersite"

    Dim strBerlin As String
    strBerlin = "(" & Forms!Orders![site] & " < 7000 And " & Forms!Orders![site] & " > 8000)"


    DoCmd.OpenReport strDocName, acPreview, , strCriteria & " AND " & strBerlin


    Forms![Orders]![month].Value = 0
    End Function

    I will appreciate very much your help

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

    Re: second criteria in opening report (Access 2000)

    Put a breakpoint in your code and use the immediate window to print out the vlaue of strBerlin. You probably have an empty string in there somewhere.
    Charlotte

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

    Re: second criteria in opening report (Access 2000)

    Try Charlotte's suggestion.

    I suspect there are two problems here.

    In the first place, in

    strBerlin = "(" & Forms!Orders![site] & " < 7000 And " & Forms!Orders![site] & " > 8000)"

    the value of Forms!Orders![site] will substituted, so it would evaluate to something like

    strBerlin = "(7654 < 7000 AND 7654 > 8000)"

    In the second place, the comparison always returns false: there are no numbers less than 7000 AND greater than 8000. You should probably either switch the lesser than and greater than signs, or replace AND by OR, depending on what you want.

    strBerlin = "([site] > 7000 And [site] < 8000)"

    or

    strBerlin = "([site] < 7000 Or [site] > 8000)"

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

    Re: second criteria in opening report (Access 2000)

    Unless what you really want is values *between* 7000 and 8000, in which case you need to use >= and <= to include the values of 7000 or 8000, as well as anything in between.
    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
  •