Changing data depending on time?

Hi, everyone:

Does anyone know if it is possible to change data in a table according
to a date?
Let's say I would like to change the contents of all fields that are
two days old and I would like to Access to do the work.  Can Access
actually do the check/change automatically?

Thanks a lot,

Tom

0
tomasz
5/5/2007 5:30:54 PM
access.formscoding 7493 articles. 0 followers. Follow

6 Replies
600 Views

Similar Articles

[PageSpeed] 38

On 5 May 2007 10:30:54 -0700, tomasz.cichon@gmail.com wrote:

>Hi, everyone:
>
>Does anyone know if it is possible to change data in a table according
>to a date?
>Let's say I would like to change the contents of all fields that are
>two days old and I would like to Access to do the work.  Can Access
>actually do the check/change automatically?

You can certainly run an Update query using a criterion 

<= DateAdd("d", -2, Date())

to select all records where a date/time field is older than two days... but
the need to do this makes me VERY suspicious of your table design. Data in
records should NOT depend on the date; what if nobody happens to open the
database on the day when data should change!?

Could you explain the nature of this table; what fields you want to change;
and *why*?

             John W. Vinson [MVP]
0
John
5/5/2007 10:55:19 PM
On May 5, 2:55 pm, John W. Vinson <jvinson@STOP_SPAM.WysardOfInfo.com>
wrote:
> On 5 May 2007 10:30:54 -0700, tomasz.cic...@gmail.com wrote:
>
> >Hi, everyone:
>
> >Does anyone know if it is possible to change data in a table according
> >to a date?
> >Let's say I would like to change the contents of all fields that are
> >two days old and I would like to Access to do the work.  Can Access
> >actually do the check/change automatically?
>
> You can certainly run an Update query using a criterion
>
> <= DateAdd("d", -2, Date())
>
> to select all records where a date/time field is older than two days... but
> the need to do this makes me VERY suspicious of your table design. Data in
> records should NOT depend on the date; what if nobody happens to open the
> database on the day when data should change!?
>
> Could you explain the nature of this table; what fields you want to change;
> and *why*?
>
>              John W. Vinson [MVP]

Thanks for the tip about "Update Queries."  This looks like what I
need.

One of the fields in the database contains credit card numbers and I
want those numbers to be visible only for 48 hours.  After that, the
number will change to something like **** **** **** 1234.  The
database will be open by users almost every day of the year.
Occasionally, when users don't open it, the "update query" will run
the following day.

Tomasz     Cichon
VANCOUVER, BC

0
tomasz
5/6/2007 11:34:50 PM
On May 6, 3:34 pm, tomasz.cic...@gmail.com wrote:
> On May 5, 2:55 pm, John W. Vinson <jvinson@STOP_SPAM.WysardOfInfo.com>
> wrote:
>
>
>
> > On 5 May 2007 10:30:54 -0700, tomasz.cic...@gmail.com wrote:
>
> > >Hi, everyone:
>
> > >Does anyone know if it is possible to changedatain a table according
> > >to a date?
> > >Let's say I would like to change the contents of all fields that are
> > >two days old and I would like to Access to do the work.  Can Access
> > >actually do the check/change automatically?
>
> > You can certainly run an Update query using a criterion
>
> > <= DateAdd("d", -2, Date())
>
> > to select all records where a date/timefield is older than two days... but
> > the need to do this makes me VERY suspicious of your table design.Datain
> > records should NOT depend on the date; what if nobody happens to open the
> > database on the day whendatashould change!?
>
> > Could you explain the nature of this table; what fields you want to change;
> > and *why*?
>
> >              John W. Vinson [MVP]
>
> Thanks for the tip about "Update Queries."  This looks like what I
> need.
>
> One of the fields in the database contains credit card numbers and I
> want those numbers to be visible only for 48 hours.  After that, the
> number will change to something like **** **** **** 1234.  The
> database will be open by users almost every day of the year.
> Occasionally, when users don't open it, the "update query" will run
> the following day.
>
> Tomasz     Cichon
> VANCOUVER, BC

It works!!

UPDATE [TABLE] SET [TABLE].CC = "****************"
WHERE (((TABLE.DATE)<=DateAdd("d",-2,Date())));

The update query runs every time the form opens.
Thanks again, John.

0
tomasz
5/7/2007 6:33:52 AM
On 6 May 2007 23:33:52 -0700, tomasz.cichon@gmail.com wrote:

>UPDATE [TABLE] SET [TABLE].CC = "****************"
>WHERE (((TABLE.DATE)<=DateAdd("d",-2,Date())));
>
>The update query runs every time the form opens.
>Thanks again, John.

If that's really what you want to do, fine.

