在同一张表中,先筛选出符合条件的某条记录,比如收费号,MoneyID=123,这样可以得到用户号为UserNo=123456
的记录,然后,根据UserNo查询该用户的交费情况,筛选出相应的记录,如何用DAO或ADO实现呢?
以下是一个函数代码,但编译有问题,请高手指正:
Public Function GetPrintData(ByRef r As TReceipt, ID As Long) As Boolean
Dim dsStation As Recordset, dsIn As Recordset, dsUser As Recordset, dsUserType As Recordset, dsSearch, dsTemp As Recordset
Dim s As String, b As String, u As String, Room As String
Dim ret As TReceipt, MaxWatchCount As Single
Dim findFlag As Boolean
Dim UserNo As String, InputDate As Date
Dim BankName As String
Dim UseGasMoney As Currency '气费总额
Dim SendedMoney As Currency '已交气费
'add
Dim lMoney1, lMoney2 As Currency
Dim UserType, days As Integer
Dim Rate As Single
Dim Wdate, Ndate As Date
'Dim db As Datebase
Dim qryDelay As QueryDef
Dim str, str1, str2 As String
Dim Number(0 To 9) As Currency
Dim bmSearch, i As Integer
Set dsStation = gDb.OpenRecordset("站点", dbOpenTable)
Set dsIn = gDb.OpenRecordset("抄表数据", dbOpenTable)
Set dsUser = gDb.OpenRecordset("用户数据", dbOpenTable)
Set dsUserType = gDb.OpenRecordset("用户类型", dbOpenTable)
Set dsSearch = gDb.OpenRecordset("抄表数据", dbOpenTable) 'Add
dsIn.Index = "PrimaryKey"
dsStation.Index = "PrimaryKey"
dsUser.Index = "PrimaryKey"
dsUserType.Index = "PrimaryKey"
dsSearch.Index = "PrimaryKey" 'Add
findFlag = True
dsIn.Seek "=", ID
If dsIn.NoMatch Then
dsIn.Close
dsStation.Close
dsUser.Close
dsUserType.Close
GetPrintData = False
Exit Function
End If
UserNo = dsIn("用户编号")
r.UserNo = UserNo
s = Mid(UserNo, 1, 3)
b = Mid(UserNo, 4, 3)
u = Mid(UserNo, 7, 2)
Room = Mid(UserNo, 9, 4)
InputDate = dsIn("录入日期")
'Set db = Workspace(0).OpenDatabse("gDb.name", False)
str1 = "Select * From 抄表数据 Where 抄表数据.用户编号="
str2 = "And 抄表数据.结算标志=False"
str = str1 & UserNo & str2
Set qryDelay = gDb.CreateQueryDef("cQueryDef", str)
With qryDelay 'qryDelay.sql = str
Set dsSearch = .OpenRecordset(dbOpenSnapshot)
End With
dsStation.Seek "=", s
If dsStation.NoMatch Then
dsIn.Close
dsStation.Close
dsUser.Close
dsUserType.Close
GetPrintData = False
Exit Function
End If
r.Address = dsStation("站名") & Val(b) & "栋" & Val(u) & "单元" & Val(Room) & "室"
dsUser.Seek "=", s, b, u, Room
r.Name = dsUser("姓名")
dsUserType.Seek "=", dsUser("类型")
If dsUserType.NoMatch Then
dsIn.Close
dsStation.Close
dsUser.Close
dsUserType.Close
GetPrintData = False
Exit Function
End If
MaxWatchCount = dsUserType("表具最大计数")
r.inDate = Format(dsIn("录入日期"), "YYYY年m月d日")
r.CurNum = Format(dsIn("抄见数"), "0.000")
r.EAmount = dsIn("额定用气量")
'add/
lMoney2 = 0
UserType = dsUser("类型")
Rate = gUserType(UserType).Rate
dsSearch.MoveLast
For i = dsSearch.RecordCount To 1 Step -1
lMoney1 = dsSearch("应交金额")
Ndate = Date
Wdate = dsSearch("录入日期")
days = DateDiff("d", Wdate, Ndate) - 10
If days > 0 Then
lMoney2 = lMoney2 + days * Rate * lMoney1
End If
dsSearch.MovePrevious
Next
'/
的记录,然后,根据UserNo查询该用户的交费情况,筛选出相应的记录,如何用DAO或ADO实现呢?
以下是一个函数代码,但编译有问题,请高手指正:
Public Function GetPrintData(ByRef r As TReceipt, ID As Long) As Boolean
Dim dsStation As Recordset, dsIn As Recordset, dsUser As Recordset, dsUserType As Recordset, dsSearch, dsTemp As Recordset
Dim s As String, b As String, u As String, Room As String
Dim ret As TReceipt, MaxWatchCount As Single
Dim findFlag As Boolean
Dim UserNo As String, InputDate As Date
Dim BankName As String
Dim UseGasMoney As Currency '气费总额
Dim SendedMoney As Currency '已交气费
'add
Dim lMoney1, lMoney2 As Currency
Dim UserType, days As Integer
Dim Rate As Single
Dim Wdate, Ndate As Date
'Dim db As Datebase
Dim qryDelay As QueryDef
Dim str, str1, str2 As String
Dim Number(0 To 9) As Currency
Dim bmSearch, i As Integer
Set dsStation = gDb.OpenRecordset("站点", dbOpenTable)
Set dsIn = gDb.OpenRecordset("抄表数据", dbOpenTable)
Set dsUser = gDb.OpenRecordset("用户数据", dbOpenTable)
Set dsUserType = gDb.OpenRecordset("用户类型", dbOpenTable)
Set dsSearch = gDb.OpenRecordset("抄表数据", dbOpenTable) 'Add
dsIn.Index = "PrimaryKey"
dsStation.Index = "PrimaryKey"
dsUser.Index = "PrimaryKey"
dsUserType.Index = "PrimaryKey"
dsSearch.Index = "PrimaryKey" 'Add
findFlag = True
dsIn.Seek "=", ID
If dsIn.NoMatch Then
dsIn.Close
dsStation.Close
dsUser.Close
dsUserType.Close
GetPrintData = False
Exit Function
End If
UserNo = dsIn("用户编号")
r.UserNo = UserNo
s = Mid(UserNo, 1, 3)
b = Mid(UserNo, 4, 3)
u = Mid(UserNo, 7, 2)
Room = Mid(UserNo, 9, 4)
InputDate = dsIn("录入日期")
'Set db = Workspace(0).OpenDatabse("gDb.name", False)
str1 = "Select * From 抄表数据 Where 抄表数据.用户编号="
str2 = "And 抄表数据.结算标志=False"
str = str1 & UserNo & str2
Set qryDelay = gDb.CreateQueryDef("cQueryDef", str)
With qryDelay 'qryDelay.sql = str
Set dsSearch = .OpenRecordset(dbOpenSnapshot)
End With
dsStation.Seek "=", s
If dsStation.NoMatch Then
dsIn.Close
dsStation.Close
dsUser.Close
dsUserType.Close
GetPrintData = False
Exit Function
End If
r.Address = dsStation("站名") & Val(b) & "栋" & Val(u) & "单元" & Val(Room) & "室"
dsUser.Seek "=", s, b, u, Room
r.Name = dsUser("姓名")
dsUserType.Seek "=", dsUser("类型")
If dsUserType.NoMatch Then
dsIn.Close
dsStation.Close
dsUser.Close
dsUserType.Close
GetPrintData = False
Exit Function
End If
MaxWatchCount = dsUserType("表具最大计数")
r.inDate = Format(dsIn("录入日期"), "YYYY年m月d日")
r.CurNum = Format(dsIn("抄见数"), "0.000")
r.EAmount = dsIn("额定用气量")
'add/
lMoney2 = 0
UserType = dsUser("类型")
Rate = gUserType(UserType).Rate
dsSearch.MoveLast
For i = dsSearch.RecordCount To 1 Step -1
lMoney1 = dsSearch("应交金额")
Ndate = Date
Wdate = dsSearch("录入日期")
days = DateDiff("d", Wdate, Ndate) - 10
If days > 0 Then
lMoney2 = lMoney2 + days * Rate * lMoney1
End If
dsSearch.MovePrevious
Next
'/
解决方案 »
- 【重要求救】代码如下:ftp下载问题
- 至少一个参数没有被指定值
- VB中怎么动态创建access数据库以及数据库表
- ListView 如果使用 Report 样式,能否在每个Item 中显示一个 Icon ?
- 用SETUP FACTORY怎么样在安装时将程序一起添加到启动组里面.
- 实时错误'-2147467259(80004005)',这是什么意思啊.
- 一个关于数据库游标的问题,大家帮帮我这个可怜的孩子吧
- 文件crtowords_en.dll是用来干吗的?
- 怎样能把插入“备注”型字段的文档再以原样(有段落、缩进)地在textbox中呈现。
- 高手告诉我文本框和LABEL怎么才能顶端对齐
- 使用MSHFLEXGRID时,如何使选中的一行的固定列之中显示一个小三角或箭头?
- 工资是不是太低?
If (dsIn("抄见数") - dsIn("用气量") >= 0) Then
r.lastNum = Format((dsIn("抄见数") - dsIn("用气量")), "0.000")
Else
r.lastNum = Format(MaxWatchCount + (dsIn("抄见数") - dsIn("用气量")), "0.000")
End If
r.Amount = Format(dsIn("用气量"), "0.000")
r.Money = Format(lMoney1 + lMoney2, "0.00")
If dsIn("用气量") > dsIn("实际额定用气量") Then
UseGasMoney = Format(dsIn("单价") * dsIn("实际额定用气量") + _
dsIn("单价2") * (dsIn("用气量") - dsIn("实际额定用气量")), "0.00") + lMoney2
Else
UseGasMoney = Format(dsIn("单价") * dsIn("用气量"), "0.00") + lMoney2
End If
SendedMoney = UseGasMoney - dsIn("应交金额")
If dsIn("预交款") - UseGasMoney > 0 Then
r.Remain = dsIn("预交款") - UseGasMoney
Else
r.Remain = ""
End If r.Price = Format(dsIn("单价"), "0.00")
r.AddPrice = Format(dsIn("单价2"), "0.00")
If dsIn("用气量") > dsIn("额定用气量") And dsIn("单价") <> dsIn("单价2") Then
If r.Price <> r.AddPrice Then
r.CurNum = r.lastNum + dsIn("额定用气量")
r.RlastNum = r.lastNum + dsIn("额定用气量")
r.RCurNum = Format(dsIn("抄见数"), "0.000")
r.SMoney = dsIn("单价") * dsIn("额定用气量")
r.rMoney = dsIn("单价2") * (dsIn("用气量") - dsIn("额定用气量"))
End If
End If
If dsStation("银行") = "" Or dsStation("银行") = "0" Then
BankName = "请交费"
Else
BankName = "请去" & dsStation("银行") & "交费"
End If
If dsIn("预交款") <= 0.001 Then
r.Re = BankName
If dsIn("结算标志") = False Then
Select Case (dsUser("收费类型"))
Case 2:
r.Re = "本月气费" & Format(dsIn("应交金额"), "0.00") & ",请到招商银行存款交费。"
Case 1:
r.Re = "本月气费" & Format(dsIn("应交金额"), "0.00") & ",请到工商银行存款交费。"
Case 0:
If lMoney2 <> 0 Then
r.Re = "本月气费" & UseGasMoney & ",请交费。" & "其中滞纳金" & lMoney2 & "元。"
Else: r.Re = "本月气费" & UseGasMoney & ",请交费。"
End If
End Select
Else
Select Case (dsUser("收费类型"))
Case 2:
r.Re = "本月气费" & UseGasMoney & ",已由招商银行托收。"
Case 1:
r.Re = "本月气费" & UseGasMoney & ",已由工商银行托收。"
Case 0:
r.Re = "本月气费" & UseGasMoney & ",已交清。"
End Select
End If
Else
If dsIn("结算标志") = False Then
Select Case (dsUser("收费类型"))
Case 2:
r.Re = "气费" & UseGasMoney & "," _
& Format(SendedMoney, "0.00") & "已扣除,应交款" _
& Format(dsIn("应交金额"), "0.00") & ",请到招商银行存款交费。"
Case 1:
r.Re = "气费" & UseGasMoney & "," _
& Format(SendedMoney, "0.00") & "已扣除,应交款" _
& Format(dsIn("应交金额"), "0.00") & ",请到工商银行存款交费。"
Case 0:
r.Re = "气费" & UseGasMoney & "," _
& Format(SendedMoney, "0.00") & "已扣除,应交款" _
& Format(dsIn("应交金额"), "0.00") & "," & BankName
End Select
Else
Select Case (dsUser("收费类型"))
Case 2:
If UseGasMoney <= dsIn("应交金额") Then
r.Re = "气费" & UseGasMoney & "已扣除,应交款" _
& Format(dsIn("应交金额"), "0.00") & ",已由招商银行托收。"
Else
r.Re = "气费" & UseGasMoney & "," _
& Format(SendedMoney, "0.00") & "已扣除,应交款" _
& Format(dsIn("应交金额"), "0.00") & ",已由招商银行托收。"
End If
Case 1:
If UseGasMoney <= dsIn("应交金额") Then
r.Re = "气费" & UseGasMoney & "已扣除,应交款" _
& Format(dsIn("应交金额"), "0.00") & ",已由工商银行托收。"
Else
r.Re = "气费" & UseGasMoney & "," _
& Format(SendedMoney, "0.00") & "已扣除,应交款" _
& Format(dsIn("应交金额"), "0.00") & ",已由工商银行托收。"
End If
Case 0:
If UseGasMoney <= dsIn("应交金额") Then
r.Re = "气费" & UseGasMoney & "已扣除,应交款" _
& Format(dsIn("应交金额"), "0.00") & ",已交清。"
Else
r.Re = "气费" & UseGasMoney & "," _
& Format(SendedMoney, "0.00") & "已扣除,应交款" _
& Format(dsIn("应交金额"), "0.00") & ",已交清。"
End If
End Select
End If
End If
r.InNo = dsIn("录入员工号")
r.GetNo = dsIn("抄表员工号")
dsIn.Close
dsStation.Close
dsUser.Close
dsUserType.Close 'add
dsSearch.Close
qryDelay.Close
'Set db = Nothing
GetPrintData = True
End Function
Set dsSearch = .OpenRecordset(dbOpenSnapshot)
str2 = "And 抄表数据.结算标志=False"
str = str1 & UserNo & str2
Set dsSearch = gDb.OpenRecordset(str,dbOpenSnapshot)
这句查询是否有问题呢?