[ 本帖最后由 cuxun 于 2008-1-3 23:40 编辑 ]作者: iamee 时间: 2007-12-31 00:07
Dim b
Dim str As String
Dim M As Integer
Dim N As Integer
M = InputBox("请输入一整数", , 87)
b = Split("3 5 8 12 17 23 28 35 46", " ")
For i = 0 To 8
If M - N > b(i + 1) Then
N = N + b(i)
str = str & b(i) & " "
Else
str = str & M - N
MsgBox str
Exit Sub
End If
Next i作者: wjb5645 时间: 2007-12-31 11:51
非常感谢 iamee
能否再帮我改写成函数,我想在查询中用,有点象计算贷款逾期计息的问题, 3 5 8 12 17....这一数列代表(因常要变动也就放在表中)贷款天数段利息不同.
如:
某客户贷了一笔款总 87天,前3天的利息是1分,第4天到第5天的利息是1.5分,第6天到第8天的利息是1.8分, 类推..........
因此是需要把众客户的贷款天数进行分割.作者: wjb5645 时间: 2007-12-31 12:19
再说明:
要求如87天查询出来的是7条记录作者: iamee 时间: 2007-12-31 15:33
Public Function strReturn(M As String, N As Integer) As String'M是数列，N是贷款天数
Dim S As String
Dim B
Dim SumM As Integer
S = Val(M)
B = Split(M)
For i = 0 To Len(M) - Len(S)
SumM = SumM + B(i)
If N > SumM Then
strReturn = strReturn & B(i) & " "
Else
strReturn = strReturn & N - (SumM - B(i))
Exit For
End If
Next i
End Function
你上传个附件，大家帮你试试作者: wjb5645 时间: 2007-12-31 21:32
谢谢!!!
现将附件上传,请帮忙看看!
[attach]27592[/attach]作者: wwwwa 时间: 2008-1-3 09:17
SELECT a.*, b.截止, b.天利息
FROM 查询1 AS a LEFT JOIN 查询2 AS b ON b.lj<=a.欠天数;
union
SELECT a.*,(select 欠天数-sum(截止) from 查询2 where lj<=[欠天数]) AS 表达式1, 0
FROM newtt AS a;
查询2:
SELECT (select sum(截止) from 计息区段 where id<=a.id) AS lj, *
FROM 计息区段 AS a;
newtt:
SELECT 出库单明细.供应商ID, 出库单明细.增票日期, Sum(出库单明细.金额) AS 小计, Date()-[增票日期] AS 欠天数
into newtt FROM 出库单明细
GROUP BY 出库单明细.供应商ID, 出库单明细.增票日期, Date()-[增票日期]
HAVING (((出库单明细.供应商ID)=30))
ORDER BY 出库单明细.增票日期 DESC
WITH OWNERACCESS OPTION;
上传附件:
[attach]27616[/attach]作者: wwwwa 时间: 2008-1-3 16:17
将我的代码修改一下即可：
SELECT a.*, b.截止 AS 区段, b.天利息 AS 息段
FROM 查询1 AS a LEFT JOIN 查询2 AS b ON b.lj<=a.欠天数;
UNION SELECT a.*,(select 欠天数-sum(截止) from 查询2 where lj<=[欠天数]) AS 表达式1, 0.0015
FROM newtt AS a
查询2:
SELECT (select sum(截止) from 计息区段 where id<=a.id) AS lj, *
FROM 计息区段 AS a;
newtt:
SELECT 出库单明细.供应商ID, 出库单明细.增票日期, Sum(出库单明细.金额) AS 小计, Date()-[增票日期] AS 欠天数
into newtt FROM 出库单明细
GROUP BY 出库单明细.供应商ID, 出库单明细.增票日期, Date()-[增票日期]
HAVING (((出库单明细.供应商ID)=30))
ORDER BY 出库单明细.增票日期 DESC
WITH OWNERACCESS OPTION;
iamee
您可能还没理解的的意思,是分时段计息:
