Cashflow query to return previous valid field if date has null val

Hello guys!

Hope someone can help as usual!
I have designed a simple query to control my cashflow, pulling data from a 
topay_query and toreceive_query. I have managed to make all the calculations, 
no problem.
The thing is that I have an "Accumulated payed" and "Accumulated received". 
When there's no moviment in a day these fields come out blank. How can I do 
that when there's no movement, the query would consider always the value of 
the previous valid field?

THanks mate!
0
Utf
11/27/2007 6:18:01 PM
access 16762 articles. 3 followers. Follow

7 Replies
2323 Views

Similar Articles

[PageSpeed] 0

How are your queries set up?  Are you linking by day?  Is one of your 
criteria to only show the current day?

"Tiago" wrote:

> Hello guys!
> 
> Hope someone can help as usual!
> I have designed a simple query to control my cashflow, pulling data from a 
> topay_query and toreceive_query. I have managed to make all the calculations, 
> no problem.
> The thing is that I have an "Accumulated payed" and "Accumulated received". 
> When there's no moviment in a day these fields come out blank. How can I do 
> that when there's no movement, the query would consider always the value of 
> the previous valid field?
> 
> THanks mate!
0
Utf
11/27/2007 6:26:08 PM
I have one table called DateMAIN with a date field that relates to:

datetopay at "topay_query";
datetoreceive at "toreceive_query".

And I have no criterias, so I have one line for each date that's why 
sometimes they are blank.

Tiago.

"Lance" wrote:

> How are your queries set up?  Are you linking by day?  Is one of your 
> criteria to only show the current day?
> 
> "Tiago" wrote:
> 
> > Hello guys!
> > 
> > Hope someone can help as usual!
> > I have designed a simple query to control my cashflow, pulling data from a 
> > topay_query and toreceive_query. I have managed to make all the calculations, 
> > no problem.
> > The thing is that I have an "Accumulated payed" and "Accumulated received". 
> > When there's no moviment in a day these fields come out blank. How can I do 
> > that when there's no movement, the query would consider always the value of 
> > the previous valid field?
> > 
> > THanks mate!
0
Utf
11/27/2007 6:34:00 PM
OK.. more questions.

What output are you looking for?   The latest entries in both tables?
And what are you using the datemain table for?

"Tiago" wrote:

> I have one table called DateMAIN with a date field that relates to:
> 
> datetopay at "topay_query";
> datetoreceive at "toreceive_query".
> 
> And I have no criterias, so I have one line for each date that's why 
> sometimes they are blank.
> 
> Tiago.
> 
> "Lance" wrote:
> 
> > How are your queries set up?  Are you linking by day?  Is one of your 
> > criteria to only show the current day?
> > 
> > "Tiago" wrote:
> > 
> > > Hello guys!
> > > 
> > > Hope someone can help as usual!
> > > I have designed a simple query to control my cashflow, pulling data from a 
> > > topay_query and toreceive_query. I have managed to make all the calculations, 
> > > no problem.
> > > The thing is that I have an "Accumulated payed" and "Accumulated received". 
> > > When there's no moviment in a day these fields come out blank. How can I do 
> > > that when there's no movement, the query would consider always the value of 
> > > the previous valid field?
> > > 
> > > THanks mate!
0
Utf
11/27/2007 6:40:00 PM
Hi Lance,

I'm looking for something like this:

 date         Revenue     Cost      Balance
01/nov -        50.0       20.00       30.00
02/nov -        00.0       00.00       30.00 NO MOVEMENT 
03/nov -        10.0       20.00       20.00
04/nov -        00.0       30.00      -10.00
05/nov -        00.0       00.00      -10.00 NO MOVEMENT

Note that the 02/nov and 05/nov there wasn't any revenue or cost, so the 
balance was equal to the one of the previous date.

The datemain table is used just to give me all dates of the year, just a 
reference to link the other 2.

Thanks in advance for your help.

Cheers, tiago.


"Lance" wrote:

