dlookup different table?

[Guest]

Hi. I'm sure this has been answered but I searched and searched and couldn't
find it. I have two tables. Table A has a invoice # and invoice dates.
based on invoice dates, I want to return a value. For example:
If invoice dates between Date and date, "A","". Right now I have multiple
if statements in the query to return the value. Problem is that new values
are added based on the date so instead of me going in the query adding it, I
created a table B with these columns. Beging Date, End Date, Value. so if
the new dates and value need to be added all I or user have to do is add it
in the table. How would I go about doing something like : iif([invoice
date] between tableB.begingdate and tableB.endDate, "tableB.Value","") ??

Thank you!

 

[Guest]

"How would I go about doing something like : iif([invoice

date] between tableB.begingdate and tableB.endDate, "tableB.Value","") ?? "

I don't understand your question.
The above statement coulkd be done in a query but you might end up with more
than one row in the result set.

-Dorian

Hi. I'm sure this has been answered but I searched and searched and couldn't
find it. I have two tables. Table A has a invoice # and invoice dates.
based on invoice dates, I want to return a value. For example:
If invoice dates between Date and date, "A","". Right now I have multiple
if statements in the query to return the value. Problem is that new values
are added based on the date so instead of me going in the query adding it, I
created a table B with these columns. Beging Date, End Date, Value. so if
the new dates and value need to be added all I or user have to do is add it
in the table. How would I go about doing something like : iif([invoice
date] between tableB.begingdate and tableB.endDate, "tableB.Value","") ??

Thank you!

 

[Guest]

Table B has following:
BeginingDate EndDate Month
3/27/2005 4/26/2006 Jan 271+
4/27/2005 5/25/2006 Feb 271+
5/25/2005 6/24/2005 Mach 271+
6/25/2005 7/24/2005 April 271+
8/25/2005 9/24/2005 May 271+ *** New Dates****



right now, in my query I have multiple iff statements like so:

Roll Month: IIf([tbltag2].[invoice date] Between #3/27/2005# And
#4/26/2005#,"Jan 271+",IIf([tbltag2].[invoice date] Between #4/27/2005# And
#5/24/2005#,"Feb 271+",IIf([tbltag2].[invoice date] Between #5/25/2005# And
#6/24/2005#,"March 271+",IIf([tbltag2].[invoice date] Between #6/25/2005# And
#7/24/2005#,"April 271+","")))))

which works fine but when I have to add next set of dates, I prefer not
going into query to do so. Instead look up the table I have listed above and
return the value. So for example, if I add "May 271+" dates in the table,
query result will show.

"How would I go about doing something like : iif([invoice

date] between tableB.begingdate and tableB.endDate, "tableB.Value","") ?? "

I don't understand your question.
The above statement coulkd be done in a query but you might end up with more
than one row in the result set.

-Dorian

Hi. I'm sure this has been answered but I searched and searched and couldn't
find it. I have two tables. Table A has a invoice # and invoice dates.
based on invoice dates, I want to return a value. For example:
If invoice dates between Date and date, "A","". Right now I have multiple
if statements in the query to return the value. Problem is that new values
are added based on the date so instead of me going in the query adding it, I
created a table B with these columns. Beging Date, End Date, Value. so if
the new dates and value need to be added all I or user have to do is add it
in the table. How would I go about doing something like : iif([invoice
date] between tableB.begingdate and tableB.endDate, "tableB.Value","") ??

Thank you!

 

[Guest]

A "Non Equi Join" query is the ticket here. You don't need ANY IIF() function
calls at all!

SELECT TableA.*, TableB.[Month]
FROM TableA INNER JOIN TableB
ON TableA.[Invoice Date] >= [TableB].[BeginningDate]
AND TableA.[Invoice Date] <= [TableB].[EndDate]
--
John W. Vinson[MVP]

Table B has following:
BeginingDate EndDate Month
3/27/2005 4/26/2006 Jan 271+
4/27/2005 5/25/2006 Feb 271+
5/25/2005 6/24/2005 Mach 271+
6/25/2005 7/24/2005 April 271+
8/25/2005 9/24/2005 May 271+ *** New Dates****



right now, in my query I have multiple iff statements like so:

Roll Month: IIf([tbltag2].[invoice date] Between #3/27/2005# And
#4/26/2005#,"Jan 271+",IIf([tbltag2].[invoice date] Between #4/27/2005# And
#5/24/2005#,"Feb 271+",IIf([tbltag2].[invoice date] Between #5/25/2005# And
#6/24/2005#,"March 271+",IIf([tbltag2].[invoice date] Between #6/25/2005# And
#7/24/2005#,"April 271+","")))))

which works fine but when I have to add next set of dates, I prefer not
going into query to do so. Instead look up the table I have listed above and
return the value. So for example, if I add "May 271+" dates in the table,
query result will show.

"How would I go about doing something like : iif([invoice

date] between tableB.begingdate and tableB.endDate, "tableB.Value","") ?? "

I don't understand your question.
The above statement coulkd be done in a query but you might end up with more
than one row in the result set.

-Dorian

Hi. I'm sure this has been answered but I searched and searched and couldn't
find it. I have two tables. Table A has a invoice # and invoice dates.
based on invoice dates, I want to return a value. For example:
If invoice dates between Date and date, "A","". Right now I have multiple
if statements in the query to return the value. Problem is that new values
are added based on the date so instead of me going in the query adding it, I
created a table B with these columns. Beging Date, End Date, Value. so if
the new dates and value need to be added all I or user have to do is add it
in the table. How would I go about doing something like : iif([invoice
date] between tableB.begingdate and tableB.endDate, "tableB.Value","") ??

Thank you!

 

[Guest]

THANK YOU!!

A "Non Equi Join" query is the ticket here. You don't need ANY IIF() function
calls at all!

SELECT TableA.*, TableB.[Month]
FROM TableA INNER JOIN TableB
ON TableA.[Invoice Date] >= [TableB].[BeginningDate]
AND TableA.[Invoice Date] <= [TableB].[EndDate]
--
John W. Vinson[MVP]

Table B has following:
BeginingDate EndDate Month
3/27/2005 4/26/2006 Jan 271+
4/27/2005 5/25/2006 Feb 271+
5/25/2005 6/24/2005 Mach 271+
6/25/2005 7/24/2005 April 271+
8/25/2005 9/24/2005 May 271+ *** New Dates****



right now, in my query I have multiple iff statements like so:

Roll Month: IIf([tbltag2].[invoice date] Between #3/27/2005# And
#4/26/2005#,"Jan 271+",IIf([tbltag2].[invoice date] Between #4/27/2005# And
#5/24/2005#,"Feb 271+",IIf([tbltag2].[invoice date] Between #5/25/2005# And
#6/24/2005#,"March 271+",IIf([tbltag2].[invoice date] Between #6/25/2005# And
#7/24/2005#,"April 271+","")))))

which works fine but when I have to add next set of dates, I prefer not
going into query to do so. Instead look up the table I have listed above and
return the value. So for example, if I add "May 271+" dates in the table,
query result will show.

"How would I go about doing something like : iif([invoice
date] between tableB.begingdate and tableB.endDate, "tableB.Value","") ?? "

I don't understand your question.
The above statement coulkd be done in a query but you might end up with more
than one row in the result set.

-Dorian

:

Hi. I'm sure this has been answered but I searched and searched and couldn't
find it. I have two tables. Table A has a invoice # and invoice dates.
based on invoice dates, I want to return a value. For example:
If invoice dates between Date and date, "A","". Right now I have multiple
if statements in the query to return the value. Problem is that new values
are added based on the date so instead of me going in the query adding it, I
created a table B with these columns. Beging Date, End Date, Value. so if
the new dates and value need to be added all I or user have to do is add it
in the table. How would I go about doing something like : iif([invoice
date] between tableB.begingdate and tableB.endDate, "tableB.Value","") ??

Thank you!