The two problems that I see with it is that this will update EVERY record
older than two days old, including all the record that were updated last week,
last month, or last year. This will cause your database to bloat rapidly
because you'll be "changing" every record in the table (even though it's a
non-change change) every day, or even multiple times each day.

I'd suggest adding a criterion to exclude already updated records:

UPDATE [table] SET [table].CC = "************" & Right([CC], 4)
WHERE [table].[DATE] <= DateAdd("d", -2, Date())
AND CC NOT LIKE "[*]*"

This will leave the last four digits in the number, as requested in your text
message; and it will only update records which *don't* start with an asterisk.

             John W. Vinson [MVP]
0
John
5/7/2007 3:21:35 PM
On 6 May 2007 23:33:52 -0700, tomasz.cichon@gmail.com wrote:

>The update query runs every time the form opens.

ah... the other problem I see (which I neglected to type) is that the CC value
will be irretrievably destroyed. You won't be able to find out what was there,
even if some customer calls up and says "Did you ever have a charge against
Visa card 3333-3333-3333-3333?"

             John W. Vinson [MVP]
0
John
5/7/2007 7:50:18 PM
On May 7, 12:50 pm, John W. Vinson
<jvinson@STOP_SPAM.WysardOfInfo.com> wrote:
> On 6 May 2007 23:33:52 -0700, tomasz.cic...@gmail.com wrote:
>
> >The update query runs everytimethe form opens.
>
> ah... the other problem I see (which I neglected to type) is that the CC value
> will be irretrievably destroyed. You won't be able to find out what was there,
> even if some customer calls up and says "Did you ever have a charge against
> Visa card 3333-3333-3333-3333?"
>
>              John W. Vinson [MVP]

John, you answered the questions before I even asked them! Thank you
so much for your help.  You are right - changing the changed fields
would be an unnecessary redundancy.  Thanks for the hint on how to
keep the last four digits from the card #.

0
tomasz
5/8/2007 4:26:19 PM
Reply:

Similar Artilces:

How do I get excel to accept (c) as text and not change to copyri.
How do I enter the text (c) in Excel without having it changed into the copyright symbol? Hi Daffyd, Try: Tools | Autocorrect | Select (c) | Delete | OK --- Regards, Norman "daffyd" <daffyd@discussions.microsoft.com> wrote in message news:8CCC3C1A-6F19-4F62-B934-8A71F236A4FD@microsoft.com... > How do I enter the text (c) in Excel without having it changed into the > copyright symbol? Go into the Tools Menu. Look for AutoCorrect. In the bottom half of the AutoCorrect Tab, look at the list for Replace text as you type. Delete the entry for (c). tj "da...

Printing Multiple Receipts at a Time From Journal
Hey guys, Is there a way to print multiple journaled receipts at a time? Say all receipts for a batch or by date or other criteria? Thanks Tom, Not that I've seen. -- = Get Secure! - www.microsoft.com/security You must be using Outlook Express or some other type of newsgroup reader to see and download the file attachment. If you are not using a reader, follow the link below to setup Outlook Express. Click on "Open with newsreader" under the MS Retail Management System on the right. http://tinyurl.com/75bgz ========== "Tom Bombadill" <Genius_pos...

Passing data from one form to another
Hello I have a form called frmMaindB and it has 5 text boxes on it (txtEmployeeTime, txtDTRegular, txtDTReason1, txtDTReason2, txtDTMaintenance) when I double click on the text box it opens up a pop up form named frm_DecimalConversion. On this form I have two text boxes one box I enter data into and the other calculates or converts the data to a decimal. The box that converts the data is called txtDecimal. Then I have a close button which I want to use to close the pop up form and insert the data into the text box I double clicked in to get the pop up or (frm_DecimalConversion). I have r...

data value in Form field if no table entry
I have a form with a field which pulls through and concentenates 2 fields called [ContactFirstname] and [ContactLastName]from my table There are however some customers for whom I do not have names and therefore instead I would like Sir/Madam to appear in the field in the form I think I have seen this done somewhere using ELSE? but can't find it Any help/ideas gratefully received Perhaps something like this: Nz(Trim([ContactFirstname] & " " + [ContactLastName]), "Sir/Madam") -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access use...

Publisher 2000 changes default font
Having loaded Publisher 2000 onto a Win98 m/c I have discovered fonts used by Groupwise have changed. This implies that P2000 has changed the default font somewhere - any ideas please? How do I change the fonts in GroupWise http://support.novell.com/cgi-bin/search/searchtid.cgi?/10061270.htm -- Mary Sauer MS MVP http://office.microsoft.com/ http://www.mvps.org/msauer/ news://msnews.microsoft.com "PS" <anonymous@discussions.microsoft.com> wrote in message news:1be4401c45220$f2b1b4a0$a601280a@phx.gbl... > Having loaded Publisher 2000 onto a Win98 m/c I have > discover...

Change outbound server in header to fix 550 Can't verify your host name error
The headers on the outbound emails show the internal DNS name of our exchange server; obviously this won't resolve properly at the destination. How/where in Exchange 5.5 can I force the IMC to use a real fqdn on outbound mail? Thanks! Frinky You can do this in TCP/IP properties\Advanced\DNS tab of machine. And yes, this is not just for Exchange, so you may consider forwarding all outgoing mail to some relay server (your firewall or ISP's server). Professor Frink wrote: > The headers on the outbound emails show the internal DNS name of our > exchange server; obviously this...

why do changes only save locally on networked excel file?
why do changes only save locally on networked excel file? ...

How can I stop charts from refreshing when changing source data?
My problem is, that I am working with a lot of data and when I change some of the ranges all charts in my view refreshes and it takes much time. My pc is aP4 3GHz, 2GB RAM so that should not be the bottleneck. Is there any way to force the charts not to update all the time? ...

Help ! formatting data to text
I am creating data in an Excel spreadsheet. I then want to get that data into a simple text email. I have some problems and questions... 1) how do I get the columns of data to line up evenly when I copy the data to email text ? Keep in mind I need to be in simple text format, not HTML or rich text. Every time I do this, all columns become chaos and are unreadable. 2) Is there a simple way to automate the creation of an email from an excel file ? this is less important to me. Thanks in advance WxMachine #1. I think it may have to do with what email client you use, too. I copy and ...

Count on cell for each time it is changed
I would like to count the number of times a cell has been amended/ changed e.g cell b2 has the word red it it, in cell c2 i would like a counter for everytime the cell in b2 has changed, at the end of the day you get final number. Is this at all possible? Jelinek, You can do it by putting the following VBA macro in your sheet: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$B$2" Then Cells(2, 3) = Cells(2, 3) + 1 End If End Sub Art "Jelinek" wrote: > I would like to count the number of times a cell has bee...

how do I remove fx from the function line, can't enter data
I have the fx displayed just under my toolbar, and I can't enter or change data in any of the cells in the file. I can't get the red X, the Green check mark, or the black = sign to appear. There are very few areas that are not "greyed out" under the headings at the top. This situation applies to all of the excel files on this computer. I have Excel 2000. Please help. Can you move the cursor around anywhere in the spreadsheet? "dmdranch" wrote: > I have the fx displayed just under my toolbar, and I can't enter or change > data in any of the c...

how do i recover data in publisher
i have been entering addresses to set up a mail merge. i cllicked the "ok" button in the window and lost all data . can i recover it Look in a folder in My Documents named "My Data Sources". Publisher data is saved as .mdb(Access) file. Did you try selecting "Edit Address List" in the Mailings and Catalog menu (Tools)? -- Mary Sauer http://msauer.mvps.org/ "dee" <dee@discussions.microsoft.com> wrote in message news:690430F1-36DE-47EE-8B7D-DD12A096C075@microsoft.com... >i have been entering addresses to set up a mail merge. i cllicked ...

hide/change color of selected row headings
Is it possible to hide the row heading numbers for selected rows (i.e. rows 51 and greater) for just the selected sheet? I'd like to have a color with no row heading number appear that matches the fill color I select for the adjacent cells. Secondly, any ideas as to how I can prevent the user from scolling further down than a certain point (i.e. row 51). Thanks in advance. Joe Row headings are either on or off, you can't hide some. However, you can achieve a similar effect by hiding *all* headings (Tools/Options/General) and putting the numbers 1:51 in A1:A51. to limit scro...

Using subtotals as single data entries
Sorry about the subject--I couldn't figure out how to describe it simply. I have a large file (16,000 records) of amounts billed by roughly 10,000 service providers. A number of these providers have multiple office locations, so each record is unique to a specific office location. In other words, a provider who billed from 3 different office locations will have 3 entries. Each provider has a unique provider ID number, which stays the same regardless of which office location he is billing from. I want to be able to subtotal the amount billed by each provider for all their office locations...

Time Delayed Response to MsgBox
I have a several "research" files that I set up to run a macro as soon as a file is opened. In each case the macro retrieves and summarizes a bunch of data taking several minutes to execute. I'm setting up a "master" file with a simple macro that will open and close each of the "research" files sequentially so I can run the series of reports without being at the computer for hours. There are times where I want to open a "research" file to review the data but not execute the macro. I'd like to add a MsgBox that prompts the user t...

How can I keep track of when (date and time) data is entered into.
I am trying to create a spreadsheet for a high school class. I need to be able to track when a student has entered data into specific cells of the spreadsheet. Any ideas? In the code behind the worksheet, enter (eg) Private Sub Worksheet_Change(ByVal Target As Range) Cells(1, 1).Value = Now() End Sub This will enter in Cell A1 the date and time at which any entry is made in that worksheet. If you need the location of the time-stamp to vary according to which cell is changed then you can test the value of Target and vary the destination cell accordingly. -- Return email address is n...

