If before today's date insert Expired

Hi Everyone,

Using Access 2003.  I have a query with 5 fields.  I have created an 
expression to insert words if the CredentialExpirationDate is after a 
1/1/2050 it inserts "No Expiration" or if the date in the 
CredentialExpirationDate field is today or before I want it to insert 
"Expired"  all other dates are as entered  I am having trouble with the last 
part  today or before.  I have tried various things I have pulled from other 
posts but I keep getting an error.  This is my most recent <Now().

Credential Expiration: Switch([CredentialExpirationDate]>#1/1/2050#,"No
Expiration",[CredentialExpirationDate],<Now(),"Expired")

Thanks,
Linda


0
Linda
3/26/2010 6:07:49 PM
access.queries 6343 articles. 1 followers. Follow

5 Replies
2126 Views

Similar Articles

[PageSpeed] 48

Whoops, I discovered that the dates that don't fit either criteria don't 
populate the Credential Expiration field.  In another query, I thought this 
[CredentialExpirationDate],"mmmd\,yyy") would work but I must be missing 
something here too.

Linda


"Linda RQ" <RomulanQueen@Work.SSTNG> wrote in message 
news:%23aJix7QzKHA.928@TK2MSFTNGP05.phx.gbl...
> Hi Everyone,
>
> Using Access 2003.  I have a query with 5 fields.  I have created an 
> expression to insert words if the CredentialExpirationDate is after a 
> 1/1/2050 it inserts "No Expiration" or if the date in the 
> CredentialExpirationDate field is today or before I want it to insert 
> "Expired"  all other dates are as entered  I am having trouble with the 
> last part  today or before.  I have tried various things I have pulled 
> from other posts but I keep getting an error.  This is my most recent 
> <Now().
>
> Credential Expiration: Switch([CredentialExpirationDate]>#1/1/2050#,"No
> Expiration",[CredentialExpirationDate],<Now(),"Expired")
>
> Thanks,
> Linda
>
> 


0
Linda
3/26/2010 6:17:49 PM
Linda:

This should do it:

Switch([CredentialExpirationDate] >#1/1/2050#,"No Expiration",
CredentialExpirationDate < Date(),"Expired",[CredentialExpirationDate] >=
Date(),Format([CredentialExpirationDate], "dd mmmm yyyy"))

You can of course format the date returned however you wish.  Note the use of
the Date function rather than the Now function.  The former returns the
current date with a zero time of day, the latter the current date and current
time of day.

Ken Sheridan
Stafford, England

Linda RQ wrote:
>Whoops, I discovered that the dates that don't fit either criteria don't 
>populate the Credential Expiration field.  In another query, I thought this 
>[CredentialExpirationDate],"mmmd\,yyy") would work but I must be missing 
>something here too.
>
>Linda
>
>> Hi Everyone,
>>
>[quoted text clipped - 12 lines]
>> Thanks,
>> Linda

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

0
KenSheridan
3/26/2010 7:02:38 PM
Adding a third set of criterion response will handle returning the value in 
CredentialExpirationDate.  You should be aware that you will return a string 
in every case.

