dcount error

[swarv]

I have the following piece of code:

Dim countme As Variant
Dim namevalue As Variant
countme = "countme"
namevalue = Combo136.Value
LTotal3 = DCount("[Name]", "Absent", "[Name] = #" & namevalue & "#" And "[absentcode] =  #" & countme & "#")

could somebody please tell me why it comes up with type mismatch.

thanks

martin

 

[wazz]

countme is a string.
#s are used for dates.

 

[boblarson]

Well, for one because a name doesn't use a date delimiter (#), it uses quotes, and if your absentcode is a number then it shouldn't have any delimiters. Also you didn't encapsulate certain parts in the string.

LTotal3 = DCount("[Name]", "Absent", "[Name] ='" & namevalue & "' And [absentcode] =" & countme)

 

[swarv]

I have changed it to this:

countme = "countme"
namevalue = Combo136.Value
 
LTotal3 = DCount("[Name]", "Absent", "Name = combo136.value" And "[absentcode] =  countme ")

and still the same

any ideas?

thanks for the # comment - I'll try to remember that for the rest of it - will come in very usefull.

 

[boblarson]

Also, you shouldn't have a field named "NAME" as it is an Access Reserved Word. I would change it if I were you as it can cause you grief at various points.

Also, before you rename things, turn off Auto Name Correct as it can mess things up for you.

 

[swarv]

Bob,

your code comes up with the error: you cancelled the previous operation.

any ideas?

thanks.

 

[swarv]

i have heard that before but i think im a bit to far in the programming to change the field name as it'll mess you some of the other coding

 

[boblarson]

Yeah, does the field "NAME" come to mind (given my previous post)?

 

[boblarson]

i have heard that before but i think im a bit to far in the programming to change the field name as it'll mess you some of the other coding

Do it anyway. You can't afford not to. This will F@#K with other things as well. This is not a suggestion - this is something YOU NEED TO DO.

 

[swarv]

ahhh, i think i catch your drift.

 

[swarv]

i guess there isn't a way to encapsulate the word NAME just for this one line then when it is working i'll go through all the code over the weekend.?

 

[boblarson]

i guess there isn't a way to encapsulate the word NAME just for this one line then when it is working i'll go through all the code over the weekend.?

Normally you can encapsulate with square brackets, but I do know that NAME is one of those things that will come back to bite you more than once because that is a MAJOR item. A form has a .NAME and a control has a .NAME and if you try to refer to a field name named "Name" then things can go all wonky on you big time. So, normally I would say you "should" rename but if you can't - you should be able to get by. But with NAME - nope - that one is a MUST.

 

[gemma-the-husky]

i doubt if its the field [name] causing the problem

LTotal3 = DCount("[Name]", "Absent", "[Name] ='" & namevalue & "' And [absentcode] =" & countme)

first try just

LTotal3 = DCount("[Name]", "Absent")

and see if that works

you should have a field called name, in a table or query called absent.

i may well be wrong, but i suspect that this will work, and its more likely that the where clause is constructed incorrectly

eg this just worked fine for me, ion a test table

Sub testit()
MsgBox (DCount("name", "table1"))
End Sub

 

[boblarson]

ii may well be wrong, but i suspect that this will work, and its more likely that the where clause is constructed incorrectly

eg this just worked fine for me, ion a test table

Sub testit()
MsgBox (DCount("name", "table1"))
End Sub

Doesn't matter - yes, you can get it working right but this is one case where the fix should not be a band-aid fix. NAME should not be used as an object name and it WILL cause problems at some point. So fix them now and then go on.

But your point about the where is correct. It is likely an incorrect use of datatypes.

 

[swarv]

gemma, your mesgbox function worked fine, just got to figure out the rest of it now.
any help would be great.

cheers

 

[boblarson]

gemma, your mesgbox function worked fine, just got to figure out the rest of it now.
any help would be great.

cheers

The big key in Domain Aggregate functions is to know what datatypes you are looking for and what data your controls are returning. If you are looking for text you need to surround it with quotes (usually single quotes will work but not always) and if dates then it is # and then if numeric it doesn't get surrounded at all.

What does your combo box return as a value for combo136?

 

[swarv]

this code returns 0

msgBox DCount("Name", "absent", "Name" = "combo136.value")

combo136.value returns abbe i.e. the what ever name is in the combo136 field.
when i run it without the name=combo then it returns 55. with it it returns 0

 

[boblarson]

If the combo is supposed to return a name then you would want:

DCount("[Name]", "absent", "[Name]" = '" & combo136 & "'")

Because you are using NAME, you must surround it with square brackets so Access knows you are referring to a field.

 

[gemma-the-husky]

"[Name] ='" & namevalue & "' And [absentcode] =" & countme)

this is written on the basis that
a) namevalue is either a textbox on a form, or a string variable
b) countme is a number

now this string will turn into

[name] ="somename" and [absentcode] =4

assuming the datatypes are ok, i think you may need an extra space after each equals sign, as sql is most unforgiving. I would tend to use chr(34) for "", rather than the way you have, so try


so try

("[Name] =  " & chr(34) & namevalue & chr(34) & " And [absentcode] = " & countme)

------
even if you can get this all working, i agree with other posts that it would be safer to rename the field [name] if you can

 

[swarv]

cheers for all your help and this really annoying me now as im trying everything you say and more.

this is the line at the moment:

MsgBox DCount("[Name]", "absent", "[Name]" = '" & combo136.value & "'")

but it errors at the first single quote, it wont let me finish the line.

combo136.value returns a persons name i.e. martin or abbe etc...

cheers