Results 1 to 13 of 13
  1. #1
    vsk1975 is offline Novice
    Windows XP Access 2007
    Join Date
    Mar 2018
    Posts
    7

    Post Arranging Exam Register numbers in access report

    sir, i have a query with starting register number and ending register number. by using these two register numbers how can i create a report as given below
    eg: starting number 344125. ending register number 344250 Pls help if a code is necessary. Thanks in advance


    Reg.Nos from To Total
    344125 344144 20 Nos
    344145 344164 20 Nos
    344165 344184 20 Nos
    344185 344204 20 Nos
    344205 344224 20 Nos
    344225 344244 20 Nos
    344245 344250 6 Nos

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    i dont know what '20 Nos' means.

    are you wanting to pick the report based on the range?
    do you want to report on the table shown?

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Use the query as the report's RecordSource.

    I don't recommend dynamic parameterized queries. Especially don't recommend input popups.

    Consider using a form for user input of criteria. If you don't want to use VBA, the query can reference controls on form for the dynamic parameters.

    Otherwise, use VBA to construct criteria string that can be used in the WHERE CONDITION argument of OpenReport. Review http://allenbrowne.com/ser-62.html

    Per your example, the criteria might be: [Reg.Nos from] BETWEEN 344125 AND 344245


    Advise not to use spaces nor punctuation/special characters (underscore only exception) in naming convention.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    vsk1975 is offline Novice
    Windows XP Access 2007
    Join Date
    Mar 2018
    Posts
    7

    Post

    Quote Originally Posted by ranman256 View Post
    i dont know what '20 Nos' means.

    are you wanting to pick the report based on the range?
    do you want to report on the table shown?

    Yes Sir I want a report as shown above . This is an examination seating arrangement . first row indicates the starting register number and ending register number in first exam room, 20 Nos means total number of students included in first exam room is 20.

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Looks like will need VBA to write records to a 'temp' table - table is permanent, records are temporary.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    How about this?

    Click image for larger version. 

Name:	rows.JPG 
Views:	27 
Size:	52.5 KB 
ID:	33237

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Yes, and apparently OP wants to accomplish with the given beginning and ending values (starting number 344125 and ending register number 344250) and automate creation of appropriate number of records to fit the range.

    Just had 'deja vu' sensation - feel like I've helped someone with very similar requirement. Oh, remember now and similar enough to use same solution. Writing records to temp table probably best approach. Something like:

    Code:
    Sub BuildRoster()
    Dim n As Integer, intRecs As Integer, y As Integer, z As Integer
    Dim dblStart As Double, dblEnd As Double
    dblStart = Me.tbxStart
    dblEnd = Me.tbxEnd
    CurrentDb.Execute "DELETE FROM Roster"
    intRecs = Int(dblEnd - dblStart) / 20 + IIf((dblEnd - dblStart) Mod 20 > 1, 1, 0)
    y = (dblEnd - dblStart) Mod 20 + 1
    z = 20
    For n = 1 To intRecs
        If n = intRecs And y <> 0 Then z = y
        CurrentDb.Execute "INSERT INTO Roster(StartNo, EndNo) VALUES(" & dblStart & ",'" & dblStart + z - 1 & "," & z & " Nos')"
        dblStart = dblStart + z
    Next
    End Sub
    Last edited by June7; 03-23-2018 at 11:35 AM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    vsk1975 is offline Novice
    Windows XP Access 2007
    Join Date
    Mar 2018
    Posts
    7
    sir could you please give an example of data base file using this code

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    No, I don't want to build your project. Why don't you just follow the instructions and make an attempt?

    Build table called Roster. Build a report that uses this table.

    Build a form for user to input the beginning and ending registration numbers. Include a button for "Create Roster".

    Put the suggested code in the button's Click event.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    vsk1975 is offline Novice
    Windows XP Access 2007
    Join Date
    Mar 2018
    Posts
    7
    sir as per your advice i have done it but while click on the button i get only the message compile error it shows the code window and gives the message expected "end of statement"

  11. #11
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Sorry, incorrect INSERT statement:

    CurrentDb.Execute "INSERT INTO Roster(StartNo, EndNo, NoRows) VALUES(" & dblStart & ", " & dblStart + z - 1 & ", '" & z & " Nos')"
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  13. #13
    vsk1975 is offline Novice
    Windows XP Access 2007
    Join Date
    Mar 2018
    Posts
    7
    Thanks, I will abide to the rules...sorry for my initial mistake

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Create an Access Random Question Exam
    By eahtrade in forum Access
    Replies: 1
    Last Post: 03-30-2017, 09:17 AM
  2. Replies: 2
    Last Post: 01-14-2015, 11:44 AM
  3. Exam in access example...
    By charly.csh in forum Access
    Replies: 1
    Last Post: 12-21-2014, 12:49 PM
  4. Register Access on my local machine
    By Philosophaie in forum Access
    Replies: 1
    Last Post: 05-07-2014, 06:42 PM
  5. Access 2007 77-605 exam
    By Sheri Kelly in forum Access
    Replies: 0
    Last Post: 10-01-2009, 02:53 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums