Microsoft Access数据库的问题 [英] Question For Microsoft Access Data Base
问题描述
Microsoft Access数据库的问题
--------------------------------- -----------------------------------------------
我正在尝试创建一个能够执行以下操作的公式：
如果A1小于或等于499，则交付B1 * 1，如果A1等于或小于999但超过并包括500，则提供B1 * 2，如果A1等于或小于1499但超过并包括1000，则提供B1 * 3，并且SO ON .....
我已经阅读了帖子，他们不工作
= Int（A1 / 500）* B1不工作，它为500,1000,1500等提供了错误的值
+ Int（A1 / 500 + 1） - （MOD（A1,500）= 0不工作，我不能得到访问除了这个公式之外。它不会保存。它会变回控制源中的任何内容。
我不是这方面的专家，而且我能说的第三个响应看起来很像就像Excel的答案而不是Access，我无法弄明白。创建表是第一部分是关于如何做到的。
该帖子如下：
[此后续内容发布到microsoft.public.excel.worksheet.functions
with电子邮件副本=？Utf-8？B？QW1hbg ==？=。
请使用新闻组进一步讨论。]
三种可能性，除了
其他人已经讨论过的选项之外，还有以下内容。没有一个选项要求门槛等于间隔，也不要求返回值为1,2,3等。
1）使用公式= B1 *（IF（A1 <= 0,0，IF（A1 <= 500,1，IF（A1 <= 1000,2,3））））
这可能是最简单的理解，但它有两个限制。
首先，IF语句只能嵌套7个深度。
有限制的方法，但它们只会使问题进一步复杂化。其次，
阈值和返回值隐藏在公式中，并且
不会立即看到工作表。
）创建一个表格，例如：
FG
2 0 1
3 500 2
4 1000 3
然后，使用公式
Question For Microsoft Access Data Base
--------------------------------------------------------------------------------
I am Trying to create a single formula that would do the following:
If A1 is less then or equal to 499, deliver B1*1, if A1 is equal to or less than 999 but more than and including 500, deliver B1*2, If A1 is equal to or less than 1499 but more than and including 1000, deliver B1*3, AND SO ON.....
I have read the Posts on this and they don''t Work
=Int(A1/500)*B1 Dont work, It gives the wrong values for 500, 1000, 1500, etc
+Int(A1/500+1)-(MOD(A1,500)=0 Dont work, I cant get Access to except this formula. It wont save. It changes back to whatever was in the Control Source.
I am not an expert at this and the third response from what I can tell looks like an answer for Excel and not Access, Well I cant figure it out. Creating the Table which is the first part makes no since on how to do it.
That post was as follows:
[This followup was posted to microsoft.public.excel.worksheet.functions
with an email copy to =?Utf-8?B?QW1hbg==?=.
Please use the newsgroup for further discussion.]
Three possibilities, in addition to the option already discussed by
others, are below. None of the options requires the thresholds to be
equally spaced, nor that the return values be 1,2,3, etc.
1) Use the formula =B1*(IF(A1<=0,0,IF(A1<=500,1,IF(A1<=1000,2,3))))
This might be the easiest to understand but it has two limitations.
First, IF statements can be nested only 7 deep. There are ways around
the limitation, but they just complicate the matter further. Second,
the thresholds and the returned values are hidden in the formula and
not immediately obvious from looking at the worksheet.
2) Create a table such as:
F G
2 0 1
3 500 2
4 1000 3
And, use the formula
推荐答案
我倾向于使用Select Case结构在标准模块中创建一个公共函数，但是你必须完成AND SO ON。在开发代码之前。以后更容易理解和维护。
I would tend to create a Public Function in a standard module using a Select Case structure but you would have to finish the "AND SO ON" before code could be developed. It would be easier to understand and maintain later.
谢谢你的回复。你在告诉我什么我不能使公共功能跨越表格
Thanks For your responce. What you are telling me. I Cant seam to get the Public Funtion to cross tables
如果您在表单或查询中使用此功能，我认为没有理由跨表。 A和B在不同的表中吗？你有没有加入查询？
我想到的函数将取两个值并返回一个。
If you are using this function in a form or query, I see no reason to cross tables. Are A and B in different tables? Do you have them joined in a query?
The Function I had in mind would take the two values and return one.
这篇关于Microsoft Access数据库的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
