Changing format on calculated field in a query

I have a calculated field in which I am using a switch function. The function 
calculates a bonus discount based on how many units a customer purchases. The 
units purchased are listed in the Quantity field and the Quantity field is 
used in my function. This query is based on one table and the Quantity field 
in that table has a number datatype. 

The switch function works fine but I can't change the formatting to a 
percent format. There is nothing in the Format text box drop down menu in the 
Field Properties dialog box. I tried typing in percent, but that didn't work. 

Can anyone enlighten me?
0
Utf
1/15/2008 4:54:03 PM
access.queries 6343 articles. 1 followers. Follow

2 Replies
1493 Views

Similar Articles

[PageSpeed] 41

Could you post the expression you are using?
When you use the switch function Access has no idea of the type of the data 
that will be returned, so it does not display the format options based on 
the type of data.

You can try forcing the data type (if a number is ALWAYS returned by the 
swtich function)
  CDbl(Switch(...))
should tell Access what type of data is being returned.  However it will 
blow up if switch returns a null or non-number value.

Or you could try
  Format(Switch(...),"Percent")
The problem with this is that the value returned is a STRING and therefore 
you will have trouble doing any math operations or sorting with it.

-- 
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"ibvalentine" <ibvalentine@discussions.microsoft.com> wrote in message 
news:85875B21-A304-4189-AF73-1246D720A3C2@microsoft.com...
>I have a calculated field in which I am using a switch function. The 
>function
> calculates a bonus discount based on how many units a customer purchases. 
> The
> units purchased are listed in the Quantity field and the Quantity field is
> used in my function. This query is based on one table and the Quantity 
> field
> in that table has a number datatype.
>
> The switch function works fine but I can't change the formatting to a
> percent format. There is nothing in the Format text box drop down menu in 
> the
> Field Properties dialog box. I tried typing in percent, but that didn't 
> work.
>
> Can anyone enlighten me? 


0
John
1/15/2008 5:34:07 PM

"John Spencer" wrote:

> Could you post the expression you are using?
> When you use the switch function Access has no idea of the type of the data 
> that will be returned, so it does not display the format options based on 
> the type of data.
> 
> You can try forcing the data type (if a number is ALWAYS returned by the 
> swtich function)
>   CDbl(Switch(...))
> should tell Access what type of data is being returned.  However it will 
> blow up if switch returns a null or non-number value.
> 
> Or you could try
>   Format(Switch(...),"Percent")
> The problem with this is that the value returned is a STRING and therefore 
> you will have trouble doing any math operations or sorting with it.
> 
> -- 
> John Spencer
> Access MVP 2002-2005, 2007-2008
> Center for Health Program Development and Management
> University of Maryland Baltimore County
> ..
> 
> "ibvalentine" <ibvalentine@discussions.microsoft.com> wrote in message 
> news:85875B21-A304-4189-AF73-1246D720A3C2@microsoft.com...
> >I have a calculated field in which I am using a switch function. The 
> >function
> > calculates a bonus discount based on how many units a customer purchases. 
> > The
> > units purchased are listed in the Quantity field and the Quantity field is
> > used in my function. This query is based on one table and the Quantity 
> > field
> > in that table has a number datatype.
> >
> > The switch function works fine but I can't change the formatting to a
> > percent format. There is nothing in the Format text box drop down menu in 
> > the
> > Field Properties dialog box. I tried typing in percent, but that didn't 
> > work.
> >
> > Can anyone enlighten me? 
> 
> 
> The switch function is as follows: 

Switch(Sum([Quantity])>=150,0.15, Sum([Quantity])>=100,0.1, 
Sum([Quantity])>=50,0.05, Sum([Quantity])<50,0))

I enclosed it in the CDbl() function and that did the trick. For some reason 
Access was not interpreting the field type correctly. The switch uses the 
Quantity fields, but it was necessary to force the data type. I did some 
searching on the web and found one explanation made some sense. A guy name 
Mike Ellison suggested that the ODBC driver sometimes misinterprets the field 
type. In these cases, a CDbl function can be used to force the datatype. 

