Solved Past Date and Current date on a report (1 Viewer)

[Chespirito]

Hello, I was wondering if any of you experts can help me with the following.
I have two tables "Tenants" and "Lease" I would like to print a letter where I can display the first lease date and also the current lease.
so if a tenant signed contracts 4 years ago and is renewing the lease now. I want to show the original lease date and the current date as well. I am not sure how to do this. any help would help. Thank you.

 

[Gasman]

Look at DMin() and DMax() functions. ?

 

[Chespirito]

Look at DMin() and DMax() functions ? I am not sure how to use it. I am new to access. I am sorry i failed to mention this was in MS access.
I would really appreciate it if you can do a sample function for Startdate (1/2/2018) and Current Date (5/7/2020) both fields are in the same table.

 

[plog]

Data questions are best communicated with data. We need to know what you are starting with and what you expect to end with. So, please post 2 sets of data that jive with each other:

A. Starting data from your table(s). Include table and field names and enough data to cover all cases.

B. Expected results of A. Show us what you expect to end up with when you feed in the data from A.

Again, 2 sets of data that jive between themselves (B should be based on A).

 

[Chespirito]

Look at DMin() and DMax() functions. ?

Look at DMin() and DMax() functions ? I am not sure how to use it. I am new to access. I am sorry i failed to mention this was in MS access.
I would really appreciate it if you can do a sample function for Startdate (1/2/2018) and Current Date (5/7/2020) both fields are in the same table

 

[Gasman]

Here are links to the functions

DMin() and DMax()

My thoughts were DMin would get you the very first lease date, and DMax woul get you the latest lease date.?

Look at the examples and replace the values with your own. pay attention to the criteria to pick up the correct recor for the tenant.

 

[Chespirito]

Data questions are best communicated with data. We need to know what you are starting with and what you expect to end with. So, please post 2 sets of data that jive with each other:

A. Starting data from your table(s). Include table and field names and enough data to cover all cases.

B. Expected results of A. Show us what you expect to end up with when you feed in the data from A.

Again, 2 sets of data that jive between themselves (B should be based on A).

Thank you for your reply:

I have a tenant that signs a contract say 1/2/2015 and ever year the contract is renewed. We send a letter with the original contract date at the top 1/2/2015 and the new contract date which is 5/7/2020. I am not sure how to get the original contract date 1/2/2015 on the report.
two fields. I hope that explains it.

 

[Gasman]

Thank you for your reply:

I have a tenant that signs a contract say 1/2/2015 and ever year the contract is renewed. We send a letter with the original contract date at the top 1/2/2015 and the new contract date which is 5/7/2020. I am not sure how to get the original contract date 1/2/2015 on the report.
two fields. I hope that explains it.

No more than your original post.

Post the names of the following fields/controls
Field that holds lease start date
Table name that holds the data
Field that holds the indentifier for the tenant, whether that is numeric or alphanumeric.
Name of the control on the form that holds the tenant indentifier

 

[plog]

I hope that explains it.

Not looking for an explanation. As I explained, I'd like data.

 

[Chespirito]

No more than your original post.

Post the names of the following fields/controls
Field that holds lease start date
Table name that holds the data
Field that holds the indentifier for the tenant, whether that is numeric or alphanumeric.
Name of the control on the form that holds the tenant indentifier

Field Name: StartDate
Table Name: Lease

The same field name holds all the dates for the contract. I would like to display the original contract date and the current contract date on the report, so the original date has an ID # and every time I create a new lease date the new date gets an ID #. Each contract date gets a new ID (new record. I have a query with two tables. Tenant table (holds tenant info) and Lease table (holds lease dates and prices) both tables are related.

On the report I want = original contract was signed 1/2/2015 and your new lease starts 5/7/2020

 

[Gasman]

You haven't given the rest of the info I asked for?
You muist have a FK for the tenant to link to the contract?

