Next Date Criteria in Query

[ijaz8883]

Hi
I want next date formula in Query with Hearing_Date
spouse I have some dates i.e 25-02-2019 & 26-02-2019 & 27-02-2019 & 03-03-2019 & 28-03-2019 and If I open the date query of 25-02-2019 I want the Next_Date column of such ID as 26-02-2019 and if I search the query of 27-02-2019 I want the Next_Date column as 03-03-2019 of the specific IDs and as more as
The DB is attached


Thanks
sample - Next_Date.zip

[isladogs]

You need two copies of the date_cr table to the query designer but do not join them
Here is the SQL

SELECT Date_Cr.Dates_ID, Date_Cr.Hearing_Date, Date_Cr_1.Hearing_Date AS NextDate
FROM Date_Cr, Date_Cr AS Date_Cr_1
WHERE (((Date_Cr_1.Dates_ID)=[Date_Cr].[Dates_ID]+1))
ORDER BY Date_Cr_1.Dates_ID;

That will list all records.
If you want users to select a date as a parameter then possibly

SELECT Date_Cr.Dates_ID, Date_Cr.Hearing_Date, Date_Cr_1.Hearing_Date AS NextDate
FROM Date_Cr, Date_Cr AS Date_Cr_1
WHERE (((Date_Cr.Hearing_Date)=[Enter a date]) AND ((Date_Cr_1.Dates_ID)=[Date_Cr].[Dates_ID]+1))
ORDER BY Date_Cr_1.Dates_ID;

You may need to tweak to get specific values for a dates_ID field e.g. Tested on 27/02/2019 and you get more than one result/answer

[ijaz8883]

Hi isladogs
Sorry When I experiment on this trick I failed brutally. There are many errors
1. If I use it same as it is it does not show the last date of IDs in date search
2. I I use it -1 then it does not show the first record of IDs in date search
3 on the other hand it shows at the IDs first next date as last date of Previous ID which is much problem
just try to write the first or Last IDs date and then search its make problem
but All of these two problem the 3rd problem is worse that its pick the other IDs date as its last or first date
plz check

[ijaz8883]

I want little help more
Mr. accesstos provided me a module of (Get Path) "for exporting the files anywhere" but when I paste or copy paste its data to any other db its not work however, when I copy my all tbl, queries, forms from any other db to this db its work perfect. I don't understand that why the module is not activating on any other db. can you check it plz.
https://www.accessforums.net/showthr...t=75234&page=3

[ijaz8883]

Problem faced

[ArviLaanemets]

Use LEFT JOIN instead. Then when you are on last record, you get Null for NextDate instead of missing the last record in result.

SELECT d1.Dates_ID, d1.Hearing_Date, d2.Hearing_Date AS NextDate
FROM Date_Cr AS d1 LEFT JOIN  Date_Cr AS d2 ON d2..Dates_ID = d1..Dates_ID
ORDER BY d2.Dates_ID;

[ijaz8883]

Let me check!!!

[ijaz8883]

SELECT d1.Dates_ID, d1.Hearing_Date, d2.Hearing_Date AS NextDate
FROM Date_Cr AS d1 LEFT JOIN  Date_Cr AS d2 ON d2..Dates_ID = d1..Dates_ID
ORDER BY d2.Dates_ID;

Sorry its not working its showing same data in Next Date field.
Lets see I just required that If I entered Date_Hearing it shows it by search data of said date of query [Enter a Date] but the next date remain empty. and If I enter again more data in Hearing_Date it shows the last date in Next_Date (Now If I search the previous data its will show the next date which is recently added)

you may check in this example. It is manually added two dates in a subform of Date_Cr I just want that I may enter Hearing_Date and it automatically Enter in the Field of Next_Date on next ID automatically and I will type the Hearing_Date in same ID for the next time.

But we may leave it if I just want the query that the Hearing_Date should be shown ID -1 in other field.
Means I write 2 dates 01-02-2019, 15-02-2019 its shows the previous date in other field infrom of Hearing_Date
Register_ID Date_ID Hearing Date Next Date
3 125 02-02-2019 15-02-2019
3 129 15-02-2019
Next date should be null infront of 15-02-2019

12 225 09-02-2019 27-02-2019
12 129 27-02-2019 05-03-2019
12 325 05-03-2019

Now the Next date should be null infront of 05-03-2019
Whenever I dont enter the date in Hearing_Date field.
Hope now my explanation is clear?

[ArviLaanemets]

Looks like you need something like this!

[ijaz8883]

Oh Thanks. I Think you got it. let me check.

[ijaz8883]

Looks like you need something like this!

Thanks a lot its worked but When I pasted my data into it its give error

otherwise its working perfect

[ArviLaanemets]

On fly! Then you have 2 different hearings for same case on same date in your table!

[ijaz8883]

Yes you are right I have checked it and Its sure that Where its found 2 same dates on one ID its conflict. But now the problem is that I have to write the last date infront of Hearing date as NextDate of Hearing what to do for this?
Now the last date is conflicting which is dire need to end the IDs Nextdate as Hearing Date and I think There should be any rule to write the last date twice to show in Nextdate or else.
Let me now what to do for the End hearingdate that should be searched and its shows the NextDate as same or it may also be written manually but it should shows this too.

I have also practiced in report when I search the last date of hearing of any ID it shows blank column in NextDate but I tried to write twice last date but nothing happen and when open query its give the above said error. Now the last date is last matter at this stage.
Thanks

[ijaz8883]

plz help in last date to enter twice. How to remove restrictions and its shows the last date as it is???