dSUM (1 Viewer)

sbaud2003

Member
Local time
Today, 11:08
Joined
Apr 5, 2020
Messages
170
Me.TOTIP.Value = Nz(DSum("IP", "qmaster", "TYPE= " & [COMORG] And "AA0_NAME = " & [COMIR]))

WHAT IS WRONG IN THE CODE I AM GETTING TYPE MISMATCH

QUERRY NAME=QMASTER
IP NUMERIC FILED
TYPE AND AAO_NAME TEXT FILED
COMORG AND COIR ARE TWO COMBOBOX IN THE SAME FORM
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 00:38
Joined
Feb 28, 2001
Messages
27,001
I suspect the problem is with fields COMORG and COMIR, both of which are text fields used via concatenation. When you compare in that way (using substitution/concatenation), the substituted part has to be in quotes.

Code:
Me.TOTIP.Value = Nz( DSum( "IP" , "qmaster" , "TYPE= '" & [COMORG] & "' And AA0_NAME = '" & [COMIR] & "'" ) )

I changed the quoting in two ways. I moved a double-quote to encompass the "AND" and then I added some apostrophes (single-quotes) to isolate the variables being used in a text comparison.

Just a word of advice: You have a variable named TYPE, but that is an Access reserved word. Using reserved words has been known to bollix up the works, so I would consider a field-name change as soon as is practical.
 

sbaud2003

Member
Local time
Today, 11:08
Joined
Apr 5, 2020
Messages
170
I suspect the problem is with fields COMORG and COMIR, both of which are text fields used via concatenation. When you compare in that way (using substitution/concatenation), the substituted part has to be in quotes.

Code:
Me.TOTIP.Value = Nz( DSum( "IP" , "qmaster" , "TYPE= '" & [COMORG] & "' And AA0_NAME = '" & [COMIR] & "'" ) )

I changed the quoting in two ways. I moved a double-quote to encompass the "AND" and then I added some apostrophes (single-quotes) to isolate the variables being used in a text comparison.

Just a word of advice: You have a variable named TYPE, but that is an Access reserved word. Using reserved words has been known to bollix up the works, so I would consider a field-name change as soon as is practical.
Thanks I have changed TYPE filed into ORG
but still getting Run Time error 2471
 

plog

Banishment Pending
Local time
Today, 00:38
Joined
May 11, 2011
Messages
11,613
Because [COMORG] is a form input you need to let the code know that. As it is, it thinks it's comming from the table\query the form is based on:

Forms!YourFormNameHere!COMORG

Same for COMIR
 

cheekybuddha

AWF VIP
Local time
Today, 05:38
Joined
Jul 21, 2014
Messages
2,237
What datatype are each of the following fields in qmaster:

IP
[TYPE] (/Org)
COMORG
COMIR

?
 

sbaud2003

Member
Local time
Today, 11:08
Joined
Apr 5, 2020
Messages
170
I suspect the problem is with fields COMORG and COMIR, both of which are text fields used via concatenation. When you compare in that way (using substitution/concatenation), the substituted part has to be in quotes.

Code:
Me.TOTIP.Value = Nz( DSum( "IP" , "qmaster" , "TYPE= '" & [COMORG] & "' And AA0_NAME = '" & [COMIR] & "'" ) )

I changed the quoting in two ways. I moved a double-quote to encompass the "AND" and then I added some apostrophes (single-quotes) to isolate the variables being used in a text comparison.

Just a word of advice: You have a variable named TYPE, but that is an Access reserved word. Using reserved words has been known to bollix up the works, so I would consider a field-name change as soon as is practical.

What datatype are each of the following fields in qmaster:

IP
[TYPE] (/Org)
COMORG
COMIR

?
IP numeric
TYPE as Text
AAO_Name as Text
COMORG is a combobox to fetch data for Type
COMIR is a combobox to fetch data from AAO_Name
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 13:38
Joined
May 7, 2009
Messages
19,169
add bracket, maybe:

Me.TOTIP.Value = Nz( DSum( "[IP]" , "[qmaster]" , "[TYPE]= '" & [COMORG] & "' And [AA0_NAME] = '" & [COMIR] & "'" ) )
 

cheekybuddha

AWF VIP
Local time
Today, 05:38
Joined
Jul 21, 2014
Messages
2,237
Are comboboxes COMORG and COMIR on the same form as TOTIP?

Which event are you running this code from?

What is the error description for Run Time error 2471?
 

Users who are viewing this thread

Top Bottom