> OK.. more questions.
> 
> What output are you looking for?   The latest entries in both tables?
> And what are you using the datemain table for?
> 
> "Tiago" wrote:
> 
> > I have one table called DateMAIN with a date field that relates to:
> > 
> > datetopay at "topay_query";
> > datetoreceive at "toreceive_query".
> > 
> > And I have no criterias, so I have one line for each date that's why 
> > sometimes they are blank.
> > 
> > Tiago.
> > 
> > "Lance" wrote:
> > 
> > > How are your queries set up?  Are you linking by day?  Is one of your 
> > > criteria to only show the current day?
> > > 
> > > "Tiago" wrote:
> > > 
> > > > Hello guys!
> > > > 
> > > > Hope someone can help as usual!
> > > > I have designed a simple query to control my cashflow, pulling data from a 
> > > > topay_query and toreceive_query. I have managed to make all the calculations, 
> > > > no problem.
> > > > The thing is that I have an "Accumulated payed" and "Accumulated received". 
> > > > When there's no moviment in a day these fields come out blank. How can I do 
> > > > that when there's no movement, the query would consider always the value of 
> > > > the previous valid field?
> > > > 
> > > > THanks mate!
0
Utf
11/27/2007 8:46:00 PM
OK, first we address the null data from missing days.  We do this by 
appending a 0 to the front of your revenue field and then getting the value 
of that.  Like this: Val("0" & [value_pay]).  Null field become 0, and fields 
with values in them remain the same since leading 0's in a numeric field are 
ignored.

Next we address your running sum.  To use a running sum in a query requires 
a function.  Such as:

Function running_sum(inPay As Long, inRec As Long)
   Static vPay As Long
   Static vRec As Long
   
   vPay = vPay + inPay
   vRec = vRec + inRec
   running_sum = vRec - vPay
End Function

Now put together your query.  Link both $$ tables to datemain and include 
all records from datemain and only records that match from the other ( AKA 
arrow pointing from datemain to each of your other two tables ).  Include the 
date from datemain, the adjusted pay from step #1, the adjusted received from 
step #1, and a function call ( being passed the adjusted pay and received ) 
to running_sum.

You should have what you need.

"Tiago" wrote:

> Hi Lance,
> 
> I'm looking for something like this:
> 
>  date         Revenue     Cost      Balance
> 01/nov -        50.0       20.00       30.00
> 02/nov -        00.0       00.00       30.00 NO MOVEMENT 
> 03/nov -        10.0       20.00       20.00
> 04/nov -        00.0       30.00      -10.00
> 05/nov -        00.0       00.00      -10.00 NO MOVEMENT
> 
> Note that the 02/nov and 05/nov there wasn't any revenue or cost, so the 
> balance was equal to the one of the previous date.
> 
> The datemain table is used just to give me all dates of the year, just a 
> reference to link the other 2.
> 
> Thanks in advance for your help.
> 
> Cheers, tiago.
> 
> 
> "Lance" wrote:
> 
> > OK.. more questions.
> > 
> > What output are you looking for?   The latest entries in both tables?
> > And what are you using the datemain table for?
> > 
> > "Tiago" wrote:
> > 
> > > I have one table called DateMAIN with a date field that relates to:
> > > 
> > > datetopay at "topay_query";
> > > datetoreceive at "toreceive_query".
> > > 
> > > And I have no criterias, so I have one line for each date that's why 
> > > sometimes they are blank.
> > > 
> > > Tiago.
> > > 
> > > "Lance" wrote:
> > > 
> > > > How are your queries set up?  Are you linking by day?  Is one of your 
> > > > criteria to only show the current day?
> > > > 
> > > > "Tiago" wrote:
> > > > 
> > > > > Hello guys!
> > > > > 
> > > > > Hope someone can help as usual!
> > > > > I have designed a simple query to control my cashflow, pulling data from a 
> > > > > topay_query and toreceive_query. I have managed to make all the calculations, 
> > > > > no problem.
> > > > > The thing is that I have an "Accumulated payed" and "Accumulated received". 
> > > > > When there's no moviment in a day these fields come out blank. How can I do 
> > > > > that when there's no movement, the query would consider always the value of 
> > > > > the previous valid field?
> > > > > 
> > > > > THanks mate!
0
Utf
11/27/2007 9:09:02 PM
One issue I just remembered.. the value in the function continues running 
through multiple queries.  You might want something like the following 
instead.

