Vlookup from the base table

[irfanparbatani]

Attachment is uploaded please help. its urgent.

[June7]

The expression in the query is not quite the one in your post. Instead of table Subbie_Clean_Data, the db has Table1.

The expression won't work if there is no data in the Prices table Road_Air field, which is the case for most of the records. And for the records with values, none are "Road" nor "Air". So of course nothing will match and the DLookup returns nothing. Example:

4 records in Table1 for RouteNo 526, 2 for Air and 2 for Road all for size 0.24, however, there is only one record in Prices for Route 526 Size 0.24 and does not have value in the Road_Air field. I put Air into the field and now Price value for 2 records shows in the query.

Bad data means bad results.

[irfanparbatani]

Hi June

Thank you for your help

I am using the below query, and it is not displaying correct data,
Price: DLookUp(Format([SvcDate],"mmm"),"Prices_table","Type='" & [Base_Table].[Type] & "' AND [Size]=" & [Base_Table].[Size] & " AND [RouteNo]=" & [Base_Table].[RouteNo] & "")

Where RouteNo and Size is Number and Type is Text. It does not return any prices though there are prices against them. Also I have this scenario where I have 3 same routes and sizes are different, it should display price against that route and that size, it is not performing a vlookup against that row. please help.

[June7]

Is that using the dataset in the posted db?

[irfanparbatani]

Yes its the same, the only thing is if there is even one column missing it would not pick up the price. I am attaching the current db.

[June7]

The Road_Air field in BNE_Subbie_Prices_Table still has no values for "Road" or "Air". No price data can be retrieved. Put "Road" and "Air" values into the table and the query works.

However, there is no reason to include BNE_Subbie_Prices_Table in the query.

[irfanparbatani]

The Road_Air field in BNE_Subbie_Prices_Table still has no values for "Road" or "Air". No price data can be retrieved. Put "Road" and "Air" values into the table and the query works.

However, there is no reason to include BNE_Subbie_Prices_Table in the query.

==============
Hi June

I have written few queries. but the result is not coming out. It just gives me just 0 values for both routeno and size, the dataset is the same.

Price3 : Dlookup(Format([SvcDate]), "Prices_Table", "[routeno]= " & [Table_Name].[routeno] &" And [size]= " &[tablename].[size] &"")

The above query just gives me wrong numbers when i execute the above query.

[June7]

Well, I added "Road" or "Air" to Road_Air field in some records in BNE_Subbie_Price_Table and the query works.

Don't know what else I can tell you.

Why does the example show Table_Name and tablename?

[alansidman]

crossposted: http://www.excelforum.com/access-tab...ml#post3785652

[irfanparbatani]

crossposted: http://www.excelforum.com/access-tab...ml#post3785652

No the query is still not sorted out.

I am not getting the desired output when combining 3 conditions in Dlookup.

Please help.

Please post your query. I need to see whats wrong with the one I have mentioned on the post.

[irfanparbatani]

No the query is still not sorted out.

I am not getting the desired output when combining 3 conditions in Dlookup.

Please help.

Please post your query. I need to see whats wrong with the one I have mentioned on the post.

=====================

This is the scenario

I have this table

Route	Type	Road_Air	JAN	FEB	MAR	APR	MAY	JUN	JUL	AUG
125	Liquid	Road	12	12	12	12	12	12	12	12
125	Metal		13	13	13	13	13	13	13	13
125		Air	14	14	14	14	14	14	14	14

If My route no is 125 and Type is Liquid and Road_Air = Road, Display Jan Price
If my route no is 125 and Type is Metal, then display second priceline i.e Jan = 13
If my route is 125 and Road_Air = "Air", then display third priceline for Jan = 14
If my route is 125 and above all criteria doesn't meet, display 4th price for Jan.


please help me with the above query.

[June7]

There is no Type field in the sample db. There is a Size field. Your query uses Size field. Previous posts all referenced Size field.

And this need to allow for variation in the number of conditions is new. That really complicates the DLookup.

Working with the posted db:

SELECT SvcDate, RouteNo, Size, Road_Air, DLookUp(Format([SvcDate],"mmm"), "[BNE_Subbie_Prices_Table]", "RouteNo='" & [RouteNo] & "' AND Size='" & [Size] & "' AND Road_Air " & IIf(IsNull([Road_Air]), "Is Null", "='" & [Road_Air] & "'")) AS Price
FROM Table1;

[irfanparbatani]

There is no Type field in the sample db. There is a Size field. Your query uses Size field. Previous posts all referenced Size field.

And this need to allow for variation in the number of conditions is new. That really complicates the DLookup.

Working with the posted db:

SELECT SvcDate, RouteNo, Size, Road_Air, DLookUp(Format([SvcDate],"mmm"), "[BNE_Subbie_Prices_Table]", "RouteNo='" & [RouteNo] & "' AND Size='" & [Size] & "' AND Road_Air " & IIf(IsNull([Road_Air]), "Is Null", "='" & [Road_Air] & "'")) AS Price
FROM Table1;

Thank you for the query. Can you please help with vba.

[June7]

VBA for what? Time for a new thread?