Declaring date ranges for a form (1 Viewer)

UKFlamingo · May 22, 2017

Hey All!

I am trying to tell access how long a period is in a year, i.e. There are 26 periods in a year each lasting 2 weeks(14 Days) starting on the 1st of Jan and ending on the 31st of Dec.

I need this so that when I type the period into a text box it will update a list box for dates that match the period.

Is this possible?

Any help is much appreciated!

Uncle Gizmo · May 22, 2017

How do you currently handle the extra day?

Sent from my SM-G925F using Tapatalk

CJ_London · May 22, 2017

to avoid suffering from 'mission creep', 26 * 14=364, so you'll be missing the 31st Dec (plus 30th in a leap year). Is this OK? Please clarify what is to happen with these dates

UKFlamingo · May 22, 2017

Yes 364 days is okay as long as it starts on January 1st. And any extra days are fine. I need to make sure a period is 14 days long.

Uncle Gizmo · May 22, 2017

Each day has a number representing it. I'm reasonably sure there's a built-in function to return this day number.

The first thing I would try is a for next loop from 1 to 364 in steps of 14. Now you store the start range which on the first iteration would be one and the end range which on the first iteration would be 14.
You also need to record which iteration you are in. I reckon this would be a simple intCounter equals intCounter plus one.

Now check to see if your day number is between the start and end dates.

If it is, return the iteration number and this should be the period you want.

The start range and end range numbers represent the dates. Just convert these back into dates and use them to fill your listbox.

Sent from my SM-G925F using Tapatalk

Uncle Gizmo · May 22, 2017

Ah! I missed that, I see you are entering a period and not the date.

So just adjust the code I have suggested. Instead of inputting a date input the period number and compare that to the counter, from that you will be able to find the start of the Range and the end of the Range and convert those to dates.

Sent from my SM-G925F using Tapatalk

UKFlamingo · May 22, 2017

Sorry I can understand what i need to do but don't know how to write this out.

I'll tell you what my ultimate goal is and maybe that may simplify things.

I have a table (MatterList) that shows all of our active jobs and the date that they started.

On a form i want to have a combo box that when i select a number between 1 and 26 it will change the dates in a list box to show all the jobs that are active.

e.g I select "1" and it shows me all the active jobs between 1st of Jan and 14th of Jan. because it knows that 1 = that date range.

Sorry for the confusion should have started with this.

CJ_London · May 22, 2017

you will need a public function something like

Code:

Function getDatesStr(period as integer) as string
dim I as integer
dim dt as date
 
dt=dateadd("d", (period-1)*14,datevalue("01/01/" & year(date()))
for I=0 to 13
   getDatesStr=getDatesStr & format(dateadd("d",i,dt),"dd mmm") & ";" next I
 
end function

then in your code, your listbox rowtype would be set to values and you would assign the rowsource

mylistbox.rowsource=getDateStr(me.period)

ah - missed the last post so above is wrong solution, but the answer is there
listbox rowsource would be

SELECT ID, projname FROM tblProjects where active=true and datestarted between dateadd("d", ([period]-1)*14,datevalue("01/01/" & year(date())) and dateadd("d", (([period]-1)*14)+14,datevalue("01/01/" & year(date()))

then in your period after update event

me.listboxname.requery

UKFlamingo · May 22, 2017

Code:

Function getDatesStr(period as integer) as string

SELECT ID, OurMaps FROM tblMatterList where Job_Status=Live 
and Matter_OpenDate between dateadd("d", ([period]-1)*14,datevalue("01/01/" & year(date())) 
and dateadd("d", (([period]-1)*14)+14,datevalue("01/01/" & year(date())) 

end function

Have I got this right, I keep getting a Compile Error: Expected: Case

CJ_London · May 23, 2017

check my answer

listbox rowsource would be

...
...
..

then in your period after update event

me.listboxname.requery

CJ_London · May 23, 2017

having been advised of your comments on the other forum, I won't be responding to this thread anymore

UKFlamingo · May 23, 2017

Yeah no sweat buddy, Cheers for your help anyway!

Declaring date ranges for a form (1 Viewer)

UKFlamingo

New member

Uncle Gizmo

Nifty Access Guy

CJ_London

Super Moderator

UKFlamingo

New member

Uncle Gizmo

Nifty Access Guy

Uncle Gizmo

Nifty Access Guy

UKFlamingo

New member

CJ_London

Super Moderator

UKFlamingo

New member

CJ_London

Super Moderator

CJ_London

Super Moderator

UKFlamingo

New member

Similar threads

Users who are viewing this thread