Credential Expiration: Switch(
[CredentialExpirationDate]>#1/1/2050#,"No Expiration", 
[CredentialExpirationDate]<Date(),"Expired",
True,[[CredentialExpirationDate])



John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Linda RQ wrote:
> Hi Everyone,
> 
> Using Access 2003.  I have a query with 5 fields.  I have created an 
> expression to insert words if the CredentialExpirationDate is after a 
> 1/1/2050 it inserts "No Expiration" or if the date in the 
> CredentialExpirationDate field is today or before I want it to insert 
> "Expired"  all other dates are as entered  I am having trouble with the last 
> part  today or before.  I have tried various things I have pulled from other 
> posts but I keep getting an error.  This is my most recent <Now().
> 
> Credential Expiration: Switch([CredentialExpirationDate]>#1/1/2050#,"No
> Expiration",[CredentialExpirationDate],<Now(),"Expired")
> 
> Thanks,
> Linda
> 
> 
0
John
3/26/2010 7:17:39 PM
On Fri, 26 Mar 2010 14:17:31 -0400, "Linda RQ" <RomulanQueen@Work.SSTNG>
wrote:

>Hi Everyone,
>
>Using Access 2003.  I have a query with 5 fields.  I have created an 
>expression to insert words if the CredentialExpirationDate is after a 
>1/1/2050 it inserts "No Expiration" or if the date in the 
>CredentialExpirationDate field is today or before I want it to insert 
>"Expired"  all other dates are as entered  I am having trouble with the last 
>part  today or before.  I have tried various things I have pulled from other 
>posts but I keep getting an error.  This is my most recent <Now().
>
>Credential Expiration: Switch([CredentialExpirationDate]>#1/1/2050#,"No
>Expiration",[CredentialExpirationDate],<Now(),"Expired")
>
>Thanks,
>Linda
>

A couple of things: for one, Now() is not today's date, it's the current date
and time accurate to the second; you've got a comma in the middle of the
second condition; and you don't have all the options covered in your Switch.
Try:

Credential Expiration: Switch(
[CredentialExpirationDate]>#1/1/2050#,"No Expiration",
[CredentialExpirationDate]<Date(),"Expired",
True,Null)

Use <= Date() if you want the expiration to take effect instantly at midnight
on the beginning of the day.
-- 

             John W. Vinson [MVP]

0
John
3/26/2010 7:45:24 PM
Thanks Everyone.  The first recommendation worked perfectly!!! I will keep 
the others and try those when time permits.

You guys are great!

Linda
"Linda RQ" <RomulanQueen@Work.SSTNG> wrote in message 
news:%23aJix7QzKHA.928@TK2MSFTNGP05.phx.gbl...
> Hi Everyone,
>
> Using Access 2003.  I have a query with 5 fields.  I have created an 
> expression to insert words if the CredentialExpirationDate is after a 
> 1/1/2050 it inserts "No Expiration" or if the date in the 
> CredentialExpirationDate field is today or before I want it to insert 
> "Expired"  all other dates are as entered  I am having trouble with the 
> last part  today or before.  I have tried various things I have pulled 
> from other posts but I keep getting an error.  This is my most recent 
> <Now().
>
> Credential Expiration: Switch([CredentialExpirationDate]>#1/1/2050#,"No
> Expiration",[CredentialExpirationDate],<Now(),"Expired")
>
> Thanks,
> Linda
>
> 


0
Linda
3/30/2010 4:07:55 PM
Reply:

Similar Artilces:

Append Query
I have a table "Rates" with the following data: StartDate: 5/15/08 End Date: 6/23/08 Rate: $53 StartDate: 6/24/08 End Date: 7/15/08 Rate: $86 StartDate: 7/16/08 EndDate: 9/19/08 Rate: $99 I have another table "Transactions". I want to create an append query such that if I supply the StartDate and EndDate, the query will populate the "Transactons" table with date and rate data from the "Rates" table for each and every day within the date range. Example: If I supply the StartDate of 6/23/08 and the EndDate of 6/25/08, the query would pop...

Date problem #5
Hi I have a spreadsheet where a code needs to be enter in one column. I type in 6-10 or something similar but the cell then turns into a date is there a way to disable this - In word I would look at the auto text section but I cant find this in excel. Any help appreciated Rexmann You can use an apostrophe ' before entering or FORMAT as TEXT -- Don Guillett SalesAid Software donaldb@281.com "rexmann" <rexmann@discussions.microsoft.com> wrote in message news:0560F9C5-4BA1-4318-B197-71C3AF211878@microsoft.com... > Hi > I have a spreadsheet where a code needs to b...

Changing start date
Scenario: In OL 2003, I have an event that's two weeks long, starting June 1 and ending June 14. I want to change the start date to May 27 but leave the end date alone. If I change the start date, OL automatically changes the end date to preserve the two-week duration. Is there a way to change the start date without affecting the end date? Richard Evans <infodex@mindspring.com> wrote: > In OL 2003, I have an event that's two weeks long, starting June 1 and > ending June 14. I want to change the start date to May 27 but leave > the end date alone. If I change the s...

How do I calculate the # of business days between two dates?
I know absolutely nothing about coding. Trying to use calculations in a report or query to accomplish this. Everything else I have found seems to include "coding" that goes way beyond my capabilities. Anything a novice can use? You need to use code: http://support.microsoft.com/kb/97757 You can call this function in a query 1. make the function a Public Function In the query have a column like this WorkDaysCalc: Work_Days([StartDate],[EndDate]) where [StartDate] and [EndDate] are actual fields in your query. Everyone starts out a "Novice" but the m...

Insert Macro
I've just seen a macro that I'd like to add into my current macro. Do I simply copy and paste to the bottom of my current macro? Thanks! If you're lucky, that would work. You may have to adjust variable names or even declare variable. Another way is to keep that macro as a different subroutine and just call it: sub yourexistingmacro() 'all your existing code call yournewmacro end sub sub yournewmacro() 'all that newstuff end sub Change "yournewmacro" to the correct name in both spots. And save your workbook before you test it. If it blows up real good...

how do I insert sequential numbering on a sheet of 10 pages?
-- Morrell http://www.publishermvps.com/Default.aspx?tabid=95 -- Ed Bennett - MVP Microsoft Publisher ...

Unable to Open "Outlook Today" in Office 2000
Several users at our company are having a problem with MS Office 2000 (SR1) v9.0.0.3821 - the "Outlook Today" main mailbox. Although every user can click on this folder/option along the left hand tree they are unable to click/open the "customize Outlook Today" icon. The icon is present, not greyed out or anyting, but no matter how many times you click on it nothing happens. - So far we've deleted all temp/temp internet files, cleaned/optimized the registry, uninstalled and reinstalled Office (Outlook), installed all options in Outlook, & deleted the word &...

CONVERT TIME & TRIM DATE
I need help converting time to an AM/PM time format our database displays time in 4 digits : 0006, 0737, 1217, 2149. I am finding that 0006 is 12:06 AM and 1217 is 12:17 PM, etc. The date displays: 2007-03-09 00:00:00.000, how do I have it display the date as 03-09-2007? Thanks. Specify the display format of the control or field: mm-dd-yyyy RENEE705 wrote: >I need help converting time to an AM/PM time format >our database displays time in 4 digits : 0006, 0737, 1217, 2149. I am >finding that 0006 is 12:06 AM and 1217 is 12:17 PM, etc. >The date displays: 2007-03-09 00:00:00.0...

Can I insert multiple scales on the Y axis of a single XY graph?
I am trying to plot three parameters on a single XY line chart. Is it possible io have three different scales charted to cover all the three parameters which are quite variable numerically? Using Excel 2000 No, you can only have a left and a right y-axis. Workaround: let's assume one series has very small numbers; have a column with these values multiplied by say 1000; plot this column and use a legend like "voltage x 1000" If one series is too large, have a column with the numbers divided; legend "Sales in $million) best wishes -- Bernard V Liengme www.stfx.ca/people...

help with range lookup and date criteria
Hello, I have a list of doctors (column A) that are each on-cal through a number of days. My argument is: if the date value of B1 an C1 is within the current date, then repeat the value of A1 -- James Spaldin ----------------------------------------------------------------------- James Spalding's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2567 View this thread: http://www.excelforum.com/showthread.php?threadid=39088 hi, James ! > ... a list of doctors (column A) that are each on-call through a number of days. > ... argument is: if the date value ...

what affect does the budget effective date have
when you save a budget money asks for the efective dates from - to. Do these have any purpose? it does not appear to chagne the way that money displays the budget and even if you go past hte budget time it will continue using the old budget amounts. If you try to have it re-calculate the past information it does not. it seems as though these are not ment for anything. ...

CFtpFileFind return null creation date
Hi All I am trying to list the URL and timestamp of some files on a server in a list box in a simple dialog using FTP. The code is as follows: void CFTPTestDlg::OnTest() { CListBox* pbox = (CListBox*)GetDlgItem(IDC_LB_FILES); pbox->ResetContent(); // Create session object to initialise libs CInternetSession sess(_T("My Test")); // Declare a FTPConnection CFtpConnection* pftpConn = NULL; try{ pftpConn = sess.GetFtpConnection("ftp.,mytestsite.co.uk", "mylogon","mypassword"); if (!pftpConn->SetCurrentDirectory("/Testdir/T...

extract year from Date Value
Good morning, Could someone help me extract the year portion from a date value such as this 11/20/2009? Thanks in advance, Mike With the Date in A1, place =YEAR(A1) in B1 Takeadoe wrote: > Good morning, > > Could someone help me extract the year portion from a date value such > as this 11/20/2009? > > Thanks in advance, > > Mike ...

can you date time stamp entries in excel
is it possible that when someone puts an entry on a shared worksheet in excel it can automatically date and time stamp their entry. Private Sub Worksheet_Change(By Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range("A1:H10")) Is Nothing Then With Target .Offset(0, 1).Value = Format(Now, "dd mmm yyy hh:mm") End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate ...

overlaping dates in two files
MS Money 2001 deluxe on Windows XP Pro I archived my 2004 accounts and never balanced them. I decided i needed to and now have two files, both .mny files with 2003Archive.mny is from 1/2002 to 12/2004 Active2005.mny is from 1/2004 to present. I'd like to combine them into one file so I can balance the accounts (three main accounts) because my statements are not ending nicely on the end of the month. Please recommend the best method so I can balance the accounts. I think I need to conbine the files or somehow get the months that are now balanced in the 2003Archive.mny file to give...

Help with Date/Time Function
I am working with date/time functions trying to figure out how many hours were spent doing a job. I have a start time and a finish time. But I need to subtract out any time between five pm and six am and weekends from the times. These would be times that no one was actually working on the job. For example I have 4/5/2005 18:23 4/6/2005 17:04 Could someone give me a clue as to how this might be done? Thanks, Steve Monczka smonczka@hotmail.com It's not a trivial thing, here's an example http://www.cpearson.com/excel/DateTimeWS.htm or here http://tinyurl.com/cbphn -- R...

expiration du mot de passe
Bonjour Depuis quelques jours, sans que je ne l'ai demand�, j'ai le message suivant au lancement de XP: "Le mot de passe expire dans 15 jours , voulez vous le changer maintenant ?" Pour le changer il faut conna�tre l'ancien....et je ne le connais pas ! Je ne veux pas me trouver bloquer dans 15 jours ! Que dois faire ??????????? Merci de votre aide Pourrait �tre le mot de passe vierge (jamais �t� d�fini). Avez-vous essay� d'entrer juste un nouveau mot de passe. D�sol� si le fran�ais n'est pas correct J'ai d� utiliser un traducteur. ...

time & date settings
The time and dateeetings keep reverting to 2007, and incorrect time. I reset it, and it stays that way until I shutdown. On reboot the time and date revert to the incorrect values. Any suggestions as to why this happens? Is there a way to keep the clock and/or date correct? Thanks in advance for any suggestions. Your computer's CMOS battery has run out of power and needs to be replaced. It means taking the computer apart, locating the battery (usually a CR2032 lithium battery) and replace.. -- Thomas Wendell Helsinki, Finland Translations to/from FI not always accu...

Past calendar dates losing their bolding
I know that the calendar has the current month, previous month and up to 10 months in the future to have the dates bolded if there are appointments there, but I have several clients who want more dates in the past - at least a year - to be bolded. Any way to do this? Mari (tulipmari.nospam@shaw.ca) remove the .nospam to respond to my email -- Sorry, no. it goes back just 2 months. -- Diane Poremsky [MVP - Outlook] http://www.poremsky.com - http://www.cdolive.com Outlook Tips: http://www.outlook-tips.net/ Expert Zone http://www.microsoft.com/windowsxp/expertzone Search for answers: http:/...

Outlook 2000
I am running Win2K with Office2K SR3. When I click on "customize Outlook Today" nothing happens. It has worked in the past. I tried "Detect and Repair" which said it repaired everything, but still nothing happens when I click "Customize Outlook Today." I am using the "winter" layout. OL2000: You Cannot Customize Outlook Today After You Install Critical Update 813489 for Internet Explorer: http://support.microsoft.com/default.aspx?scid=kb;EN-US;820575 Next time, search the archives using google.groups.com, you will find several thousand of the ...

Notification Email on duration date
Hi I have a question that is there any way to send email to owner when their task is reaching due date set for the task. I tried to make it work with workflow but I couldn't. Also I read through all the post here and couldn't find anything relating to my question. Sorry, subject should be "Notification Email on due date", note "duration date" Thks, motoC "motoC" wrote: > Hi > > I have a question that is there any way to send email to owner when their > task is reaching due date set for the task. > > I tried to make it work with...

Date calcutation
Good afternoon, i need some help with the following: I have a maintenace DB, i have the following fields MachineHours (long int), MaintenaceHours (long int), MaintenaceDate (date) and WeeklyHourWork (long int). I have a text box NextMaintenace (date) where i calcute the date of the next maintenace based on the date of the maintenace (MaintenaceDate) difference between MachineHours, MaintenaceHours and WeeklyHourWork. example: if MachineHours=4000, MaintenaceHours=7000, MaintenaceDate=26-10-2007, WeeklyHourWork=40 then NextMaintenace=15-03-2009 This works fine but I need to put a limit to...

Date Stamp
I've used this Macro and need to tweak it: Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count > 1 Then Exit Sub If Not Intersect(Range("a10"), .Cells) Is Nothing Then Application.EnableEvents = False With Me.Range("b10") .NumberFormat = "dd mmm yyyy" .Value = Now End With Application.EnableEvents = True End If End With End Sub This macro stamps B10 if something is entered in A10 but I also want this t...

ActiveSync 3.7 doesn't stay up to date with Outlook 2003
I just recently upgraded to Outlook 2003 (from XP) and I've found that my Pocket PC won't stay current with emails while it's connected to my PC in the cradle. I've noticed the problem when I delete emails from my inbox on the PC or I move emails to a folder on the PC. I would expect ActiveSync to fire up and update the Pocket PC but it doesn't seem to recognize that anything has changed. However, if I disconnect the Pocket PC from the cradle and reconnect it seems to recognize the differences and get itself up to date. Has anyone seen this problem? Any ideas o...

Inserting an Adobe Illustrator logo into Publisher 2007?
A graphic designer sent me a logo in .ai (Adobe Illustrator) format. When I go to Insert > Object, it inserts something with the filename on it, but not the logo itself. I absolutely cannot get this figured out. How do I get this logo displayed correctly in my document? Thanks. Do you have Illustrator on your computer? -- JoAnn Paules MVP Microsoft [Publisher] ~~~~~ How to ask a question http://support.microsoft.com/KB/555375 "MadDog" <MadDog@discussions.microsoft.com> wrote in message news:EFA63883-D066-4A1F-8E08-3B5E498B613F@microsoft.com... >A graphic desig...