Get Number From Day Name

Aug 12, 2015

Is there an expression that will return the day name to day number Monday = 1, Tuesday = 2..... Sunday = 7

I can easily do it from day number to day name however I would like to do the reverse

I can create my own function to do this no problem(Trying to stay away from VBA)

I would like to do this as an expression only

View Replies


ADVERTISEMENT

Forms :: Auto Generate File Number Based On Number Of Records In Year

Jan 21, 2014

I have a form [IUDATA]

I have a add record button.

I have a date field [DATEIN]

I have a text field [DRPNO]

If the [DPRNO] field is empty, I would like the user to have the [DPRNO] field be automatically populated after the user enters a date.

I'd like the format of [DPRNO] to be "dpr YY-XXX"

Where:
YY is the year of the [DATEIN] field and
XXX is number of records in that year.

So for example, if it was the 4th record with a 2013 date the [DPRNO] would be dpr 13-004.

View 12 Replies View Related

Tables :: Consecutively Number Table Rows With User Determined Start Number

Jul 13, 2014

I 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.

View 14 Replies View Related

Number Fields In Text To Number Field, Formula For Fiscal Year

Nov 26, 2004

Date of Birth (DOB) field etc. in one program are text - how do I make another file with the same data into number fields for Date of Birth field etc? When I copy data to file that has number fields the 09252004 is changed to 9252004. Can I get reports with the correct Date of Birth in them by moving data from text file to number file?

There is data entered monthly in file and formula has been set up for January, February etc as ---quarter: Int(([month]-1)/3)+1. I would like formula for the fiscal year for April to be counted as month 1, May - month 2, June as month 3, July as month 4, August as month 5, Sept as month 6, October as month 7, Nov as month 8, Dec as month 9, Jan as month 10, Feb as month 11 and March as month 12.

Thank you

View 6 Replies View Related

Reports :: Generate Number Of Rows In Report Depending On Number In Another Field

Nov 4, 2013

I have a form where we fill in information for supply of equipment to employees.

Each item must be signed for on a printed report.

I am encountering problems trying to create enough rows in my report detail for each signature of the items supplied.

For example, on the form I will select the "equipment" - 4 hats supplied and 3 boots. On the report I want the equipment set as the group and the detail to be a number or rows which equals the number of selected items. therefore under the Hats group heading I want 4 blank rows which are made up of 3 text boxes - Print Name, Signature & Date and another group heading for boots but with 3 lines.

View 11 Replies View Related

Tables :: Procedure To Restrict / Stop Auto Number Increment For Certain Number Of Record Count

Mar 16, 2014

I would like to know if there is any procedure to restrict/stop auto number increment for certain number of record count (say 50), then increment by 1 for next 50 records.

View 8 Replies View Related

Auto Increasing A Number, Depending On Maximum Number When Condition Is Met

Sep 18, 2006

Hi,

I'm trying to get the maximum number in a table field to increase it by one depending on the member that is selected in a drop down in a field.

I have three tables: members, programs and times. Each member can have N programs and each program can be broadcast N times.

Each member has a three digit code, like XXX. Each program has the three digit code of the member + three numbers that are supposed to auto increment. That is, the first program of member X with the member code XXX is called XXX001.

What I'm trying to do is that when a new program is filled in and I select the member, then the program code should update automatically, adding one to the latest program by that member.

That is, if the last program by member X that was inserted in the database is XXX010, then if a new program is inserted it should automatically be XXX011, even though programs by other members have been added in between.

This is the code I use now, for the AfterUpdate when selecting the member in a dropdown in the form. But although I've played around a bit, I just get error messages...

Private Sub medlemsruta_AfterUpdate()
Dim medlemskod
medlemskod = Me![medlemsruta].Column(2)

Dim strMax As String

strMax = DMax("programs_kod", "table_programs", "Left$(programs_kod, 3) = medlemskod")
Me!program_kod = Left$(strMax, 3) & Format$(Val(Right$(strMax, 3)) + 1, "000")
End Sub

Medlemsruta is a dropdown where one selects the member from the members table, where the three digit code is in the third column (Column(2)).

I'm trying to use DMax to get the maximum number for the particular member and after that adding 1 to that for the new program code.

Grateful for any advice! Thanks!

View 3 Replies View Related

Find The Lowest Number And Place The Number And Column Name Into In Field

Dec 15, 2005

I have a access table with 32 columns and 42,000 rows of numbers. I need to find the MIN number in the row and if the MIN number has duplicates then I need them all placed into another column by column name.
Example:
Starting file
DEST,ORIGIN1,ORIGIN2,ORIGIN3,ORIGIN4, ETC
05512,3,2,4,2

ENDING table needed
DEST,ORIGIN1,ORIGIN2,ORIGIN3,ORIGIN4, NEWCOLUMNname
05512,3,2,4,2,2 ORIGIN2 ORIGIN4

Where the new column name contains the MIN number in the row and all of the associated duplicates column names.

View 1 Replies View Related

General :: Getting Invoice Number Field To Auto Generate Next Number

Jun 2, 2014

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?

View 5 Replies View Related

Auto Number, Year, Month Number Combination

Apr 20, 2008

I need to create an auto number for service calls that show the following:

year as 08, 09 etc, month as a letter, Jan = A, Feb = B etc then an incremental number starting at 300 for each month/year combination.

So for example: 08A300

Can anyone help me as I am stuck?

View 10 Replies View Related

Query That Result Incremental Number, With No Auto-Number

Sep 10, 2006

Well, here's my database :

Name : String
Address : String

I want to add an automatically generated column, "no.", to show which number it's in. Like this :

No. Name Address
1. A Unknown
2. B Unknown2

The problem is, I can do that with Auto-Number, but if the user delete row 1, the number in row 2 is still 2, not 1.

Is it possible to generate query that have "no." column in it ?

View 1 Replies View Related

Report Prints The Same Number Of Times As The Number Of Records

Jul 10, 2006

I've created a report and report has the same number of pages as the number of records that it's displaying.

If there are two records, the report has 4 pages...the first 2 are the actual report and then the other 2 are a copy. If there are 3 records, the report would have 3 copies (...6 pages).

Any idea how to change this so that I have only one copy of the report?

View 1 Replies View Related

General :: Auto Number PK Change Starting Number?

Dec 18, 2013

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 Related

Modules & VBA :: Sequence Number In Query Use A Starting Number

Sep 20, 2014

creating sequence number in a query using a starting number. For example i want to use the starting sequence number as 5

---SEQNUM---------COLOR-----------
-----5------------------blue--------------
-----6------------------red---------------
-----7------------------orange-----------
-----8------------------black-------------

View 2 Replies View Related

Forms :: Generate Random Number To Use As Invoice Number

Mar 17, 2014

I am working on a form and need it to be able to generate a random number to use as an invoice number. The only parameters i need it to meet is that it is at least 5 digits in length. I was hoping i could use a button to make the number generate in a separate box.

View 5 Replies View Related

Auto Sequence Number For Reference Number

Dec 8, 2005

The title probably doesn't give an idea of what I need, so here goes.

We currently have reference #'s for our bills that include the date requested in mmddyy format, the first 3 letters of the customer, the initials of the order taker and finally a sequential number to show the sumber of bills that day.

example: 120705SEAGMM02 2nd order taken by GMM for Seagate on 12/7
120705SEARLH01 1st order taken by RLH for Seagate on 12/7

I want to generate this number automatically based on the date entered and the initials given of the user. We only deal with one customer at our desk so that will always be "SEA".

I have a query that generates the first portion (date, customer, and initials):

