Query involving dates (1 Viewer)

[BBK]

I want to add a textbox field to my Lease table that will contain the date that each tenant would like to pay their rent, so that each day I would be able to run a query to check who owes rent for that day.

EG: The textbox field will only contain a single number beteeen 1 and 31, eg: 15 (so this would mean the tenant would pay his rent on the 15th of every month)

I then can check my bank statements and enter the payment for these tenants for that particular date and update their payment record. Now I would like to be able check who has not paid for this date by using another query.

How do I go about doing this, and how to avoid Saturday and Sunday results, cause there would be no payments on these two days.

Im completly lost on this one (as usual), and any help, advice or assistance is greatly appreciated.

Thank you

I attached the DB if it makes it any easier to give advice to a novice like myself. Thank you once again.

 

[John Big Booty]

Check out the DateDiff() function

 

[BBK]

Thanks John for taking the time to reply and give advice, but im still not sure how i would use datediff....

I edited my original post with more info to my prediciment:
EG: The textbox field will only contain a single number beteeen 1 and 31, eg: 15 (so this would mean the tenant would pay his rent on the 15th of every month)

 

[John Big Booty]

You will need to reconstruct a date using your number lets call that X so that you can compare it with Date() using date DateDiff(). You Should be able to reconstruct your date using DateSerial() and DatePart() and might look something like;

DateSerial(DatePart("yyyy",Date()),DatePart("m",Date()),[B][COLOR="Purple"]X[/COLOR][/B])

You could then compare that with the current like using somthing like;

DateDiff("d",Date(),DateSerial(DatePart("yyyy",Date()),DatePart("m",Date()),[B][COLOR="Purple"]X[/COLOR][/B]))

 

[BBK]

Not able to get to grasp with this , sorry John

 

[John Big Booty]

At what point are you having trouble?

 

[BBK]

Well i have my textbox added as a number field, user can enter a number <32

Im not sure how or where i go about converting this number to a date using DateSerial.

My knowledge is very limited and along with my stupidity it makes it more difficult

 

[John Big Booty]

You could put an unbound text box on your form and put the following as it's Control Source;

=DateSerial(DatePart("yyyy",Date()),DatePart("m",Date()),[FieldName])

FieldName would be the name of the field in which the user is putting the number.

 

[BBK]

I could have sworn i done that and it gave me an error, but now it seems to be working fine.....

Thank for your time and patience John.

So now i create a query with the DateDiff to compare the dates, or am i wrong

 

[John Big Booty]

You could do it in a query, our you could do it in an unbound field on your form, depends on the the result you are looking for, and what you want to do with the result.

 

[BBK]

I want to be able to get a list of all Tenant that are due to pay today.

So if i have say 20 tenants that agreed to pay on the 30th of every month, when i run my query it will list just them 20 tenants

 

[John Big Booty]

Use a query then

 

[BBK]

SELECT tblTenant.FirstName, tblTenant.LastName, tblLease.RentAmount, tblLease.PaymentFrequency, tblLease.MonthlyRentDue, tblLease.DayOfPayment, tblLease.CurrentTenant
FROM tblTenant INNER JOIN tblLease ON tblTenant.TenantID = tblLease.TenantID
WHERE (((tblLease.CurrentTenant)=Yes)) and DateDiff("d",Date(),DateSerial(DatePart("yyyy",Date()),DatePart("m",Date()),DayOfPayment));

I can get it to list only all of the tenants, but not the ones that are due for today only. Would i be right in saying i need to add a 'GetDate' somewhere into my query?

 

[BBK]

Oops, i got it working....

Thanks a million John for all the advice, and your patience.

I added to your reputation

 

[John Big Booty]

Good work, glad you got it working and thanks for the rep points

 

[BBK]

John, your going to kill me.

I just had a thought. I have another problem, i am only going to run this query Monday thru Friday, but how am i going to pick up the tenants that are due to make payments if there payment day happens to fall on a Saturday or Sunday.

Any help once again is GREATLY appreciated, sorry to be a pain in the @$$

The SQL code i am using now is:

SELECT tblTenant.FirstName, tblTenant.LastName, tblLease.RentAmount, tblLease.PaymentFrequency, tblLease.MonthlyRentDue, tblLease.DayOfPayment, tblLease.CurrentTenant
FROM tblTenant INNER JOIN tblLease ON tblTenant.TenantID = tblLease.TenantID
WHERE (((tblLease.CurrentTenant)=Yes) AND ((DateDiff("d",Date(),DateSerial(DatePart("yyyy",Date()),DatePart("m",Date()),[DayOfPayment])))<>True));

 

[John Big Booty]

Just as a quick thought before I head off to stack a few Z's; you could do a logical test on your payment due date and if it returns a Saturday or Sunday, force it to either the Friday or Monday. Have a look at the various available Date functions here.

 

[BBK]

Thanks John for the reply, but that is way over my head. Any easier way to overcome this, i dont even know where to start