How to report on date differances (1 Viewer)

[Pari]

Hi,

I have an Access database that contains the start and end dates of a permit. I have been asked to check if anyone with a valid permit also has recevied a ticket.

Therefore I need to be able to identify from my list of tickets issued if they fall within the valid permit period.

Can anyone please advise on how this can be done?

Thanks. Pari.

 

[Brianwarnock]

TicketDate<=Permit_EndDate and >=Permit_Startdate

Brian

 

[Pari]

Thank you Brian,

The ticket table is called Notices and the lable Issue_date and the permit table is called Parking permits and the lables Issue_date and expiry_date.

Do you mind advising how this should be formatted please?

Regards. Pari.

 

[vbaInet]

Thank you Brian,

The ticket table is called Notices and the lable Issue_date and the permit table is called Parking permits and the lables Issue_date and expiry_date.

Do you mind advising how this should be formatted please?

Regards. Pari.

First create your query adjoining the necessary fields from both tables, and use what Brian gave as the condition. ensure that you amend the join to Show only those records where both fields match

 

[Pari]

Date: [Notices].[Issue_date]>=[Residents' Permits].[Issue_date] And [Notices].[Issue_date]<=[Residents' Permits].[Expiry_date]

I have tried to add the above but a bit confused with this.

sorry.

 

[vbaInet]

Date: [Notices].[Issue_date]>=[Residents' Permits].[Issue_date] And [Notices].[Issue_date]<=[Residents' Permits].[Expiry_date]

I have tried to add the above but a bit confused with this.

sorry.

Have you created your query that lists all the Residents, Start and End Permit Dates and their penalties? Once you've done that I can direct you on where to place Brian's code

 

[Pari]

Hi,

Yes I have created that query.

Regards. Pari.

 

[Brianwarnock]

Switch to Sql view and put this after the From clause

Where [Notices].[Issue_date]>=[Residents' Permits].[Issue_date] And [Notices].[Issue_date]<=[Residents' Permits].[Expiry_date]

or in design view put this

>= [Residents' Permits].[Issue_date] And >= [Residents' Permits].[Expiry_date]

in the CRITERIA of the [Notices].[Issue_date] column

Brian