Function running_sum(inPay As Long, inRec As Long, Optional reset As Boolean)
   Static vPay As Long
   Static vRec As Long
   
   If reset Then
      vPay = 0
      vRec = 0
   End If
   
   vPay = vPay + inPay
   vRec = vRec + inRec
   running_sum = vRec - vPay
End Function


Do something like this whenever you need to reset the values.
Sub reset_sum()
   Call running_sum(0, 0, True)
End Sub


"Lance" wrote:

> OK, first we address the null data from missing days.  We do this by 
> appending a 0 to the front of your revenue field and then getting the value 
> of that.  Like this: Val("0" & [value_pay]).  Null field become 0, and fields 
> with values in them remain the same since leading 0's in a numeric field are 
> ignored.
> 
> Next we address your running sum.  To use a running sum in a query requires 
> a function.  Such as:
> 
> Function running_sum(inPay As Long, inRec As Long)
>    Static vPay As Long
>    Static vRec As Long
>    
>    vPay = vPay + inPay
>    vRec = vRec + inRec
>    running_sum = vRec - vPay
> End Function
> 
> Now put together your query.  Link both $$ tables to datemain and include 
> all records from datemain and only records that match from the other ( AKA 
> arrow pointing from datemain to each of your other two tables ).  Include the 
> date from datemain, the adjusted pay from step #1, the adjusted received from 
> step #1, and a function call ( being passed the adjusted pay and received ) 
> to running_sum.
> 
> You should have what you need.
> 
> "Tiago" wrote:
> 
> > Hi Lance,
> > 
> > I'm looking for something like this:
> > 
> >  date         Revenue     Cost      Balance
> > 01/nov -        50.0       20.00       30.00
> > 02/nov -        00.0       00.00       30.00 NO MOVEMENT 
> > 03/nov -        10.0       20.00       20.00
> > 04/nov -        00.0       30.00      -10.00
> > 05/nov -        00.0       00.00      -10.00 NO MOVEMENT
> > 
> > Note that the 02/nov and 05/nov there wasn't any revenue or cost, so the 
> > balance was equal to the one of the previous date.
> > 
> > The datemain table is used just to give me all dates of the year, just a 
> > reference to link the other 2.
> > 
> > Thanks in advance for your help.
> > 
> > Cheers, tiago.
> > 
> > 
> > "Lance" wrote:
> > 
> > > OK.. more questions.
> > > 
> > > What output are you looking for?   The latest entries in both tables?
> > > And what are you using the datemain table for?
> > > 
> > > "Tiago" wrote:
> > > 
> > > > I have one table called DateMAIN with a date field that relates to:
> > > > 
> > > > datetopay at "topay_query";
> > > > datetoreceive at "toreceive_query".
> > > > 
> > > > And I have no criterias, so I have one line for each date that's why 
> > > > sometimes they are blank.
> > > > 
> > > > Tiago.
> > > > 
> > > > "Lance" wrote:
> > > > 
> > > > > How are your queries set up?  Are you linking by day?  Is one of your 
> > > > > criteria to only show the current day?
> > > > > 
> > > > > "Tiago" wrote:
> > > > > 
> > > > > > Hello guys!
> > > > > > 
> > > > > > Hope someone can help as usual!
> > > > > > I have designed a simple query to control my cashflow, pulling data from a 
> > > > > > topay_query and toreceive_query. I have managed to make all the calculations, 
> > > > > > no problem.
> > > > > > The thing is that I have an "Accumulated payed" and "Accumulated received". 
> > > > > > When there's no moviment in a day these fields come out blank. How can I do 
> > > > > > that when there's no movement, the query would consider always the value of 
> > > > > > the previous valid field?
> > > > > > 
> > > > > > THanks mate!
0
Utf
11/27/2007 9:15:03 PM
Thanks Lance, let's see how it goes!

"Lance" wrote:

> One issue I just remembered.. the value in the function continues running 
> through multiple queries.  You might want something like the following 
> instead.
> 
> Function running_sum(inPay As Long, inRec As Long, Optional reset As Boolean)
>    Static vPay As Long
>    Static vRec As Long
>    
>    If reset Then
>       vPay = 0
>       vRec = 0
>    End If
>    
>    vPay = vPay + inPay
>    vRec = vRec + inRec
>    running_sum = vRec - vPay
> End Function
> 
> 
> Do something like this whenever you need to reset the values.
> Sub reset_sum()
>    Call running_sum(0, 0, True)
> End Sub
> 
> 
> "Lance" wrote:
> 
> > OK, first we address the null data from missing days.  We do this by 
> > appending a 0 to the front of your revenue field and then getting the value 
> > of that.  Like this: Val("0" & [value_pay]).  Null field become 0, and fields 
> > with values in them remain the same since leading 0's in a numeric field are 
> > ignored.
> > 
> > Next we address your running sum.  To use a running sum in a query requires 
> > a function.  Such as:
> > 
> > Function running_sum(inPay As Long, inRec As Long)
> >    Static vPay As Long
> >    Static vRec As Long
> >    
> >    vPay = vPay + inPay
> >    vRec = vRec + inRec
> >    running_sum = vRec - vPay
> > End Function
> > 
> > Now put together your query.  Link both $$ tables to datemain and include 
> > all records from datemain and only records that match from the other ( AKA 
> > arrow pointing from datemain to each of your other two tables ).  Include the 
> > date from datemain, the adjusted pay from step #1, the adjusted received from 
> > step #1, and a function call ( being passed the adjusted pay and received ) 
> > to running_sum.
> > 
> > You should have what you need.
> > 
> > "Tiago" wrote:
> > 
> > > Hi Lance,
> > > 
> > > I'm looking for something like this:
> > > 
> > >  date         Revenue     Cost      Balance
> > > 01/nov -        50.0       20.00       30.00
> > > 02/nov -        00.0       00.00       30.00 NO MOVEMENT 
> > > 03/nov -        10.0       20.00       20.00
> > > 04/nov -        00.0       30.00      -10.00
> > > 05/nov -        00.0       00.00      -10.00 NO MOVEMENT
> > > 
> > > Note that the 02/nov and 05/nov there wasn't any revenue or cost, so the 
> > > balance was equal to the one of the previous date.
> > > 
> > > The datemain table is used just to give me all dates of the year, just a 
> > > reference to link the other 2.
> > > 
> > > Thanks in advance for your help.
> > > 
> > > Cheers, tiago.
> > > 
> > > 
> > > "Lance" wrote:
> > > 
> > > > OK.. more questions.
> > > > 
> > > > What output are you looking for?   The latest entries in both tables?
> > > > And what are you using the datemain table for?
> > > > 
> > > > "Tiago" wrote:
> > > > 
> > > > > I have one table called DateMAIN with a date field that relates to:
> > > > > 
> > > > > datetopay at "topay_query";
> > > > > datetoreceive at "toreceive_query".
> > > > > 
> > > > > And I have no criterias, so I have one line for each date that's why 
> > > > > sometimes they are blank.
> > > > > 
> > > > > Tiago.
> > > > > 
> > > > > "Lance" wrote:
> > > > > 
> > > > > > How are your queries set up?  Are you linking by day?  Is one of your 
> > > > > > criteria to only show the current day?
> > > > > > 
> > > > > > "Tiago" wrote:
> > > > > > 
> > > > > > > Hello guys!
> > > > > > > 
> > > > > > > Hope someone can help as usual!
> > > > > > > I have designed a simple query to control my cashflow, pulling data from a 
> > > > > > > topay_query and toreceive_query. I have managed to make all the calculations, 
> > > > > > > no problem.
> > > > > > > The thing is that I have an "Accumulated payed" and "Accumulated received". 
> > > > > > > When there's no moviment in a day these fields come out blank. How can I do 
> > > > > > > that when there's no movement, the query would consider always the value of 
> > > > > > > the previous valid field?
> > > > > > > 
> > > > > > > THanks mate!
0
Utf
11/28/2007 3:20:02 PM
Reply:

