Multiple Criteria - Type Mismatch

[MADali]

Hi everyone,

I'm not a programmer but I've been making something for the company to help with the productivity and I'm learning as I go along. I've done a decent enough job, but something which would take everyone else 5 minutes probably takes me an hour, which involves constantly hitting F1, googling every site, and doing a million trial and errors =P

However, I figure, this time I'll ask, before I throw my monitor out of the window.

I can't figure this out -

countrecord = DCount("[Sales Person]", "[Customers Data]", "[Sales Person] = '" & valueSalesPerson & "'" And "[Customers Data].[Next Action Date] => #" & Date & "#")

If I seperate them, it works, but once I put an AND in between them, it screws up with the type mismatch crap. I feel I got all the quotes & parameters and all that right, so I don't get it why its not working.

Thanks, guys

 

[SteveSchapel]

MADali,

... which involves constantly hitting F1, googling every site, and doing a million trial and errors

I'm sure this is familiar to all of us!

I feel I got all the quotes & parameters and all that right

Well, not quite. Try it like this:

countrecord = DCount("*", "Customers Data", "[Sales Person] = '" & valueSalesPerson & "' And [Next Action Date] => Date()")

By the way, what is valueSalesPerson?

 

[vbaInet]

Here you go:

countrecord = DCount("*", "[Customers Data]", "[Sales Person] = '" & valueSalesPerson & "' And [Next Action Date] => #" & Date() & "#")

 

[MADali]

Thanks guys. I continued having problems even after the below suggestions although the problem changed to Syntax error. It was getting more and more frustration until I realized => should be >=

By profession, I'm a sales manager, we sales people don't pay attention to small details like => and >= so all this is frustrating, but strangely addictive...

And Steve, valueSalesPerson just refers to another field, I'm trying to get into the habit of doing using Dim ... as String (or whatever) and then setting the value, so I get a cleaner code, although so far, I've been relatively unsucessful of having any code even resembling clean...

 

[vbaInet]

By profession, I'm a sales manager, we sales people don't pay attention to small details like => and >= so all this is frustrating, but strangely addictive...

But you sell sell sell

All working now?

 

[SteveSchapel]

MADali,

LOL, well it looks like vbaInet and I both missed that >= vs => too! Maybe I should go into sales.

By the way...

Dim ... as String (or whatever) and then setting the value, so I get a cleaner code

In my opinion, unnecessarily using variables is a common way to make code less clean. But if you are interested in clean, I always like to see references to fields/controls qualified, so I would have put Me.ValueSalesPerson, as in:
countrecord = DCount("*", "Customers Data", "[Sales Person] = '" & Me.valueSalesPerson & "' And [Next Action Date] >= Date()")

 

[DCrake]

Also is valueSalesPerson a string or a number? As if the latter then the single quotes are not needed.

 

[vbaInet]

In my opinion, unnecessarily using variables is a common way to make code less clean. But if you are interested in clean, I always like to see references to fields/controls qualified, so I would have put Me.ValueSalesPerson

I even do the same when calling/referring to functions/variables created in modules. In this context (only) it's easier to locate where you declared the variable.