Dlookup Type Mismatch

[bbwolff]

        cntr = 0
        For i = 0 To stp - 1
              t(i) = DLookup("[OpID]", "[Ops]", "[bID] = " & lkp And "[OpID] >  " & cntr)
              cntr = t(i)
    
        Next

dlookup is driving me nuts
this works with only one criteria but returns type mismatch if I add the other criteria
all values are numbers (between 0 and 10 in my test base)

I'm trying to compile an array of all records that contain certain bID.

tx for help

 

[plog]

Your ' AND ' portion of your DLookup needs to be part of the string, right now its out there with lkp screwing things up. Assuming its the correct criteria, your criteria argument should look like this:

"[bID] = " & lkp & " And [OpID] > " & cntr

It's so easy to mix up your fields and your variables. One thing to do is to always use that terminology and to use brackets around your fields. 'Fields' are in the table you are looking into and 'variables' are being supplied by the code.

Also, when I have multiple criteria in the criteria string I usually write it as one long string in the first pass making the actual variables standout and then go back through a second time and insert the starting/ending quotes and ampersands around the variables. For example:

Pass 1: "[bID] = @lkp@ And [OpID] > @cntr@"

I surround the variables that need to be replaced with at symbols so I can find them on the second pass and then add quote marks and ampersands where they are:

Pass 2: "[bID] = " & lkp & " And [OpID] > " & cntr

 

[namliam]

Or not use DLookup but rather use "proper" SQL instead by opening a recordset...

 

[David R]

Depends on what he's looking for and how many records he's trawling through. DLookup is fine for simple searches where you don't want to do anything else to the recordset once you have the piece of data... the syntax of the WHERE clause is identical in any case.

 

[bbwolff]

tx for help
that worked well, and is so obvious now

proper sql.... ill look into that later
the base will contain app 10000 records per year

 

[namliam]

True David, but I have seen so many people have trouble with DLookup that I know can write proper SQL its rediculous....

And too often is DLookup abused where a normal SQL would serve so much better.

It may be my "warped" oppinion, but reusable proper SQL over any DLookup any time any day any database for me. I tend to avoid it like the plague UNLESS I am absolutely 100% sure I will NEVER ever NEVER ever after 10 reconsiderations NEVER ever need anything but this one piece of information in my code... from this one table....
But yeah... Dlookup has its place sure... but yeah... abuse is right around the corner

 

[missinglinq]

...It may be my "warped" oppinion...

Of course it's warped! You're not simply a 'mailman,' but a 'backwards mailman, to boot!'

;0)>

 

[namliam]

Hey atleast I always deliver and am never lost or missing

 

[missinglinq]

Hey at least I...am never lost or missing

You staid Dutch must be no fun at all!

 

[Cronk]

Guys, just to get back on a serious note, can someone tell me what is "proper SQL"?

Dlookup is just an internal function and no doubt uses generated SQL passed to the Jet Engine.

I think I recall that the claim has been made that Dlookup is slower than opening a recordset, but it's a lot less typing.

 

[plog]

What's proper english? There is no such thing. SQL comes in many, many different flavors, each with its own irritating idiosyncracies.

Technically each flavor of SQL is a domain; thus Dlookup is a domain function and a "proper" part of SQL when working with Access. Doesn't mean its great or the best solution, but its perfectly valid when writing SQL in Access.

Now, when you try and move your SQL from Access to another domain (i.e. MySQL, ORACLE, etc) it won't work because of the Dlookup which isn't part of those domains. To get the same functionality, they might have equivalent functions or you could always use a sub-query.

 

[namliam]

Disagree with Plog here, SQL and DLookup have rarely anything in common, but that may be one of my idiosyncracies

"Proper SQL" instead of your
DLookup("[OpID]", "[Ops]", "[bID] = " & lkp And "[OpID] > " & cntr
would be something along the lines of
"Select * from [OPS] where bid = """ & lkp & """ AND OpID > " & Cntr

It may be faster or slower than a DLookup, it will be more typing than a DLookup....
But its generic and returns more than only the single column of the DLookup....
This is the one BIG drawback of DLookup, one table, one column.... Not that often that you want that... If that is what you need, obviously its a viable alternative... but dont abuse it :/

 

[bbwolff]

so if I want to get just the ID of the record that I'll use later for other things then dlookup is good enough / or maybe even better? and if i want more values at the same time the sql is faster?

how do you display results and work with individual results?
do you need definitions of dao, database, tabele before sdelect?

 

[namliam]

Yes need definitions of Dao etc...
Dim rs as dao.recordset
Set rs = Currentdb.openrecordset("Select a,b,c,d,e,f from anytable inner join [another table] where ...")
debug.print rs!a, rs.b, rs.Fields(3), rs.Fields("d")
rs.movelast

Also beatifull things like:
set rs = Me.recordsetclone
To easily take a copy of data from a form...