d look up

[Chey]

I need some help with this
I have a table with 6 columns
If my Date Ticket Purchased is between this date and this date then LC
This is what my table looks like

ID LC CC FY FY Start Date FY End Date
1 06060130 6216407 06 7/1/2005 6/30/2006
2 06070130 6216407 07 7/1/2006 6/30/2007
3 06080130 6216407 08 7/1/2007 6/30/2008

So if the date ticket purchased fall between any of these dates then I want
for example the proper LC to show.
I need to do this for LC CC and FY
Once I get the first one I think I can do the rest.

Thanks so much

 

[ti976]

dlookup("[LC]","NAMEOFTABLE","[FY START DATE] >= #DATEVALUE# AND [FY
START DATE] >= #DATEVALUE#")
if you are using external date source:
dlookup("[LC]","NAMEOFTABLE","[FY START DATE] >= #" & extDATEVALUE &"#
AND [FY START DATE] >= #" & extDATEVALUE & "#")

 

[Chey]

I didn't realize this when I first started but I want to use TA Number Start
and End instead of Start Date and End. Plus it has to iditify this number
off of a report.
So what I have going is a report with the TA number on it
Then a subreport when I made out of the table I showed you. I added the TA
Number Start and Ta number end
so now this is what I have
ID LC CC FY FY Start Date FY End Date TA Start TA End
4 06070130 6216407 07 7/1/2006 6/30/2007 06CA070001 06CA070400
5 06060130 6216406 06 7/1/2005 6/30/2006 06CA060001 06CA060400
6 06080130 6216408 08 7/1/2007 6/30/2008 06CA080001 06CA080400

Can I still have it look at the TA number on the main report and Put it in
my expression on my subreport?

 

[Chey]

okay I have been playing around and this is what I have came up
=DLookUp("[FY]","FY","[ID] = 4") If I do it this way it works however I
don't care about he ID number
How do I get this to be [TA Number] which is out of a dif table is between
TA Start and TA End then the proper FY
So I have been trying to figure this out. [FY] is the column I want, "FY"
is the table and [ID] is the row. But what happens when I want the criteria
out of a dif table?

 

[John Vinson]

But what happens when I want the criteria
out of a dif table?

Maybe: Don't use criteria, and don't use DLookUp - create a query
joining the other table by ID.

John W. Vinson[MVP]

 

[Chey]

I am still not quite understanding. In my one table I have a list of about
400 TA numbers. Then I have another table with general information that will
change every year. So when I made the yearly table it all depends on the TA
number for which row of data it should call for. I don't know how I could
link something from these 2 tables. They don't really have anything in
common. I have the TA start and TA end, but it of course just states the
first and last number.
Any suggestions?

 

[John Vinson]

I am still not quite understanding. In my one table I have a list of about
400 TA numbers. Then I have another table with general information that will
change every year. So when I made the yearly table it all depends on the TA
number for which row of data it should call for. I don't know how I could
link something from these 2 tables. They don't really have anything in
common. I have the TA start and TA end, but it of course just states the
first and last number.
Any suggestions?

Chey, you've been doing this for several weeks: *you're assuming we
understand your data and can see your tables*.

I do not understand your data.
I do not even know what business you're in.
I do not know the structure of your tables.
I do not know what a TA number is.

Based on the above, I'm GUESSING that a "non equi join" query would
work: first join the TA table to the TA Start field, and edit the SQL
to

ON [TA] >= [TA Start] AND [TA] <= [TA End]

but... again... *I don't know if this will work, because you have
chosen to keep your table structures concealed from view*.

I'll help if I can... but I do need your cooperation.

John W. Vinson[MVP]

 

[Chey]

I am trying my best. I even copied and pasted my one table.
I joined two tables. Then opened the SQL
SELECT FY.LC, FY.CC, FY.FY, FY.[FY Start Date], FY.[FY End Date], FY.[TA
Start], FY.[TA End], [TA Numbers].[TA Number]
FROM [TA Numbers] INNER JOIN FY ON [TA Numbers].[TA Number] = FY.[FY Start
Date];
Where in here do I paste the info you gave me?

I am still not quite understanding. In my one table I have a list of about
400 TA numbers. Then I have another table with general information that will
change every year. So when I made the yearly table it all depends on the TA
number for which row of data it should call for. I don't know how I could
link something from these 2 tables. They don't really have anything in
common. I have the TA start and TA end, but it of course just states the
first and last number.
Any suggestions?

Chey, you've been doing this for several weeks: *you're assuming we
understand your data and can see your tables*.

I do not understand your data.
I do not even know what business you're in.
I do not know the structure of your tables.
I do not know what a TA number is.

Based on the above, I'm GUESSING that a "non equi join" query would
work: first join the TA table to the TA Start field, and edit the SQL
to

ON [TA] >= [TA Start] AND [TA] <= [TA End]

but... again... *I don't know if this will work, because you have
chosen to keep your table structures concealed from view*.

I'll help if I can... but I do need your cooperation.

John W. Vinson[MVP]

 

[John Vinson]

I am trying my best. I even copied and pasted my one table.

I must have missed that post. My apologies.

I joined two tables. Then opened the SQL
SELECT FY.LC, FY.CC, FY.FY, FY.[FY Start Date], FY.[FY End Date], FY.[TA
Start], FY.[TA End], [TA Numbers].[TA Number]
FROM [TA Numbers] INNER JOIN FY ON [TA Numbers].[TA Number] = FY.[FY Start
Date];
Where in here do I paste the info you gave me?

Try

SELECT FY.LC, FY.CC, FY.FY, FY.[FY Start Date], FY.[FY End Date],
FY.[TA
Start], FY.[TA End], [TA Numbers].[TA Number]
FROM [TA Numbers] INNER JOIN FY
ON [TA Numbers].[TA Number] >= FY.[FY Start Date]
AND [TA Numbers].[TA Number] <= FY.[FY Start Date];

This assumes that TA Number and FY Start Date are both of the same
datatype (text, or date/time) and that you want those records out of
TA Numbers between those dates.

John W. Vinson[MVP]

 

[Chey]

Thanks for hanging in there with me. I don't want to use the FY Start and
End date anymore. I don't think this will benifit me. I think if I just
stick with [TA Numbers].[TA Number] and TA Start and TA End I should be in
good shape. Plus those two are the same data type. (Text) So I altered it
a little and it seems to have worked. Then I played some more and realized I
had to add one more table. Now I have this

SELECT FY.LC, FY.FY, FY.[TA Start], FY.[TA End], [TA Numbers].[TA Number],
Traveler_LDAP_Dept_Division.CC
FROM FY INNER JOIN ([TA Numbers] INNER JOIN Traveler_LDAP_Dept_Division ON
[TA Numbers].[First and Last Name] = Traveler_LDAP_Dept_Division.[Travelers
Name]) ON FY.[TA Start] = [TA Numbers].[TA Number];
Where do I put this now?
FROM [TA Numbers] INNER JOIN FY ON ([TA Numbers].[TA Number]>=FY.[TA Start])
AND ([TA Numbers].[TA Number]<=FY.[TA End]);

I am trying my best. I even copied and pasted my one table.

I must have missed that post. My apologies.

I joined two tables. Then opened the SQL
SELECT FY.LC, FY.CC, FY.FY, FY.[FY Start Date], FY.[FY End Date], FY.[TA
Start], FY.[TA End], [TA Numbers].[TA Number]
FROM [TA Numbers] INNER JOIN FY ON [TA Numbers].[TA Number] = FY.[FY Start
Date];
Where in here do I paste the info you gave me?

Try

SELECT FY.LC, FY.CC, FY.FY, FY.[FY Start Date], FY.[FY End Date],
FY.[TA
Start], FY.[TA End], [TA Numbers].[TA Number]
FROM [TA Numbers] INNER JOIN FY
ON [TA Numbers].[TA Number] >= FY.[FY Start Date]
AND [TA Numbers].[TA Number] <= FY.[FY Start Date];

This assumes that TA Number and FY Start Date are both of the same
datatype (text, or date/time) and that you want those records out of
TA Numbers between those dates.

John W. Vinson[MVP]

 

[Chey]

Nevermind I figured it out

I am trying my best. I even copied and pasted my one table.

I must have missed that post. My apologies.

I joined two tables. Then opened the SQL
SELECT FY.LC, FY.CC, FY.FY, FY.[FY Start Date], FY.[FY End Date], FY.[TA
Start], FY.[TA End], [TA Numbers].[TA Number]
FROM [TA Numbers] INNER JOIN FY ON [TA Numbers].[TA Number] = FY.[FY Start
Date];
Where in here do I paste the info you gave me?

Try

SELECT FY.LC, FY.CC, FY.FY, FY.[FY Start Date], FY.[FY End Date],
FY.[TA
Start], FY.[TA End], [TA Numbers].[TA Number]
FROM [TA Numbers] INNER JOIN FY
ON [TA Numbers].[TA Number] >= FY.[FY Start Date]
AND [TA Numbers].[TA Number] <= FY.[FY Start Date];

This assumes that TA Number and FY Start Date are both of the same
datatype (text, or date/time) and that you want those records out of
TA Numbers between those dates.

John W. Vinson[MVP]