Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Jun 2002
    Location
    Nottingham, Nottinghamshire, United Kingdom
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Formatting True/False fields (Access 2000)

    Hi

    I've been asked to write some data dump routines in my work database so that the results of queries can be downloaded into Excel. I'm trying to work this out manually in the query builder before writing code to hide the process.

    There is a boolean field in my table called "ResSex". The underlying field has no formatting, as I do this explicitly in all the forms and reports that reference this field. In the query builder I have given the field the format ;"Male";"Female". When the query runs I get the tick boxes and when I transfer the data to Excel using the toolbar button I get TRUE or FALSE. I've tried to use male/female as Access rejects ;"M";"F" and substitutes /M/F for some reason.

    How can I set up the query so that the words Male or Female get passed to Excel?

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

    Re: Formatting True/False fields (Access 2000)

    Add a column to the query that explicitly formats the field:

    <code>Gender: Format([ResSex],"""Male"",""Female""")</code>

    (you may have to switch the order)

  3. #3
    3 Star Lounger
    Join Date
    Jun 2002
    Location
    Nottingham, Nottinghamshire, United Kingdom
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formatting True/False fields (Access 2000)

    Hans

    Many thanks ... however when I enter this into the query grid it automatically changes it to:

    Gender: Format([ResSex],"""Male,Female""")

    I'm copying and pasting your text so I know that I'm entering all the quote marks as you have them.

    David

  4. #4
    3 Star Lounger
    Join Date
    Jun 2002
    Location
    Nottingham, Nottinghamshire, United Kingdom
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formatting True/False fields (Access 2000)

    OK, solved it for now by using an Iif construct.

    Gender: Iif([ResSex],"Male","Female")

    Can't say I understand why this has proved to be a problem. The query design properties dialog shows an option to format and pressing F1 leads to help suggesting that one can enter ;"Male";"Female". Why it doesn't work is beyond me.

    If I can understand why Hans' Format construct doesn't work it would help, as I think I've read somewhere that its not good practice to use Iif constructs in queries.

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

    Re: Formatting True/False fields (Access 2000)

    Does this work for you?

    <code>Gender: Format([ResSex],"""Male"";""Female""")</code>

  6. #6
    3 Star Lounger
    Join Date
    Jun 2002
    Location
    Nottingham, Nottinghamshire, United Kingdom
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formatting True/False fields (Access 2000)

    Yes Hans, that now works with the semicolon rather than the comma

    ... except that it reverses the logic so that the first part of the format relates to False and the second to True, which is the opposite of what I'd expect. I've changed the text and can live with this.

    Regards
    David

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

    Re: Formatting True/False fields (Access 2000)

    You can either switch the texts, or use

    <code>Gender: Format([ResSex],";""Male"";""Female""")</code>

  8. #8
    3 Star Lounger
    Join Date
    Jun 2002
    Location
    Nottingham, Nottinghamshire, United Kingdom
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formatting True/False fields (Access 2000)

    I can now see how the format command in this context relates to the normal way of entering formatting for yes/no fields in forms and reports.

    I'm going to use your last option and explicitly put in the first ";" so that I will not be confused next time I have to work with this query.

    Many thanks
    David

Posting Permissions

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