Results 1 to 9 of 9
  1. #1
    KevinMCB is offline Novice
    Windows XP Access 2007
    Join Date
    Nov 2010
    Posts
    29

    Format in Property Sheet

    Hey all, once again I have a quick question. In a report I have built I recently added a new field. Everything is working correctly except that I cannot choose a Format for the field in the property sheet. I would like it in the currency format, however, when I click the down arrow to select the Format, there is nothing to select. Just a blank box drops down.



    If I click on a similar field in the report and look at the property sheet, I can choose any of the formats for it. For some reason the previous field does not have any choices for Format. Any ideas on why this is?

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    It seems to me that Access determines the options it offers based on its perception/knowledge of the data type of the field. It sounds like Access doesn't know in this case. You should be able to type in the format you want, either by name ("Currency") or by definition ("#,###.##").
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    KevinMCB is offline Novice
    Windows XP Access 2007
    Join Date
    Nov 2010
    Posts
    29
    I tried both of those, neither worked. Does it matter that the data in the field is generated from an Expression in the Query? Or what type of equation is used in the Expression? I have a field in the equation that is already defined as a currency in the table.

    Other fields in the report are generated form expressions and show up just fine in the Format box.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Could be; some expressions cause the result to be treated as text. You can tell if you just run the query; numeric values align right, text values align left. Depending on the expression, you can probably force it back to numeric.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    KevinMCB is offline Novice
    Windows XP Access 2007
    Join Date
    Nov 2010
    Posts
    29
    Yep, they are all aligning on the left as a text value. I am not sure where the expression is defining the value as a text value, although this is the first time I have used the Switch() function. The [OrgPrin] field is defined as a currency in it's table and is a numeric value. All the other fields except [Efdate] are numeric values. Below is the expression.

    Current Curtailment: Switch(DateDiff("m",[Efdate],[Enter date of report])<[Term 1],[OrgPrin]*0,DateDiff("m",[Efdate],[Enter date of report])<[Term 2],[OrgPrin]*[rate 1],DateDiff("m",[Efdate],[Enter date of report])<[Term 3],[OrgPrin]*[rate 2],DateDiff("m",[Enter date of report],[Efdate])<[Term 4],[OrgPrin]*[rate 3],DateDiff("m",[Efdate],[Enter date of report])>[Term 4],[OrgPrin]*[Rate 4])

    Thanks for your help so far!

  6. #6
    KevinMCB is offline Novice
    Windows XP Access 2007
    Join Date
    Nov 2010
    Posts
    29
    Ok - I switched back to my old equation with the IIF statements. I had been using the Switch() function to simplify the equation so I didn't have to use the IIF statements throughout the equation. I went back to the following:

    Current Curtailment: IIf(DateDiff("m",[Efdate],[Enter date of report])<[Term 1],[OrgPrin]*0,IIf(DateDiff("m",[Efdate],[Enter date of report])<[Term 2],[OrgPrin]*[rate 1],IIf(DateDiff("m",[Efdate],[Enter date of report])<[Term 3],[OrgPrin]*[rate 2],IIf(DateDiff("m",[Enter date of report],[Efdate])<[Term 4],[OrgPrin]*[rate 3],IIf(DateDiff("m",[Efdate],[Enter date of report])>[Term 4],[OrgPrin]*[Rate 4])))))


    I am not going to say the thread is solved because I still don't know why one of the expressions results in a numerical value and the other does not. However, I am able to continue on with no problem now that I know which one works best for me.
    Thanks!

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I don't see anything obvious either. You can try to force the issue by wrapping your expression in the CCur() function (or one of the other conversion functions):

    Current Curtailment: CCur(Switch(...))
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    KevinMCB is offline Novice
    Windows XP Access 2007
    Join Date
    Nov 2010
    Posts
    29
    The CCur() function did work on the Switch() function expression. I'll just go with that and call it a day. Thanks for your help!

  9. #9
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Happy to help! Some functions will always return a text value, but Switch() isn't one of them. Something like IIf(Whatever, 123, "none") will do it because of the text in the False argument, but I don't see anything like that in your Switch() function. I guess this is one of those mysteries we'll never know why.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Time sheet setup help
    By xAkademiks in forum Access
    Replies: 1
    Last Post: 10-13-2010, 10:44 PM
  2. Data sheet sub-form
    By cb19366 in forum Forms
    Replies: 1
    Last Post: 03-31-2010, 01:05 AM
  3. Property Sheet issue desperate!!
    By pavarga in forum Reports
    Replies: 5
    Last Post: 07-23-2009, 08:18 PM
  4. Property Sheet Average Function How to do?
    By techexpressinc in forum Reports
    Replies: 2
    Last Post: 06-25-2009, 11:10 AM
  5. Property Sheet issues
    By Tim in forum Access
    Replies: 1
    Last Post: 05-28-2009, 08:35 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums