Unable to use number in criteria; string, however, works.

[Budatlitho]

Hello again!

I'm still fighting with this function, months later...
When the variable eid is a string the function will run. If eid is a number, it fails (Run-time error "3464": Data type mismatch in criteria expression"
I need eid to remain a number.
If I change these two lines to string it runs. The Jumbula.Participant Member ID (Seven digits) field Data Type is Number

Dim eid As String
"(eid VARCHAR, firstname VARCHAR, lastname VARCHAR, name VARCHAR, amountdue CURRENCY, amountpaid CURRENCY, registrationfee CURRENCY, creditcardfee CURRENCY, discount CURRENCY, balance CURRENCY);"

Public Function memberJumbula()
RefreshDatabaseWindow
DoCmd.SetWarnings False
Dim db As Database
Dim rs As Recordset
Dim rs1 As Recordset
Dim rs2 As Recordset
Dim strSql As String
Dim eid As Long
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 count As Integer
Set db = CurrentDb
db.Execute "DELETE * FROM Symposium_member"
db.Execute "CREATE TABLE JumbulaTemp " & _
"(eid NUMBER, 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("JumbulaTemp")
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 "Order amount= " & rs("Order amount")
    amountdue = DSum("[Order amount]", "Jumbula", _
            "[Participant Member ID (Seven digits)] =""" & eid & """")
'fails at this line
    amountpaid = DSum("[Paid amount]", "Jumbula", _
            "[Participant Member ID (Seven digits)] =""" & eid & """")
    registrationfee = Nz(DSum("Registration Fee", "Jumbula", _
            "[Participant Member ID (Seven digits)] =""" & eid & """"))
    creditcardfee = Nz(DSum("Credit Card Convenience Fee", "Jumbula", _
            "[Participant Member ID (Seven digits)] =""" & eid & """"))
    discount = DSum("[Total discount]", "Jumbula", _
            "[Participant Member ID (Seven digits)] =""" & eid & """")
    balance = DSum("Balance", "Jumbula", _
            "[Participant Member ID (Seven digits)] =""" & eid & """")
    Debug.Print "count =" & count & " eid = " & eid & ", " & amountdue & ", " & amountpaid & ", " & registrationfee & ", " & creditcardfee & ", " & discount & ", " & balance; ""
        DoCmd.RunSQL "INSERT INTO JumbulaTemp" & _
        "(eid, firstname, lastname, name, amountdue, amountpaid, registrationfee, creditcardfee, discount, balance)" & _
        "VALUES ('" & eid & "','" & firstname & "','" & lastname & "','" & name & "','" & amountdue & "','" & amountpaid & "','" & registrationfee & "','" & creditcardfee & "','" & discount & "','" & balance & "')"
    rs.MoveNext
Loop
'copy to Symposium_member
rs1.MoveFirst
Do While Not rs1.EOF
    If rs1.EOF Then Exit Do
    eid = rs1("[eid]")
    firstname = rs1("[firstname]")
    lastname = rs1("[lastname]")
    name = rs1("[name]")
    amountdue = rs1("[amountdue]")
    amountpaid = rs1("[amountpaid]")
    registrationfee = rs1("[registrationfee]")
    creditcardfee = rs1("[creditcardfee]")
    discount = rs1("[discount]")
    balance = rs1("[balance]")
    'unit = rs2("[Unit_Number]")
        DoCmd.RunSQL "INSERT INTO Symposium_member" & _
        "(eid, firstname, lastname, name, amountdue, amountpaid, registrationfee, creditcardfee, discount, balance)" & _
        "VALUES ('" & eid & "','" & firstname & "','" & lastname & "','" & name & "','" & amountdue & "','" & amountpaid & "','" & registrationfee & "','" & creditcardfee & "','" & discount & "','" & balance & "')"
    rs1.MoveNext
Loop
rs.Close
rs1.Close
strSql = "DROP TABLE [JumbulaTemp];"
DoCmd.RunSQL (strSql)
RefreshDatabaseWindow
DoCmd.SetWarnings True
End Function

[Welshgasman]

Either eid is a number type or a string type.
If number, no surrounding characters required.

[davegri]

If eid is a number it must be specified in the DSum as

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

[Budatlitho]

If eid is a number it must be specified in the DSum as

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

I see that now. I know I need help with where quotes are required. Does anywhere come to mind where I might find a tutorial/reference?

THANKS!

[June7]

Accidental duplication of post.

[June7]

Here: https://learn.microsoft.com/en-us/of...ng-expressions

Use # character for date/time parameters.

[Budatlitho]

Here: https://learn.microsoft.com/en-us/of...ng-expressions

Use # character for date/time parameters.

Great resource. Thank you.

[Missinglinq]

"The Jumbula.Participant Member ID (Seven digits) field Data Type is Number"

It should be noted that 'numbers,' such as IDs (or SSNs, etc) probably should be defined as Strings, with only those used for math, measurements, etc, being defined as Numbers.

Linq ;0)>

[Budatlitho]

"The Jumbula.Participant Member ID (Seven digits) field Data Type is Number"

It should be noted that 'numbers,' such as IDs (or SSNs, etc) probably should be defined as Strings, with only those used for math, measurements, etc, being defined as Numbers.

Linq ;0)>

Yes, I understand that string is probably better, but a lot of references in the database need number. Also, it prevents users from entering straight text.

Have a great day!