start at nth record then select every 15th record after

[Slimjimmpc]

access 2000

The query is basically getting the user to enter a month/year and a number from 1 to 15 which would select a row within that month then also select every 15th record.

So if the user entered 01/2011 and the number 4 it would select all records in jan start at the 4th record then add 15 to the record number(19,34,49 etc) until the end of the month.

Using Like "*0" brings back every 10th record but i want to start based on the number the user would enter then select every 15th record onwards.

Guessing an easy way would be to use IF statements just don't know how to select one nth record then every 15th after that

 

[Brianwarnock]

In the design grid


Field (([recnum]-[p])/15-(Int(([recnum]-[p])/15))=0)=True

Criteria True

where recnum is the recordnumber field and P is the starting record number

Brian

 

[Slimjimmpc]

In the design grid


Field (([recnum]-[p])/15-(Int(([recnum]-[p])/15))=0)=True

Criteria True

where recnum is the recordnumber field and P is the starting record number

Brian

Excellent. It works like a dream, thanks so much.

Only problem i have now is to figure out how to open a form on a specific record from what the user would enter(date, ranger, site).
If you have any ideas it would be greatly appreciated.

Unless i could use a query and a new form.

 

[naungsai]

Excellent!!!
I have tried it.
It works.

 

[Slimjimmpc]

So its turns out it does work but now i can't get the date parameter to work with it

Format([Date],"mm/yyyy")


[Enter Date MM/YYYY]

So anyone know how i can use both the date one and the nth record parameters to work together?


Also have a problem trying to parameter a location which is a text field.

using
Like "*" & [Enter Site] & "*"

and it doesn't work


*EDIT*

They enter a month [Enter Month MM/YYYY] e.g. 01/2011
Then they enter a number [Enter Number 1-15] e.g. 4
Query selects the 4th record within 01/2011 then selects the 19th record, 34th record, etc

 

[Brianwarnock]

I cannot think of any reason why it does not work, if your DB is not 2007 or2010 then if you can attach a sample I'll take a look.

Brian

 

[Slimjimmpc]

Ok thanks alot.

Take a look and try anything.

The main problem is the 1-15 misuses on the patrol sheet form.

The user needs to be able to pick out one of those rows by month and then another by month and site.

If you can think of a better way of doing the entire database, tables/relationships to make that easier i am all ears.

Whats is on the Patrol Sheet Form is everything that is needed.

 

[Brianwarnock]

Had a quick look but hey its more complex than expected so not sure I'll get much of a look today, however yourcriteria is not correct.

Why is [Enter Sign of Misuse/Incident Number 1-15] being entered in the criteria? Plus if it were needed then the other criteria cols must cover all rows.

Brian

 

[Slimjimmpc]

Had a quick look but hey its more complex than expected so not sure I'll get much of a look today, however yourcriteria is not correct.

Why is [Enter Sign of Misuse/Incident Number 1-15] being entered in the criteria? Plus if it were needed then the other criteria cols must cover all rows.

Brian

Well i need to be able to do reports by bringing back one particular misuse/incident and supplying information such as the patrol it belongs too, location, new, action taken which is where they would enter 1-15 (e.g 7=litter and would bring back all rows related to litter within the month selected)
so in my mind the user would need to select a misuse/incident via a parameter and also the month.

Like i said if you can think of a better way of doing the tables/relationships i don't mind starting again. Everything on the patrol sheet form is what was given as a word document, the major problem is figuring out that checklist.



Added the original document

 

[Brianwarnock]

But if you look at the SQL for test2 the Where clause makes no sense, you are not testing the incident number against a field with it in. To be honest I'm not sure about your DB design but I'm unlikely to have time to really consider it, my wife is under treatment for cancer and I tend just to pop on here to answer simple questions requireing not much timefrom me.

The issue I have is what happens if a 16th incident type is added, I would be thinking of coding the incident types and looking for them by code and only storing positive reports, your patrol form would probably not have all the incidents shown but the incidents selected from drop down lists.

Perhaps somebody with more time and whose expertise is not as rusty as mine could help

Brian

WHERE ((((([id number]-[Enter Sign of Misuse/Incident Number 1-15])/15-(Int(([id number]-[Enter Sign of Misuse/Incident Number 1-15])/15))=0)=True)=[Enter Sign of Misuse/Incident Number 1-15])) OR ((((([id number]-[Enter Sign of Misuse/Incident Number 1-15])/15-(Int(([id number]-[Enter Sign of Misuse/Incident Number 1-15])/15))=0)=True)=True) AND ((Information.[Yes/No])="Yes"));

 

[Brianwarnock]

the incident is related to the record number therefore that is all you need. One row of criteria, see attached screen image
note that having looked at the where clause in SQL I realised that I had gone OTT with the formula all you need is

([id number]-[Enter Sign of Misuse/Incident Number 1-15])/15-(Int(([id number]-[Enter Sign of Misuse/Incident Number 1-15])/15))

and 0 in the criteria.

Brian

 

[Slimjimmpc]

the incident is related to the record number therefore that is all you need. One row of criteria, see attached screen image
note that having looked at the where clause in SQL I realised that I had gone OTT with the formula all you need is

([id number]-[Enter Sign of Misuse/Incident Number 1-15])/15-(Int(([id number]-[Enter Sign of Misuse/Incident Number 1-15])/15))

and 0 in the criteria.

Brian

Thanks for your time anyways.

I decided to redo it all and eliminate the yes/no and change them into the misuse combo box. This way i don't get lots of empty records taking up space and searching for them becomes very easy.

To me it seems more logical to eliminate yes/no and save space and response times. If they demand them in though i am back to square one

 

[Brianwarnock]

I presume that you will base the misuse combo on a misuse table of 2 fields - Code and Description, this could then be used in an outer join query to give the no results, ie if listing all misuse at a site it could show the Null occurences which you could convert to a No.
I was going to suggest such a table again , I did earlier for adding incident types I think, but anyway it would replace the 3 switch expressions with a simple DLookup or maybe even a join if the code is carried elsewhere.

One other point regarding syntax or rather naming of objects, avoid spaces or special characters except the underscore, this becomes more of an issue in VBA when they prevent the use of intellisense, infact it replaces spaces in names with underscores.
So End Date becomes End_Date

Best of Luck

Brian