Similar Artilces:

Build Dynamic Query from Form
I am trying to build a dynamic Query from a Form. I keep getting an error that reads ‘Object qryFilter already exists’ I suspect it has something to do with the string of dates being passed to the Query; strDateCondition = "([Trades].[TDATE] Between [Forms]![SearchForm]![cboFrom] And [Forms]![SearchForm]![cboTo])" I am trying to add a means for a user to Query by Customer and Trader AND all records between two dates. This was working fine for Customer and Trader; when I added in the code to filter by dates I started having problems. I know the SQL will be li...

Date format issue when submitting from a userform to a spreadsheet
Hi, I have a userform that I've generated which routes dates onto a spreadsheet based on the users input. I am having a bit of a frustrating time with the dates, it would appear that in the process of moving the date from the userform to the spreadsheet some dates are switched/transposed. I'll give an example. If someone enters 09/02/2004 on the userform excel seems to look at 09 and assume it is a month and transposes the dates to 02/09/2004(this does not appear to be a US/UK format issues as I have already gone down that road). However, if the date 13/09/2004 is entered i...

How to get paper size from dmPaperSize returned from GetDevMode?
For example, dmPaperSize returns 1 which is DMPAPER_LETTER, how do I calculate the paper size like in this case 8.5"x11"? There are so many paper sizes, is there any function call to calculate based on the return value of dmPaperSize or mapping between the value of dmPapersize and actual size? Thank you. The reason I asked the question was because dmPaperLength and dmPaperWidth are 0 for some printers. Also, my HP laserjet returns as a color printer (dmColor = 2) from GetDevMode call. Anyone knows why? ...

Using Sumproduct when some of the values are null
I am having a problem using Sumproduct when some of the cells have null values. I will try to describe the problem. Please let me know if you need additional information. Do any of you have any ideas of how I can fix that sumproduct to get it to work? A B C Row Trend Claims 5 Emergency $0 6 Emergency $0 7 Emergency $0 8 Emergency 81.68% $24,444 9 Emergency 35.00% $164,758 10 Emergency 35.00% ...

Keyboard shortcut for current date and time
Hi, Ctrl+ ; inserts current date and Ctrl+Shift+; inserts current ti me Ctrl+Shift+; inserts the current time with the date serial as 0 and not the current date's date serial. Presently I am adding the two (ie current date and time) to get the current date and time. Is there a keyboard shortcut that does this? Thanks in advance. Regards, Raj CTRL+; then SPACE then CTRL+SHIFT+; -or- =Now() -- Jim Cone Portland, Oregon USA http://www.mediafire.com/PrimitiveSoftware "Raj" <rspai9@gmail.com> wrote in message Hi, Ctrl+ ; inserts current dat...

please help with this query
Ost Ocity Dstate Dcity Carrier Price Rank Diff A B C D X 1200 1 100 A B C D Y 1300 2 100 A B C D Z 1350 3 100 A B C D W 1789 4 100 A1 B1 C1 D1 X1 785 1 A1 B1 C1 D1 Y1 789 2 The rank for every carrier is based on the price . If rank1 carrier is not a pariticular carrier(say if it is not X1 or Y1 or Z1), then i want to calculate the difference be...

Contact looses Post Office Box field when synchronized to Outlook
When I sync contacts from CRM 4.0 to Outlook, the Post Office Box field from the contact gets empty. Anyone has the same issue? ...

using dates Part 2
Karl was great in helping me get to this point with dates, now I'm wondering if we can take it 1 step further? For Activity Dates prior to 2/1/2007 they are using a normal reporting year and the formulas below take care of Activity dates >2/1/2007? So for example prior to 2/1/2007 1/1/2006 would have a B_Qtr of 2006-1 1) B_Qtr - 2011-1 --- Format(DateAdd("m",11,[ActivityDate]), "yyyy - q") 2) Year - 2011 ---- Year(DateAdd("m",11,[ActivityDate])) 3) Qtr - Q1 ---- Format(DateAdd("m",11,[ActivityDate]), "q") -- Than...

