Math Help

[arttienet]

I have an artwork book which I am cataloging. Each page of the book contains 24 pices of artwork.
What I need to do is display a page number to whatever record number I enter into the field.

Example 2: I enter any record number from 1140 and the field will display 48 for the page the artwork is on in the book.

Example 2: I enter any record number from 1153 and the field will display 49 for the page the artwork is on in the book.

Example 3: I enter any record number from 457 to 480 and the field will display 20 for the page the artwork is on in the book.

Thanks for any help I can get on this

 

[Pat Hartman]

I'm sure that someone will come up with something slick but brute force =

If RecNum/24 <> RecNum\24 Then
    PageNum = (RecNum\24) +1
Else
    PageNum = RecNum/24
End If

 

[Steve R.]

My advice is based on the assumption that you would like a flexible and adaptable data base.

First, I would suggest dispensing with the concept of record number. Record numbers in Acess are relative.
----------------------------------------------------------------------
Second: Table structure.

IDNUM ARTNUM PAGENUM NOTES

IDNUM = unique auto number - record identifier.
ARTUNUM = The unique number for the artwork.
PAGENUM = The page number
NOTES = A memo field for any descriptive narrative for the artwork.
-----------------------------------------------------------------------
Third: You can create a form (FORMA) that has two fields and two command buttons, one field to retreive your data by ARTNUM and the other by PAGENUM.
-----------------------------------------------------------------------
Fourth: If you wish to retreive the page number for a particular piece of artwork you can use a command button with something similar to DoCmd.OpenForm stDocName, , , stLinkCriteria, where stlinkCriteria="[ARTNUM]=" & Me![Text2] and stDocName = "FORMB" This should return one record.
-----------------------------------------------------------------------
Fitfh: if you wish to retreive ALL the pieces of work on one page, you use a command button that can do the following. DoCmd.OpenForm stDocName, , , stLinkCriteria, where stlinkCriteria="[PAGENUM]=" & Me![Text3] and stDocName = "FORMC" This should return 24 records.
---------------------------------------------------------------------
This is only a skeleton outline and leaves a lot of issue unanswered, such as the use of a subform to display the 24 records retrieved when you want to see all the art work on one page. You may also want to scan each piece of art work into your database. Others may have addtional thoughts.

 

[arttienet]

Thank you for the info.
I am trying to keep this pretty simple since I am some what new to access.

I have field 1 with the name ArtworkNumber
and the second field with the name of PageNumber

What I am trying to make happen is I enter the artwork number in the ArtworkNumber field and it will display the page number from my book in the PageNumber.

All that is going to happen is the number will be displayed. I indicated record number in my previous post. I should have not used that term.


Still using the informaion I detailed before.
Example 2: I enter any record number from 1140 and the field will display 48 for the page the artwork is on in the book.

Example 2: I enter any record number from 1153 and the field will display 49 for the page the artwork is on in the book.

Example 3: I enter any record number from 457 to 480 and the field will display 20 for the page the artwork is on in the book.

Again Thanks

 

[tkoh78]

hopefully this helps

Here is a simple solution, especially if you are new to the Access. I am assuming that you are using a form to view the page numbers.

1. Create a form with an unbound combo box containing ArtWorkNumber.
2. In the same form, create an unbound list box containing PageNumber. Follow the steps below:
a. Cancel the pop-up wizard message.
b. In the property of the list box, click the data tab.
c. In the Row Source click "...". This opens a design view of a query.
d. Select the table where you entered your data.
e. Select the PageNumber field.
f. Select the ArtWorkNumber field, and make it invisible by unchecking show box.
g. In the creteria box of ArtWorkNumber field, type in forms![form name]![combo box name].
h. Save the query. Close the query.
i. Go to the property of the combo box. Under Event tab, click on After Update event. Click "..."
j. You want to create a macro, so click macro.
k. Give the macro a name. Under Action column, choose Requery.
l. Under Action Argument, type in the name of the list box, e.g. list1. Save the macro, close.

This should do the job. When you click or type the value in the ArtWorkNumber, you will see the page number that the art belongs to.

Please let me know if this works. I am writing this without trying it myself. Everything is in my head, so I might have misguide you at some point.

Thanks

 

[arttienet]

I do not know why I would need to create a query or a macro.

These are the only two fields on the form and they are unbound boxes.

Field 1 name ArtworkNumber and the second field with the name of PageNumber.

In the PageNumber field I entered =[ArtworkNumber]/24 but this is returning an incorrect number.

If I enter 1 in the ArtworkNumber it get 0.0416666666666667 in the PageNumber field it should display 1
If I enter 25 in the PageNumber field it should display 2 not 1.04166666666667.

very confusing


This is my original Post

I have an artwork book which I am cataloging. Each page of the book contains 24 pices of artwork.
What I need to do is display a page number to whatever record number I enter into the field.

Example 2: I enter any record number from 1140 and the field will display 48 for the page the artwork is on in the book.

Example 2: I enter any record number from 1153 and the field will display 49 for the page the artwork is on in the book.

Example 3: I enter any record number from 457 to 480 and the field will display 20 for the page the artwork is on in the book.

 

[ColeChaW]

In the PageNumber field I entered =[ArtworkNumber]/24 but this is returning an incorrect number.

If I enter 1 in the ArtworkNumber it get 0.0416666666666667 in the PageNumber field it should display 1
If I enter 25 in the PageNumber field it should display 2 not 1.04166666666667.

1 / 24 does equal 0.04166666
25 / 24 = 1.041666666666

You want to know what page the fraction of your division will occur on. So you need to find the page, like you've done. Since books don't begin on page 0, then you add 1. Use the INT() function to drop the decimal now.

Try =INT(([ArtworkNumber]/24)+1)

If you want to get fancy you could add the word 'Page' to your equation.

="Page " & INT(([ArtworkNumber]/24)+1)

I would also recommend some event to refresh the page.

sub cmdOK_Click
me.refresh
end sub
Take care,
-Charlie

 

[arttienet]

I like the ="Page " & INT(([ArtworkNumber]/24)+1)

That seems to work ok but when I enter 24 into the ArtworkNumber the PageNumber display Page 2 it should be Page 1
same thing when you enter 48 it goes to Page 3 should be Page 2
when you enter 72 it goes to Page 4 should be Page 3
Goes to the next higher page one number to soon.

How simple things can become so complex.

Thanks for you help

 

[ColeChaW]

Let's Cheat

I would just tilt the data a little in the direction we want. The trouble comes when the numbers divide evenly, so let's change 24 to 24.00001 so they never evenly divide. This should solve the trouble.

="Page " & INT(([ArtworkNumber]/24.00001)+1)

I just tested this up to page 1000 and it works. If the book ever gets to be a few million pages long we may have undesired results, but this will work for what you need.

Take care, chat with you later
-Charlie

 

[arttienet]

Thanks that worked
="Page " & INT(([ArtworkNumber]/24.00001)+1)

I tried ="Page " & INT(([ArtworkNumber]/24.1)+1) and .5 but did not think to go to .00001

I tested up to 20,000 works great.


Thanks for your help.
Art

 

[Pat Hartman]

I showed you the calculation earlier. It compares the different results from using the / and \ to divide a number. The \ truncates the result so it always returns a whole number.

 

[arttienet]

Pat
Thanks for the tip.
Art