DSum field type "Currency", Data type mismatch

[Budatlitho]

The "Order amount" field Data Type is defined in 'rs' as "Currency"
The "amountdue" field Data Type is defined in 'rs1' as "Currency"
Code fails at the last line, resulting with "Data Type mismatch"


I've tried

 amountdue = Format(DSum("[Order amount]", "Jumbula", _
            "[Participant Member ID (Seven digits)] =""" & eid & """"), "Currency"

in addition to

amountdue = DSum("[Order amount]", "Jumbula", _
            "[Participant Member ID (Seven digits)] =""" & eid & """")

DoCmd.SetWarnings False
Dim db As Database
Dim rs As Recordset
Dim rs1 As Recordset
Dim rs2 As Recordset
Dim strSql As String
Dim firstname As String
Dim lastname As String
Dim name As String
Dim amountdue As Currency
Dim amountpaid As Currency
Dim registrationfee As Currency
Dim creditcardfee As Currency
Dim discount As Currency
Dim balance As Currency
Dim eid As String
Dim count As Integer
Set db = CurrentDb
db.Execute "DELETE * FROM Symposium_member"
'strSql = "DROP TABLE [JumbulaTemp];"
'DoCmd.RunSQL (strSql)
db.Execute "CREATE TABLE JumbulaTempMember " & _
"(eid VARCHAR, firstname VARCHAR, lastname VARCHAR, name VARCHAR, amountdue CURRENCY, amountpaid CURRENCY, registrationfee CURRENCY, creditcardfee CURRENCY, discount CURRENCY, balance CURRENCY);"
RefreshDatabaseWindow
Set rs = db.OpenRecordset("Jumbula")
Set rs1 = db.OpenRecordset("JumbulaTempMember")
rs.MoveFirst
count = 0
Do While Not rs.EOF
    If rs.EOF Then Exit Do
    count = count + 1
    eid = rs("[Participant Member ID (Seven digits)]")
    Debug.Print "eid = " & eid;
    firstname = rs("[Participant First name]")
    lastname = rs("[Participant Last name]")
    name = firstname & " " & lastname
    Debug.Print "name = " & name
    Debug.Print "Order amount= " & rs("Order amount")
    amountdue = DSum("[Order amount]", "Jumbula", _
            "[Participant Member ID (Seven digits)] =""" & eid & """")

What am I missing?

THANKS in advance!

[jojowhite]

when you use Format() function, the result will be a String, not Number.
so you do not need to use Format() on Dsum().

also you declared the variable amountDue as Currency (Number).

[June7]

[Participant Member ID (Seven digits)] is a text field?

[davegri]

June7, I wondered about that too.
OP did not include what the result was used for, but the logic seems strange.

Read sequentially through table Jumbula. For each record, add up the [order amount] in all records in the same table that match the ID of the current record. Advance and do same. How can the results be useful? The amountdue will be equal for all records with same ID.

[Budatlitho]

I tried with and without Format()
Cannot variable amountdue be declared as Currency?

[davegri]

I tried with and without Format()
Cannot variable amountdue be declared as Currency?

Well, try more stuff. These ideas are to diagnose the problem, not directly fix it.

Change it to double and see what happens.
Try the DSum without the criteria to see if that's the problem.
Change DSum to DCount to check if your syntax is correct.

Gotta keep on truckin' to get anywhere.

[Budatlitho]

... still exploring field types...

    Debug.Print "Table name is: " & MyTableDef.name;
    Debug.Print
    For i = 0 To MyTableDef.Fields.count - 1           ' Step through fields in current table
        ' Update Table Definitions table
        TableDefinitions.AddNew      ' Create new record
        TableDefinitions![Table Name] = MyTableDef.name
        TableDefinitions![Field Name] = MyTableDef.Fields(i).name
        TableDefinitions![Data Type] = TypeName(MyTableDef.Fields(i).Type) 'example was wrong
        TableDefinitions![Field Size] = MyTableDef.Fields(i).Size
        TableDefinitions![Field Description] = MyTableDef.Fields(i).Properties("Description")
     'Add to immediate window
        Debug.Print "Field Name is: " & MyTableDef.Fields(i).name _
        & " Data type is: " & TypeName(MyTableDef.Fields(i).Type) _
        & " Field size is: " & MyTableDef.Fields(i).Size

Immediate window shows:

Table name is: Jumbula
Field Name is: Program Data type is: Integer Field size is: 255
Field Name is: Schedule start/end date Data type is: Integer Field size is: 255
Field Name is: Tuition name Data type is: Integer Field size is: 255
Field Name is: Balance Data type is: Integer Field size is: 8


Table Design shows


All fields show Integer, not what was selected in Table Design.

I'm trying to find out why they are different.

[pbaldy]

Try declaring the amountdue variable as a Variant. I'd also point out that neither = "[Order amount]" nor "[Participant Member ID (Seven digits)]" are shown in the table you showed above.

[June7]

The TypeName() function is not returning correct type name because this is not valid use. This function returns the type for a VBA declared variable. You are using it on constants returned by the Type property, which are all integer numbers. Remove the function and output the numbers. I am not finding any property or function for translating the number values to a string name. Will probably have to build your own table and do a lookup or JOIN in query. https://learn.microsoft.com/en-us/of...e-property-dao

[Budatlitho]

THANKS for the explanation! Now I can compare field types from a db from different years tables.