Update Query
Access 2003 XP SP2 I am having a problem with an update query. Table is in a one-to-one relationship, referentail integrity and cascading data are checked. (The fileds I want to update are not in both tables) Table name= payForward Has 15 fields ie: ID, Name, MemID, Oct , Nov, Dec, etc Oct-Sep fields are yes/no type I want to "select" a field (Oct-Sep) via a query parameter and repalce "yes" with "no". Here is my query: UPDATE payForward SET [Enter month]=No The messages I get is 'operation must use an updateable query' Wh...

makro to return email
I want a makro that if a check box is checked, a email is send to a recipient. See your other mail and http://www.rondebruin.nl/sendmail.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Esrei" <anonymous@discussions.microsoft.com> wrote in message news:174801c50514$05025190$a401280a@phx.gbl... >I want a makro that if a check box is checked, a email is > send to a recipient. ...

sent and received fields
I've just switched from Outlook Express to Outlook 2000. In one of my folders, I have sent and received emails that I want to sort together based on the sent or received date, but all my sent emails sort together because the received field has no value. And the same for the received emails. Is there a way to combine these fields or tell Outlook to sort based on both of them? Do I need to write a VBA macro to accomplish this? Thanks, Michael ...

Option for fields to show up in datasheet view
Hello, Since most people are more familiar with excel then access, my users prefer to see it in datasheet view. However, since it could be too long, anyone can help me how to make an option to show up only field that we can chose in the form. I remember there is a sample of database that has that one, but I do not know where is it now. Please help. Thanks in advance, Frank -- Frank Situmorang Simplest solution might be to set the Visible property of the controls on the form, based on the fields the user wants to see. -- Allen Browne - Microsoft MVP. Perth, West...

Making a template that puts the current date in the document so that does NOT change
I'd like to make a template that sets up some standard headers and formatting for new Word documents for a night school course I'm enrolled in. Among other requirements for all papers is to put the date the paper was created at the top. I'd furthermore like it so that if I need to reopen the document after creating it to say print another copy, the date written at the top will not change. In other words, when I create a new document using the template the current date is put near the top, but when I subsequently open the file for editing or reprinting it does NOT automat...

Null date parameter
Hi How can I pass a null value to a date parameter in a Sub? Thanks Regards hi John, John wrote: > How can I pass a null value to a date parameter in a Sub? you have to pass it as Varian as normal data types cannot be Null: Public Sub yourSub(ADateNullable As Variant) On Local Error GoTo LocalError Dim DateValue As Date If IsNull(ADateNullable) Then Else DateValue = CDate(ADateNullable) End IF Exit Sub LocalError: If Err.Number = 13 Then ' Type mismatch. End If End Sub mfG --> stefan <-- ...

auto fill in data when changing fields
i new to crm 4 and i would like to; once i selected the account i need it to grab the main phone and put it in the phone field, and could you tell me where i need to do this, thanks -- j.hardy you can modify the mappings of the corresponding relationship "moon" wrote: > i new to crm 4 and i would like to; once i selected the account i need it to > grab the main phone and put it in the phone field, and could you tell me > where i need to do this, thanks > -- > j.hardy ...

