Tommorrow's Date Highligted

Trying to Conditional Format a text box to highlight tommorrows Date!

[tbAlertDate]=Now()+1

-- 
Thanks in advance for any help with this......Bob
MS Access 2007 accdb
Windows  XP Home Edition Ver 5.1 Service Pack 3


0
Bob
2/20/2010 8:58:57 PM
access 16762 articles. 3 followers. Follow

11 Replies
802 Views

Similar Articles

[PageSpeed] 22

Bob,

Now() returns date and time. If you only need the date use the Date() 
function.

Steve
santus@penn.com


"Bob Vance" <rjvance@ihug.co.nz> wrote in message 
news:%23kslB%23msKHA.4360@TK2MSFTNGP05.phx.gbl...
> Trying to Conditional Format a text box to highlight tommorrows Date!
>
> [tbAlertDate]=Now()+1
>
> -- 
> Thanks in advance for any help with this......Bob
> MS Access 2007 accdb
> Windows  XP Home Edition Ver 5.1 Service Pack 3
>
> 


0
Steve
2/20/2010 9:35:55 PM
On Sun, 21 Feb 2010 09:58:57 +1300, "Bob Vance" <rjvance@ihug.co.nz> wrote:

>Trying to Conditional Format a text box to highlight tommorrows Date!
>
>[tbAlertDate]=Now()+1

Now() does not return today's date. It returns the current time, accurate to
microseconds; unless tbAlertDate contained #02/20/2010 03:09:03PM#... oops the
time changed it doesn't match any more...!

Use Date() instead of Now().
-- 

             John W. Vinson [MVP]
0
John
2/20/2010 10:09:43 PM
Thanks by using Now() I will get the warning on todays date if the date is 
Feb21 and the day is feb21
[tbAlertDate]=Date()+1 am tring to get tommorrows date to condition but this 
is not working!
Also when I go to my form with the conditional Formatting the conditions 
dont work but if I close the form go back to Main page then go back to my 
form the conditions work! ..Regards Bob

"John W. Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message 
news:i9n0o5p5m7vsbmcfd9n7gl7hq3vh68ruk5@4ax.com...
> On Sun, 21 Feb 2010 09:58:57 +1300, "Bob Vance" <rjvance@ihug.co.nz> 
> wrote:
>
>>Trying to Conditional Format a text box to highlight tommorrows Date!
>>
>>[tbAlertDate]=Now()+1
>
> Now() does not return today's date. It returns the current time, accurate 
> to
> microseconds; unless tbAlertDate contained #02/20/2010 03:09:03PM#... oops 
> the
> time changed it doesn't match any more...!
>
> Use Date() instead of Now().
> -- 
>
>             John W. Vinson [MVP] 


0
Bob
2/20/2010 11:20:28 PM
Oops fix the Conditional Format by requering my subform :)
but still cant get tommorrows date to work..Thanks Bob

"Bob Vance" <rjvance@ihug.co.nz> wrote in message 
news:ucMhGNosKHA.732@TK2MSFTNGP06.phx.gbl...
> Thanks by using Now() I will get the warning on todays date if the date is 
> Feb21 and the day is feb21
> [tbAlertDate]=Date()+1 am tring to get tommorrows date to condition but 
> this is not working!
> Also when I go to my form with the conditional Formatting the conditions 
> dont work but if I close the form go back to Main page then go back to my 
> form the conditions work! ..Regards Bob
>
> "John W. Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message 
> news:i9n0o5p5m7vsbmcfd9n7gl7hq3vh68ruk5@4ax.com...
>> On Sun, 21 Feb 2010 09:58:57 +1300, "Bob Vance" <rjvance@ihug.co.nz> 
>> wrote:
>>
>>>Trying to Conditional Format a text box to highlight tommorrows Date!
>>>
>>>[tbAlertDate]=Now()+1
>>
>> Now() does not return today's date. It returns the current time, accurate 
>> to
>> microseconds; unless tbAlertDate contained #02/20/2010 03:09:03PM#... 
>> oops the
>> time changed it doesn't match any more...!
>>
>> Use Date() instead of Now().
>> -- 
>>
>>             John W. Vinson [MVP]
>
> 


