auto numbering without auto number field

Hi All,
Using Access 2007
I need a field I will call "CASE_NO" that must be autogenerated with each 
new record. The number must be consecutive with the first record being 
07-001 the next 07-002 etc.... As I am already using the AutoNumber field in 
my table, I cannot use another so how would this be done.

Now here is where it gets sticky (although to you guys its probably a no 
brainer). At midnight on January 1 of every year the first two digits must 
change to the new year (08 for 2008, 09 for 2009 etc..) and the next 3 
digits will have to start back at 001. I am only using 3 digits as I dont 
feel that I will have over 1000 records in a given year but if that were to 
change, if I could incorporate that into the equation it would be better.

As always, I cant thank you all for the help.

Andrew J. Brofman
Chief Fire Marshal
Lake Grove, NY


0
firecop1
7/2/2007 4:24:23 PM
access.forms 6864 articles. 2 followers. Follow

8 Replies
1218 Views

Similar Articles

[PageSpeed] 36

Pieces that might help:

1)  Determine the current year: Right(Year(Date()),2)
2)  Parse existing data into two parts (in a query): Left([Field],2) and
Right([Field],3)
3)  Find the maximum dash number (and add 1): DMax("[DashComponent]","[Query]
","[YearComponent]='07'")
Instead of '07', use part 1 or something else that returns the year you want.
4)  Padding a numerical dash component: Right("000" & MaxDashNumber, 3)


firecop1 wrote:
>Hi All,
>Using Access 2007
>I need a field I will call "CASE_NO" that must be autogenerated with each 
>new record. The number must be consecutive with the first record being 
>07-001 the next 07-002 etc.... As I am already using the AutoNumber field in 
>my table, I cannot use another so how would this be done.
>
>Now here is where it gets sticky (although to you guys its probably a no 
>brainer). At midnight on January 1 of every year the first two digits must 
>change to the new year (08 for 2008, 09 for 2009 etc..) and the next 3 
>digits will have to start back at 001. I am only using 3 digits as I dont 
>feel that I will have over 1000 records in a given year but if that were to 
>change, if I could incorporate that into the equation it would be better.
>
>As always, I cant thank you all for the help.
>
>Andrew J. Brofman
>Chief Fire Marshal
>Lake Grove, NY

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200707/1

0
kingston
7/2/2007 4:49:00 PM
Ok its official, you lost me

I understand some of the concepts but being an amateur in every sense of the 
word here I am not sure where and how to set this up.

Do I need to add new fields to a table? Do I do this as an expression, as 
code or as a macro?

Sorry for the required expanded explainations but I am not that good yet.

Andy
"kingston via AccessMonster.com" <u27511@uwe> wrote in message 
news:7494319972799@uwe...
> Pieces that might help:
>
> 1)  Determine the current year: Right(Year(Date()),2)
> 2)  Parse existing data into two parts (in a query): Left([Field],2) and
> Right([Field],3)
> 3)  Find the maximum dash number (and add 1): 
> DMax("[DashComponent]","[Query]
> ","[YearComponent]='07'")
> Instead of '07', use part 1 or something else that returns the year you 
> want.
> 4)  Padding a numerical dash component: Right("000" & MaxDashNumber, 3)
>
>
> firecop1 wrote:
>>Hi All,
>>Using Access 2007
>>I need a field I will call "CASE_NO" that must be autogenerated with each
>>new record. The number must be consecutive with the first record being
>>07-001 the next 07-002 etc.... As I am already using the AutoNumber field 
>>in
>>my table, I cannot use another so how would this be done.
>>
>>Now here is where it gets sticky (although to you guys its probably a no
>>brainer). At midnight on January 1 of every year the first two digits must
>>change to the new year (08 for 2008, 09 for 2009 etc..) and the next 3
>>digits will have to start back at 001. I am only using 3 digits as I dont
>>feel that I will have over 1000 records in a given year but if that were 
>>to
>>change, if I could incorporate that into the equation it would be better.
>>
>>As always, I cant thank you all for the help.
>>
>>Andrew J. Brofman
>>Chief Fire Marshal
>>Lake Grove, NY
>
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200707/1
> 

0
firecop1
7/2/2007 5:07:48 PM
Here's what I recommend, but there are a lot of other ways to accomplish this.
Create a query based on your existing table (don't add fields to your table)
with two calculated fields.
YearComponent: Left([Field],2)
DashComponent: Right([Field],3)

At this point, run the query to see what the functions Left() and Right() do.
In addition, you can use Right(Year(Date()),2) or data from a form as the
criteria for the calculated field YearComponent.  After that, you can change
the order of DashComponent to descending and the properties of the query to
return Top 1 to find the maximum value.  The DMax() function does pretty much
the same thing but returns a value in code that you can work with.

You'll have to restrict creation of a new record so that it only happens with
an action that you control, e.g. a command button.  This action will create a
new record with the data you calculated using the pieces I presented before.
hth

firecop1 wrote:
>Ok its official, you lost me
>
>I understand some of the concepts but being an amateur in every sense of the 
>word here I am not sure where and how to set this up.
>
>Do I need to add new fields to a table? Do I do this as an expression, as 
>code or as a macro?
>
>Sorry for the required expanded explainations but I am not that good yet.
>
>Andy
>> Pieces that might help:
>>
>[quoted text clipped - 29 lines]
>>>Chief Fire Marshal
>>>Lake Grove, NY

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200707/1

0
kingston
7/2/2007 5:33:27 PM
On Mon, 2 Jul 2007 12:24:23 -0400, "firecop1" <ajb5540@optonline.net> wrote:

>Hi All,
>Using Access 2007
>I need a field I will call "CASE_NO" that must be autogenerated with each 
>new record. The number must be consecutive with the first record being 
>07-001 the next 07-002 etc.... As I am already using the AutoNumber field in 
>my table, I cannot use another so how would this be done.

Are you ABSOLUTELY CERTAIN that you'll never have 1000 cases in a year? (Hint:
if you have over 500 in 2006 the answer is No).

>Now here is where it gets sticky (although to you guys its probably a no 
>brainer). At midnight on January 1 of every year the first two digits must 
>change to the new year (08 for 2008, 09 for 2009 etc..) and the next 3 
>digits will have to start back at 001. I am only using 3 digits as I dont 
>feel that I will have over 1000 records in a given year but if that were to 
>change, if I could incorporate that into the equation it would be better.

You can use some VBA code in some appropriate form event (the Beforeinsert
event is handy unless you have many people inserting records concurrently).
Use code like:

Private Sub Form_BeforeInsert(Cancel as Integer)
Dim strYr As String
Dim vID As Variant
strYr = Format(Date, "yy") ' get the two digit year
vID = DMax("[CASE_NO]", "[tablename]", "[CASE_NO] LIKE " & strYr & "-*")
If IsNull(vID) Then
   Me!CASE_NO = strYr & "-001"
Else
   Me!CASE_NO = strYr & "-" & Format(Val(Mid(vID, 3)) + 1, "000")
End If
End Sub

             John W. Vinson [MVP]
0
John
7/2/2007 9:39:13 PM
John,
I am laughing as the jurisdiction that this is for only has 300 homes and 3 
commercial establishments If every place is inspected once and there are the 
usual 40 incidents annually I am at <350. I am pretty confident that I will 
be ok although I guess if it were close I could just change the "000" to 
"0000" in your code?

In your experience do you think it would be better to do it the way you 
describe below or would I be better off with a button on my form that when 
"pressed" it autogenerates this number and shows it in a popup field and 
then I manually enter it where necessary?

If that is a better option, how is it done and what if any fields would be 
necessary (a field to show the result or would I need a table to store the 
results so the databse knows what the next number to generate would be)/.

Andy
"John W. Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message 
news:n1li83pncshd37o1k2fh9m537bevnrrrub@4ax.com...
> On Mon, 2 Jul 2007 12:24:23 -0400, "firecop1" <ajb5540@optonline.net> 
> wrote:
>
>>Hi All,
>>Using Access 2007
>>I need a field I will call "CASE_NO" that must be autogenerated with each
>>new record. The number must be consecutive with the first record being
>>07-001 the next 07-002 etc.... As I am already using the AutoNumber field 
>>in
>>my table, I cannot use another so how would this be done.
>
> Are you ABSOLUTELY CERTAIN that you'll never have 1000 cases in a year? 
> (Hint:
> if you have over 500 in 2006 the answer is No).
>
>>Now here is where it gets sticky (although to you guys its probably a no
>>brainer). At midnight on January 1 of every year the first two digits must
>>change to the new year (08 for 2008, 09 for 2009 etc..) and the next 3
>>digits will have to start back at 001. I am only using 3 digits as I dont
>>feel that I will have over 1000 records in a given year but if that were 
>>to
>>change, if I could incorporate that into the equation it would be better.
>
> You can use some VBA code in some appropriate form event (the Beforeinsert
> event is handy unless you have many people inserting records 
> concurrently).
> Use code like:
>
> Private Sub Form_BeforeInsert(Cancel as Integer)
> Dim strYr As String
> Dim vID As Variant
> strYr = Format(Date, "yy") ' get the two digit year
> vID = DMax("[CASE_NO]", "[tablename]", "[CASE_NO] LIKE " & strYr & "-*")
> If IsNull(vID) Then
>   Me!CASE_NO = strYr & "-001"
> Else
>   Me!CASE_NO = strYr & "-" & Format(Val(Mid(vID, 3)) + 1, "000")
> End If
> End Sub
>
>             John W. Vinson [MVP] 

0
firecop1
7/3/2007 12:38:09 AM
On Mon, 2 Jul 2007 20:38:09 -0400, "firecop1" <ajb5540@optonline.net> wrote:

>John,
>I am laughing as the jurisdiction that this is for only has 300 homes and 3 
>commercial establishments If every place is inspected once and there are the 
>usual 40 incidents annually I am at <350. I am pretty confident that I will 
>be ok although I guess if it were close I could just change the "000" to 
>"0000" in your code?

<chuckle> Well, I live in a small town too but it's not THAT small. You're
fine for the next few years and your suggested change is right on the mark.


>In your experience do you think it would be better to do it the way you 
>describe below or would I be better off with a button on my form that when 
>"pressed" it autogenerates this number and shows it in a popup field and 
>then I manually enter it where necessary?

That's pretty much your call. If you go with a button you don't need to have
the user retype it - just explicitly set the control to the calculated value.

>If that is a better option, how is it done and what if any fields would be 
>necessary (a field to show the result or would I need a table to store the 
>results so the databse knows what the next number to generate would be)/.

Such a table is indeed useful if there may be multiple users updating the
database simultaneously; the BeforeInsert event is somewhat vulnerable in that
case, as user Joe might start a new record; get a CASE_NO assigned; ten
seconds later (while Joe is still editing the other data in that record) Jane
starts a new record and (since Joe's CASE_NO has not been saved) gets a
duplicate value. I use code from the Access Developer's Handbook which locks
the next-number table, gets a value, increments it, stores it back in the
table, and then unlocks it - preventing duplicates. It's even got some clever
wait code so the second user to try to get an increment doesn't get impacted
(much) - it just pauses their form for a random interval and tries again.

             John W. Vinson [MVP]
0
John
7/3/2007 3:08:30 AM
Ok John,
I entered the code as you wrote it to no avail. My "CASE_NO" field is a text 
field although I am unsure if that matters. I tried with the field on a form 
and then on a subform but creating new records does not populate the field 
in either place. (Was not using the field in two places simultaneously 
though).

 vID = DMax("[CASE_NO]", "[tablename]", "[CASE_NO] LIKE " & strYr & "-*")
I also changed the [tablename] to "[activities]" as that is where the field 
is.

I am sure it is something I am doing wrong however I am not sure what.
Unfortunately I dont understand what every part of the code does so I am not 
picking up on anything.

Andy

"John W. Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message 
news:64fj83pjrbqcqo9d0msoo6rhoktrl8p8ee@4ax.com...
> On Mon, 2 Jul 2007 20:38:09 -0400, "firecop1" <ajb5540@optonline.net> 
> wrote:
>
>>John,
>>I am laughing as the jurisdiction that this is for only has 300 homes and 
>>3
>>commercial establishments If every place is inspected once and there are 
>>the
>>usual 40 incidents annually I am at <350. I am pretty confident that I 
>>will
>>be ok although I guess if it were close I could just change the "000" to
>>"0000" in your code?
>
> <chuckle> Well, I live in a small town too but it's not THAT small. You're
> fine for the next few years and your suggested change is right on the 
> mark.
>
>
>>In your experience do you think it would be better to do it the way you
>>describe below or would I be better off with a button on my form that when
>>"pressed" it autogenerates this number and shows it in a popup field and
>>then I manually enter it where necessary?
>
> That's pretty much your call. If you go with a button you don't need to 
> have
> the user retype it - just explicitly set the control to the calculated 
> value.
>
>>If that is a better option, how is it done and what if any fields would be
>>necessary (a field to show the result or would I need a table to store the
>>results so the databse knows what the next number to generate would be)/.
>
> Such a table is indeed useful if there may be multiple users updating the
> database simultaneously; the BeforeInsert event is somewhat vulnerable in 
> that
> case, as user Joe might start a new record; get a CASE_NO assigned; ten
> seconds later (while Joe is still editing the other data in that record) 
> Jane
> starts a new record and (since Joe's CASE_NO has not been saved) gets a
> duplicate value. I use code from the Access Developer's Handbook which 
> locks
> the next-number table, gets a value, increments it, stores it back in the
> table, and then unlocks it - preventing duplicates. It's even got some 
> clever
> wait code so the second user to try to get an increment doesn't get 
> impacted
> (much) - it just pauses their form for a random interval and tries again.
>
>             John W. Vinson [MVP] 

0
firecop1
7/4/2007 1:18:24 AM
On Tue, 3 Jul 2007 21:18:24 -0400, "firecop1" <ajb5540@optonline.net> wrote:

>Ok John,
>I entered the code as you wrote it to no avail. My "CASE_NO" field is a text 
>field although I am unsure if that matters. I tried with the field on a form 
>and then on a subform but creating new records does not populate the field 
>in either place. (Was not using the field in two places simultaneously 
>though).
>
> vID = DMax("[CASE_NO]", "[tablename]", "[CASE_NO] LIKE " & strYr & "-*")
>I also changed the [tablename] to "[activities]" as that is where the field 
>is.
>
>I am sure it is something I am doing wrong however I am not sure what.
>Unfortunately I dont understand what every part of the code does so I am not 
>picking up on anything.

Text does matter, and you need some quotemark delimiters. Try 

vID = DMax("[CASE_NO]", "[tablename]", "[CASE_NO] LIKE '" & strYr & "-*'")

For clarity, that's


LIKE ' " & strYr & "-*' "

so that the criterion will be

[CASE_NO] LIKE '07-*'

once the pieces are concatenated.

             John W. Vinson [MVP]
0
John
7/4/2007 1:52:10 AM
Reply:

Similar Artilces:

Number Formatting #3
Thank you, now at least it makes sense! :) -- Nan ------------------------------------------------------------------------ Nan's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=6785 View this thread: http://www.excelforum.com/showthread.php?threadid=272052 You're welcome. See the thread started by M.Johnson about rounding. You might find something of use to you there too. Carlos "Nan" <Nan.1eop7y@excelforum-nospam.com> wrote in message news:Nan.1eop7y@excelforum-nospam.com... > > Thank you, now at least it makes sense! :) > &...