how to set up a query
I am using sql server 2005 express and have 3 tables Table1 Dept_Id (primary key) Dept_Name Table2 Employee_Id (primary key) Dept_Id (foreign key with table1) Employee_Name Table3 WorkSchedule_Id (primary key) Employee_Id (foreing key with table 2) Date_To_Work (date type) I want to list all the Departments (Dept_Name) that do not have anyone scheduled to work on a particular date (ie '1/20/2010' ) Any help would be appriciated. Thanks in advance, RABMissouri2010 Try this: SELECT dept_name FROM Table1 AS D WHERE NOT EXISTS(SELECT * ...

Excel comparative query
I'm respectfully requesting assistance with the correct formula to use for the following query: I'd searching all of column A on spreadsheet 1 to see if a value in column A of spreadsheet 2 is there, and placing a result X (or another) next to (adjacent cell) the confirmed value on spreadsheet 2. Please help if you can. Chris Hi Chris see your answer in puclic.excel -- Regards Frank Kabel Frankfurt, Germany Chris wrote: > I'm respectfully requesting assistance with the correct formula > to use for the following query: > > I'd searching all of column A on ...

Transaction dates a day early
Hello, Using M06 with direct connect & bill pay to my bank, the downloaded transaction dates entered are a day early in Money. If I view my account through my bank's web interface, the date would show as I'd expect (e.g. 11/21) for a given transaction. But when viewing my account in M06 (again directly connected/downloaded from bank), that same transaction has a date of 11/20. All transactions from this bank seem to "post" a day early in Money. I do let Money change the transaction date from what I entered to what the bank says (that's the way I want it). I a...

input date
hi all, is this possible: i will type 011005 and then excel will automatically format it as 01/10/05 and will be treated as date? thanks. Rufino Only with VBA code or a function in another cell, there is no way with the user interface of doing this automatically -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS "rufino palacol jr" <rufinojr54@gawab.com> wrote in message news:%23Ve5wjg%23EHA.3376@TK2MSFTNGP12.phx.gbl... > hi all, > > is this possible: i will type 011005 and then excel will automatically > fo...

Date Elimination
I have a worksheet with mainly dates in column A in the format of '25 Aug 2008'. Is it possible with a macro or similar to delete lines beyond a certain date (2 years hence)? Basically, I'm not interested in data more than 2 years old. This would eliminate a lot of data and make for a more viewable worksheet. try this Sub del_date() ActiveCell.Range("A1").Select Do Until ActiveCell.Value =3D "" dt =3D Date - 730 If ActiveCell.Value <=3D dt Then ActiveCell.ClearContents ActiveCell.Offset(1, 0).Select Else ActiveCell.Offset(1, 0).Select End If Loop End ...

Currency Format Lost in Union Query
Thanks to John Spencer, I resolved one problem in my union query (Thank you, John). But I have another problem. Some of my fields in my union query are currencies and others percents. I see both formats are lost. I am sure it is a union query problem, because when I ran an individual query, none of the formats were lost. Is there any way I can keep currency and percent formats without writing, FORMATCURRENCY, etc for each field? Thank you. What data types are these fields? The field in a UNION query will normally take on the data type of the field in the first SELECT. So, if ...

Getting all Sproc through one query
Hi all, can anyone help in getting below information from all stored procedure from one DB we have 100 procedure in one DB of sql server 2005.instead if of manully getting info for each procedure i want all below details in one shot or one query Procedure Name : Input Parameter : Output Parameter : Called By : Calls : Dependent tables : Thanks in advance ...

date format #9
Hi everyone Is there any way to add automatically "rd" or "st" or "th" etc after the date. I have a date in A1 as "30-01-2001" and I need to show in B1 as "January 30th" Hope someone has an idea Thanks Toms --- Message posted from http://www.ExcelForum.com/ Hi SMILE! One Way. Use: =DAY(A1)&IF(AND(MOD(DAY(A1),100)>=10,MOD(DAY(A1),100)<=14),"th",CHOOSE (MOD(DAY(A1),10)+1,"th","st","nd","rd","th","th","th","th","th","th")...

How to carry over date value from one record to the next.
I have searched this site and the internet in search of an answer to this problem but none of the solutions I've found have seemed to help. I have a form that is used for data entry. The first two text boxes are called Supervisor and W/E Date. These two values will remain the same for several dozen entries. I have tried just using CTRL + ' to repeat the data but that is getting quite time consuming as well. How do I automatically carry over the value to the next record once I input the first data in? Any help on this would be much appreciated. -- Message posted via AccessMo...

Date function
Hi, I have a date in a field 03/25/2006...I would like to change this date to only show day and month of date 03/25 . I think I can use the Day and Month functions to pull out the day and month from the date. Can someone help me with using one fuction to get day and month from this date field. Thanks for you help Natalie, You could just re-format your cells with the date in. Select the cells Open up the format cells dialog (will vary depending on your version of Excel) Select 'Custom' from the list of formats. In the 'Type' line, type in 'mm/dd' (without quo...