0
Bob
2/20/2010 11:29:23 PM
Now() returns the current date and time according to the system clock. The 
date portion returns today's date but the time portion continually changes 
as the current time changes.

Steve


"John W. Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message 
news:i9n0o5p5m7vsbmcfd9n7gl7hq3vh68ruk5@4ax.com...
> On Sun, 21 Feb 2010 09:58:57 +1300, "Bob Vance" <rjvance@ihug.co.nz> 
> wrote:
>
>>Trying to Conditional Format a text box to highlight tommorrows Date!
>>
>>[tbAlertDate]=Now()+1
>
> Now() does not return today's date. It returns the current time, accurate 
> to
> microseconds; unless tbAlertDate contained #02/20/2010 03:09:03PM#... oops 
> the
> time changed it doesn't match any more...!
>
> Use Date() instead of Now().
> -- 
>
>             John W. Vinson [MVP] 


0
Steve
2/21/2010 12:15:12 AM
When you say  [tbAlertDate]=Date()+1 is not working, what do you mean? Where 
and when are you executing this expression? Date() + 1 returns a number.  Is 
[tbAlertDate] formatted as a number?

Steve

"Bob Vance" <rjvance@ihug.co.nz> wrote in message 
news:OYcZFSosKHA.6004@TK2MSFTNGP04.phx.gbl...
> Oops fix the Conditional Format by requering my subform :)
> but still cant get tommorrows date to work..Thanks Bob
>
> "Bob Vance" <rjvance@ihug.co.nz> wrote in message 
> news:ucMhGNosKHA.732@TK2MSFTNGP06.phx.gbl...
>> Thanks by using Now() I will get the warning on todays date if the date 
>> is Feb21 and the day is feb21
>> [tbAlertDate]=Date()+1 am tring to get tommorrows date to condition but 
>> this is not working!
>> Also when I go to my form with the conditional Formatting the conditions 
>> dont work but if I close the form go back to Main page then go back to my 
>> form the conditions work! ..Regards Bob
>>
>> "John W. Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message 
>> news:i9n0o5p5m7vsbmcfd9n7gl7hq3vh68ruk5@4ax.com...
>>> On Sun, 21 Feb 2010 09:58:57 +1300, "Bob Vance" <rjvance@ihug.co.nz> 
>>> wrote:
>>>
>>>>Trying to Conditional Format a text box to highlight tommorrows Date!
>>>>
>>>>[tbAlertDate]=Now()+1
>>>
>>> Now() does not return today's date. It returns the current time, 
>>> accurate to
>>> microseconds; unless tbAlertDate contained #02/20/2010 03:09:03PM#... 
>>> oops the
>>> time changed it doesn't match any more...!
>>>
>>> Use Date() instead of Now().
>>> -- 
>>>
>>>             John W. Vinson [MVP]
>>
>>
>
> 


0
Steve
2/21/2010 12:18:58 AM
Thanks Steve, tbAlertDate is a text box on my continous form that has a 
Date/Time field, I am trying to get tomorrows date background green, through 
Conditional format,,,,,,,,,Thanks Bob

"Steve" <notmyemail@address.com> wrote in message 
news:%23Zkb2tosKHA.5868@TK2MSFTNGP05.phx.gbl...
> When you say  [tbAlertDate]=Date()+1 is not working, what do you mean? 
> Where and when are you executing this expression? Date() + 1 returns a 
> number.  Is [tbAlertDate] formatted as a number?
>
> Steve
>
> "Bob Vance" <rjvance@ihug.co.nz> wrote in message 
> news:OYcZFSosKHA.6004@TK2MSFTNGP04.phx.gbl...
>> Oops fix the Conditional Format by requering my subform :)
>> but still cant get tommorrows date to work..Thanks Bob
>>
>> "Bob Vance" <rjvance@ihug.co.nz> wrote in message 
>> news:ucMhGNosKHA.732@TK2MSFTNGP06.phx.gbl...
>>> Thanks by using Now() I will get the warning on todays date if the date 
>>> is Feb21 and the day is feb21
>>> [tbAlertDate]=Date()+1 am tring to get tommorrows date to condition but 
>>> this is not working!
>>> Also when I go to my form with the conditional Formatting the conditions 
>>> dont work but if I close the form go back to Main page then go back to 
>>> my form the conditions work! ..Regards Bob
>>>
>>> "John W. Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message 
>>> news:i9n0o5p5m7vsbmcfd9n7gl7hq3vh68ruk5@4ax.com...
>>>> On Sun, 21 Feb 2010 09:58:57 +1300, "Bob Vance" <rjvance@ihug.co.nz> 
>>>> wrote:
>>>>
>>>>>Trying to Conditional Format a text box to highlight tommorrows Date!
>>>>>
>>>>>[tbAlertDate]=Now()+1
>>>>
>>>> Now() does not return today's date. It returns the current time, 
>>>> accurate to
>>>> microseconds; unless tbAlertDate contained #02/20/2010 03:09:03PM#... 
>>>> oops the
>>>> time changed it doesn't match any more...!
>>>>
>>>> Use Date() instead of Now().
>>>> -- 
>>>>
>>>>             John W. Vinson [MVP]
>>>
>>>
>>
>>
>
> 


