Now()

**Anthony England** · Jan 11 '06, 12:05 AM

Re: Now()

"Bob Wickham" <wickham_bob@ya hoo.com.au> wrote in message
news:43c444d2@d news.tpgi.com.a u...[color=blue]
> Hi,
>
> I have some sql in a sub that finds dates in a table but only if the date
> is less than today
>
> WHERE [MonthYear] < Now()
>
> I want it to do that but to not include the current month.
>
> For instance, a list of dates in dd/mm/yy format
> 1/01/05
> 01/02/05
> etc
> etc
> 01/11/05
> 01/12/05
> 01/01/06
> 01/02/06
>
> If I run this today (11/01/06) 01/01/06 is included in the result because
> it is less than today.
> But I dont want the current month included.
>
> Any ideas.
>
> Bob[/color]

So you want to include dates less than the first of the current month?

You can use the DateSerial function:

....WHERE MyDate<DateSeri al(Year(Date),M onth(Date),1)

I'm assuming that your field [MonthYear] is an actual DateTime field

**Arno R** · Jan 11 '06, 12:05 AM

Re: Now()

"Bob Wickham" <wickham_bob@ya hoo.com.au> schreef in bericht news:43c444d2@d news.tpgi.com.a u...[color=blue]
> Hi,
>
> I have some sql in a sub that finds dates in a table but only if the
> date is less than today
>
> WHERE [MonthYear] < Now()
>
> I want it to do that but to not include the current month.
>
> For instance, a list of dates in dd/mm/yy format
> 1/01/05
> 01/02/05
> etc
> etc
> 01/11/05
> 01/12/05
> 01/01/06
> 01/02/06
>
> If I run this today (11/01/06) 01/01/06 is included in the result
> because it is less than today.
> But I dont want the current month included.
>
> Any ideas.
>
> Bob[/color]

So you want to select all dates less than the first day of the current month?

WHERE DateField < DateSerial(Year (Now), Month(Now), 1)

Arno R

**Anthony England** · Jan 11 '06, 12:05 AM

Re: Now()

"Arno R" <arraNOcomSPAM@ tiscali.nl> wrote in message
news:43c4486c$0 $10090$ba620dc5 @text.nova.plan et.nl...
"Bob Wickham" <wickham_bob@ya hoo.com.au> schreef in bericht
news:43c444d2@d news.tpgi.com.a u...[color=blue]
> Hi,
>
> I have some sql in a sub that finds dates in a table but only if the
> date is less than today
>
> WHERE [MonthYear] < Now()
>
> I want it to do that but to not include the current month.
>
> For instance, a list of dates in dd/mm/yy format
> 1/01/05
> 01/02/05
> etc
> etc
> 01/11/05
> 01/12/05
> 01/01/06
> 01/02/06
>
> If I run this today (11/01/06) 01/01/06 is included in the result
> because it is less than today.
> But I dont want the current month included.
>
> Any ideas.
>
> Bob[/color]

So you want to select all dates less than the first day of the current
month?

WHERE DateField < DateSerial(Year (Now), Month(Now), 1)

Arno R

Hey! Are you copying my answers?

**Bob Wickham** · Jan 11 '06, 12:15 AM

Re: Now()

Arno R wrote:[color=blue]
> "Bob Wickham" <wickham_bob@ya hoo.com.au> schreef in bericht news:43c444d2@d news.tpgi.com.a u...
>[color=green]
>>Hi,
>>
>>I have some sql in a sub that finds dates in a table but only if the
>>date is less than today
>>
>>WHERE [MonthYear] < Now()
>>
>>I want it to do that but to not include the current month.
>>
>>For instance, a list of dates in dd/mm/yy format
>>1/01/05
>>01/02/05
>>etc
>>etc
>>01/11/05
>>01/12/05
>>01/01/06
>>01/02/06
>>
>>If I run this today (11/01/06) 01/01/06 is included in the result
>>because it is less than today.
>>But I dont want the current month included.
>>
>>Any ideas.
>>
>>Bob[/color]
>
>
> So you want to select all dates less than the first day of the current month?
>
> WHERE DateField < DateSerial(Year (Now), Month(Now), 1)
>
> Arno R[/color]

Thats it, perfect.

Thankyou to you both.

Bob

**Randy Harris** · Jan 11 '06, 12:35 AM

Re: Now()

"Anthony England" <aengland@oops. co.uk> wrote in message
news:dq1hgc$bd4 $1@nwrdmz03.dmz .ncs.ea.ibs-infra.bt.com...[color=blue]
>
> "Arno R" <arraNOcomSPAM@ tiscali.nl> wrote in message
> news:43c4486c$0 $10090$ba620dc5 @text.nova.plan et.nl...
> "Bob Wickham" <wickham_bob@ya hoo.com.au> schreef in bericht
> news:43c444d2@d news.tpgi.com.a u...[color=green]
> > Hi,
> >
> > I have some sql in a sub that finds dates in a table but only if the
> > date is less than today
> >
> > WHERE [MonthYear] < Now()
> >
> > I want it to do that but to not include the current month.
> >
> > For instance, a list of dates in dd/mm/yy format
> > 1/01/05
> > 01/02/05
> > etc
> > etc
> > 01/11/05
> > 01/12/05
> > 01/01/06
> > 01/02/06
> >
> > If I run this today (11/01/06) 01/01/06 is included in the result
> > because it is less than today.
> > But I dont want the current month included.
> >
> > Any ideas.
> >
> > Bob[/color]
>
> So you want to select all dates less than the first day of the current
> month?
>
> WHERE DateField < DateSerial(Year (Now), Month(Now), 1)
>
> Arno R
>
>
> Hey! Are you copying my answers?[/color]

Based on the time stamps on your posts, it was a dead heat. :-{)

**Wayne Morgan** · Jan 11 '06, 05:25 AM

Re: Now()

Bob,

Just a quick FYI.

I see that you got your answer and it will take care of the problem I'm
about to mention also. The problem with the original code is that not only
would it have included dates from earlier in the month, it would also have
included dates from the current date. You were comparing to Now() which is
both date and time. If the values you were comparing to this were from the
current day but with an earlier time, they would have been included also. A
date with no time component (i.e. time component is zero) will be treated as
midnight. So any date without a time component that was from the current
date would have been less than Now() unless you happened to catch it just a
midnight.

The reason this happens is because VBA treats dates as a floating point
number. The integer part is the date and the decimal part is the time of
day. The integer portion gives the number of days since 30 Dec 1899 and the
decimal portion give the time as fractions of a day (.0 = midnight, .25 =
6am, .5 = noon, etc). So, a date with no time component would have a decimal
portion of zero (i.e. midnight). If all you want is the date component, I
recommend using the Date() function instead of the Now() function.

--
Wayne Morgan
MS Access MVP

"Bob Wickham" <wickham_bob@ya hoo.com.au> wrote in message
news:43c444d2@d news.tpgi.com.a u...[color=blue]
> Hi,
>
> I have some sql in a sub that finds dates in a table but only if the date
> is less than today
>
> WHERE [MonthYear] < Now()
>
> I want it to do that but to not include the current month.
>
> For instance, a list of dates in dd/mm/yy format
> 1/01/05
> 01/02/05
> etc
> etc
> 01/11/05
> 01/12/05
> 01/01/06
> 01/02/06
>
> If I run this today (11/01/06) 01/01/06 is included in the result because
> it is less than today.
> But I dont want the current month included.
>
> Any ideas.
>
> Bob[/color]

**Arno R** · Jan 11 '06, 08:06 AM

Re: Now()

"Anthony England" <aengland@oops. co.uk> schreef in bericht news:dq1hgc$bd4 $1@nwrdmz03.dmz .ncs.ea.ibs-infra.bt.com...[color=blue]
>
> Hey! Are you copying my answers?
> [/color]

Funny this is:
Your Timestamp: Date: Tue, 10 Jan 2006 23:50:50 +0000 (UTC)
My Timestamp: Date: Wed, 11 Jan 2006 00:50:50 +0100
So there is not even a second difference here ...
both almost identical answers ...

Are you *my* evil twin ??
I am looking for one ;-)

Btw:
According to Google this means for both of us:
Local: Wed, Jan 11 2006 12:50 am ??

Arno R

**Bob Wickham** · Jan 11 '06, 09:35 PM

Re: Now()

Wayne Morgan wrote:[color=blue]
> Bob,
>
> Just a quick FYI.
>
> I see that you got your answer and it will take care of the problem I'm
> about to mention also. The problem with the original code is that not only
> would it have included dates from earlier in the month, it would also have
> included dates from the current date. You were comparing to Now() which is
> both date and time. If the values you were comparing to this were from the
> current day but with an earlier time, they would have been included also. A
> date with no time component (i.e. time component is zero) will be treated as
> midnight. So any date without a time component that was from the current
> date would have been less than Now() unless you happened to catch it just a
> midnight.
>
> The reason this happens is because VBA treats dates as a floating point
> number. The integer part is the date and the decimal part is the time of
> day. The integer portion gives the number of days since 30 Dec 1899 and the
> decimal portion give the time as fractions of a day (.0 = midnight, .25 =
> 6am, .5 = noon, etc). So, a date with no time component would have a decimal
> portion of zero (i.e. midnight). If all you want is the date component, I
> recommend using the Date() function instead of the Now() function.
>[/color]

Thanks Wayne,

I understand your advice but, in practise, I can't get it to work.

Should I be able to simply change Now to Date in the solution offered by
Arno and Anthony

WHERE DateField < DateSerial(Year (Now), Month(Now), 1)

like

WHERE DateField < DateSerial(Year (Date), Month(Date), 1)

because doing that results in a "Too few parameters error"

Bob

**Jana** · Jan 11 '06, 10:05 PM

Re: Now()

Bob:

If you're using a European version, I think you need to use ; instead
of , as the parameter separator. (Not 100% sure, though!)
Try WHERE DateField < DateSerial(Year (Date); Month(Date); 1)

HTH,
Jana

**Red** · Jan 11 '06, 10:15 PM

Re: Now()

I just wanted to note, if you are doing this in a VBA (like in a form,
or in a module), then you need to play with it a bit more....

This is an example of a recent thing I did with dates in a dynamic SQL
statement in a module... it has been modified so I can legally use it
(Darn proprietary agreements...), and so it better fits your
purposes....

'Put this under "Option Explicit"
Global Const JetDateFmt = "\#mm\/dd\/yyyy\#;;;\N\u\l \l"

'This is the 'where' part of your sql
WHERE MyTable.MyDate < Format$(DateSer ial(Year(Date() ), Month(Date),
1), JetDateFmt)

**Bob Wickham** · Jan 11 '06, 10:25 PM

Re: Now()

Jana wrote:[color=blue]
> Bob:
>
> If you're using a European version, I think you need to use ; instead
> of , as the parameter separator. (Not 100% sure, though!)
> Try WHERE DateField < DateSerial(Year (Date); Month(Date); 1)
>
> HTH,
> Jana
>[/color]
Thanks Jana, but that results in a Syntax error.

The Now() function works OK but I do like to understand the alternatives.

Bob

**Bob Wickham** · Jan 11 '06, 10:35 PM

Re: Now()

Red wrote:[color=blue]
> I just wanted to note, if you are doing this in a VBA (like in a form,
> or in a module), then you need to play with it a bit more....
>
> This is an example of a recent thing I did with dates in a dynamic SQL
> statement in a module... it has been modified so I can legally use it
> (Darn proprietary agreements...), and so it better fits your
> purposes....
>
> 'Put this under "Option Explicit"
> Global Const JetDateFmt = "\#mm\/dd\/yyyy\#;;;\N\u\l \l"
>
>
> 'This is the 'where' part of your sql
> WHERE MyTable.MyDate < Format$(DateSer ial(Year(Date() ), Month(Date),
> 1), JetDateFmt)
>[/color]
Thanks Red,

Yes, it is part of a much larger VBA routine.
I'll give your suggestion a try and see what happens.

Bob

**David W. Fenton** · Jan 11 '06, 10:45 PM

Re: Now()

"Red" <douglas.dion@g mail.com> wrote in
news:1137017306 .148138.112360@ z14g2000cwz.goo glegroups.com:
[color=blue]
> I just wanted to note, if you are doing this in a VBA (like in a
> form, or in a module), then you need to play with it a bit
> more....
>
> This is an example of a recent thing I did with dates in a dynamic
> SQL statement in a module... it has been modified so I can legally
> use it (Darn proprietary agreements...), and so it better fits
> your purposes....
>
> 'Put this under "Option Explicit"
> Global Const JetDateFmt = "\#mm\/dd\/yyyy\#;;;\N\u\l \l"
>
>
> 'This is the 'where' part of your sql
> WHERE MyTable.MyDate < Format$(DateSer ial(Year(Date() ),
> Month(Date), 1), JetDateFmt)[/color]

That's idiotic.

Format() returns a string, so you're using implicit coercion to
compare the result to a date filed.

Secondly, the US format date will give incorrect results if run on a
machine with different OS-supplied default date formats (e.g.,
DD/MM/YYYY).

DateSerial() returns a value of type variant, but it's always going
to be of a type that compares universally correctly to all stored
data values, independent of local date format settings.

Thus, formatting the output of DateSerial() is undoing the good that
comes from using DateSerial() in the first place.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

**David W. Fenton** · Jan 11 '06, 10:55 PM

Re: Now()

Bob Wickham <wickham_bob@ya hoo.com.au> wrote in
news:43c577fd@d news.tpgi.com.a u:
[color=blue]
> I understand your advice but, in practise, I can't get it to work.
>
> Should I be able to simply change Now to Date in the solution
> offered by
> Arno and Anthony
>
> WHERE DateField < DateSerial(Year (Now), Month(Now), 1)
>
> like
>
> WHERE DateField < DateSerial(Year (Date), Month(Date), 1)
>
> because doing that results in a "Too few parameters error"[/color]

Are you sure you're typing:

WHERE DateField < DateSerial(Year (Date()), Month(Date()), 1)

Both Date() and Now() are functions (hence the trailing
parentheses). In VBA code, the () will disappear when you leave the
line, but in SQL, they are necessary.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Now()

Now()

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment