Question Dlookup

gctsundar

Registered User.
Local time
Today, 21:07
Joined
Aug 7, 2008
Messages
17
i am having 2 tables
1> name, passportexpirydate
2> Expiryname, duration


1> aaaa , 02-02-2008
bbbb , 02-05-2009
cccc , 07-08-2024


2> passport , 60
visa , 30
insurance , 15

i have a text box in the form which should display the count of the name for whom the passport is going to expire in the mentioned duration from today.

count = (datenow - date of expiry) >= 60

how can i acheve this using d look up..

at present i am hard coding 60
=DLookUp("Count([Date of Expiry])","[Honeywell Travel Tracker]","[Date of Expiry]<(Now()+60)")

but that 60 needs to be fetched from another table. pls help me out with this issue.
 
Why use a lookup? Why not do a select and join the two tables... ??? Much easier...
 
Pls Send Out Some Examples Where U Can Bind Some Value To The Front End By Coding At The Back End Using Join Queries.
 
Well, how would you join these two tables??
I presume you also have fields VisaExpiry date etc?

Something like
Code:
Select name, passportexpirydate, Date - [passportexpirydate] >= [PassPortDuration]
from YourTabl1
, (Select Duration as PassPortDuration 
   from YourTable2 
   where Expiryname = 'passport')
That should do more or less what you are looking for...

Disclaimer:
Above is Air code and may/will contain some errors that you need to iron out but it is the general idea.
Also you will need to expand on this to incorporate everything...

Note:
I didnt use something from table1 which I do expect to be there.
You have PassPortExpirydate, unless you have 3 more fields for each type you mentioned... which might prove to be bad design... you will have a IDType somewhere that should contain Visa, Passport etc so you only have 1 expirydate.
But I presummed you would have (instead) a seperate field VisaExpiryDate... (again this may or may not be the optimal design, but you probably have to live with it)
 
i have attached the application. in the form named MAIN. i have hardcoded the value as 60 in the passport details. can you please write a query at the back end and send me.
 

Attachments

Hiya,

If you want to populate the form via the two tables

You should base the form on a query of the two tables

go to form properties and under 'record source' create a query selecting all from both tables

Then your'll be able to link the fields in

If this isnt what your after let me know
 
You really shouldnt use spaces in any names anywhere that will be hidden to the ultimate user

You should use Prefixes in names... tbl for tables and frm for forms... qry for queries etc...

All this will help you in the future...

Take this query and save it as qryPassportDuration:
Code:
SELECT ExpiryDetails.Name
,      ExpiryDetails.Duration AS PPDuration
FROM   ExpiryDetails
WHERE  ExpiryDetails.Name="Passport";
Now make a second query for your endresult:
Code:
SELECT [Travel Tracker].S_No
,      [Travel Tracker].Name
,      [Travel Tracker].[Passport No]
,      [Travel Tracker].[Date of Issue]
,      qryPassportDuration.PPDuration
,      [date of expiry]-Date() AS Calculation
,      [date of expiry]-Date()>=[PPDuration] AS Calculation60
FROM   [Travel Tracker]
,      qryPassportDuration;

I hope you understand whats goings ons...
 
hey this is not the exact thing i am looking for.. i need the count of the persons for whom the passport is getting expired with in 60 days in the text box in the MAIN form.

i am not aware of how to use these queries for fetching the datas in the UI.

pls incorporate in the attached zip earlier and send me.
 
I have given you a clear cut example of how to calculate and fetch the data you need
This query limits your resultset to only the ones that will expire within the [duration]
Code:
SELECT [Travel Tracker].S_No, [Travel Tracker].Name, [Travel Tracker].[Passport No], [Travel Tracker].[Date of Issue], qryPassportDuration.PPDuration, [date of expiry]-Date() AS Calculation, [date of expiry]-Date()>=[PPDuration] AS Calculation60
FROM [Travel Tracker], qryPassportDuration
WHERE ((([date of expiry]-Date())<[PPduration]));


Using that but doing it a little different... you can calculate what you need...
Code:
SELECT Sum(Abs([date of expiry]-Date()<=[PPDuration])) AS PPExpiry
FROM [Travel Tracker], qryPassportDuration;
Just expand on this and use the query created as the recordsource for your form.
 
i am setting the query as record source and i am fetching the passport expiry details in the text box.. similarly for the visa also i have to write a query and i have to display the value. how to set 2 or more record sources for a single form.
 
You dont... you only have one recordsource for the form.

But you can expand the ONE query to incorporate all the counts.
 
Perhaps posting your final query and/or DB can help future visitors... ;)
 
sweet:P

namliam's advice of not using spaces in names is very important,
I spent a whole morning going through my db replacing spaces and it wasn't fun:(
 
As a lost note I would like to add that your controls in your main form are called "Text22" etc...

It is also a good idea to give ALL your controls usefull names instead of the default ones... It will help you with further development and future support.

P.S. It doesnt look anywhere near what I suggested, but it works ....
 

Users who are viewing this thread

Back
Top Bottom