DLookup or SQL String Help

[ppkgoldengun]

Being fairly new at VBA coding I need help getting the following to work (I think my syntax is screwed up).

I need to either do a Dlookup or set a variable equal to a SQL string result.

Scenario.

One variable set to something
Second variable set to something.
Third variable dimensioned to be result.

Lookup in a table a resultant variable by either using Dlookup or SQL with a Greater than first variable clause AND a less than second variable clause. The table is structured so only one result will be returned.

I can get a dlookup to work on a single variable but the multi variable clause has me stumped (probably a quotation mark error).

Any examples or psuedo code? Would SQL or Dlookup be faster as I must use a three lookups (to different tables) within looping through a record set.

Many thanks from this newbie.

 

[lameid]

It depends on the datatype of the fields...

Here's a tip, start in the query QBE and set the values to literals for an example. Next switch to sql view. There is the whole SQL statement. For the where parameter of Dlookup you want everything in the where clause (most likely everything after where except the semi-colon).

If anything is text (has double quotes) you will need to double the double quotes up. It takes two double quotes in a string to represent one double quote in a double quote delimeted string.

var3 = Dlookup("FieldToFind","TableToFindIn","numeric1 = " & var1 & " And Text2 = """ & var2 & """&quot

BTW You dimension an array when you declare it, you set variables or elements of an array to values.

 

[ppkgoldengun]

Thanks for the info. I've been traveling with no access to a pc.