I have 2 tables one with an "NCO " number in it and another table with a "PCO" number in it
the field name is NCONO and the number could be "12345"
the field name BATCH NO could be "PCO1234"
the table that holds the PCO number is called orders
the table that holds the Batch in is called order details
I have a form that I want to put the LAST ncono and batch number into it that was entered into the tables ...
In Access 2002, I have a Phone Number field (Text) that is meant to store (obviously enough) phone numbers. However, when I enter 2009 as the last four digits (ex. (555)-555-2009) the number is changed to (555)-555-2010. This also occurs if I enter the number directly into the table (which doesn't have an input mask). I have tested it in a new form (even without input mask), and a new table, which both result in the exact same thing.
View 2 Replies View RelatedOccasionally a user of mine needs to reset a sequence number in a table.
A few months ago I made him a simple app that has two text entry boxes and a button. The first text entry box uses a dlookup:
Code:
=DLookUp("sSessionNumID","qryGetAPIsessionLastValue")
...the other textbox ties to an update query, which updates the sequence number with the new value supplied by the user. Simple.
Then about a week ago it stopped running the dlookup part - nothing shows in the text box that is to show the current sequence num.
Oddly, if I enter a value in the update text box and press the button to update the sequence number (which still works) -- the previously dormant dlookup textbox now shows the new value - so it works ...it just doesn't want to work on this one workstation unless the update query is run first.
What can this be? The update button merely calls a DoCmd.RunSQL with both an insert (storing old value locally in ms access table) and an update (updating the sql server table's seq number using an ODBC DSN and sql server driver).
It's almost like the dlookup falls asleep now and won't wake up until the update is run...
The "PersonalDetails" table is related to the "Application" table using the autonumber from table, "PersonalDetails".
The "PersonalDetails" table has "StudentID" (autonumber), "First Name" and "Last Name" fields.
The "Application" table also has "StudentID", "First Name" and "Last Name" fields.
The "Application" table is related to the "PersonalDetails" table using the "StudentID" field.
How do I make it so that the first and last names on the "Application" table are automatically updated when the StudentID is entered?
I would like to be able to copy a single record in a table and then paste this record a pre-defined number of times 10,20,30 depending on requirements. The reason I need to do this is that I want to set up a number of identical records that can easily be amended into unique records. This will vastly reduce the amount of work and time spent entering records individually.
The only way that I have been able to achieve this is to copy and transfer the master record into Excel, and then copy the row and paste it into a range of cells. Copy the range of cells back from Excel and then paste this range back into the Access table.
This works but is long winded; what I would like to know is is there a simply way of achieving this.
I'm trying to get an invoice number field to auto generate the next number, keeping the format as "00000"...this is what I have, which gets the next number but drops the leading 0
Code:
Private Sub Customer_AfterUpdate()
If Len(Me.[InvoiceNumber] & vbNullString) = 0 Then
Me.[InvoiceNumber] = (DMax("[InvoiceNumber]", "[tblInvoiceNumber]") + 1)
DoCmd.RunCommand acCmdSaveRecord
End If
End Sub
invoice numbers are 04024, 04025 etc...how I keep the formatiing?
I have a table with an auto number PK. This table will contain orders. I'd like to use the PK from this table as the Invoice number on the invoice. I'd like to have it start at a number other than "1" just because it looks better on an invoice. I don't know how to do this. I looked at the table design to see if there were options available to me there but couldn't find anything. Is it possible? (I do not know how to use code.)
View 7 Replies View RelatedI have an Access2007/SQL Server 2012 system with 20 users for an insurance company. The company does most of its business via a network of vehicle dealers around the country. If someone comes in to buy a motorcycle, boat, or recreational vehicle at a dealership they need insurance to take it home, and our dealers send the quotes to us.
The dealers, in turn, receive payment from us each month for their efforts. Some are paid a % commission on the premium, some are paid for each quote they send regardless of whether the policy actually sells or not, and some are paid a set amount per sold policy. (Yes, that is relevant information!)
We already have reports that tally the amounts due each dealer based on their payment scheme, but last month our bookkeeper had to write about 650 checks manually because the check writing is not automated. She'd look at the report, and then enter name, address, and amount (in digits and words) into Quick Books and print the checks from there, a horribly tedious process. I've been asked to print the checks from Access. Basically one click would print all 650 checks.
I've opted to use a Make Table query to move the commissioned dealers amounts to a single location, and then to run two append queries to add the records from those paid per quote and those paid per policy. At the end of the day, one table contains all the information necessary to print the checks...except one.
The check number.
I need a way to sequentially number each record in the new table with a user generated starting point, the first check number.
By the way, the check blanks are on standard letter sized paper, three to a page, with tear-off perforations to separate them, in case that information has any relevance.
I think the best way to accomplish this is from the report itself. I've created a blank field on each record for the check number, and what seems most logical is that the sequential number is generated on print and written back to the table, rather than just generating all the numbers at once. That way, should print ever be interrupted, it will be easy to take up where we left off.
Here is my issue. In a table with an Auto Number index some records have been deleted. I have been able to recreate them along with their original auto number. The problem is that I do not know how to append these records forcing the original auto number. I have tried changing the auto number field to a number field in the table, this works except I cannot change it back to auto number.
I am sure I’m not the first with this question or issue. I did search through a couple hundred entries about auto number before I posted this question.
Jim
I'm putting in a date and I need to have the small "st" beside the number 1.
I'm not sure how to do this or if there is a chr() call for it.
I am creating a file and I am not stuck on the last part. I have to keep track of which numbers of a particular item is sold due to serial number tracking. Items are bundled in sets of 50 so I need to form to auto populate the next set of numbers (by 50) when I create a new line.
I found how to change my start number to 1751 but I can not find how to have a Begining number of 1751 Ending number 1800 and have the next line auto populate begin $ 1801 end # 1850. I have a feeling it will not be the Auto Number option and I am sure there is another way but I just can't find it.
i am going to use the autonumber as an order number but I want " SC2015 in front of it so i end up with " sc20151 , sc20152 but next year i want it to change the year SC201650 .how to put the sc2015 in my report without any input from the user
View 2 Replies View RelatedI have several fields that were set up as text fields since the numbers would never be used for calculation. But now my users want to see 100,000 instead of 100000. I have looked everywhere for an answer but do not know if it is possible without retyping the data. The text or rather the numbers that are text can be from anywhere of 1 to 1,000,000 plus. They are just used to show population figures. How to do this with formatting?
View 9 Replies View RelatedI have a text field with numbers like this: 2014556682 or 2014236781, etc.
My problem is that I need to insert 00 between the 2014 and the last 6 numbers.
I have a form that retrieves information from a query (which of course uses data from a table).Everything is working great and I want to roll it out.will multiple users accessing the form/query/table at the same time corrupt the database or not return a result?
View 4 Replies View RelatedAnyway, here is what I am looking to do:
1.) Generate a random number between 1 and 100 and assign the result an identifier, like a person's name.
2.) Generate a second random number between 1 and 100 and assign the result a different identifier, like another person's name.
3.) Compare the two randomly generated numbers.
4.) Have the identifier of the LARGER random number displayed on the screen.
I would like to have this linked into a button click.I am hoping there is an easy way to accomplish this task.
before ACCESS Excel was used to create orders etc,we are up to order number 16223 now I have access and the auto number will start at 1.how can I get the auto number to start at 16223.
View 11 Replies View RelatedI would like to convert a number such as 15.25 to 3:15 pm. What is the easiest way to accomplish this?
View 2 Replies View RelatedI have a set of numbers, say (these could also be values of a column in a table)
FieldA = { 11, 16, 20, 23, 30, 37, 40, 50 }
I have a number, say 196.
I want {16,23,30,37,40,50} to be returned as these numbers add up to 196.
Note :
1) There is no possibility of two solutions with the kind of numbers that I may be using.
2) A solution using excel is also OK, though, personally I would prefer access.
Edit :
3) {16,23,30,37,40,50} - Each value is a separate record i.e. they are not in a single field, rather :
16
23
30
37
40
50
I have created a booking system for a set of resources for schools. Most schools have a membership which entitles them to 2 free sets. I have a booking form with a membership subform (membership table), and a booking details subform (kitloan table).
Once a school is selected on the main form, the membership subform shows the most recent record for that school based on schoolID.I want to display the number of sets they have already had within their membership period (can start at any time of the year, and lasts for 1 year) on the membership subform, so we know how many free ones they have left.
I therefore need to count the number of KitBkID (ID of the booking) in the Kitloan table where SchoolID = the SchoolID displayed on the membership subform, and the DateOut (booking date on kitloan table) is between the DateJoined and DateRenewal displayed on the membership subform (from membership table).
I can do this with a query which works when run and provided with the parameters SchoolID, DateJoined, and DateRenewal.
SELECT Count(Kitloan.KitBkID) AS CountOfKitBkID, Kitloan.SchoolID, Kitloan.DateOut
FROM Kitloan INNER JOIN Membership ON Kitloan.SchoolID = Membership.SCHOOLID
GROUP BY Kitloan.SchoolID, Kitloan.DateOut
HAVING (((Kitloan.SchoolID)=[Me].[SCHOOLID]) AND ((Kitloan.DateOut) Between [Me].[DateJoined] And [Me].[DateRenewal]));
What I can't do is get it to run on the form and take those values from the form.From the searching I've done, I'm thinking a DCount should be the way to go, but I cannot get the criteria right. I created a query (KitloanCountQry) so that criteria could come from both the kitloan and membership tables.
SELECT Kitloan.KitBkID, Kitloan.SchoolID, Membership.DateJoined, Membership.SCHOOLID, Kitloan.DateOut
FROM Kitloan INNER JOIN Membership ON Kitloan.SchoolID = Membership.SCHOOLID;
I have put the DCount as the control source for a textbox on the Membership subform (but have tried it in VBA too):
=DCount("KitBkID","KitloanCountQry")
This works but obviously gives me the total for all bookings.
[code]....
Although I have to admit to getting lost in the syntax. This produces #Error.
How can I convert a number format into a short time format?
View 3 Replies View RelatedIs it possible to easily convert 02/02/2015 15:30:00 to 201502021530 in a query?
View 4 Replies View RelatedI have a form based on a query. I've disabled "Navigation Buttons" on the form and am trying to recreate their functionality in a little more user friendly way. I've created next / previous record buttons and have those working great.
I'm now trying to re-create the record counter / tracker. I'd like to setup a box that shows which record I'm on (this can simply be based on the order the query returns them, the same way the navigation buttons does it when they are enabled. ), and how many records there are total in the query.
I tried messing around a little bit with =DCount but wasn't able to make it work I suspect because I'm counting the number of records returned in a query, not in a table.
I am importing a delimited .txt file that has a number field. A value for a record coming in is 36,767 and Access is not accepting it. If I redefine the field as long integer or as double, I can manually update the record, but as soon as the file containing the record is imported, the field reverts back to integer.
How do I format the field with VBA so that Access will accept the value and not revert to integer?
I should find the position of a number in a sentence and later extracted. The number is attached always to an "E". ie: E1, E2, E3,E4. The range of the number is 1-4. I have this until now:
X: InStr("E[#]",[Col1_Comment_1])
but it always return 0
I want to regularly run a number of reports and emails. But I want them to run automatically at a certain time daily.
View 7 Replies View Related