Formatting True/False fields (Access 2000)

Topic

New Reply

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?

Reply | Quote

Replies

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

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

(you may have to switch the order)

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

Does this work for you?

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

Reply | Quote

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

Reply | Quote

You can either switch the texts, or use

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

Reply | Quote

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

Reply | Quote