If you cannot follow the simple examples that I linked to, then you need to give the *EXACT* named data, else the statement is not going to work?

We need to say (in English) 'Give me the min/max StartDate in table Lease where TenantID =the TenantID on the form for the record I am working on'

 

[Chespirito]

You haven't given the rest of the info I asked for?
You muist have a FK for the tenant to link to the contract?

If you cannot follow the simple examples that I linked to, then you need to give the *EXACT* named data, else the statement is not going to work?

We need to say (in English) 'Give me the min/max StartDate in table Lease where TenantID =the TenantID on the form for the record I am working on'

GasMan I am sorry, I am new to this. This is my first DB I am working on. Thank you for your patience. I don't what FK means.

I followed the link and this is what I have

In the query I typed =DMin("StartDate","Tenants Extended") i get only one date for all the records. I need each date to match each LeaseID #.
I am sorry if it feels like you are pulling teeth. Thank you so very much for the time and effort.

 

[Gasman]

That is OK, at least you are trying.
You need to also tell the function what particular lease you are looking for? This is why I asked for the names of your fields/controls otherwise the function will not work.

Let's try anyway

dtContractFirstDate = DMin("StartDate","[Tenants Extended]","LeaseID=" & Me.LeaseID)

Firstly you previously stated that the table name was Lease?, now it is [Tenants Extended] ? (You need to enclose names with [] if they have spaces in them, best not to have them).

So I am *assuming* that the field in the table is called LeaseID and the control on the form for the contract you are working on is also LeaseID

That LeaseID is known as a foreign key (FK) in the table, as it links the lease to the contract. The ContractID would be the primary key (PK).

HTH

 

[Chespirito]

That is OK, at least you are trying.
You need to also tell the function what particular lease you are looking for? This is why I asked for the names of your fields/controls otherwise the function will not work.

Let's try anyway

dtContractFirstDate = DMin("StartDate","[Tenants Extended]","LeaseID=" & Me.LeaseID)

Firstly you previously stated that the table name was Lease?, now it is [Tenants Extended] ? (You need to enclose names with [] if they have spaces in them, best not to have them).

So I am *assuming* that the field in the table is called LeaseID and the control on the form for the contract you are working on is also LeaseID

That LeaseID is known as a foreign key (FK) in the table, as it links the lease to the contract. The ContractID would be the primary key (PK).

HTH

 

[Chespirito]

Gasman, I get an error message (#Name?). Let me try one more time since not having the correct info is difficult to figure out and I understand.
Image1 shows the field "Original lease date" where I want the result in. Look Image2 two see the control names and Image3 is where I would ultimately display to print out. I try uploading DB but it was too large. I thank you very much.

 

[Gasman]

If you are uploading the DB, make sure no personal info is in it?
You would need to zip the DB (right click and Send To/Compresses)
The file size allowed now is pretty generous, so that should not be a problem.?
Copy the whole DB and remove pictures/atatchments, then Compact & repair. Zip and upload.

Looking at the pics, you appear to have an original date
If you just want it in the form like in Pic 2, then put an = at the start. Also you did not surround the table name with [] because you have a space in the name?

 

[Chespirito]

I compressed the DB and put it in a zip folder 24.6 MB. it still says the file is too large. I only have two records in the DB. The name Lease Extended is the name of the query that joins "Tenants" and "Lease" tables.

 

[Chespirito]

Look at "Lease" form and that's where I need the date in the field. and ultimately in a report.

 

[Gasman]

OK,
I have put the DMin() function in your control. That appears to work.
I also amended your TenantsExtended query to bring in the original lease start date from a new query that produces just the orig startdate for each TenantID. That shpuld show you how to bring in that data for your report. Do not to use the domain functions in your queries, as that is not recommended.
Try and understand what is happening here, as all the domain functions DMin,DLookup etc work much the same way.
HTH

 

[Chespirito]

Excellent! that did it. Thank you very much.