Criteria syntax for Access

[ernmeister]

I am trying to use the DSUM function in Access. Here is the code as I currently have it.

Private Sub Command0_Click()
Dim rstUpdate As Recordset, dbs As Database
Dim intWeek As Integer, intTotal As Integer
Dim strLine As String
Set dbs = CurrentDb
Set rstUpdate = dbs.OpenRecordset("USED", dbOpenTable)
With rstUpdate
.MoveFirst
Do While Not .EOF
intWeek = !week
strLine = !Line
intTotal = DSum("[MIS]+[pty]", "USED", "[WEEK] = " & intWeek And "[LINE] = " & strLine)
.Edit
!totalused = intTotal
.Update
.MoveNext
Loop
End With
End Sub

When I run the program, I get a run-time error '13' and it states there is a type mismatch. It highlights the line of code using the DSUM function. I'm sure it is due to the code involving ("[LINE] = " & strLine) because it will work it I take it out. But I have to use the line as part of the criteria in order to get the info I need. Anyone know how it should be written?

Thanks,
ern

[HaxSoft]

Hi, is the strLine holding a textual value? If so, try changing this line:

intTotal = DSum("[MIS]+[pty]", "USED", "[WEEK] = " & intWeek And "[LINE] = " & strLine)

to this instead:

intTotal = DSum("[MIS]+[pty]", "USED", "[WEEK] = " & intWeek And "[LINE] = '" & strLine & "'")

Maybe you just forgot the quotes.

Just a suggestion, hope it works.

[ernmeister]

strLine does hold a text value. Unfortunately, the change in code still causes a type mismatch error.

I do appreciate your assistance.

ern

[barrk]

Are you trying to get a sum from a text value? I thought you could only sum up numeric values (int, doubles, etc)

[ernmeister]

I'm trying to find out the total qty of MIS + PTY (TotalUsed) by Week/Line. MIS and PTY are number of boxes being shipped. There are several different types of boxes. Here is an example of the USED table.

Week Box Line Mis Pty TotalUsed
1 blue 1 FCO 2 5
1 blue 2 FCO 4 7
1 blue 1 FFR 5 10
1 blue 2 FFR 6 9

In the above example, if the program was running correctly, I would end up with 18 as the TotalUsed for the first and second records and 30 as the TotalUsed for the third and fourth records.

Hope this makes some sense.

ern