I have 2 tables with dates. Table and Field names are
tblCustRMPriceMaster->Pricedate
tblCustRFQ->RFQDate
Table structure of tblCustRMPriceMaster is as below
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
A similar expression to display the RM rate in a form, as below, works
However when removed the '' on month expression of the query as below, I get an error
What the right way to build the expression?
tblCustRMPriceMaster->Pricedate
tblCustRFQ->RFQDate
Table structure of tblCustRMPriceMaster is as below
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 use Code Tags when posting VBA Code
To make your code easier to read, please use the Code tag around your code, this will ensure that any code you copy and paste from your DB retains it's formatting making it easier to read and follow; Firstly click on the Code button, that's the button at the top of the posting window with the...
www.access-programmers.co.uk
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: