Using AND with month function in Dlookup - query expression (1 Viewer)

AnilBagga

Member
Local time
Today, 07:45
Joined
Apr 9, 2020
Messages
137
I have 2 tables with dates. Table and Field names are

tblCustRMPriceMaster->Pricedate
tblCustRFQ->RFQDate

Table structure of tblCustRMPriceMaster is as below

1606057353187.png


In a query, I want to build an expression wherein I fetch values of PP or HDPE matching the Endcustcode and month of the 2 date fields mentioned above

The query I built is as below. There is no error but it picks up the first value of PP or HDPE in the table - the month expression is not being read

Code Tags Added by UG
Please use Code Tags when posting VBA Code

Please read this for further information:-
Please feel free to Remove this Comment

Code:
RMRate: IIf(
[RMType]='PP',
DLookUp("PP","tblcustRMPriceMaster","Endcustcode='" & [tblcustrfq].[Endcustomercode] & "' AND 'Month([tblcustrfq].[RFQdate])= month([pricedate])'"),
DLookUp("HDPE","tblcustRMPriceMaster","Endcustcode='" & [tblcustrfq].[Endcustomercode] & "' AND 'Month([tblcustrfq].[RFQdate])= month([pricedate])'")
)

A similar expression to display the RM rate in a form, as below, works

Code:
=IIf([RMType]='PP',
DLookUp("PP","tblcustRMPriceMaster","Endcustcode='" & [tblCustRFQ.EndCustomerCode] & "' AND month([RFQdate])=month([pricedate])"),
DLookUp("HDPE","tblcustRMPriceMaster","Endcustcode='" & [tblCustRFQ.EndCustomerCode] & "' AND Month([RFQdate])=month([pricedate])")
)

However when removed the '' on month expression of the query as below, I get an error

Code:
RMRate: IIf(
[RMType]='PP',
DLookUp("PP","tblcustRMPriceMaster","Endcustcode='" & [tblcustrfq].[Endcustomercode] & "' AND Month([tblcustrfq].[RFQdate])= month([pricedate])"),
DLookUp("HDPE","tblcustRMPriceMaster","Endcustcode='" & [tblcustrfq].[Endcustomercode] & "' AND Month([tblcustrfq].[RFQdate])= month([pricedate])")
)

What the right way to build the expression?
 
Last edited by a moderator:

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 02:15
Joined
Jul 9, 2003
Messages
12,288
Please use code blocks. It makes it much easier to read.
 

plog

Banishment Pending
Local time
Yesterday, 20:15
Joined
May 11, 2011
Messages
10,258
Dlookups don't belong in a query, so the correct way is an entirely different method. I do not understand what you are trying to achieve. To communicate data needs it's best just to use data. Nplease provide 2 sets:

A. Starting data from your tables. Include table and field names and enough data to cover all cases.

B. Expected results of A. Show the exact data you expect your query to generate when you feed it the data from A.

Again, 2 sets of data that tie together;. Not one set andan explanation.
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 21:15
Joined
Feb 19, 2002
Messages
30,570
Also, unless Nov 2020 should match Nov 2019, you must include year in your criteria.
 

Users who are viewing this thread

Top Bottom