Add a new field Thru CRM schema manager.....
Hi, I want to Add a new field Thru CRM schema manager how can i do it.. Regards. Click the button ... New Attribute. "Richard I.P" <iamiamiam_77@yahoo.com> wrote in message news:68b0f2fd.0401200210.724d8ece@posting.google.com... > Hi, > > I want to Add a new field Thru CRM schema manager how can i do it.. > > Regards. ...

Need a tool to create large number of test mailboxes in exchange 2003...
I want to create large number of test mailboxes in Exchange 2003 server. Not getting any tool to do this. Could any one please let me know a tool or a script for me ? Regards, Shivaraj You can use ldifde or csvde. Read this: http://www.serverwatch.com/tutorials/article.php/2199331 "shivaraj" wrote: > I want to create large number of test mailboxes in Exchange 2003 > server. Not getting any tool to do this. Could any one please let me > know a tool or a script for me ? > > Regards, > Shivaraj > > ...

Handle Duplicate Primary Key Field at Time of Entry Instead of at Form Update
Note: This was originally posted at the queries group by mistake ...... Hi Folks - Currently, I am using the Error Event of my form to capture duplicate key entry. However, this is not invoked until the form updates. Is there a way to check for a duplicate after the user tabs out of the primary key field? Thanks. -- Michael "Jeff Boyce" <nonsense@nonsense.com> wrote in news:uScYzaNHIHA.284@TK2MSFTNGP02.phx.gbl: > Michael > > The Primary Key of a table is used to ensure each row/record is > unique. How is it that you are allowing your users to "crea...

Auto scroll with CDHtmlDialog #2
[this is a re-post of the post I made August 10. I tried setting up my MSDN email alias again with a new alias to see if it makes a difference] I am working on a little MFC 7.1 program for a friend and one thing I want to do is load HTML into a fullscreen window. I want the contents of this window to smoothly scroll automatically (no user input) and when it reaches the bottom to pause for a few seconds and then jump back to the top. So I need to know: 1. How to automatically vertically scroll the CDHtmlDialog window smoothly without any user input (with a user adjustable rate). 2. How ...

Numbering Images when saved as jpeg
When I save a presentation as jpegs, I'd like to have two or three digit numbering conventions ... instead of Slide1, Slide2, etc ... I'd like Slide01, Slide02 .... etc. Any way to do this? I'm using PPT 07 "AV Guy" wrote: > When I save a presentation as jpegs, I'd like to have two or three digit > numbering conventions ... instead of Slide1, Slide2, etc ... I'd like > Slide01, Slide02 .... etc. > > Any way to do this? In article <C43BD0B9-3178-424B-9000-874B33E1B49F@microsoft.com>, AV Guy wrote: > When I save a p...

Trying to add a field from another table on a form
I've added relationship between the form's master table and the other table. I've updated the control source on the txt box to the table/field that I need (=[table]![field]) but all of the fields are displaying as "#Name?" on my form. I'm sorry, I know this is probably something stupid. Use a query as the forms source and join the tables in the query. Redo the text box control source. -- Build a little, test a little. "FergusonH" wrote: > I've added relationship between the form's master table and the other table. > I...

inactive auto
<a href="<=======}==O"><=======}==O</a> ------------------------------------- How can I Automatic item Inactive when it goes to 0 on hand, is there anyway. can anyone help me. Also I need to know how not let the cashiers change the item on hand. tahnk you ##-----------------------------------------------## Newsgroup Access Courtesy http://www.rockryno.com/ Tax and Accounting Software Forums Web and RSS access to your favorite newsgroup - microsoft.public.pos - 39423 messages and counting! ##-----------------------------------------------## ...

New Contacts Field/Folder/Section
Hi, I have Outlook 2003. I would like to ask if it's possible to only view selected contacts when creating a new email? I would like to only see the contacts I have already set up in Outlook Express. I have a bunch of contacts in the contacts section of Outlook but I would like to only see about 20 or so contacts (that I choose) when creating a new email. Maybe this sounds ridiculous but I figured I'd give it a shot. Thanks for any help Jay Create a view of your Contacts folder that shows only those 20 contacts, then when it's time to write the e-mail, switch to that vi...

auto sort macro
I am trying to use a macro to sort a table of data on worksheet 3, whenever I update a value on worksheet 1 using Excel 2003 (Copy of macro below). Although the macro picks up the change in data on sheet 1 and runs the sort subroutine, nothing happens. I have discovered that by moving the tables to sheet 1 and repointing the macro to the data that it does work and while this is sufficient, I would like to know why the macro does not work when the tables are on sheet 3. Thank you for any advice anyone can provide Private Sub Worksheet_Change(ByVal Target As Range) If Not ...

How to stop Excel from auto updating chart?
Hi, I have written a VBA that will find some averages for me. It'll output the averages in the same range of cells no matter which monthly/seasonal averages I wanted. Is there a way to make the chart so that let's say...after I run the script to calculate the winter averages, then make a chart of it on a worksheet, when I re-run the script for summer, the chart wouldn't update to the summer data? In another word, can I have the chart to not update? I know under there's Tools->Option->Edit->Ask to update automatic links but I don't think this is what I'm loo...

Genrating text and auto number
How to generate auto number but i want it to be combination with text Example L0001, THEN L0002 and so on..how? -- Message posted via http://www.accessmonster.com Emily, What does the 'L' stand for ? Will this field contain other series of numbers that start with some other letter but which must also start sequencing at 00001? By storing more than one piece of information in a single database field, you are violating the first normal form of database design. I would recommend you store the letter designator in a separate field. Assuming that you have other letters, that mu...

Unmatching row numbers
Okay, I'm trying to use this formula to tell me if there is a matching number in another worksheet IF(Sheet1!$E$1:$E$65536 = "Value from Sheet2 (D3)","Found","") But Excell will only tell me it is "found" when the row numbers match, how can I overcome this setback. Thanks Again, ~E~ ------------------------------------------------- Dear Creperum You dont mention if the row numbers need to match (eg sheet1 d1=sheet2 e1=Sheet3 e1 or whether you only want sheet1 a1 to look at both ranges to see if the value is present. depending on which way you w...

How can I go to a specific page number in a large document
How do I go to a specific page number in a large word document. Read the replies to your earlier identical post -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ><<> <>>< ><<> <>>< <>><<> "yisaajao" <yisaajao@discussions.microsoft.com> wrote in message news:CFC4289D-FC93-4C1B-951B-DB08CED833...

Auto Calculate Question / HELP!
When I click on the fill handle to drag the data down the cells fill with data grabbed veritcally, for example: When I drag the fill handle down the formulas look like: =D21 =D22 =D23 =D24 =D25 But is there a way for Excel to be adjusted so that they attach the information laterally instead of veritcally? For example, I want to drag the auto fill DOWN but want the cells to read: =D21 =E21 =F21 =G21 =H21 Thank you! Try this... Assume you enter the first formula in cell A1 =INDEX(D$21:H$21,ROWS(A$1:A1)) Drag down as needed. Adjust for the correct end of range ...

Linking fields in different table in same database
I have a database (Access 2007) that is used for holding data on and communicating with FE colleges. Obviously (?) each college has a number of contacts (up to 14) and these are represented in two tables – one for College Details and one for Contact Details which are linked by a one to many relationship. Most colleges have one representative on one of 3 groups, but not all colleges are represented on all groups and some not on any. The membership of a group is noted by a field in the Contact details. Getting a query to run to identify membership of groups is OK. But I can’t fig...

PO number field length should go back to 20 characters
In previous RMS versions, PO number field length was 20 characters. With RMS 2.0, the field length shrunk to 15 characters for no apparent reason. I have already had 1 customers who changed to 2.0 complain about that. They have long PO numbers/Transfers numbers because the use descriptions in the transfer numbers. Others usning 1.2 and 1.3 may also have done this. Plus what happens to those long po numbers after conversion? ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the &qu...

How do I delete a CC account without deleting....
Dear group, Thanks in advance for any help that you are able to give in this matter. I have just upgraded from 2004 to 2005 deluxe. Both my checking account and credit card account transferred over in the upgrade. The credit card account is now closed and I would like to delete it, not just close it. When I delete it, I get a screen saying that if I delete, it will delete all accounts from that one bank. My checking and CC account are labeled from the same bank so if I delete the CC, it will wipeout my checking account information aswell. I dont like my options here; closing the accoun...

Calculated fields problem
I have calculated fields that do not update after changing the value of their precedent fileds but only one case which is getting inside the calculated field's formula bar and then pressing the "enter" button. Do you know what is the problem? Thanks. Hi have you checked tha automatic calculation is enabled? -- Regards Frank Kabel Frankfurt, Germany Shaul wrote: > I have calculated fields that do not update after changing > the value of their precedent fileds but only one case > which is getting inside the calculated field's formula bar > and then pressing ...

Replace a value with the one above it in a field
Hi, I have a transaction table that has fields such as Date, TransactionNumber, ItemNumber, Quantity and Amount. It is data taken from a transactional system and imported in Access. The ItemNumber fields can have values of our Item Numbers such as 3142 or it will have the value of Discount. What I want to do is to somehow run a query that will replace Discount with the value above it. So if there is one record with a value of 3142 in the ItemNumber field and the next record has Discount in this field I want to change this value to 3142. Ten records later there may be a value of 20...

required field error
I had already imported my data into a table and created a form. I realized that I didn't require a certain field to be required until after the form was completed. Can I go back and change that? It works when I add a new record, but I need it to be required for all records. Any help is greatly appreciated!!! "Jimmy" wrote: > I had already imported my data into a table and created a form. I realized > that I didn't require a certain field to be required until after the form was > completed. Can I go back and change that? It works when I add a new recor...

auto number columns in excel
Is there a way to set up a column of numbers so that it automatically updates the numbers every time a line is added or deleted? >-----Original Message----- >Is there a way to set up a column of numbers so that it automatically updates >the numbers every time a line is added or deleted? >. >Use =count(A1:A10) or more rows as required. If its the last row used - =row(B1) extended down to maybe B100. Then you can use =large(B1:B100) this will give you the last row used. Mal I either did not understand the reply given or was not clear in what I am trying to accom...

Auto Accept not removing cancelled meetings
The Auto Accept agent (running on E2k3 server) is not removing cancelled meetings from my resource calendars. Although the resource will let users book over the cancelled meetings, the calendar still shows the time slot as busy in the attendee availability view. If you go directly to the resource calendar, it will show the meeting as being cancelled. Am I missing a config somewhere that would totally remove the cancelled off the recource? Thanks in advance for any help! If you want a product that works properly, check out http://www.swinc.com/erm. -- Ed Crowley MVP - Exchange &qu...

Can you round numbers to display a specific set of numbers, for e.
I am working on a timesheet, and have a question. Due to a request by a user to be able to only enter time with a signle keystroke, for example, 8:00, only enter 8. Due to that, I have changed my cell formatting from time to numbers. Now, I need a formula that will round the number to a set of predetermined minutes. Example: If a user enters 8.25 for a time in, we would like a formula to change the number to 8.30. I need the formula to do this for the following entries: 0-14: 0 15-29: 15 30-44:30 45-59: 45 Any ideas, or am I very far off base? Try this =ROUNDDOWN(TIME(INT(A11)...

Comment indicators show as numbers in HTML
have recently posted a workbook to a server and am viewing the document as a web page. The comments I have created for certain cells show much better but the indicator is showing as a linked number [2] centered within the cell. It there a way to remove the numbers or to at least have the indicator show? ...