DLookup with 2 criteria (1 Viewer)

estelleh

Member
Local time
Today, 14:23
Joined
Jul 16, 2021
Messages
56
Hi all :)

I have a table "Branches" that has the fields "CustCode" ,"BranchNo" and "BranchName". I have another table "Jobs" where each record has a field "CustCode" and "Branch No".

I am using DLookup to look up various fields in various tables in order to construct a new record in the "JobDeliveryNotes" table. I am trying to look up "BranchName" from the "Branches" table" using 2 integer variables - intCustCode and intBranchNo (I have checked the values of these 2 variables just before the DLookup statement and they have the correct values in them).

The DLookup statement I have is as follows:
Code:
DLookup("[BranchName]", "[Branches]", "[CustCode]= " & intCustCode & " AND [BranchNo]= " & intBranchNo)

I cannot for the life of me work out the syntax for the second criteria.

I then tried using a Query which looks like this:

qryDelBranchName.JPG


The SQL view looks like this:
Code:
SELECT Branches.CustCode, Branches.BranchCode, Branches.BranchName
FROM Branches
WHERE (((Branches.CustCode)=[intCustCode]) AND ((Branches.BranchCode)=[intBranchNo]));

When I run this query from within Access I get the expected results:
QueryResult.JPG


I changed my DLookup statement to this:
Code:
strBranchName = DLookup("[BranchName]", "[qryDelBranchName]")

Which gives me this error:

QueryError.JPG


Please help! o_O
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 12:23
Joined
Sep 12, 2006
Messages
15,613
Why does jobs table need BOTH a branch code and a customer code? Just branch code would do.

1 Customer = n Branches
1 Branch = n Jobs

I would put all 3 tables in the query, and pull down the fields you wanrt.
 

Gasman

Enthusiastic Amateur
Local time
Today, 12:23
Joined
Sep 21, 2011
Messages
14,038
You cannot just throw variables into SQL code. :(
You would need to concatenate as you did for the DLookUp().

Strings need to be surrounded by single quotes ' unless it contains a single quote, then triple double quotes " works I think?
Dates with # and in mm/dd/yyyy or yyyy-mm-dd format
Numbers do not need anything
 

LarryE

Active member
Local time
Today, 05:23
Joined
Aug 18, 2021
Messages
562
Don't use brackets when referencing the table criteria portion of the function. The following should return the BranchName field.
DLookup("[BranchName]", "qryDelBranchName")
 

estelleh

Member
Local time
Today, 14:23
Joined
Jul 16, 2021
Messages
56
Why does jobs table need BOTH a branch code and a customer code? Just branch code would do.

1 Customer = n Branches
1 Branch = n Jobs

I would put all 3 tables in the query, and pull down the fields you wanrt.
Both fields are needed, because branch no. 1 is always the Head Office for each client, who may or may not then have other branches. Changing that structure would entail major changes through the rest of the system.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:23
Joined
May 7, 2009
Messages
19,169
can you Use Query in your form Joining table Branches (on CustCode + BranchCode) with
your first table (Left Join table Branches).
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 12:23
Joined
Sep 12, 2006
Messages
15,613
Both fields are needed, because branch no. 1 is always the Head Office for each client, who may or may not then have other branches. Changing that structure would entail major changes through the rest of the system.
It's still unnecsssary.

You could always have branch 1 as the head office.
You could have a head office flag on a branch record.
You still don't need the customerID in the job record. You can get the customerID from the branch record for the job.

It's not the end of the world. It means when you have an active job, you can get the customer either from the job, or from the branch record, but having two values does raise the possibility that the customerIDs could become out of synch, and have different values in each case.

It was more of an observation. I appreciate that you may have used both CustomerID references at different points.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:23
Joined
Feb 19, 2002
Messages
42,970
SELECT Branches.CustCode, Branches.BranchCode, Branches.BranchName
FROM Branches
WHERE (((Branches.CustCode)=[intCustCode]) AND ((Branches.BranchCode)=[intBranchNo]))
Assiming that intCustCode and intBranchNo are both variables or even form fields, the query cannot possibly be working.


Code:
Dim strWhere As String
strWhere = "[CustCode]= " & intCustCode & " AND [BranchNo]= " & intBranchNo
DLookup("[BranchName]", "[Branches]", strWhere)

I can't tell if the "int" fields are variables or form controls, If they are controls on a form, they should be referenced in code as:
Me.intBranchNo
Me.intCustCode

And, they would not be prefixed with their data type. Form controls frequently have prefixes to identify the type of control that holds the dat but this is to distinguish the control named txtCustCode from CustCode the bound column when you are writing code.
 

Users who are viewing this thread

Top Bottom