changing a name in mulit parts of a document
I am setting up a word document. This will be used as a template In this document I wish to insert the same "name" throughtout this document. Is it possible to change the name in one spot and all of the others change automatically thanks See http://gregmaxey.mvps.org/Repeating_Data.htm -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ><<> <>...

How can I cut data out of HTML table, into msExcel and just take the data & columns? (but NOT the formatting & URLs!)
Hi This is driving me ABSOLUTELY NUTS! How can I keep the rows & columns of data that I am copying and pasting off a website (my own in this case!), into a spreadsheet... WITHOUT taking all the data formatting? If I paste out of Ms IE v6 into Ms Excel (2003), it does at least keep the columns (something that doesnt happen if I paste out of FireFox, fwiw). But it pastes with all the formatting & URLs etc - which I DONT WANT! OK, I can save as .CSV, close, 2 warnings, and re-open but when done REPEATEDLY this is a damned nuicance! Any suggestions? Ship Shiperton Henethe ship w...

Run-time error 1004
I am using Excel 97 on NT and have a command button on a sheet to whic I attached a macro for copying the sheet to a new workbook, deletin specific columns and other unnecessary info, and saving the new book t a network drive under a name with today's date for emailing to anothe area. When I perform the function manually everything works beautifully bu when I attempt to execute the macro I get the following error "Run-time error '1004': Copy method of worksheet class failed". when use the Debug button, VBA highlights Sheets("Daily").Copy as th culprit. Macro f...

Retrieving sorted data from same table.
Hi All, I am working on a table (mentioned below) I am looking for a query which can get me the data according to the =93id=94 column with respect to speed. The condition is that I have to get three consecutive entries which have speed > 60 Below is the sample table with data on which I have to retrieve the data on above condition. The output i need can be as given below DVXC002 12/10/09 0:12 96 DVXC002 12/10/09 18:40 89 DVXC002 12/10/09 19:43 65 DVXC005 12/10/09 11:56 69 DVXC005 12/10/09 15:26 62 DVXC005 12/10/09 17:35 85 Need your help urgently....Thanks in advan...

change exchange name suffix
Hi We need to change our exchange server 'name' suffix. The situation is that we have a local DNS domain 'bs.si' where all our company computers and server are (Win2k srv AD) We have also external domain 'bs-group.si' that is also for delivering mail (mx record). The Exchange server name is 'posta' Now when someone connects to Exchange server from outside via port 25 it responds like '220 posta.bs.si ...', but I want that the response will be like '220 posta.bs-group.si'. So that mail that is coming from our server will be seen like that i...

Import directory data into Excel 2003
I have over 1000 media files that I would like to extract information from and put into an Excel spreadsheet. Using Explorer, I have defined the fields I would like to see, such as title, duration, comment etc. Now, I need to import this data into Excel. So far, I've not been able to find a way to do this. Can someone offer some suggestions please? Thanks, Nigel -- www.myoldcontacts.com - Tell your friends to tell their friends www.sysadmininc.com - Consultancy, Service, Sales, Networking... www.british-expats.com - Connect with British Expats World Wide www.kxez.com/shows_britishinv...

Showing the perimeter of a set of (X,Y) data!
Good day all, I need to plot the perimeter of a set of data. I have a set of (X,Y) data with error bar and it is a nice mess so I just actually need to see (show) the area were the data can be found. Then hopefully overlay an other set of (X',Y') data and show that they both cover the same surface of existence. i.e this is a set of metrology measurement in X and Y of a part build from different mould. Obviously you get a nice cloud of X and Y but does the new material offer the same 'cloud' ? Thank you I think the easiest way to do this is plot the data on a XY Scatter cha...

show last data point in chart
Hello, I am charting a range of observations/data points. Is there a way to make the last data point show up differently on the chart (different color/shape)? Thank you. Nathan - > I am charting a range of observations/data points. Is there a way to make the last data point show up differently on the chart (different color/shape)? < Click the charted data once to select the entire data series. Pause. Click the single point to select it. Then use the Format menu. - Mike www.mikemiddleton.com Thanks for your reply. Well, that would work if I knew which point on the chart ...

Changing password #2
Hi, If I change a domain user's password, will that user's Exchange Server 2003 mailbox's pssword be changed too? I mean, does Active Directory share the same password with Exchange Server 2003? Thanks! Exchange never had passwords separate from domain, not even in Exchange 4-5.5 era. So you would change just one password, which belongs to AD account. Michael wrote: > Hi, > > If I change a domain user's password, will that user's > Exchange Server 2003 mailbox's pssword be changed too? > I mean, does Active Directory share the same password > w...