时间戳

首页 > 解决方案 > Microsoft Access 字段添加 1

ms-access - Microsoft Access 字段添加 1

问题描述

我有一个奇怪的错误，我的货币字段为每个条目添加了 1 美元。我将值存储在表中，并使用查找来获取这些值

ReducedLunch = DLookup("[Cost]", "LunchCost", "[ID]=2")
NormalLunch = DLookup("[Cost]", "LunchCost", "[ID]=1")
Milk = DLookup("[Cost]", "LunchCost", "[ID]=4")
NoLunch = DLookup("[Cost]", "LunchCost", "[ID]=5")

然后我有一些VB代码将适当的值写入数据库字段......

If (rs!TodaysLunch = "Lunch" And rs!FreeLunch = False And rs!ReducedLunch = False) Then
    DailyCost = NormalLunch
    rs.Edit
    rs!TodaysCost = DailyCost
    rs!Balance = rs!Balance - DailyCost
    rs.Update
End If

如果我在 Endif 之前放置一个 msgbox 命令以显示 DailyCost 值，则此时它会显示正确的值，但是当我实际将数据写回表时

DoCmd.RunSQL "INSERT INTO Lunch (StudentID, DateOfLunch, TypeOfLunch, Cost) SELECT [ID],[TodaysDate],[TodaysLunch],[TodaysCost] FROM Students"

TodaysCost 增加了额外的美元。我已经检查并仔细检查了所有内容，但找不到它从哪里获得这个神秘价值。我尝试将 DailyCost 设置为零（它被定义为货币），但它仍然写回值 + 1。

这是我的完整代码，末尾附近的 msgbox 命令是添加 1 的位置...

Dim DailyCost As Currency
Dim ReducedLunch, NormalLunch, Milk, NoLunch As Variant
Dim rs As Recordset
Dim db As Database


DailyCost = 0

Set db = CurrentDb()
Set rs = db.OpenRecordset("SELECT * FROM Students")

    ReducedLunch = DLookup("[Cost]", "LunchCost", "[ID]=2")
    NormalLunch = DLookup("[Cost]", "LunchCost", "[ID]=1")
    Milk = DLookup("[Cost]", "LunchCost", "[ID]=4")
    NoLunch = DLookup("[Cost]", "LunchCost", "[ID]=5")


Do Until rs.EOF = True
    'Check for free or reduced and get price

    If (rs!TodaysLunch = "Lunch" And rs!FreeLunch = True) Then
        DailyCost = 0
        rs.Edit
        rs!TodaysCost = DailyCost
        rs!Balance = rs!Balance - DailyCost
        rs.Update
    End If

    If (rs!TodaysLunch = "Lunch" And rs!ReducedLunch = True) Then
        DailyCost = ReducedLunch
        rs.Edit
        rs!TodaysCost = DailyCost
        rs!Balance = rs!Balance - DailyCost
        rs.Update
    End If

    If (rs!TodaysLunch = "Lunch" And rs!FreeLunch = False And rs!ReducedLunch = False) Then
        DailyCost = NormalLunch
        rs.Edit
        rs!TodaysCost = DailyCost
        rs!Balance = rs!Balance - DailyCost
        rs.Update
    End If

    If (rs!TodaysLunch = "Milk") Then
        DailyCost = Milk
        rs.Edit
        rs!TodaysCost = DailyCost
        rs!Balance = rs!Balance - DailyCost
        rs.Update
    End If

    If (rs!TodaysLunch = "Lunch XtraMilk") Then
        ElseIf (rs!ReducedLunch = True) Then
         DailyCost = ReducedLunch + Milk * 2
        ElseIf (rs!FreeLunch = True) Then
            DailyCost = FreeLunch + Milk * 2
        ElseIf (rs!FreeLunch = False And rs!ReducedLunch = False) Then
            DailyCost = NormalLunch + Milk * 2
        rs.Edit
        rs!TodaysCost = DailyCost
        rs!Balance = rs!Balance - DailyCost
        rs.Update
    End If

    If (rs!TodaysLunch = "No Lunch") Then
        DailyCost = 0
        rs.Edit
        rs!TodaysCost = DailyCost
        rs!Balance = rs!Balance - DailyCost
        rs.Update
    End If

    'Set date to today
    rs.Edit
    rs!TodaysDate = Date
    MsgBox (rs!TodaysCost) 'Point where 1 is added
    rs.Update

   rs.MoveNext


Loop
    rs.Close
    Set rs = Nothing

    DoCmd.RunSQL "INSERT INTO Lunch (StudentID, DateOfLunch, TypeOfLunch, Cost) SELECT [ID],[TodaysDate],[TodaysLunch],[TodaysCost] FROM Students"

标签： ms-accessvbafield

解决方案

确保 TodaysCost 不是长整数。

推荐阅读