Thanks for your help John. I would have never figured this one out.

0
Utf
1/16/2008 12:20:01 AM
Reply:

Similar Artilces:

TempVars unusable in field default value
Hello, I'm trying to use a temporary variable to keep track of which CSR is inputting data. I have a macro which prompts user for ID code, which is stored in the temp variable TempUser. On a form control default value property, I can use the expression [TempVars]![TempUser], which will populate that user's ID code into the control. However, I cannot use that same expression in the tables field default value property. If I try, when I save the changes to the table, I get the error message "Could not find the field 'TempVars]![TempUser'. " Any ideas why I ca...

send the same e-mail with one or two fields changed.......
I would like to send the same e-mail to many differnet people with one or two fields changed (for example the name of recipient and the date).How canthis be done?? I would also like to be able to save the e-mail and use it again and again. can anyone help cheers john If you have Word installed and it's the same version as Outlook (both 2003, for example), you can do a mail merge between the two. This would allow you to set up the text the way you want it to, and you can save the document for future use. Look at the following page for further information: http://www.slipstick.com/con...

Paragraph formating jumps to defaults.
Hi, We are using Office/Outlook 2007 We have an email signature script that is run when users login, it´s working ok except for two things; If the default paragraph settings in Word 2007 for a user matches those in the signature script, the signature will use the "Base defaults" of Word 2007, that is SpaceAfter = 10 and LineSpacing = "Multiple" with a value of 1,15. So if I change the script to use SpaceAfter=0 and the user has set his/her Word 2007 to use SpaceAfter=0, the result will be SpaceAfter=10. If the script is set to SpaceAfter=0 and the us...

Where is the lasso feature in 2008? (was in formatting palette in 2004)
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel Hi, <br><br>In Excel 2004 in the formatting palette in image, there are different selection marquees and lasso's. Where are these features in 2008 Excel? I can't find them anywhere. <br><br>thnx I just found my answer, the &quot;genius's&quot; at Microsoft / MAC Office decided to kill off all these editing features... <br><br>Over $200 to upgrade to a product with LESS features... I don't think so... unbelievable, I will continue to use 2004 because 2008...

why does sorting change a scatter plot graph?
Why does the way a spread sheet is sorted change the look of a scatter plot graph??? the graph is just a plot of two points, (X, Y) and these two points are definded by two collumns for a given row. The two collumns don't change, and the row all stays together, so why does it change where points are plotted out on the graph when you re-sort it? AndrewT420 - Usually, for an XY (Scatter) chart, with values of X in a column and corresponding values of Y in an adjacent column, for three or more points, Excel assumes (correctly) "Series in Columns." But, when you have only...

Formatting a subtotal line with a macro
I already have in use a macro that runs a sort and subtutal function. I would like to format the subtotal row using visual basic since there is already a macro in place. Hi Look at this: ActiveCell.Font.Bold = True Regards, Per "DJDKAL" <daniel.kalfayan@hotmail.com> skrev i meddelelsen news:A86040B0-B4C4-4E11-A80C-E80132C9710F@microsoft.com... >I already have in use a macro that runs a sort and subtutal function. I > would like to format the subtotal row using visual basic since there is > already a macro in place. This is what I've g...

Changing Prices in HQ.
Hi, I have this little issue. I want to change the put items on promotion using the price wizard using HQ. Unfortunately if I have stores who has differents prices for a same item the wizard do not make the proper change becuase it use the price already stored in the master table. Does anyone saw this issue before? Who was solved?. Thks in advance for your help. Rgds Rodrigo Hi there, The easiest way to look after this is to not change any data on the ITEM in HQ, but to simply do the worksheet for altering the sale price and then send it to the respective stores. Then in the works...

Content of emails is changing without any reason !
Hallo I changed operating system last week. From Win XP to Win 7. Used to work with Outlook Express at full satisfaction. I could transfer most of my emails automatically with export/import features of Microsoft software. But I suddenly discover 1 very big problem (bug ???) I am used to work with several maps, and hereby go to several levels deep. Such as : Saved mails Companyname Projectname Date of action Department Activity Name of patient Different emails So sometimes maps can go several levels deep. When I check ema...

formatting auto replies
Does anyone know of a method that allows outlook 2k2 to format automatic replies regardless of the format of the original message? I setup this automatic response rule that replies with a template, but problem is, when replying to http clients (hotmail or yahoo), the message at the receiver's end has words totally misplaced, all over the page. Any suggestions would be appreciated. ...

Queries and Charts
Does anyone know why the expressions in queries work fine for reports but not charts? ...

Saving toolbar changes
After spending a lot of time to customize a toolbar in Excel 2003, it disappears when opening up another file, or starting the app again. I repeatedly change it, save it as XLB, XLT, save multiple copies in every possible location...but the damn thing always defaults to its own toolbar settings. This makes toolbars almost useless. How can one insist that PPT use YOUR toolbar setting, rather than its own default Thanks. Hi Jeff, If I have a lot of tool bar changes to make, I close all the workbook that are not hidden then unhide my personal.xls from the Window menu. I don't know why...

Pass parameter from FORM to QUERY
I have an append query that I trying to call from a cmd btn...but it prompts me for the parameter(ie QuoteID)... How do I tye it into my call: Dim stQueName As String stQueName = "Quote Query" DoCmd.OpenQuery stQueName, acNormal, acEdit On Apr 13, 12:56 pm, jlt...@hotmail.com wrote: > I have an append query that I trying to call from a cmd btn...but it > prompts me for the parameter(ie QuoteID)... > How do I tye it into my call: > > Dim stQueName As String > stQueName = "Quote Query" > DoCmd.OpenQuery stQueName, acNormal, acEd...

Field Service Calls and Invoices
We use the Service Call modules for repairs in our Stores and have a situation that I'm trying to find a workaround. We have situations where our technicians will pick-up items without prior notification to our dispatchers. All of a sudden we get an invoice and currently we have to track down the charge and then we are forcing a PO into the system from the Service Call in order to properly receive and enter the invoice. Does anyone have other ways to do this and track the information/cost on the Service Call without needing to create a PO after the fact. This is not an applicat...

Change cannot be saved due to sharing violation
Hello I've this message while saving the excel file even if no change ha been done to the file. There is no share on this file (exclusive use) File resides on a network drive It's very disturbing Thanks for your help Vobiscu -- Vobiscu ----------------------------------------------------------------------- Vobiscum's Profile: http://www.msusenet.com/member.php?userid=245 View this thread: http://www.msusenet.com/t-187102186 http://support.microsoft.com/default.aspx?scid=kb;en-us;328170 Thanks for your answer, I will try tomorrow noo Vobiscu -- Vobiscu ----------------...

Changing query execution sequence
Hi all, I got a spreadsheet which would execute a bunch of queries. It's noted that the queries are executing in the sequence of when it was added to the spreadsheet. Does anyone out there know of a way to switch the order without deleting and recreating them? Thanks! Wing ...

Format for credit card numbers
I've tried a custom format for entering credit card numbers (four groups of four digits, separated by dashes: xxxx-xxxx-xxxx-xxxx. I have tried to use ####-####-####-#### and 0000-0000-0000-0000 but each of these causes the last digit to change to zero. So if I enter 5415779800902512 I get 5415-7798-0090-2510. Anybody already solved this problem? -- Schmacker ------------------------------------------------------------------------ Schmacker's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28041 View this thread: http://www.excelforum.com/showthread.php?th...

Forumla to calculate a percentage
I have a column of figures that I need to calculate that if the figure in column A is Transport costs to us is �100 and column B is the price sold to the customer is �25.00, i need the third column to work out < 72% Theoretical Transport Recovery in other words if the value in column B is <72% mark it red Like wise for fuel is the value in column B is <132% mark it red Try as I might I cannot get it to work it out any ideas Julie, This formula gives the correct percentage... =(B1-A1)/A1 Format the formula cell with this custom number format... 0%;[Red]-0% If you don'...

Automatic changes in cells
Hi for some reason I now have to save my work for any formlas etc to change when I update a worsheet, how can I stop this as it is a pain and sometimes I need to do changes to see how they work before saving the work. Many thanks Click on Tools | Options | Calculation tab and set to Automatic calculation, as it is probably set to Manual. You can press F9 to force a recalculation under a manual setting. Make sure you save the file with the Automatic setting, to avoid it happening next time. Hope this helps. Pete On Feb 1, 11:42=A0am, Office 2004 Test Drive User <heepenm...@yahoo.co.u...

Change the text of a shape rather than its master
Hi, I build custom masters by mixing two general shapes, say square and circle together, and have text on both the shapes. But after I drop an instance of the master into a page, I cannot modify the text of the instance. To do so, I need to modify the text on the master, which is non-sense for me. How to change the text of a shape without modifying its master? Thanks! How are you doing this? By code or by the UI? Are you grouping the shapes? If you drag two shapes to the stencil, it will group the shapes. So instead of a square and a circle you have three shapes. A Square, Circle and the...

cell colour change when set markers are reached
i need to get a cell to change colour when markers are reached eg a qualification lasts 12 months. what i want to do is have the cell change from yellow to orange to red as the expiry date gets closer. If column A contains expiry dates then select column A, Formats>Conditional Formatting>formula1: =DATEDIF(TODAY(),A1,"m")<1 red for 1 month Click Add button, formula2: =DATEDIF(TODAY(),A1,"m")<2 orange for 2 month Click Add button, formula3: =DATEDIF(TODAY(),A1,"m")<3 yellow for 3 month Adjust number of months as you like! Regards,...

Varying format
Is there any way to format a cell differently depending on the number of decimal places typed in? What I need is a minimum of 2dp but 3 if the typed entry matches. eg 1.2 appears as 1.20 and 1.234 appears as typed. TIA Ian Hi! Try formatting the cell with a custom number format such as 0.00## Al -- Alf ----------------------------------------------------------------------- AlfD's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=478 View this thread: http://www.excelforum.com/showthread.php?threadid=27494 "AlfD" <AlfD.1f5q6o@excelforum-nospam.c...

Changing ip address of exchange server #2
Hi, I have a back-end server and a smtp server in DMZ. I want to change ip address of back-end server. are there any issues? all incoming and outgoing emails are going via smart host. Hi, No issues at all as long as you remember to change all the references to this server in your firewall, SMTP scanner etc. Leif "Jack Dorson" <JackDorson@discussions.microsoft.com> wrote in message news:FE5927A1-D20D-4C6B-991F-2E1EFD19434D@microsoft.com... > Hi, > > I have a back-end server and a smtp server in DMZ. > > I want to change ip address of back-end server. are ...

Setting appointment start & end time increments format
In Outlook 2002, I can easily set the Day View to display the time increments in 15 minute blocks, but on the Appointments form, the Start and End Times available on the drop-down menus, respectively, are in 30 minute increment blocks only. How can I set the format for the appointment page to have the Start and End Times for appointments have increment time blocks of 15 minutes? Thanks! My direct email address is: solutioncounselor@comcast.net . If you set your appointments in 15 minute blocks Outlook will start to offer that. It takes a few uses to work. -- Nikki Peterson [MVP ...

Calculating on alphabetic cell content
Hi, A selection of 4 different letters in a column representing different values to be used in a formula shall be run through. The calculated result of each cell in the column shall be placed in the cell next to the read one that holds the letter. Thanks in advance. Hi i think you're after the COUNTIF function with your column of letters in A1:A100 and the letter you're interested in in C1 then in D1 =COUNTIF(A1:A100,C1) this will count the number of times the value in C1 occurs in your range. If this isn't what you're after, could you type out a few examples of your ...

multiple iterations of same query
Running Access 2003 on Vista. I have a query that chooses 15 records at random. This works fine. What I want to do is run the same query for approx 110 different users, each with their own randomly selected 15 records. Obviously, I want to avoid manually running the same query that many times. How can I get around this? Post your query SQL. -- Build a little, test a little. "Nathan" wrote: > Running Access 2003 on Vista. > > I have a query that chooses 15 records at random. This works fine. > > What I want to do is run the same query fo...