Results 1 to 11 of 11
  1. #1
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Burgas, Bulgaria
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    OnOpen event in report (Access 2000)

    I need to use the following condition in the OnOpen event of the report

    If Me![TxtMyCategory] = 6 Or 12 Then
    ........


    But i get the error " you entered an expression that has no value"
    I can evade the error if i put the expression in the OnFormat event.
    But in the OnFormat event i cannot create the control source for the
    following line :

    Me![MyLiters].ControlSource = "=[liters] & "" kg"""


    The above line works only in the OnOpen event,and not in the OnFormat event.
    Thus i find myslef in a vicous circle and i cannot do my task.
    Is there any possiblity to place the following code in the OnPen event :
    If Me![TxtMyCategory] = 6 Or 12 Then
    Me![MyLiters].ControlSource = "=[liters] & "" kg"""
    Else
    Me![MyLiters].ControlSource = "=[liters] & "" liters"""
    End If


    Just to explain why i need this code.

    On each invoice, that may contain 12 products, i need to write kilograms or liters
    depending on the category of the product contained in the MyCategory control.
    So, for example if the category is 6 ,the products should be in kilograms.
    Unfortunately i cannot achieve it. I will be grateful for any help.

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

    Re: OnOpen event in report (Access 2000)

    You cannot do it this way, for txtCategory is not valid yet when the report is opened. You don't need code for this. You can set the control source of MyLiters in design view, as follows:

    =[Liters] & IIf([txtMyCategory]=6 Or [txtMyCategory]=12," kg", " liters")

    Note that I repeat the field name in the IIf function. [txtMyCategory] = 6 Or 12 is not correct.

  3. #3
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Burgas, Bulgaria
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: OnOpen event in report (Access 2000)

    Thank you very much for your reply. I followed your advice. I get a syntax error in my control source :
    =[Liters] & IIf([txtMyCategory]=6 Or [txtMyCategory]=12," kg", " liters")
    I aslo tried to replace the comma with semicolon, but again i get the syntax error.
    =[Liters] & IIf([txtMyCategory]=6 Or [txtMyCategory]=12;" kg"; " liters")
    Could you tell me where am i wrong ?
    I couldnt inderstand what you mean thath the field name txtMycategoy is not correct ?

    I am placing this in my control [MyLiters] which is an unbound control.Maybe it is superflous.
    It could me simpler if i put this is the control [liters] and concatenate the kg or liters depending on
    txtMyCategory. Can i do it ?

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

    Re: OnOpen event in report (Access 2000)

    There is nothing wrong with the name txtMyCategory, but the condition you had originally

    [txtMyCategory] = 6 Or 12

    will not do what you want, it should be

    [txtMyCategory] = 6 Or [txtMyCategory] = 12

    Whether you should use a comma or a semicolon depends on your Windows settings, you should use the list separator character.

    Is liters a field in the record source of the report, or is it a calculated control?

  5. #5
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Burgas, Bulgaria
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: OnOpen event in report (Access 2000)

    I am afraid something is wrong in my computer with the IIf clause. For example, i have copied from
    the help of Access the following line :

    = IIf([OrderAmount] = 1000, "Large", "Small")

    And again i get the same errors :

    The expression you have entered contains invalid syntax
    Syntax error: you may have entered an operand without an operator

    Why is it so ?

    The control liters is not a calculated control, the filed liters is from the record source
    of the report.However, in some cases, when the category is 4 or 12 i want to be written kg instead of liters

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

    Re: OnOpen event in report (Access 2000)

    In Windows, select Start | Control Panel.
    Open the Regional Settings control panel.
    Check the List Separator that is used on your system. Where exactly this is specified, depends on the Windows version you are using.
    You should use this character between the arguments for the IIf function. On a US or UK system, the list separator is a comma; on my Dutch system, it is a semicolon. I don't know what the standard setting for Bulgaria is.

  7. #7
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Burgas, Bulgaria
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: OnOpen event in report (Access 2000)

    Thank you for your reply. I have checked the List Separator, it is semoicolon ;

    i have replaced it in my line but againt i get the message that i have a syntax error.
    Maybe i have something more or less in my code ?

    Below is what i have written.

    =[Liters] & IIf([txtMyCategory]=6 Or [txtMyCategory]=12;" kg"; " liters")

    The controls txtMyCategory and liters are not calculated and exist in the report.


    Best regards

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

    Re: OnOpen event in report (Access 2000)

    I don't think we can solve this without seeing the database. If possible, post a stripped down version of the database:
    <UL><LI>Make a copy of the database and work with that.
    <LI>Remove all database objects (tables, queries, forms, reports, macros and modules) that are not relevant to the problem.
    <LI>In the remaining table(s), remove most records - leave only the minimum number necessary to demonstrate the problem.
    <LI>Remove or modify data of a confidential nature.
    <LI>Do a compact and repair (Tools/Database Utilities).
    <LI>Make a zip file containing the database; it should be below 100KB.
    <LI>If you have difficulties getting the zip file below 100 KB, save the database in Access 97 format and then zip it.
    <LI>Attach the zip file to a reply.[/list]That will allow Loungers to look at the problem directly.

  9. #9
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Burgas, Bulgaria
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: OnOpen event in report (Access 2000)

    sorry i have written = instead of IIF but the error with me stays

    regards

  10. #10
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Burgas, Bulgaria
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: OnOpen event in report (Access 2000)

    Dear Hans,

    I want to thank you for the detailed explanations and answers and also for your patience.With your help i have solved my
    task and now the line works as i wanted. It is :

    =[liters] & IIf([txtMyCategory]=4 Or [TxtMyCategory]=12;"kg";"liters")

    The only awkard design in the above line is that the words kg or liters follow immdeiately after the figure,
    for example 20liters and i want to be 20 liters, thats is to say with some spce between the figures and the words.
    I have tried different situation but Acces always moves back.Is it possible to get a space ?

    Please accept my kind regards

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

    Re: OnOpen event in report (Access 2000)

    You should be able to include a space in the two strings:

    <code>=[liters] & IIf([txtMyCategory]=4 Or [TxtMyCategory]=12;" kg";" liters")</code>

Posting Permissions

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