DLookup within Iif statement

I'm having trouble getting an Iif Statement that includes a Dlookup to work.  
Here is what I have but the records where the Iif is true show nothing for 
this field.  
IIf([tblOrders].[FreightOnly]=False,[tblOrders].[Price]-DLookUp("Price","tblProductCost","EffectiveDate 
<=#" & [qryNetRevenue].![Loadeddate] & "#"),[tblOrders].[Price])
Thanks!
Walter
0
Utf
3/20/2008 9:19:04 PM
access.queries 6343 articles. 1 followers. Follow

2 Replies
1989 Views

Similar Articles

[PageSpeed] 47

Walter,

there is a typo here:
    [qryNetRevenue].![Loadeddate]
you have both a . and a !

Maybe you need to remove the ! from
    [qryNetRevenue].![Loadeddate]


If the above doesn't work, please post back with more details of what you 
are trying to do.

Jeanette Cunningham


"Walter" <Walter@discussions.microsoft.com> wrote in message 
news:DC08ADD0-1B45-47EA-94A8-F485A95FC484@microsoft.com...
> I'm having trouble getting an Iif Statement that includes a Dlookup to 
> work.
> Here is what I have but the records where the Iif is true show nothing for
> this field.
> IIf([tblOrders].[FreightOnly]=False,[tblOrders].[Price]-DLookUp("Price","tblProductCost","EffectiveDate
> <=#" & [qryNetRevenue].![Loadeddate] & "#"),[tblOrders].[Price])
> Thanks!
> Walter 


0
Jeanette
3/20/2008 10:31:46 PM
On Thu, 20 Mar 2008 14:19:04 -0700, Walter wrote:

> I'm having trouble getting an Iif Statement that includes a Dlookup to work.  
> Here is what I have but the records where the Iif is true show nothing for 
> this field.  
> IIf([tblOrders].[FreightOnly]=False,[tblOrders].[Price]-DLookUp("Price","tblProductCost","EffectiveDate 
> <=#" & [qryNetRevenue].![Loadeddate] & "#"),[tblOrders].[Price])
> Thanks!
> Walter

All arguments in a DLookUp must refer to the same table. You can't
lookup a value in tblProductCost using criteria from qryNetRevenue.
Perhaps you can use a DlookUp within the DLookUp. 
Off the top of my head, try:

=IIf([FreightOnly]=False,[Price]-DLookUp("Price","tblProductCost","EffectiveDate 
 <=#" & DLookUp("[Loadeddate]","qryNetRevenue") & "#")
,[tblOrders].[Price])
The above assumes qryNetRevenue returns only one record, so no
additional criteria is needed.

-- 
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
0
fredg
3/20/2008 10:39:50 PM
Reply:

Similar Artilces:

billing statements
How can I cut down the size of the billing statement I send out to a regular customer? The last one printed out the complete history of the customer and was three pages long. I only want to send recent activity. Teri ...

Sroll Box within Scroll Bar
If I highlight a range in a work sheet to have row height increased and say range is from A1-A400. The scroll box within the scroll bar resizes for that range. If my spreadsheet however turns out to be only 50 rows(after entering data) not 400 the bar will still be sized to go from row 1 at the top to Row 400 at bottom(It can obvously go past that but the scroll box is sized for 400). I would like it to go back to its to size for the actual size of the data in the spreadsheet. I have tried to delete the rows from 50-400 but it has no effect. Any help would be appreciated as this is annoying pr...

How do I write a basic VLOOKUP statement?
I know most of the posts here are for advanced users... but, please help! I have a column of data in one spreadsheet (column A) in a workbook and another column of data (also column A) in another spreadsheet in the same workbook. I want to confirm that all of the data in the first spreadsheet is in the second, and identity those that are not. I know this is a very very basic VLOOKUP statement, but I am lost. thanks for anyones help, It's not a vlookup job, look here http://www.cpearson.com/excel/Duplicates.aspx -- Regards, Peo Sjoblom "Maureen" <Maureen@dis...

If Statement #17
Is there a way to produce an IF statemnt that relates to the Fill Color of a Cel. For instance IF(the Fill Color of A1 = Red, then I want B2 * C2, IF not then b2 *d2) . I thought I saw something in this newsgroup beefore on this but I was unable to find it. Thanks in advance for help. You need a custom function for this. There is one at http://www.xldynamic.com/source/xld.ColourCounter.html -- HTH RP (remove nothere from the email address if mailing direct) "DME" <craigjoseathotmaildotcom> wrote in message news:OKTp7IwAFHA.1400@TK2MSFTNGP11.phx.gbl... > Is there ...

Trying to import South African statement in CSV format
How do I import statements to microsoft money? is there some way of converting a standard CSV file to an importable version? I am currently on 2000 version. My statement has following columns Description Amount DR /CR Date Balance Thanks this is very urgent for me! In microsoft.public.money, Claire wrote: >How do I import statements to microsoft money? is there >some way of converting a standard CSV file to an >importable version? See http://xl2qif.chez.tiscali.fr/links_en.php ...

Internal and External Account within same Outlook client
Hello all, I will explain this issue the best I can as I am still a bit baffelled how it even was working. I have a client that has uses an internal exchange 2007 server with the domain jj@abc.local for sending internal emails and an external account ap@duh.com for external customers to contact them. Within the Outlook client both accounts are setup. The internal one is setup as an Exchange account and the external is setup as POP3 with the external being the default account. Only about 5-10 people have external and internal accounts with everyone else just having internal. ...

timeline chart within a scatter plot
I have an excel scatter plot that displays some point information when the mouse cursor hovers over the data point. The information displayed are beginning and end time for different events that occured for that particular data point. What I would like to do now is be able to chart a timeline chart that will display the different time event when I click on a datapoint. Has anyone done anything similar to this? A chart that can generate another chart in excel? Thanks, You can use the click event to trigger a VBA procedure that creates the second chart. Here's an article about chart ...

Using IF statement with sheets
I have several sheets named with the date such as 4.8 up to 5.8. On each sheet have rows of data with calculations for percentages of trying to determine if the row of data is at 3%. I created a tab called Summary. I am manually inputting the row of data that is above 3% by going to each tab(sheet) and doing a filter and looking at the bottom left hand corner of the screen that displays the number of rows above 3% in the filter. Then I input that number on the Summary sheet. Example: Summary Sheet row 2 col a 4.8 and col b 100 row 3 col a 4.9 and col b 50 What I'm trying to do is th...

FRx statements with GPv8 inactive accounts
I printed out our last (closed) year end via FRx P & L statement. I discovered that the descrepancy between the FRx and GP reports was the inactive accounts. I have some 40 inactive accounts (closed/sold departments), so I want to know how to convince FRx to print active as well as inactive accounts (outside of going back to GP and making each account "active"). JD JD, On the catalog in FRx, click on the Report Options tab, then the Advanced tab under that. In the lower right-hand corner there is a checkbox to Exclude inactive accounts - uncheck that. Hope that helps...

if statement with or
Hello, Could someone help me understand why it doesn't like "If cUser = "XJOHN" Or "YPAUL" Then" in the macro below? Thanks. Sub UnprotectAllSheets() Dim cUser As String cUser = UCase(Environ("username")) 'change to uppercase because this seems to be case sensitive. If cUser = "XJOHN" Or "YPAUL" Then Dim wSheet As Worksheet For Each wSheet In ActiveWorkbook.Worksheets wSheet.Unprotect Password:="opensesame" Next Else UserForm1.Show End If End Sub Chec...

Using <= in an IIF Statement
I know the answer is right in front of my eyes, but its one of those days and I have wasted too much time on it already.. I have a column within a table called Networth. I want the user to be able to enter a number in a textbox on a form and return only records <=[Networth]. But there is a catch, the textbox is optional. I have tried several different IIF statements and I was not successful. I used the "*" wildcard and was able to return all records as long the textbox was blank(null), if a number was entered, no records were returned. Here is an example of what I am tryi...

if statement #24
I have two worksheets with various fields. The common numeric field is ID in both sheets. I want a IF for VBA.. when If ID in Cell A1 ins within sheet 2. range C:C copy that row from sheet 2... nad paste in Sheet 1.. in cell A1. then go to next record and repeat... The idea is to merge both sheets into 1 with al lthe fields... Is it possible? ...

No DLookUp
Is there a way of doing this without DLookUP or Me.TxtPath? I have the Path stored in a table which I need to do incase I have to swith paths. This way I don't have to change all of my code. Thanks DS Me.TxtPath = Nz(DLookup("BackName", "tblBackPath", "BackID=1"), "") If Len(Dir$(Forms!Form1!TxtPath)) > 1 Then Dim Test2SQL As String DoCmd.SetWarnings False Test2SQL = "UPDATE table1 IN '" & Forms!Form1!TxtPath & "' " & _ "SET table1.IDName = '" & Forms!Form1!TxtInfo &...

Working with IIf statements
I'm working on a query that bouncing dates against one another. I think it needs nested IIf statements, but my brain just can't seem to put it all together. I'd like it to work in a single query to keep things trim and because I'll need to modify it for 8 different cases. Last_Promotion (already captured) Eligibility_date (calculated field - Last_Promotion + 4 months) Cutoff (will be criteria) Hire Promotion I need to come up with something that says: [Promotion] = [Eligibility_date] If [Hire] is populated, then [Promotion] = [Hire] +1 day If [Hire] is null, [Promotion]...

Search statement not working
I have a search form and this code is on the keypress of a textbox Me.RecordSource = "SELECT * FROM [qrysearchcomplaint] WHERE [txtcomplainant] like ""*" & Me.txtSearch.Value & "*"" or [txtbusinessname] like ""*" & Me.txtSearch.Value & "*"" or [txtsurname] like ""*" & Me.txtSearch.Value & "*"" Or [txtrefnbr] Like "" * " & Me.txtSearch.Value & " * "" Or [txtmemnbr] Like "" * " & Me.txtSearch.Value & " * &quo...

If Statement Overwhelm Part Ii
Hi, I have a sinking feeling this one may be impossible but let's see. have a ROW of data (actually about 240 rows) that I want an IF THE function to test for various things. There are so many tests and signals that I' not sure if an IF THEN is the right kung fu, but I'm sure Pete o another Excel master will know. Here is the project: Here are the tests and corresponding signals that I want Excel t "say". The values in the row of data will be referred to as "the ratio" in th following, the data is organized from left to right by date, i.e. cel B2's ...

How can I change the size of different series within a legend?
I have several data series with different amount of text in the legend. At the moment,excel provides automatically equal space for all series, but i need for some series more than for others, otherwise the legend gets too big and i have to maximize the chart, which i do not want. Is there a way to adjust the space according to the needed space? It is not clear what you mean by "Excel provides automatically equal space for all series" Why not just use abbreviations in the legends You do know you can drag the legend box anywhere you wish on the chart? best wishes -- Bernard V Lie...

IIf statement in query criteria 12-08-09
I am having a problem with the iif statement when I use it in the criteria for a query for a combo box (Combo2) that is based on the selection of another combo box (Combo1) Essentially, what I am trying to do is show all choices in combo2 if there has not been a selection in Combo1; And show a filtered selection in Combo2 if there is a selection in Combo1. I am trying to do this using the iif statement when setting the criteria in the Combo2 query. iif (isnull(combo1), No Criteria is Set, Criteria is set) I am having trouble with the "No Criteria is Set" part of...

Save DB connection and query within spreadsheet
Hi. I created a web form that dumps the data into a SQL server database. This form is for three, not so technical co-workers to use and need to make retrieving new data as simple as possible. The users would like to use Excel to retreive the data. Is there any way to imbed SQL server access from within Excel so that all the user has to do is open the spreadsheet and click refresh data? I've been playing around with it with DSN's and dbq files and can easily make it work myself, but I need to simplify the process. If I exit out of the spreadsheet, and go back in, I need to reestablis...

Calculating LN( ) within VBA
Is there any way to calculate the natural log (LN() in excel) of a number using VBA within Access? I can do Log () but I can't fine LN(). thanks Stratis Actually, the Log function in VBA gives you the natural log, not the base 10 log. As it says in the Help file, you can calculate base-n logarithms for any number x by dividing the natural logarithm of x by the natural logarithm of n as follows: Logn(x) = Log(x) / Log(n) The following example illustrates a custom Function that calculates base-10 logarithms: Static Function Log10(X) Log10 = Log(X) / Log(10#) End Function -- Doug St...

Hey PUTZKE - Ex Ill Gov Blagoievich Guilty of False Statements to FBI
Just FYI. Now it's your turn to post the same thing! Just FYI. ...

Bank Statement Download Issues
I am downloading statements from our credit union and can only receive transactions through 6/6/2005. There are many transactions after this date that are displayed on the website, but they do not download. Is there a setting somewhere that is preventing transactions after 6/6/2005 from being downloaded? Another issue: The same account shows that there are transactions to be read, but I cannot them to indicate that they have been read. Please advise. Thanks in advance for your assistance. ...

VLOOKUP with IF statement
I want to automatically produce a list of all events that will occur between two given dates. I am using VLOOKUP to search the column which has the date for each event. I want to return the names of all the events that occur before the user-defined dates. My formula looks like this: =IF('CH Milestones'!GM5<Sheet1!A$2,VLOOKUP('CH Milestones'!GM5,'CH Milestones'!GL$5:GM$87,2,TRUE),"") GM is the column with the dates. GL is the column with the event names. A2 has a user-defined date entered into it. (In the above formula I was only looking for dates b...

IIF Syntax error
I am trying to include the following condition in the control source of a label =IIf([Number of Courses]>=1 ,IFF([Course 1 Enrolled?] = “No”,”Course1”))+ IIf([Number of Courses]>=2 ,IFF([Course 2 Enrolled?] = “No”,”Course 2”)) +IIf([Number of Courses]>=3 ,IFF([Course 3 Enrolled?] = “No”,”Course3”)) and I get an Syntax Error. I tried the following variations and these are the results i got =IIf([Number of Courses]>=1 ,IFF([Course 1 Enrolled?] = “No”,”Course1”)) Result: Syntax Error =IIf([Number of Courses]>=1 ,IFF([Course 1 Enrolled?] = “No”,”Course1”, ""),"...

IIF function with variables
I have column A with about 10 different categories, and I want to create column B that is populated based on the entries in column A. If for row 1, Column A has categotry 1 through 5 then column B should read "web based", if it has 6 through 10 then it should be left blank. How do I adjust my Iif function to account for a variable in the "if true criteria" portion of the function? Expr1: IIf([soc]="Variable","web") -- --coastal One approach, rather than trying to build this dynamically within a query, might be to create a "lookup" ta...