Date Brackets as an IIf statement
Looking for help writing two IIF queries that will put some dates into brackets. I have some sku's that have a expiration date and the idea is to put the product into 1 of 5 brackets. The brackets are: Brackets: Age Date Bracket Code 1. Age Date Bracket Desc "Greater than 6 months left", = > 181 days Age Date Bracket Code 2. Age Date Bracket Desc "6 months to 3 months" = 91 up to 180 days Age Date Bracket Code 3. Age Date Bracket Desc "3 months to 1 month" = 31 up to 90 days Age Date Bracket Code 4. Age Date Bracket Desc "1 month left" = 1 up to 30 Age Date Bracket Code 5. Age Date Bracket Desc "expired" = <=0 I have the following in my data: Production Date, Shelf Life, and Expiration Date. Can someone help me make two fields: Age Date Bracket Code and a Age Date Bracket Description? Test Data is as follows: Production DT Shelf Life Expiration DT Age DT CD Age DT DESC 12/22/2009 365 12/20/2010 1 New Product 10/22/2009 365 10/22/2010 1 New Product 10/21/2009 365 10/21/2010 2 Mild, 6 to 3 months left 06/21/2009 365 06/21/2010 2 Mild, 6 to 3 months left 06/20/2009 365 06/20/2010 3 Moderat, 3 to 1 months left 05/22/2009 365 05/20/2010 3 Moderat, 3 to 1 months left 05/21/2009 365 05/21/2010 4 Critical, 1 months left 10/24/2009 180 04/22/2010 4 Critical, 1 months left 10/23/2009 180 04/21/2010 5 Expired Any help create a IIF statement in my query is greatly appreciated. Regards, Gary
|
0 |
|
Utf
4/21/2010 5:24:03 PM
access.queries
6343 articles. 
1 followers.
3 Replies
655 Views
NOTE: I have wrote my statement as follows: Age_DT_CD: IIf([# of Days left]<=0,"5",IIf([# of Days left] Between 0 And 30,"4",IIf([# of Days left] Between 31 And 90,"3"),IIf([# of Days left] Between 91 And 180,"2"),IIf([# of Days left] >180,"1"))))) Currently "Gary" wrote: > Looking for help writing two IIF queries that will put some dates into > brackets. I have some sku's that have a expiration date and the idea is to > put the product into 1 of 5 brackets. The brackets are: > > Brackets: > Age Date Bracket Code 1. Age Date Bracket Desc "Greater than 6 months left", > = > 181 days > Age Date Bracket Code 2. Age Date Bracket Desc "6 months to 3 months" = 91 > up to 180 days > Age Date Bracket Code 3. Age Date Bracket Desc "3 months to 1 month" = 31 > up to 90 days > Age Date Bracket Code 4. Age Date Bracket Desc "1 month left" = 1 up to 30 > Age Date Bracket Code 5. Age Date Bracket Desc "expired" = <=0 > > I have the following in my data: Production Date, Shelf Life, and Expiration > Date. Can someone help me make two fields: Age Date Bracket Code and a Age > Date Bracket Description? > > Test Data is as follows: > Production DT Shelf Life Expiration DT Age DT CD Age DT DESC > 12/22/2009 365 12/20/2010 1 New Product > 10/22/2009 365 10/22/2010 1 New Product > 10/21/2009 365 10/21/2010 2 Mild, 6 to > 3 months left > 06/21/2009 365 06/21/2010 2 Mild, 6 to > 3 months left > 06/20/2009 365 06/20/2010 3 Moderat, 3 > to 1 months left > 05/22/2009 365 05/20/2010 3 Moderat, 3 > to 1 months left > 05/21/2009 365 05/21/2010 4 Critical, 1 > months left > 10/24/2009 180 04/22/2010 4 Critical, 1 > months left > 10/23/2009 180 04/21/2010 5 Expired > > Any help create a IIF statement in my query is greatly appreciated. > > Regards, > Gary
|
|
0 |
|
Utf
4/21/2010 6:11:03 PM
Once you next about 3 IIf's, things get hard to maintain. Instead you can use
Select Case within a function inside a module. Then you can call upon it in a
query/SQL statement. Below is an example.
Function fTiers(strTiers As Variant) As String
Dim TheTier As String
Select Case strTiers
Case Is 1
TheTier = "Greater than 6 months left"
Case = 2
TheTier = "6 months to 3 months"
Case = 3
TheTier = "3 months to 1 month"
Case = 4
TheTier = "1 month left"
Case = 5
TheTier = "expired"
Case Else ' Other values.
TheTier = "Not Tier"
End Select
fTiers = TheTier
End Function
Then in the query field put something like:
Age Date Bracket Desc: fTiers([Age DT CD])
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
"Gary" wrote:
> Looking for help writing two IIF queries that will put some dates into
> brackets. I have some sku's that have a expiration date and the idea is to
> put the product into 1 of 5 brackets. The brackets are:
>
> Brackets:
> Age Date Bracket Code 1. Age Date Bracket Desc "Greater than 6 months left",
> = > 181 days
> Age Date Bracket Code 2. Age Date Bracket Desc "6 months to 3 months" = 91
> up to 180 days
> Age Date Bracket Code 3. Age Date Bracket Desc "3 months to 1 month" = 31
> up to 90 days
> Age Date Bracket Code 4. Age Date Bracket Desc "1 month left" = 1 up to 30
> Age Date Bracket Code 5. Age Date Bracket Desc "expired" = <=0
>
> I have the following in my data: Production Date, Shelf Life, and Expiration
> Date. Can someone help me make two fields: Age Date Bracket Code and a Age
> Date Bracket Description?
>
> Test Data is as follows:
> Production DT Shelf Life Expiration DT Age DT CD Age DT DESC
> 12/22/2009 365 12/20/2010 1 New Product
> 10/22/2009 365 10/22/2010 1 New Product
> 10/21/2009 365 10/21/2010 2 Mild, 6 to
> 3 months left
> 06/21/2009 365 06/21/2010 2 Mild, 6 to
> 3 months left
> 06/20/2009 365 06/20/2010 3 Moderat, 3
> to 1 months left
> 05/22/2009 365 05/20/2010 3 Moderat, 3
> to 1 months left
> 05/21/2009 365 05/21/2010 4 Critical, 1
> months left
> 10/24/2009 180 04/22/2010 4 Critical, 1
> months left
> 10/23/2009 180 04/21/2010 5 Expired
>
> Any help create a IIF statement in my query is greatly appreciated.
>
> Regards,
> Gary
|
|
0 |
|
Utf
4/21/2010 6:49:02 PM
On Wed, 21 Apr 2010 10:24:03 -0700, Gary <Gary@discussions.microsoft.com>
wrote:
>Looking for help writing two IIF queries that will put some dates into
>brackets. I have some sku's that have a expiration date and the idea is to
>put the product into 1 of 5 brackets. The brackets are:
>
>Brackets:
>Age Date Bracket Code 1. Age Date Bracket Desc "Greater than 6 months left",
>= > 181 days
>Age Date Bracket Code 2. Age Date Bracket Desc "6 months to 3 months" = 91
>up to 180 days
>Age Date Bracket Code 3. Age Date Bracket Desc "3 months to 1 month" = 31
>up to 90 days
>Age Date Bracket Code 4. Age Date Bracket Desc "1 month left" = 1 up to 30
>Age Date Bracket Code 5. Age Date Bracket Desc "expired" = <=0
>
>I have the following in my data: Production Date, Shelf Life, and Expiration
>Date. Can someone help me make two fields: Age Date Bracket Code and a Age
>Date Bracket Description?
>
>Test Data is as follows:
>Production DT Shelf Life Expiration DT Age DT CD Age DT DESC
>12/22/2009 365 12/20/2010 1 New Product
>10/22/2009 365 10/22/2010 1 New Product
>10/21/2009 365 10/21/2010 2 Mild, 6 to
>3 months left
>06/21/2009 365 06/21/2010 2 Mild, 6 to
>3 months left
>06/20/2009 365 06/20/2010 3 Moderat, 3
>to 1 months left
>05/22/2009 365 05/20/2010 3 Moderat, 3
>to 1 months left
>05/21/2009 365 05/21/2010 4 Critical, 1
>months left
>10/24/2009 180 04/22/2010 4 Critical, 1
>months left
>10/23/2009 180 04/21/2010 5 Expired
>
>Any help create a IIF statement in my query is greatly appreciated.
>
>Regards,
>Gary
I would suggest that IIF is simply the wrong tool for the job. You could use
VBA as Gary suggests, or - perhaps even better - use a table-driven solution.
Create a table with the days cutoff and the label for values less than that
number of days. You can then use an expression like
DateAdd("d", [Shelf Life], [Production Date])
to calculate the expiration date, and
DateDiff("d", DateAdd("d", [Shelf Life], [Production Date]), Date())
to calculate the number of days until (or past) the expiration. Join this
field to your table of labels to get the appropriate label for today.
--
John W. Vinson [MVP]
|
|
0 |
|
John
4/22/2010 5:15:59 AM
Similar Artilces:
Date errors upgrading
I just transfered data from an older version of Microsoft money to the new version that came loaded on my new computer. All my entries for the years 2000-2003 transfered as 1900, 1901, 1902, and 1903. Therefore all deposits, checks, etc. show way out of sequence. Is there any "quick" fix methods to change these dates. I really don't want to waste DAYS literally changing every date for those 4 years of entries. Thanks Cindy What are the old/new versions involved? This seems very odd and has never been reported before--it seems likely that this would have been if it wer...
year conversion in date fields eg 1900 to 2000
I moved a existing database from foxpro to access 2000. The dates in foxpro had a 2 digit year and when brought in to access it converted them to 1900 (eg. 11/12/00 to 11/12/1900 instead of 11/12/2000). Is there a way in access to convert (replace) only the year of vaious dates to another year. If this is not possible is there a way to prevent this while converting? thanks alex An update query can work for this. Something like this: UPDATE TableName SET [DateFieldName] = DateAdd("yyyy", 100, [DateFieldName]) WHERE Year([DateFieldName]) = 1900; -- Ken Snell <MS...
Display Date Range In Text Box
I have a pie-chart on my main menu that totals the number of billable hours for each Service Technician for the last week (always 7 days from today.) How would I make a text box display the actual date range, much like what is done in reports. I'd like it to look like this: From: 2/7/2008 To: 2/7/14/2008 I don't think I can use the query that generates the data for the pie-chart becuase it would have to be outside that sub-form on my main menu. My main menu does not have a data source. If you have any ideas, I'd appreciate it. Thx, JK, If your chart always prints in ...
Convert date string (ie: 1/3/2010) to seriel
How do i convert a date formatted like: 3/8/2010 (string) to its corresponding seriel? Use CDate. ? CDate("3/8/2010") 2010.03.08 ( In my case, it took the first number as the month; my Regional setting tell Windows to format my dates in the ISO format, which is year dot month dot day ). Vanderghast, Access MVP "Steve P" <SteveP@discussions.microsoft.com> wrote in message news:4CE0A688-FAE5-437C-B1AF-F0E6DED0EEBB@microsoft.com... > How do i convert a date formatted like: 3/8/2010 (string) to its > corresponding seriel? Van, ...
Some accounts don't show up on the Get online statement list
I've set up some new accounts with my financial broker and check "get online statements". But when I go to connect these accounts do not show up in the "Get Statement" List for that institution. What do I need to do to add these to the list? In microsoft.public.money, Steven Friedman wrote: >I've set up some new accounts with my financial broker and >check "get online statements". But when I go to connect >these accounts do not show up in the "Get Statement" List >for that institution. What do I need to do to add these to &g...
Getting External Data from Credet Card Statement
I have one particular credit card account that does not have a download feature. How would I download a statement into Excel. I tried Data, Get External Data, typed in the web site address, but I received a message the the data could not be imported. Thanks, Victor ...
Convert Date-as-Text
Using Excel 2000 I have an Excel worksheet (.xls) that was produced by an accountant exporting data from a particular accounting package. There is a colum of dates of the m/d/yyyy format that don't sort as dates. They sort alphabetically, which is of no use. I'm looking for the VBA function that will convert text in the m/d/yyyy format to an Excel date number. Cells(n, m).formula = DateConverter(Cells(n, m).value) or some such And I will then write a loop macro to process each cell in the column/range to convert the cell contents to an Excel date of the usu...
REPOST: Date format pre-1900.
Hi, I have a table of immigration ships that have arrived in Australian shores. Some....okay most.....of the ships arrived before 1900. I was trying to sort by the arrival date, which is in the format of DD MMM YYYY, but that didn't work, as there were some 1900 dates. Tried to re-style the column to DD/MM/YYYY, but the pre-1900 dates don't change to the new style. Any suggestions on how to utilise the pre-1900 dates, so that Excel can recognise them. Cheers in advance. Craig. P.S. Originally sent on the 15/02/2005 - not sure if someone knowledgable missed it in the other 1000s o...
DIM Statement
I read a warning that a Dim statement such as: Dim mySQL, RecSource, fldNames As String is incorrect and should instead be written: Dim mySQL as String, RecSource as String, fldNames as String Is this true? It compiles ok. What is the problem? Yes. That's correct. This is a valid line of code: Dim mySQL Since you did not specify a type, you get a variant. Similarly the line: Dim mySQL, RecSource, fldNames As String gives you 2 untyped variables (so Variant), and one string. That's a trap if you expect to get 3 strings. To demonstate it's true, add these 2 lines ...
Omega Speedmaster Day Date Mens Watch 323.53.40.44.01.001, Best Wristwatch World
Omega Speedmaster Day Date Mens Watch 323.53.40.44.01.001, Best Wristwatch World Click Here To Website : http://www.watchess.net/Omega_Speedmaster_Day_Date_Mens_Watch_323.53.40.44.01.001.html Wristwatch World: http://www.watchess.net/ Omega Speedmaster Day Date Mens Watch 323.53.40.44.01.001 Information : Brand : Omega Watches ( http://www.watchess.net/Omega_Watches.html ) Gender : Mens Code : 323.53.40.44.01.001 Also Called : 32353404401001 Case Material : 18k Rose Gold Case Thickness : Dial Color : Black Bezel : 18K Rose Gold Movem...
"If" statement using "And"
I am trying to use an "If" statement that also contains "And". Below is an example of what I am trying to do: Sub test() If Sheets("Sheet 1").Range("A1").Value = "1" And _ Sheets("Sheet 1").Range("A2").Value = "Yes" Then Sheets("Sheet 2").Select ElseIf Sheets("Sheet 1").Range("A1").Value = "1" And _ Sheets("Sheet 1").Range("A2").Value = "No" Then Sheets("Sheet 2").Select Rows("1:2&...
Anniversary Date
How do you calucate employee anniversary dates in excel? Tonnia This will give you thier 5th anniversary date =DATE(YEAR(A1)+5,MONTH(A1),DAY(A1) Assumes date start is in A1. Adjust for other anniversaries Good Luck Mark Graesse mark_graesser@yahoo.co ----- Tonnia wrote: ---- How do you calucate employee anniversary dates in excel? Hi Tonnia! With the joining date in A1: Try: =(DATE(YEAR(TODAY()),MONTH(A1),DAY(A1))) Returns the anniversary date this year. If this year was not a Leap Year, then if the employee joined on 29-Feb the anniversary date would be returned a...
Repost
Using VBA does anyone know how to retrieve the latest Forefront updates date? -- Steve Don't know about VBA but here is the home site. Maybe you can find something there. http://www.microsoft.com/forefront/en/us/default.aspx "AltaEgo" <Somewhere@NotHere> wrote in message news:ev2818iwKHA.1796@TK2MSFTNGP02.phx.gbl... > > Using VBA does anyone know how to retrieve the latest Forefront updates > date? > > > -- > Steve Good answer. Bad question! More detail: I need to audit many computers checking many setti...
Attendee clicking and dragging meeting to a new time/date
One of my users called. She is the organizer of the meeting. She said it has happend now several times that a particular attendee ends up with the meeting at a completely different date. She thinks he is clicking and dragging the meeting, but she is not notified when he changed it. I had her test with me, and sure enough I was able to click and drag the meetting to a new date, and she was never notified of the change. She wanted to know if there is a way to disable the ablity to click and drag or to notofu jer wjem a change has been made.. I haven't the slightest idea o...
After a cell changes colour when a date is near
When the cell changes to a specific colour say a12 goes red when a date is within 30 days (a12 is a date an assessment is due) can a12 and a1 (which a persons name is in a1) be put on a different worksheet and the same for each row so i can have a list of names and assessments on a seperate worksheet to print off so it would look like this A N OTHER FDA 02-JAN-06 A BODY SUMMARY 02-JAN-06 You could use a filter to do that. Select your table, then use Data Filter... Auto Filter. Then use Custom on your date field, and some combination of "Less th...
help regading date tracking across tables
It is required of me to validate a date of birth entry into a table named adminmast between the limits given as udate and ldate in another table named as classdetails. I will specify the whole thing : User is supposed to enter a class and then a date of birth into a form/ table, as soon as he enters the DOB the value entered should be validated in the following manner. If it is class I then the Ldate and Udate from the table class details should be treated as the lower and upper limits for the DOB recordwise similarly for all other classes these should take place recordwise the structure of t...
Help with IIF statement
I need help with an iff statement on my form. Basically I have a new record when the "Insert" key is pressed. But I want to stay with the current record if [TotalHrs] is greater or less than IDRb_subform.Form!SumHours. Otherwise a new record is called for. I think I'm close here but a little help would be appreciated. Thanks, Randy Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer) On Error GoTo ErrRoutine Select Case KeyCode Case vbKeyInsert If [TotalHrs] > 0 Then If IDRb_subform.Form!SumHours <> [TotalHrs] Then If MsgBox("Total Ho...
If statement formulas
I have two sheets in a workbook. One called Availability, and the other called schedule. The availability tells what time my employees are available to work and it look like this: A1 B1 C1 Name Start End Tom 6 AM 2 PM Jason 9 AM 5 PM John 1 PM 9 Pm Susan 8 AM 4 PM In the schedule sheet look similar to availability sheet accept it has one more column that will if I schedule an employee that will have a time conflict with availablity sheet. Look like this. A1 B1 C1 ...
Logical statement on a Switchboard
Is there way to use logical statement on a switchboard. Example: Not allowing user to access a certain switchboard, if option is clicked. Thank Ileana "iholder" <iholder@discussions.microsoft.com> wrote in message news:800FF6F6-227E-4009-8857-A79CF8810171@microsoft.com... > Is there way to use logical statement on a switchboard. > > Example: Not allowing user to access a certain switchboard, if option is > clicked. Certainly. You can disable any control on a form based on an option group or any other control. Use the after update event...
insert a range of date
Hi, i need to insert a date range into excel in the following format, where the first date is today's date and the 2nd date is 4 days later. Example: 28/11/2005 - 02/12/2005 How can I achieve the above format so that the month will also be updated according to calendar. thanks Wit the start date in A1, this in A2 or B1: =A1+4 Copy down (or right) as far as you need. Excel will take care of the correct month and year -- Kind regards, Niek Otten "Lynn" <moley_cruz@yahoo.com.au> wrote in message news:1133620474.113498.50270@f14g2000cwb.googlegroups.com... > Hi, >...
Importing bank statement that has a different date format
Is there a way import a money format statement file that has a different date format? I use mm-dd-yyyy format in my Money but i would like to import statements that have the dd-mm-yyyy format! Curretnly i open the file in notepad and manually change the dates and then import the file into money. It would be nice to have an option to automate this during the import process! Thanks VJ ...
If Statement
I'm trying to develop a formula to calculate a sales team incentive bonus. A bonus amount is calculated based on sales made, but then is adjusted 3 months later based on the criteria below. Less than 50% of sales still on the books 0% payable 50% to 60% of sales 25% 61% to 70% of sales 50% 71% to 80% of sales 75% 81% to 90% of sales 100% Over 90% of sales 125% I'm afraid I don't know where to start - can anyone point me in the right direction? Thanks Look at the VLOOKUP() function Here's an excellent tutorial: http://www.contextures.com/xlFunctions02.html -- ...
Formatting a date different from the date options available
I am using ACCESS 2007 and would like to format the date as ex: 2009-Nov-19 in a table. It would also be nice if this cell would automatically format the date to the above format even if the person entering the date typed in 7/9/1975. Could anyone help me do this, or can it even be done? Thanks, Janet B Janet B <Janet B@discussions.microsoft.com> wrote: >I am using ACCESS 2007 and would like to format the date as ex: 2009-Nov-19 >in a table. It would also be nice if this cell would automatically format >the date to the above format even if the person entering ...
Using external query (mdb) for dynamic date range
I'm trying to set up an Excel workbook with a dynamic link to an Access database containing quality data, involving dates. I want the workbook to always import data for the last 90 days. When I try to set this up using MS Query, to try to filter data I've tried several different variations where time is greater than or equal to: date()-30 today()-30 now()-30 And every time, it comes back as an error. It seems to be setting the query so that the entire statement is a date variable, eg "Syntax error in query expression '((Table1.Time>=#date()-30#))'...
Money 2007 -- QIF import
Good Evening! After a little trial and error, I am able to import account transactions from another system (not a bank statement, my actual register transactions) with little trouble except for the date format. (Before someone gets mad at me, I really don't think this has anything to do with my regional settings!) As recommended by an MVP from this forum, I open my CSV file with excel, then use a converter (add-in) called XL2QIF to create a qif output file. The actual "data" in a date field in my original csv file is "060106" (mm/dd/yy) which is June 1, 2006,...
I just transfered data from an older version of Microsoft money to the new version that came loaded on my new computer. All my entries for the years 2000-2003 transfered as 1900, 1901, 1902, and 1903. Therefore all deposits, checks, etc. show way out of sequence. Is there any "quick" fix methods to change these dates. I really don't want to waste DAYS literally changing every date for those 4 years of entries. Thanks Cindy What are the old/new versions involved? This seems very odd and has never been reported before--it seems likely that this would have been if it wer...
year conversion in date fields eg 1900 to 2000
I moved a existing database from foxpro to access 2000. The dates in foxpro had a 2 digit year and when brought in to access it converted them to 1900 (eg. 11/12/00 to 11/12/1900 instead of 11/12/2000). Is there a way in access to convert (replace) only the year of vaious dates to another year. If this is not possible is there a way to prevent this while converting? thanks alex An update query can work for this. Something like this: UPDATE TableName SET [DateFieldName] = DateAdd("yyyy", 100, [DateFieldName]) WHERE Year([DateFieldName]) = 1900; -- Ken Snell <MS...
Display Date Range In Text Box
I have a pie-chart on my main menu that totals the number of billable hours for each Service Technician for the last week (always 7 days from today.) How would I make a text box display the actual date range, much like what is done in reports. I'd like it to look like this: From: 2/7/2008 To: 2/7/14/2008 I don't think I can use the query that generates the data for the pie-chart becuase it would have to be outside that sub-form on my main menu. My main menu does not have a data source. If you have any ideas, I'd appreciate it. Thx, JK, If your chart always prints in ...
Convert date string (ie: 1/3/2010) to seriel
How do i convert a date formatted like: 3/8/2010 (string) to its corresponding seriel? Use CDate. ? CDate("3/8/2010") 2010.03.08 ( In my case, it took the first number as the month; my Regional setting tell Windows to format my dates in the ISO format, which is year dot month dot day ). Vanderghast, Access MVP "Steve P" <SteveP@discussions.microsoft.com> wrote in message news:4CE0A688-FAE5-437C-B1AF-F0E6DED0EEBB@microsoft.com... > How do i convert a date formatted like: 3/8/2010 (string) to its > corresponding seriel? Van, ...
Some accounts don't show up on the Get online statement list
I've set up some new accounts with my financial broker and check "get online statements". But when I go to connect these accounts do not show up in the "Get Statement" List for that institution. What do I need to do to add these to the list? In microsoft.public.money, Steven Friedman wrote: >I've set up some new accounts with my financial broker and >check "get online statements". But when I go to connect >these accounts do not show up in the "Get Statement" List >for that institution. What do I need to do to add these to &g...
Getting External Data from Credet Card Statement
I have one particular credit card account that does not have a download feature. How would I download a statement into Excel. I tried Data, Get External Data, typed in the web site address, but I received a message the the data could not be imported. Thanks, Victor ...
Convert Date-as-Text
Using Excel 2000 I have an Excel worksheet (.xls) that was produced by an accountant exporting data from a particular accounting package. There is a colum of dates of the m/d/yyyy format that don't sort as dates. They sort alphabetically, which is of no use. I'm looking for the VBA function that will convert text in the m/d/yyyy format to an Excel date number. Cells(n, m).formula = DateConverter(Cells(n, m).value) or some such And I will then write a loop macro to process each cell in the column/range to convert the cell contents to an Excel date of the usu...
REPOST: Date format pre-1900.
Hi, I have a table of immigration ships that have arrived in Australian shores. Some....okay most.....of the ships arrived before 1900. I was trying to sort by the arrival date, which is in the format of DD MMM YYYY, but that didn't work, as there were some 1900 dates. Tried to re-style the column to DD/MM/YYYY, but the pre-1900 dates don't change to the new style. Any suggestions on how to utilise the pre-1900 dates, so that Excel can recognise them. Cheers in advance. Craig. P.S. Originally sent on the 15/02/2005 - not sure if someone knowledgable missed it in the other 1000s o...
DIM Statement
I read a warning that a Dim statement such as: Dim mySQL, RecSource, fldNames As String is incorrect and should instead be written: Dim mySQL as String, RecSource as String, fldNames as String Is this true? It compiles ok. What is the problem? Yes. That's correct. This is a valid line of code: Dim mySQL Since you did not specify a type, you get a variant. Similarly the line: Dim mySQL, RecSource, fldNames As String gives you 2 untyped variables (so Variant), and one string. That's a trap if you expect to get 3 strings. To demonstate it's true, add these 2 lines ...
Omega Speedmaster Day Date Mens Watch 323.53.40.44.01.001, Best Wristwatch World
Omega Speedmaster Day Date Mens Watch 323.53.40.44.01.001, Best Wristwatch World Click Here To Website : http://www.watchess.net/Omega_Speedmaster_Day_Date_Mens_Watch_323.53.40.44.01.001.html Wristwatch World: http://www.watchess.net/ Omega Speedmaster Day Date Mens Watch 323.53.40.44.01.001 Information : Brand : Omega Watches ( http://www.watchess.net/Omega_Watches.html ) Gender : Mens Code : 323.53.40.44.01.001 Also Called : 32353404401001 Case Material : 18k Rose Gold Case Thickness : Dial Color : Black Bezel : 18K Rose Gold Movem...
"If" statement using "And"
I am trying to use an "If" statement that also contains "And". Below is an example of what I am trying to do: Sub test() If Sheets("Sheet 1").Range("A1").Value = "1" And _ Sheets("Sheet 1").Range("A2").Value = "Yes" Then Sheets("Sheet 2").Select ElseIf Sheets("Sheet 1").Range("A1").Value = "1" And _ Sheets("Sheet 1").Range("A2").Value = "No" Then Sheets("Sheet 2").Select Rows("1:2&...
Anniversary Date
How do you calucate employee anniversary dates in excel? Tonnia This will give you thier 5th anniversary date =DATE(YEAR(A1)+5,MONTH(A1),DAY(A1) Assumes date start is in A1. Adjust for other anniversaries Good Luck Mark Graesse mark_graesser@yahoo.co ----- Tonnia wrote: ---- How do you calucate employee anniversary dates in excel? Hi Tonnia! With the joining date in A1: Try: =(DATE(YEAR(TODAY()),MONTH(A1),DAY(A1))) Returns the anniversary date this year. If this year was not a Leap Year, then if the employee joined on 29-Feb the anniversary date would be returned a...
Repost
Using VBA does anyone know how to retrieve the latest Forefront updates date? -- Steve Don't know about VBA but here is the home site. Maybe you can find something there. http://www.microsoft.com/forefront/en/us/default.aspx "AltaEgo" <Somewhere@NotHere> wrote in message news:ev2818iwKHA.1796@TK2MSFTNGP02.phx.gbl... > > Using VBA does anyone know how to retrieve the latest Forefront updates > date? > > > -- > Steve Good answer. Bad question! More detail: I need to audit many computers checking many setti...
Attendee clicking and dragging meeting to a new time/date
One of my users called. She is the organizer of the meeting. She said it has happend now several times that a particular attendee ends up with the meeting at a completely different date. She thinks he is clicking and dragging the meeting, but she is not notified when he changed it. I had her test with me, and sure enough I was able to click and drag the meetting to a new date, and she was never notified of the change. She wanted to know if there is a way to disable the ablity to click and drag or to notofu jer wjem a change has been made.. I haven't the slightest idea o...
After a cell changes colour when a date is near
When the cell changes to a specific colour say a12 goes red when a date is within 30 days (a12 is a date an assessment is due) can a12 and a1 (which a persons name is in a1) be put on a different worksheet and the same for each row so i can have a list of names and assessments on a seperate worksheet to print off so it would look like this A N OTHER FDA 02-JAN-06 A BODY SUMMARY 02-JAN-06 You could use a filter to do that. Select your table, then use Data Filter... Auto Filter. Then use Custom on your date field, and some combination of "Less th...
help regading date tracking across tables
It is required of me to validate a date of birth entry into a table named adminmast between the limits given as udate and ldate in another table named as classdetails. I will specify the whole thing : User is supposed to enter a class and then a date of birth into a form/ table, as soon as he enters the DOB the value entered should be validated in the following manner. If it is class I then the Ldate and Udate from the table class details should be treated as the lower and upper limits for the DOB recordwise similarly for all other classes these should take place recordwise the structure of t...
Help with IIF statement
I need help with an iff statement on my form. Basically I have a new record when the "Insert" key is pressed. But I want to stay with the current record if [TotalHrs] is greater or less than IDRb_subform.Form!SumHours. Otherwise a new record is called for. I think I'm close here but a little help would be appreciated. Thanks, Randy Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer) On Error GoTo ErrRoutine Select Case KeyCode Case vbKeyInsert If [TotalHrs] > 0 Then If IDRb_subform.Form!SumHours <> [TotalHrs] Then If MsgBox("Total Ho...
If statement formulas
I have two sheets in a workbook. One called Availability, and the other called schedule. The availability tells what time my employees are available to work and it look like this: A1 B1 C1 Name Start End Tom 6 AM 2 PM Jason 9 AM 5 PM John 1 PM 9 Pm Susan 8 AM 4 PM In the schedule sheet look similar to availability sheet accept it has one more column that will if I schedule an employee that will have a time conflict with availablity sheet. Look like this. A1 B1 C1 ...
Logical statement on a Switchboard
Is there way to use logical statement on a switchboard. Example: Not allowing user to access a certain switchboard, if option is clicked. Thank Ileana "iholder" <iholder@discussions.microsoft.com> wrote in message news:800FF6F6-227E-4009-8857-A79CF8810171@microsoft.com... > Is there way to use logical statement on a switchboard. > > Example: Not allowing user to access a certain switchboard, if option is > clicked. Certainly. You can disable any control on a form based on an option group or any other control. Use the after update event...
insert a range of date
Hi, i need to insert a date range into excel in the following format, where the first date is today's date and the 2nd date is 4 days later. Example: 28/11/2005 - 02/12/2005 How can I achieve the above format so that the month will also be updated according to calendar. thanks Wit the start date in A1, this in A2 or B1: =A1+4 Copy down (or right) as far as you need. Excel will take care of the correct month and year -- Kind regards, Niek Otten "Lynn" <moley_cruz@yahoo.com.au> wrote in message news:1133620474.113498.50270@f14g2000cwb.googlegroups.com... > Hi, >...
Importing bank statement that has a different date format
Is there a way import a money format statement file that has a different date format? I use mm-dd-yyyy format in my Money but i would like to import statements that have the dd-mm-yyyy format! Curretnly i open the file in notepad and manually change the dates and then import the file into money. It would be nice to have an option to automate this during the import process! Thanks VJ ...
If Statement
I'm trying to develop a formula to calculate a sales team incentive bonus. A bonus amount is calculated based on sales made, but then is adjusted 3 months later based on the criteria below. Less than 50% of sales still on the books 0% payable 50% to 60% of sales 25% 61% to 70% of sales 50% 71% to 80% of sales 75% 81% to 90% of sales 100% Over 90% of sales 125% I'm afraid I don't know where to start - can anyone point me in the right direction? Thanks Look at the VLOOKUP() function Here's an excellent tutorial: http://www.contextures.com/xlFunctions02.html -- ...
Formatting a date different from the date options available
I am using ACCESS 2007 and would like to format the date as ex: 2009-Nov-19 in a table. It would also be nice if this cell would automatically format the date to the above format even if the person entering the date typed in 7/9/1975. Could anyone help me do this, or can it even be done? Thanks, Janet B Janet B <Janet B@discussions.microsoft.com> wrote: >I am using ACCESS 2007 and would like to format the date as ex: 2009-Nov-19 >in a table. It would also be nice if this cell would automatically format >the date to the above format even if the person entering ...
Using external query (mdb) for dynamic date range
I'm trying to set up an Excel workbook with a dynamic link to an Access database containing quality data, involving dates. I want the workbook to always import data for the last 90 days. When I try to set this up using MS Query, to try to filter data I've tried several different variations where time is greater than or equal to: date()-30 today()-30 now()-30 And every time, it comes back as an error. It seems to be setting the query so that the entire statement is a date variable, eg "Syntax error in query expression '((Table1.Time>=#date()-30#))'...
Money 2007 -- QIF import
Good Evening! After a little trial and error, I am able to import account transactions from another system (not a bank statement, my actual register transactions) with little trouble except for the date format. (Before someone gets mad at me, I really don't think this has anything to do with my regional settings!) As recommended by an MVP from this forum, I open my CSV file with excel, then use a converter (add-in) called XL2QIF to create a qif output file. The actual "data" in a date field in my original csv file is "060106" (mm/dd/yy) which is June 1, 2006,...