0
Bob
2/21/2010 12:44:40 AM
Why are you restating what John Posted?

Are you trying to gain some credibility after another round of spamming the 
newsgroups witn another feable attempt to pimp your services?

John... Visio MVP
"Steve" <notmyemail@address.com> wrote in message 
news:%23xLwvrosKHA.4652@TK2MSFTNGP02.phx.gbl...
> Now() returns the current date and time according to the system clock. The 
> date portion returns today's date but the time portion continually changes 
> as the current time changes.
>
> Steve
>
>
> "John W. Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message 
> news:i9n0o5p5m7vsbmcfd9n7gl7hq3vh68ruk5@4ax.com...
>> On Sun, 21 Feb 2010 09:58:57 +1300, "Bob Vance" <rjvance@ihug.co.nz> 
>> wrote:
>>
>>>Trying to Conditional Format a text box to highlight tommorrows Date!
>>>
>>>[tbAlertDate]=Now()+1
>>
>> Now() does not return today's date. It returns the current time, accurate 
>> to
>> microseconds; unless tbAlertDate contained #02/20/2010 03:09:03PM#... 
>> oops the
>> time changed it doesn't match any more...!
>>
>> Use Date() instead of Now().
>> -- 
>>
>>             John W. Vinson [MVP]
>
>


0
John
2/21/2010 3:11:29 PM
Bob,

Date()+1 returms tomorrow's date. If you put the expression =Date()+1 in the 
control source of an unbound textbox, you will see tomorrow's date in the 
textbox. Please answer ALL the questions in my previous post so I can try to 
help you.

Steve


"Bob Vance" <rjvance@ihug.co.nz> wrote in message 
news:%23T$jJ8osKHA.4752@TK2MSFTNGP04.phx.gbl...
> Thanks Steve, tbAlertDate is a text box on my continous form that has a 
> Date/Time field, I am trying to get tomorrows date background green, 
> through Conditional format,,,,,,,,,Thanks Bob
>
> "Steve" <notmyemail@address.com> wrote in message 
> news:%23Zkb2tosKHA.5868@TK2MSFTNGP05.phx.gbl...
>> When you say  [tbAlertDate]=Date()+1 is not working, what do you mean? 
>> Where and when are you executing this expression? Date() + 1 returns a 
>> number.  Is [tbAlertDate] formatted as a number?
>>
>> Steve
>>
>> "Bob Vance" <rjvance@ihug.co.nz> wrote in message 
>> news:OYcZFSosKHA.6004@TK2MSFTNGP04.phx.gbl...
>>> Oops fix the Conditional Format by requering my subform :)
>>> but still cant get tommorrows date to work..Thanks Bob
>>>
>>> "Bob Vance" <rjvance@ihug.co.nz> wrote in message 
>>> news:ucMhGNosKHA.732@TK2MSFTNGP06.phx.gbl...
>>>> Thanks by using Now() I will get the warning on todays date if the date 
>>>> is Feb21 and the day is feb21
>>>> [tbAlertDate]=Date()+1 am tring to get tommorrows date to condition but 
>>>> this is not working!
>>>> Also when I go to my form with the conditional Formatting the 
>>>> conditions dont work but if I close the form go back to Main page then 
>>>> go back to my form the conditions work! ..Regards Bob
>>>>
>>>> "John W. Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message 
>>>> news:i9n0o5p5m7vsbmcfd9n7gl7hq3vh68ruk5@4ax.com...
>>>>> On Sun, 21 Feb 2010 09:58:57 +1300, "Bob Vance" <rjvance@ihug.co.nz> 
>>>>> wrote:
>>>>>
>>>>>>Trying to Conditional Format a text box to highlight tommorrows Date!
>>>>>>
>>>>>>[tbAlertDate]=Now()+1
>>>>>
>>>>> Now() does not return today's date. It returns the current time, 
>>>>> accurate to
>>>>> microseconds; unless tbAlertDate contained #02/20/2010 03:09:03PM#... 
>>>>> oops the
>>>>> time changed it doesn't match any more...!
>>>>>
>>>>> Use Date() instead of Now().
>>>>> -- 
>>>>>
>>>>>             John W. Vinson [MVP]
>>>>
>>>>
>>>
>>>
>>
>>
>
> 


0
Steve
2/21/2010 3:26:45 PM
Bob:

This should do it:

Expression is		DateValue([tbAlertDate])=DateAdd("d",1,Date())

This will cater for tbAlertDate containing a date with or without a non-zero
time of day element.  Unless you've taken specific steps in the table
definition to prevent it, i.e. by means of a ValidationRule, its all to easy
for non-zero times of day to creep into date/time values without you being
aware of it; the inappropriate use of the Now() function is the most common
culprit.

Ken Sheridan
Stafford, England

Bob Vance wrote:
>Thanks Steve, tbAlertDate is a text box on my continous form that has a 
>Date/Time field, I am trying to get tomorrows date background green, through 
>Conditional format,,,,,,,,,Thanks Bob
>
>> When you say  [tbAlertDate]=Date()+1 is not working, what do you mean? 
>> Where and when are you executing this expression? Date() + 1 returns a 
>[quoted text clipped - 24 lines]
>>>>>
>>>>> Use Date() instead of Now().

-- 
Message posted via http://www.accessmonster.com

0
KenSheridan
2/21/2010 4:24:01 PM
Thanks Ken worked brilliant :) and thanks Steve...Regards Bob

"KenSheridan via AccessMonster.com" <u51882@uwe> wrote in message 
news:a3f8c79cee5a1@uwe...
> Bob:
>
> This should do it:
>
> Expression is DateValue([tbAlertDate])=DateAdd("d",1,Date())
>
> This will cater for tbAlertDate containing a date with or without a 
> non-zero
> time of day element.  Unless you've taken specific steps in the table
> definition to prevent it, i.e. by means of a ValidationRule, its all to 
> easy
> for non-zero times of day to creep into date/time values without you being
> aware of it; the inappropriate use of the Now() function is the most 
> common
> culprit.
>
> Ken Sheridan
> Stafford, England
>
> Bob Vance wrote:
>>Thanks Steve, tbAlertDate is a text box on my continous form that has a
>>Date/Time field, I am trying to get tomorrows date background green, 
>>through
>>Conditional format,,,,,,,,,Thanks Bob
>>
>>> When you say  [tbAlertDate]=Date()+1 is not working, what do you mean?
>>> Where and when are you executing this expression? Date() + 1 returns a
>>[quoted text clipped - 24 lines]
>>>>>>
>>>>>> Use Date() instead of Now().
>
> -- 
> Message posted via http://www.accessmonster.com
> 


0
Bob
2/21/2010 5:23:39 PM
Reply:

Similar Artilces:

Auto populate date in a form
I have a form with a textbox that I need to input today's date each time someone goes into the textbox. This textbox is tied to a date field in my table. Can someone please give me suggestions on this. Thanks. Hi Chris J. put the following sub in the gotfocus event of the form the textbox is on, also change tbxName to the name of your textbox Private Sub dates_GotFocus() tbxName.Value = Date End Sub Chris J. wrote: >I have a form with a textbox that I need to input today's date each time >someone goes into the textbox. This textbox is tied to a date field in...

How format SUMIF formula for matching date range and column value?
Here is my current SUMIF formula. How would I modify this to add the selection of a date range from the same data columns? Example, start date of 2/1 and end date of 2/8. =SUMIF(Jan!$F$9:$F$55202,"groceries",Jan!$D$9:$D$55202) The answer depends on which version of Excel you are using. For Excel 2007, you can use Sumifs. For other versions, it's Sumproduct, as in: =Sumproduct(--(Jan!$F$9:$F$55202="groceries"),--(Jan!$G$9:$G$55202>=Date(2010,2,1)),--(Jan!$G$9:$G$55202<=Date(2010,2,8)),Jan!$D$9:$D$55202) As an aside, my bet is that using a Piv...

MS Query and date format
Hi I have a csv file that I try to link with Excel spreadsheet using MS Query. I have one issue: the date format in MS Query is incorrect. MS Query imports a column as YMD when it should be DMY. How can I change this? Kind regards IgorM hi MS Query is good but sometimes you may have to do a tad bit of clean up after the refresh. how are you refreshing now? all of my MSQ's refreshed from a command button on the sheet. if i had your problem i might do something like this...... Sub RefreshData() Sheets("Sheet1").range("A1").Refresh Backgroundquer...

Another Date Related Formula Question
I just can't seem to figure out which formula to use to make this work. I've got invoice dates in column D (D4:D233) and Invoice Amounts in column E (E4:E233). I need to do two things: count the number of invoices in each month, and sum the invoices for each month. I was able to get the count formula correct, as an example for January: =SUMPRODUCT((MONTH($D$4:$D$223)=1)*1) But can not figure out how to SUM column E (invoice amounts) for each given month. Your assistance is appreciated. =SUMPRODUCT(--(MONTH($D$4:$D$223)=1),$E$4:$E$233) -- HTH Bob Phillips (remove nother...

Looking up a date and also a word for a sum function
I have a range of cells in sheet 1 that has a place to input dates in cells A1:A50. In cells B2:B50 there is a list box with options to choose from and in cells C1:C50 there is a place to put the number of items received. On sheet 2 I have in cells A2:A31 all the dates within the month and in row B1:G1 there are the same options that are in the list box on sheet 1 as column headings. In cell B2 on sheet 2 I would like a formula that will sum all of the items received from C1:C50 if the date and option from the list box match the date and column heading on sheet2. Thanks for the h...

Simple question: How do I sort a column by date???
I have a column that I have defined as "Date" format and all dates are in the mm/dd/yyyy format. But, when I sort the column it sorts it like text; e.g., 01/01/2003, 01/01/2004,02/01/2003, etc. How do I tell Excel to sort the column in date order, not "text" order? TIA, Larry Woods Probably your dates are text, rather than "real" Excel dates. Just to check, format one date as general. If it still shows the date, than it's text. A date would have shown a number, somewhere around 3800 (today is day 38001 since 1-1-1900) -- Kind Regards, Niek Otten Mi...

Convert Date to Text?
Hi. Is there a way to convert a date value to text? For instance, in cell A1 I have the date 6/24/04 in the cell. If I change the format of the cell to "Text" the number 38162 appears. I would actually like to covert this to be 6/24 but as text, not as a date ... Thanks, Mike. Hi Excel dates and times are stored as numbers (as you now know!). One way to get what you want is with a helper column alongside your existing data. In this, type: =TEXT(A2,"mm/dd") This should give you what you want. You can AutoFill this down. To fix these values (so that they don't...

Formula to change number into date
I am trying to insert a VBA function that requires a date to run. My dates are being pulled into a data sheet in the 20050301 format. I there a formula that I can insert that will change it into a date tha excel will recognize? Thanks, An -- anjgos ----------------------------------------------------------------------- anjgoss's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2660 View this thread: http://www.excelforum.com/showthread.php?threadid=47111 Hi there - I think this is the same problem I had a while ago. THi solves it: This is the formula which ref...

Calculated dates
I am running a query that subtracts a number in a table against a date in the same table. Plant Date: (([Sales Date])-([Grow Weeks]*7)) The answer is correct but when I run a critiera of Between[1st date]and[2nd date] I don't get the filter information desired. JV On Tue, 19 Feb 2008 21:38:49 -0800, JV <JV@discussions.microsoft.com> wrote: >I am running a query that subtracts a number in a table against a date in the >same table. Plant Date: (([Sales Date])-([Grow Weeks]*7)) The answer is >correct but when I run a critiera of Between[1st date]and[2nd date] I don'...

Calcualte date and time
Hi Please help me how to calculate the number of hours and minutes Between two columns: Ex: Beginning Apt. Ending Apt. 2/1/10 9:40am and 2/1/10 2:00pm. It will be 4 hours and 20 minutes or (4:20) if I calculate manually. Please help me to write the formula. Thanks Chi If your beginning time is in A1, and ending time in A2, the formula is: =a2-a1 Format as a time. Regards, Fred "Chi" <Chi@discussions.microsoft.com> wrote in message news:44CD6F0D-F59D-4FAF-B331-F45FFC2EBF41@microsoft.com... > Hi > > Please help me h...

How to add a pop up calendar to date field in a subform?
Greetings forum members, It would be greatly appreciated if someone could advise me how to add a pop up calendar to a date field in a subform. Basically, what I want to happen (...or something close to this) is: 1. The user clicks inside the subform field, 2. A pop up calendar opens, 3. The user selects a date, 4. The user selected date is entered into the subform field, and 5. The pop up calendar closes. Adding to the complexity, my subform is in (and must remain in) datasheet view. I am attempting to use the following code, from KB article #190194. This code works fine for fields on ...

No dates/times in Outlook emails or calendar
I have neither received/sent datestamps showing in any views, nor do any of the dates show in my calendar. However, if I access my email over the internet, all the dates/times are displayed. I already tried starting Outlook with /cleanviews, but that didn't fix the problem. I started with Outlook 2000, but re-installed my software, even upgrading to Outlook 2002, but the problem has persisted from the original 2000 to the new version! ...

Showing last editing date
I was wondering if there is a way to have one cell show the date of when another cell was last edited. You can use a change event. Assume you want cell B1 to track when last A1 was changed. Right click sheet tab, select View Code and paste the macro below: Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ErrorHandler Application.EnableEvents = False If Target.Address = "$A$1" Then Range("B1").Value = Date End If ErrorHandler: Application.EnableEvents = True End Sub Hope this helps Rowan "Kenny" wrote: > I was ...

day/date/year all together
can I enter a day into my date & year and then drag to auto fill if so how? Not sure what you want, but how about formatting the date as Custom dddd, mmmm yyyy Gord Dibben MS Excel MVP On Mon, 3 Jul 2006 16:08:01 -0700, Dumber than a pocket full of rocks <carlritcey@bendbroaband.com> wrote: >can I enter a day into my date & year and then drag to auto fill if so how? Hi, enter the date 04/Jul/2006 for eg click on the little black squre on the right down of the selected cell end drag to auto fill hth regards from Brazil Marcelo "Dumber than a pocket full of ...

Filter Date Field by month using VBA
I have a list with transactions. The first field is Date (maybe that's not a good name) and I want to filter the records by month. I tried this Mon = Val(InputBox("Which month")) Selection.AutoFilter Field:=1 Criteria:= "Month(Field 1) = Mon" but this gives a syntax error as do several variations which I have tried, although Month(Date) is apparently a VBA function. Can anyone suggest the right syntax ? Stuart I don't think you can do that with autofilter. You'll have to use an advanced filter with a critaria range HTH -- AP "Stuart Grant" ...

grouping date in pivot table
I have a pivot table containing dates, products and amount. I have right clicked the date field to group the dates bi-weekly since the only dates in the column are 1/15/2009,1/31/2009,2/15/2009,2/28/2009..etc YET i receive an error that states "cannot group that selection".....i ensured it is in date format...what else could cause this??? Hi If there are any empty cells in the range of dates, then Excel will not group them. Perhaps you have defined a range for your PT which included blank rows to allow for future data input. That would cause it not to group. Instead, define ...

Undefined Function DATE
Access 2003 On 3 PCs the query works, on two PCs it gives the error message Undefined function DATE when I use Date() as a query Criteria Any ideas? Thank you. On Fri, 16 Nov 2007 17:12:15 -0500, Brendan on Comcast.net wrote: > Access 2003 > On 3 PCs the query works, on two PCs it gives the error message > Undefined function DATE > when I use Date() as a query Criteria > Any ideas? > Thank you. Those PC's have a missing reference. Open any module in Design view (or click Ctrl + G). On the Tools menu, click References. Click to clear the check box for the typ...

Date Calculations
I have to create a formula in excel that takes a date (4-1-2010) and subtracts another date from it (2-1-2008) and gives me the remainder of months. Any ideas? How do you define a month difference? 30 days? Calendar months? What about months with different numbers of days. E.g., how many months between 28-Feb and 31-March. 1? 1+3/30? You need to define how the months should be calculated. At its simplest, just subtract one date from the other and divide by 30. That will give one of several possible answers. Cordially, Chip Pearson Microsoft Most Valuable Professional, Excel,...

Date Changes to General
We have worksheets with dates in them. They are formatted at Month and year (Nov-09 mmm-yy for 11/16/2009. Sometimes when I open the sheets the formatting is lost and the dates are show as general numbers 40134 in this case. Any idea why this happends? We share workbooks in the office - could it be that custom formats are not stored witth the file? any ideas of how to fix this annoying problem would be helpful. Thanks Formatting is stored with the file. Could it be that someone is inadvertently changing the format of those cells? HTH Otto "Michael" <mfgtcb@gma...

date display, ie; 5th of the month, quit showing the "small" th.
My documents quit showing/printing the small "th" and "nd" after a numerical date; it shows/prints 25th and not the 25 with the small "th". What did I do and how can I get it to display the small letters again. Thanks In the AutoCorrect dialog box, click the AutoFormat As You Type tab; under "Replace as you type," make sure to select "Ordinals (1st) with superscript." To display the dialog box, do the following: - Click Tools | AutoCorrect Options. (Word 2003) - Click the Office button, and then click Word Options. In the Pr...

Return all dates for 'next month' irrespective of year or day
Hello, Sorry to ask such a simple question; I would like my query to return all rows were the my 'date' field is any day in the next month. The year & day of month are not relevant. I have been getting stuck using (DatePart("m",Date())+1), this works fine except on December at which point the expression returns 13, and not 1, and therefore does not find the entries with a date in Jan. I also need to a similar sets of data where my date field is (DatePart("m",Date())-1) or ...-3 but again where the months cross over Dec/Jan this breaks down.. ...

Sales recorded in future dates.
In January the RMS customer somehow changed the system date to February. He did not notice that sales in January were recorded in Feb. In February he calls me, the RMS Partner, telling me the system is recording the sales incorrectly and he wants me or Microsoft to give him a new computer. (In a very unpleasant tone) Yelling at me. So I get the proof of what happened. See Microsoft Dynamics Support Incident Number 8810939 : Sales recorded in future dates. I had a thought about this that I want to share with you. I sell a software package, Garagekeeper 2000; it is a DOS based progr...

Subquery with Max(date)
I have a result set that I need to filter down to the last date something was done. The problem is that I can't seem to use the derived table in the From clause of my Subquery. The Query looks something like: SELECT sFirstname, sLastName, nProductID, VisitDate, dVisitDate FROM ( SELECT P.sFirstName, P.sLastName, PE.nProductID, CONVERT(varchar,PE.dVisitDate,101), PE.dVisitDate From Person P Join PersonEvent PE on P.PersonID = PE.PersonID WHERE ... ) AS A WHERE dStart = (Select Max(dStart) fro...

Month Year Date Format
I need help! I have been working with ASP.NET and .NET win forms, and have not worked in Access for awhile, so Access coding is very rusty. I have a access 2003 db that someone else created, that needs a quick fix. There is a a main form and tabs with subforms. There are date fields on the subforms. On one of the date fields it looks like they have it storing just the month and year (no day) in the table. On the subform, from what I can tell someone changed from a textbox to a combobox. I need to change it back to a textbox with input mask. I have the input mask as 99/0000;0;_, but...

Advanced Search Criteria
I am attempting to create a Search Folder that will show all e-mails received over 45 days ago. I am having a devil of a time trying to figure out how to specify this in the Advanced Search Criteria. If I put the field 'Sent' with condition 'on or before' and the value of '8/3/2007' it returns the correct list of messages. Since 8/3/2007 will only be valid on the date 9/17/2007 I was trying to put something in the value field that would be dynamic. I have tried 'now-45', 'today-45','dateadd("d",-45,now', and 'dateadd("d&qu...