Replace Date

Hi,

I have a list of Customer IDs and delivery dates as

Cust_ID	Deliv_Date
1000	2/24/2010
1001	2/25/2010
1002	1/1/1998
1003	1/1/1998
1004	3/1/2010
1005	3/15/2010

Here, I am trying to query this table so that if Deliv_Date = 1/1/1998
then Deliv_Date = Null.

My result should be something like this

Cust_ID	Deliv_Date
1000	2/24/2010
1001	2/25/2010
1002
1003
1004	3/1/2010
1005	3/15/2010

Can you please help me to query this result using Access query?

Thank you,
Sajan
0
Saz
3/1/2010 4:03:09 PM
access.gettingstarted 618 articles. 1 followers. Follow

3 Replies
776 Views

Similar Articles

[PageSpeed] 26

SELECT [Cust_Id], IIF([Deliv_Date]=#1/1/1998#,Null,[Deliv_Date]), ...
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they 
eat for a lifetime".


"Saz" wrote:

> Hi,
> 
> I have a list of Customer IDs and delivery dates as
> 
> Cust_ID	Deliv_Date
> 1000	2/24/2010
> 1001	2/25/2010
> 1002	1/1/1998
> 1003	1/1/1998
> 1004	3/1/2010
> 1005	3/15/2010
> 
> Here, I am trying to query this table so that if Deliv_Date = 1/1/1998
> then Deliv_Date = Null.
> 
> My result should be something like this
> 
> Cust_ID	Deliv_Date
> 1000	2/24/2010
> 1001	2/25/2010
> 1002
> 1003
> 1004	3/1/2010
> 1005	3/15/2010
> 
> Can you please help me to query this result using Access query?
> 
> Thank you,
> Sajan
> .
> 
0
Utf
3/1/2010 5:01:01 PM
Backup database first ---
UPDATE YourTable SET YourTable.Deliv_Date = Null
WHERE (((YourTable.Deliv_Date)=#1/1/1998#));

-- 
Build a little, test a little.


"Saz" wrote:

> Hi,
> 
> I have a list of Customer IDs and delivery dates as
> 
> Cust_ID	Deliv_Date
> 1000	2/24/2010
> 1001	2/25/2010
> 1002	1/1/1998
> 1003	1/1/1998
> 1004	3/1/2010
> 1005	3/15/2010
> 
> Here, I am trying to query this table so that if Deliv_Date = 1/1/1998
> then Deliv_Date = Null.
> 
> My result should be something like this
> 
> Cust_ID	Deliv_Date
> 1000	2/24/2010
> 1001	2/25/2010
> 1002
> 1003
> 1004	3/1/2010
> 1005	3/15/2010
> 
> Can you please help me to query this result using Access query?
> 
> Thank you,
> Sajan
> .
> 
0
Utf
3/1/2010 5:05:02 PM
On Mon, 1 Mar 2010 08:03:09 -0800 (PST), Saz <sajan.sajan@gmail.com> wrote:

>Hi,
>
>I have a list of Customer IDs and delivery dates as
>
>Cust_ID	Deliv_Date
>1000	2/24/2010
>1001	2/25/2010
>1002	1/1/1998
>1003	1/1/1998
>1004	3/1/2010
>1005	3/15/2010
>
>Here, I am trying to query this table so that if Deliv_Date = 1/1/1998
>then Deliv_Date = Null.
>
>My result should be something like this
>
>Cust_ID	Deliv_Date
>1000	2/24/2010
>1001	2/25/2010
>1002
>1003
>1004	3/1/2010
>1005	3/15/2010
>
>Can you please help me to query this result using Access query?
>
>Thank you,
>Sajan

SELECT CustID, IIF([Deliv_Date] = #1/1/1998#, NULL, [Deliv_Date]) AS
New_Deliv_Date;

If you wish to make this change permanent, you can run an Update query
updating Deliv_Date to NULL:

UPDATE mytable SET Deliv_Date = Null WHERE Deliv_Date = #1/1/1998#;
-- 

             John W. Vinson [MVP]
0
John
3/1/2010 5:27:01 PM
Reply:

Similar Artilces:

Replace word in sentence function...
I have the following function which replaces a word in a sentence with "....." =REPLACE(I2,SEARCH(E2,I2),LEN(E2),".....") Where the word is E2 and the sentence is I2. At the moment, if the word is "work" and the sentence is "I worked at home" I get the sentence "I .....ed at home". Any ideas how I could capture any remaining letters at the end of the word and get the result "I ..... at home"? I'd greatly appreciate your help. Thanks in advance, Chris can you upload example, would be easier to view -- Message posted from h...

find and replace hyperlink?
Hi, I want to globally find and replace the contents of a hyperlink with a new hyperlink. For example, I want this Jul.xls#'Title Page'!A1 to be replaced with Aug.xls#'Title Page'!A1 I can't seem to find a way do it. Is there a way? Thank you, Mike Take a look at David McRitchie's site: http://www.mvps.org/dmcritchie/excel/buildtoc.htm look for: Fix Hyperlinks (#FixHyperlinks) If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm me wrote: > > Hi, I want to g...

Exporting Date received and Date Sent to Access
How can export both 'date received' and 'date sent' fields to another program such as Access or Excel? However, if you use Access to set up a linked table connected to your = mail folder, you will see the Received date among the available = properties. (I don't remember whether Sent is also shown.) Third-party = export tools are also available; see = http://www.outlookcode.com/d/customimport.htm#tools --=20 Sue Mosher, Outlook MVP Author of Configuring Microsoft Outlook 2003 http://www.turtleflock.com/olconfig/index.htm and Microsoft Outlook Programming - Jump...

Replacing Powerpoint 97 SR-2 on M/S Office 97 Pro Edit on Win XP
I dread asking this in case someone descends on me like a ton of bricks and says the answer is in the posts already! - but i I have never used the PP 97 which came with the MS Office 97 Pro Edit I have installed on my computer which uses Win XP with Ser Pack 3. I am about to embark on using PowerPoint for the FIRST time and wonder whether I should, before anything else, acquire a more up to date version first - and one that will work problem free with my XP - which version should I go for and will it, on loading, overwrite and replace the Powpoint I already have and still operat...

Search and replace problem
Hi, I have an Excel worksheets where in several cell's a symbol was added to mark the cell's "to review". The symbol used was "(*)". I have tried now to delete all the entries with a "search and replace", but Excel interpretes the asterisk as a DOS-symbol and deletes everythng between brackets. Is there a way to avoid that or to let Excel know that I only want to delete the specific characters. TIA Melissa It's because it is a wildcard, you can replace it by preceding it with a tilde ~ which tell Excel to find an asterisk in find what p...

replace a negative result with a zero?
I have my formula entered but need it to return a zero if the result is negative. Any help is greatly appreciated. -- Pam Well, what is the formula that you are using? You could try this: =3DMAX(your_formula,0) to replace your_formula. Hope this helps. Pete On Apr 19, 5:17=A0pm, Pam2277 <Pam2...@discussions.microsoft.com> wrote: > I have my formula entered but need it to return a zero if the result is > negative. =A0Any help is greatly appreciated. > -- > Pam Hi, =if(your formula<0,0,your formula) "Pam2277" wrote: > ...

Selective Replace in Search and Replace
I need to search and replace in a large document. I don't want to replace the entire search term/expression just part of it. In this case I am searching for a paragraph return followed by any letter (not digit) and then want to change that to paragraph/tab but keep the letter. The search looks like this ^p^$ but naturally if replace is ^p^t I lose the first letter of the text string which I don't want How can I construct either search and replace so that ^p Alexander turns into ^p ^tAlexander and not ^p ^tlexander Thanks in a...

Replace wildcards with a space
I have a text field with all values containing an asterisk. The asterisk can be located anywhere within the contents. I am trying to replace the asterisk with a space. I will then use TRIM to remove any spaces at the beginning of the text. I have been unable to get an update query to work. When my criteria is like "~[*}", Access says that 0 rows will be updated. When the criteria is [*], I am asked to enter a parameter value. Any assistance will be greatly appreciated. Thanks - bkh Barbara wrote: >I have a text field with all values containing an aste...

Change text based on date
I want to change the text color on a report using the attached code but can't get it to wor not sure where I'm going wrong txtOrderDueDate is a long date? 12/08/2009 Private Sub Report_Load() If Me.txtOrderDueDate.Value <= Date + 2 Then Me.txtOrderDueDate.ForeColor = RGB(255, 0, 0) Else Me.txtOrderDueDate.ForeColor = RGB(0, 255, 0) End If End Sub Thanks! Jerry -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-reports/200912/1 jbair via AccessMonster.com wrote: >I want to change the text color on a report using t...

Replace more characters using replace
Hi I have number of characters in a string, that I like to replace with "nothing". I use a code like this: Sub test() Dim a As String a = "Jan+& Per" a = Replace(a, " ", "") a = Replace(a, "&", "") a = Replace(a, "+", "") MsgBox a End Sub As I have about 15 characters, that all should be replaced with nothing, I am looking ofr another way to it. Something like Sub test() Dim a As String a = "Jan+& Per" a = Replace(a, {" ",...

Replacing old Windows XP and Outlook Express with new Windows 7
How do I export the contents and settings out of Outlook Express and import the accounts, settings and emails into Windows Live Mail on another machine? For messages: Copy the *ENTIRE* OE message store folder to flash or CD. (Folders.dbx must be included). Place this on the Desktop or other location on the machine using WLMail. Open WLMail and: File | Import | Messages | Microsoft Outlook Express 6 and point to where you saved it. For Addresses: Open the Address Book in OE and File | Export | Address Book (wab) and save it to the Desktop. Copy to flash or CD. Place this on the...

Change format of the date in Visio?
When I want to use the Timline-function in Visio I get disturbed that it isn't possible to change the format of the date for example for an interval or a milestone to the way i want it. I would like it to be "Friday 16/2" instead of the ways that is possible to chose (not written out the month in letter nor the year). Is it possible to change into this easily? Thanks On Sun, 28 Jan 2007 14:27:01 -0800, Smeeed <Smeeed@discussions.microsoft.com> wrote: >When I want to use the Timline-function in Visio I get disturbed that it >isn't possible to change the for...

MATCH command does not work with dates
Hi, I have 2 excel worksheets assuming "sheet1" and "sheet2". example: Sheet1: user date1 date2 date3 date4 date5...dateN AA BB CC Sheet2: user data date1 date2 date3 date4 date5 dateN AA total 100 102............................................. AA mean 30 32.............................................. AA Average 31 35................................................ CC total........................................................................ CC mean..................................................

Conditional formatting dates
Hi, Help please. I have a column of 'Due Dates' in the mmm-yy format. I want the highlight a 'date' a month before it is due to expire. How is it done? I can get so far with DATEVALUE but then become stucK. Many thanks Hi Lofty, For a date in A1, you could use a formula for the conditional format like: =A1>=DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY())) -- Cheers macropod [Microsoft MVP - Word] "Lofty" <Lofty@discussions.microsoft.com> wrote in message news:848E191C-832F-4C60-924D-32A1AF70A0FA@microsoft.com... > Hi, > Help ...

How do I convert the date from 1900's to 1800's?
I'm working on a spreadsheet in which I'm using dates from the 1800's. Is there a way to convert the date from 11/29/65 to 29 November 1865? I've tried to change it, but of course it goes to 1965 instead. In the meantime I've been changing each date manually, but I wanted to see if there was an easier and quicker way to convert the dates. Thanks! Hi Diana Try =MID(A1,4,2)&" "&CHOOSE(LEFT(A1,2),"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct&...

Do you want to replace the contents of
When my macro runs, it stops after this procdure and asks "Do you want to replace the contents ofthe destination cells" is there something I can add that will automatically tell it yes? Here is the part of the macro that it stops on. Selection.TextToColumns Destination:=Range("C1"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _ Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _ :="/", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1)) Thanks H...

sum columns between certain dates
I have a spreadsheet with headings Jan, Date Received, Feb, Date Received, Mar, Date Received etc up to Dec. An amount due in Jan is entered into the Jan column but date received could be Jan, Feb or Mar. I need to add all amounts received in Jan which means I need to look in all of the Date Received columns if the date falls between 1 and 31 Jan. In the Feb column I need to look at amounts received in Feb and Jan and Mar and total them. In conclusion I would total the amount due in Jan and show the amount received in Jan. Any help would be appreciated. Jan 2010 Dat...

How do I replace just a few characters?
Hello, I have an excel worksheet with a street name column. I need to rename some names from "Main Street" to "Main St". I can do it for one name easily with find and replace, but how do I make it work for an entire column of different names such as "Easy Street" and "Main Street". If I do find "* Street" and replace with "* ST" I get "* ST" for the output instead of "Easy ST" What am I missing? -- Tomsriv ------------------------------------------------------------------------ Tomsriv's Profile: http:/...

Replacing Character
I need to replace/delete the " >" character at the end of a number... when the data was brought into the excel 1234> was the number that cam it happens in about 7000 lines any suggestions on how to replace it with a blank?? thank -- tico31p ----------------------------------------------------------------------- tico31pl's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1040 View this thread: http://www.excelforum.com/showthread.php?threadid=46572 Select the range to fix. Edit|Replace what: " >" (without the quotes with: (leav...

convert date format
hello I need to convert dates that are formatted as 10-Mar-2008 to 03/10/08. I tried to just select a new format for the cells, but that did not work. I tried to run calc now, but no luck. If I double click on each cell it will convert, but I have several thousand cells to do. Thanks Fred Sounds like you do not have real dates, just text If the date is in cell A1 what does =ISNUMBER(A1) return? If should be TRUE. It will return FALSE if you have text and we need to now more to help you. How is a one digit date shown? 05-Sep-2008? Are all months three characters? You could try Data ...

VBA to find filename and replace
Hi I just found the answer to "Find and Replace in VBA" and tried to adapt it for what I'm doing, but it's more difficult so I need some help please. I have multiple worksheets in an excel 2007 workbook - a monthly report. Within the worksheets I have many cells that link to last months workbooks - (i.e I compare last month figures to this months figures). I need the macro to do the following: - Create an Input box for the user to "enter the name ofTHE OLD months workbook" - Create an Input box for the user to "enter the name of THE NEW mont...

Replacing A Column Of Numbers
Hello, New user of Excel 2007, and Excel in general. Should be simple, but I just can't figure out how. Have a column of numbers in Column A In Column B (row 1)I have the formula of what's in A (row 1) multiplied by 1000 And I have dragged this formula downwards, so I now have in each row B what is in the same row in Column A multiplied by 1000. So far, all is fine. Now, I want to replace what's in Column A by the newly generated values in Column B (row by row replacement) I can't just Cut out everything in Column A, as B then disappears, as, I'v...

date #5
Hi Peeps Many thanks in advance for any help/ advice that you can give me I am trying to construct a simple database in Excel to track sales and payments made Is there a way to use conditional formatting/or any other tool to highlight payment due not yet received by referecing it to the date on my computer i.e the date on my computer is 6th June 2004, payment was due 30th May 2004, I would like the cell that has the due date to be highlighted Best Regards Yinka Yinka Format>Conditional Formatting>Cell Value is: less than =TODAY() Gord Dibben Excel MVP On Sat, 5 Jun 2004 14:51:02 ...

Date Range for X-Axis Labels
I want my line chart to plot to 9 dates selected from the worksheet. How do I get the x-axis scale to match and display my dates? I am using the x-axis labels option and it doesn't return the dates on the x-axis of my chart. Thanks for your help. Tom -- Sony Luvy By default, Excel applies a time or date scale axis, which includes all of the dates as if it were a continuous scale, with ticks and labels at regular intervals. If you want only to show specific dates without regard to the date (i.e., with data points equally spaced), change to a category type axis. In 2003 and earl...

Find / Replace
In a field, I would like to update all instances of "Daily Billing" to "Rental Payment - Daily Billing". The Replace function works the first time, but when I add data to the file, and run the query again, it updates the fields which have already been upated to "Rental Payment - Rental Payment - Daily Billing". I can see why it is doing this, but is there a way for the update query to look at just the first 11 characters of the field and only perform the update if it finds the "Daily Billing" text there? Thanks. Use "Daily Billing" as ...