Error using ELookup but not DLookup

[Rawb]

OK, maybe my last post was too long >.>



I get an error when I try and use Allen Browne's ECount function (found here). When I pass the exact same parameters to DCount however, it works!

The exact error message I get is "Run-time error '3061': Too few parameters. Expected 1."

The above error occurs on the following line of the ECount Function:

Set rs = db.OpenRecordset(strSql)

The variable strSQL contains the following SQL string:

SELECT Count(*) AS TheCount FROM qryThingyLog WHERE [UserName]='Rawb' And [NeedThingy]=1 And Left([ThingyCode],2)=21

When I copy this string into a Query, it runs just fine.

I'm at my wits end here. I need a Form that is responsive, not one that takes 10 seconds to update every time the user does something! And, as far as I can tell, there's no problem with either the SQL Query string or the VB Code.

[ssanfu]

The SQL cannot evaluate the clause "Left([ThingyCode],2)". It needs to be outside of the quotes.

You could try (in your code):

Dim MyCriteria as string

MyCriteria = "[UserName]='Rawb' And [NeedThingy]= 1 And " & Left([ThingyCode],2) & " = 21;"

'check the criteria. comment out if/when correct
Msgbox MyCriteria

' and call it like this (or whatever your variable name is):
TheCount =   ECount("*", "qryThingyLog", MyCriteria)

[Rawb]

I would, but this is actually in the Form itself. I can't use that kind of code from within the Form itself. . .

If that's the problem though, I COULD make another Query that handles the Left([ThingyCode], 2) portion there so I can just reference it like another Field. . .

Lemme give that a try, thanks!

[slave138]

You could use the code (as described previously) in the Form if you set it as the Control Source for whatever field is displaying the count.

[Rawb]

OK, it didn't work. . .

I set up a query specifically for use by the Form and replaced DCount with DLookup (the Query does the counting for me, now I just need to look up the right Record).

My Form's Control Source now looks like this:

=DLookup("[SumOfNeedThingy]","qryNeededThingiesByThingyCode","[UserName]='" & [UserName] & "' And [ThingyCode]=21")

The above DLookup (just like the original DCount) works perfectly. When I try and switch it to ECount however, I get error "3061: Too few parameters. Expected 1."

The highlighted line of code is:

Set rs = db.OpenRecordset(strSql, dbOpenForwardOnly)

This is essentially the exact same error I was running into before.

When I take the SQL string (strSql) though and paste it into it's own Query, the query works perfectly!

The resulting SQL was:

SELECT TOP 1 [SumOfNeedThingy] FROM qryNeededThingiesByThingyCode WHERE [UserName]='Rawb' And [ThingyCode]=21;

The Function is using DAO Recordsets and I have the Microsoft DAO 3.6 Object Library loaded (and am using DAO Recordsets elsewhere with no problem).

@slave138:
What do you mean that I can set the code as the Control Source for my Form Object? placing "Set rs = db.OpenRecordset(strSql, dbOpenForwardOnly)" in there wouldn't do anything useful, even if it would work. . .

[slave138]

@slave138:
What do you mean that I can set the code as the Control Source for my Form Object? placing "Set rs = db.OpenRecordset(strSql, dbOpenForwardOnly)" in there wouldn't do anything useful, even if it would work. . .

I would, but this is actually in the Form itself. I can't use that kind of code from within the Form itself. . .

If the code can be used anywhere in Access, it can probably be used in the Form. I wasn't saying you should use "Set rs = db.OpenRecordset(strSql, dbOpenForwardOnly)" as the control source for the form (I'm not sure where you came up with that from what I said...). I said you could use the ECount function (with the Criteria string formatted like ssanfu suggested) as the Control Source for whatever field you wanted to display the count.

If the error is telling you there were too few parameters and then highlights: Set rs = db.OpenRecordset(strSql) it means there is something in strSQL which is preventing the OpenRecordset method from executing correctly.

Upload a stripped version of the DB so we can see it in action and we might be able to make a little more sense of why it's not working. My best guess at this point is that there is something ECount/ELookup doesn't like about the syntax used for the criteria parameter but it's hard to say what exactly.

As it is, it seems pretty obvious DCount & ECount (DLookup & ELookup) do not work the same way even though they are called in a similar manner so testing the string in them isn't really a productive way to figure out why they're not working.

[Rawb]

I don't mean to be rude or inappropriate, I just wasn't exactly sure what you were suggesting. That's what I though you might be saying, but I'm already doing that.

The following Code is what I'm using in the Control Source of (one of the) Form Objects:

=DLookUp("[SumOfNeedThingies]","qryNeededThingiesByThingyCode","[UserName]='" & [UserName] & "' And [ThingyCode]=21")

I agree that the problem is something with the SQL String, I just can't figure out what. The arguments I'm passing to DLookup/ELookup though are pretty simple and the SQL string (shown above) works on it's own. . . I'm at a loss as to what's wrong.

I'll see what I can do setting up an example database (I can't show you the one I'm actually working on, but I should be able to set up an analog for you to play with).

[slave138]

"[UserName]='" & [UserName] & "' And [ThingyCode]=21"

What data type is ThingyCode in the table? I'm assuming number but I want to make sure.

In case DAO doesn't like the single quotes in your SQL string, try the code using:

"[UserName]=" & Chr(34) & [UserName] & Chr(34) & " And [ThingyCode]=21"

[ssanfu]

I agree, the arguments look OK.

So try this (note - using ELOOKUP() ):

=ELookup("[SumOfNeedThingy]","qryNeededThingiesByThingyCode",
"[UserName]='Smith'")

Replace "Smith" with a name you know is in the query. If that returns a value, try:

=ELookup("[SumOfNeedThingy]","qryNeededThingiesByThingyCode", "[ThingyCode]=21")

If both of the above returned values, finally try:

=ELookup("[SumOfNeedThingy]","qryNeededThingiesByThingyCode", "[UserName]='Smith' And [ThingyCode]=21")

Again, replace "Smith" with a name you know is in the recordset (domain).


If you get errors, then put a breakpoint at the start of the ELOOKUP function and single step thru the code, looking at the variables. You should find where the problem is...

It would help if you have a demo mdb......

[Rawb]

Interesting that you should mention a demo DB. . .

I just finished putting together that DB for you and. . .

Well, ELookUp works in it!

GRRRR

OK, well at least I know it's definitely a problem with the Query. Well, at least that narrows down the problem.

Also, just because, here's the Demo DB

[slave138]

I have tried modifying the queries in the demo to match your earlier SQL but it still works perfectly - even when using the SQL from your original post:

=ECount("NeedsThingy","qryNeededThingiesByThingyCode","[UserName]='" & [UserName] & "' AND [NeedsThingy]=1 AND Left([ThingyID],2)=20")

The only thing else I can figure is that there might be a field name not matching in your actual DB but that is purely a guess...

[Rawb]

The thing is, it can't be that. . .

If you look at the Function, I've added a Debug.Print line in there. For ones that didn't work, I used that to copy/paste the SQL string exactly as it appeared into a blank Query. And it worked!

But, I guess that's the only real possibility. Anyway, I'll keep looking into it now that I know the function can work.

[Rawb]

. . .

So, I figured out what the problem was. And I'm a little miffed that I didn't think of that before (seeing as how there was a warning about it in the Allen Browne's web site).

In the Query I was trying to run the Lookup on, I was referencing a Form Variable (Forms!frmDataEntry!ForecastDate). Apparently when using the "E" Functions of Lookup, Count, and Avg, you can't reference anything except Tables or Queries.

I've fixed the problem by having the ForecastDate written to a "SystemVariables" Table and referencing it from there.