SELECT Format([REQDATE],"mm") AS [Month], Format([REQDATE],"dd") AS [Day], Format([REQDATE],"yy") AS [Year], Format([REQDATE],"mmddyy") AS [Both], [Both] & "SEA" & [PickUpReqData]![INITIALS] AS REFNO
FROM PickUpReqData;

But I can't seem to get my head around the part of generating the number. I know I had done this in a database I created 2 jobs ago but all my files were flooded out in Katrina. (I have since relocated to Atlanta, although I'm not looking for sympathy. But I will take what I can get!)

Anything to point me in the right direction will be appreciated. I am looking to have this on a form and feed the REFNO field in the PickUpReqData table.

Thanks.

Gary

View 3 Replies View Related

Convert Positive Number To Negative Number

Nov 22, 2011

This has to be a simple way to convert a positive number to a negative number. For example, convert 5 to -5. The field is quanitity and I wanted to convert to negative number. I tried to use negative = "-" & [quantity] but it doesn't work.

View 2 Replies View Related

Creating A Serial Number That Is NOT Auto-number

Mar 20, 2012

I have a table called "OrderDetails" with following fields:

Num
OrderID (Primary key)
Product
Quantity
Price

I want to create a data entry subform that can used to enter order details in this table such that, for a given OrderID, the Num field is automatically set to previous number + 1. For example, for OrderID = 12, if there are 4 products that need to be entered, the 4 records should automatically take 1 , 2, 3, 4.

View 6 Replies View Related

Auto Number Not Displaying Number

Mar 17, 2005

I have a form with an Auto number field. When the form is opened in new record mode the auto number field displays the following: (Auto Number) instead of the actual number that it has generated. I want the number to be displayed

View 6 Replies View Related

Auto Number Starting At A Particular Number

Jul 25, 2005

Hi, I have a form with some fields on it, there is one called Pro Number. what I would like to happen is when a new record is created, it starts at a certain number and continues to increment by one. In other words, the first record would be 5600 and the next new record would be 5601 etc.

I did have this working in an previous database, by creating a append query to start the number, but that does not seem to be working now...

Any help would be very much apprecited.

David

View 14 Replies View Related

Appending Records With A Desired Auto Number In A Table With Auto Number

Oct 26, 2005

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

View 7 Replies View Related

Queries :: Extract Number Out Of Field And Update Extracted Number To Another Field

Feb 6, 2015

791335.12pack. This is the object that is in one of my access fields. I need to extract the 12 and place that in another column called qty.

View 1 Replies View Related

Forms :: Creating Number Of New Forms To Edit Number Of Tables

Oct 29, 2013

I am using a template database that I downloaded from the Microsoft Access template website and have been modifying it and adding new forms and tables etc. I have used the forms wizard to create a number of new forms to edit a number of the tables. The forms are "split forms".I can't seem to get many of the forms to stay the size that I set them to! They seem to have a mind of their own and often when I think that I have sized them correctly, I then open the form and they display in full screen mode or larger!

View 8 Replies View Related

Row Number

Feb 28, 2008

Hi

Can anyone please tell me how I look up the row number where the value of one of the columns is equal to something.

e.e


Name

Paul
John
Steve
Matt


I want to find the row of the list box where the name = "Steve"

I need the answer = 3

Please can anyone help...

Thanks

View 2 Replies View Related

Yes/no Or Number

Mar 5, 2007

I have rarely used the yes/no field, so I need some assistance. I have a few questions that need to be on a form, the questions are yes/no. I thought I would make the field type a yes or no, but then realized if a no is selected it needs to store a value of 0 and if user selects yes a 1 should be stored, is there a way to do this? or should I just make it a number field type and restrict the values to 0 or 1's?

View 4 Replies View Related

Number Criteria

May 16, 2005

i have a six digit roling number, (100565)

i have a totals query trying to add the amount of entries are in this query, however i need a criteria for it to look for a six digit number

unless i have this criteria it returns 1 for 100565, and 1 for 100566 and so on

any ideas searching db now

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved