Rounddown

[xpnovice]

Hi,

Access 2002

Anyone give me information on how I can get access to Rounddown numbers in a
calculated query?

I am calculating ages and I just want to know the age in years from the
given birthday.

My formula is - Round((Now()-[dob])/356,0), which is fine until the current
date is into the second half of the current birthday year and then off
course they are a shown a year older than they actually are (i.e. 31.6
becomes 32 because it is rounded up).
Rounddown does not appear to be a valid expression.

Help greatly accepted.

Thanks
John

 

[xpnovice]

I can get the correct year by creating using Year(now()) - Year([dob]) but
would be nice to know if there is a rounddown option that is easier

thanks
John

 

[Mike Labosh]

Change Round to Int. The Int Function converts a real number to an Integer
by simply truncating, so that 31.999 gets chopped to 31 and returned as an
Integer:

Int((Now()-[dob])/356,0)

--
Peace & happy computing,

Mike Labosh, MCSD

Feed the children!
Save the whales!
Free the mallocs!

 

[Douglas J. Steele]

Actually, the correct way to determine age is

Age=DateDiff("yyyy", [dob], Date()) - _
IIf(Format(Date(),"mmdd") < Format([dob],"mmdd"), 1, 0)

DateDiff("yyyy", [dob], Date()) isn't sufficient, because it's too literal.
DateDiff("yyyy", #12/31/2003#, #1/1/2004#) will return a 1 year difference,
even though it's only 1 day. The second half of the formula takes care of
that: it subtracts one from the number of years calculated by DateDiff if
the birthday hasn't occurred yet this year.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I can get the correct year by creating using Year(now()) - Year([dob]) but
would be nice to know if there is a rounddown option that is easier

thanks
John

Hi,

Access 2002

Anyone give me information on how I can get access to Rounddown numbers in
a calculated query?

I am calculating ages and I just want to know the age in years from the
given birthday.

My formula is - Round((Now()-[dob])/356,0), which is fine until the
current date is into the second half of the current birthday year and then
off course they are a shown a year older than they actually are (i.e.
31.6 becomes 32 because it is rounded up).
Rounddown does not appear to be a valid expression.

Help greatly accepted.

Thanks
John

 

[xpnovice]

Thanks Mike.

Question - I am slowly building up a list of useful functions/expressions,
such as the Int and Month Year etc which seem to get you out of a lot of
corners.

I know in Excel you just have to look in the function list and there they
all are. Is there a definative list where all the access functions etc are
listed. They are very rarely mentioned in any of the off-the-shelf books
you use to learn access.

Just a thought.

Thanks
John

 

[xpnovice]

Thanks again, yes that works fine.
Re my point about where do we novices learn about these different formula, I
understand them once shown them, as you have Douglas, but its the bit to
kick-start me that is missing. Any pointers please.

Thanks
John

Actually, the correct way to determine age is

Age=DateDiff("yyyy", [dob], Date()) - _
IIf(Format(Date(),"mmdd") < Format([dob],"mmdd"), 1, 0)

DateDiff("yyyy", [dob], Date()) isn't sufficient, because it's too
literal.
DateDiff("yyyy", #12/31/2003#, #1/1/2004#) will return a 1 year
difference,
even though it's only 1 day. The second half of the formula takes care of
that: it subtracts one from the number of years calculated by DateDiff if
the birthday hasn't occurred yet this year.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I can get the correct year by creating using Year(now()) - Year([dob])
but
would be nice to know if there is a rounddown option that is easier

thanks
John

Hi,

Access 2002

Anyone give me information on how I can get access to Rounddown numbers in
a calculated query?

I am calculating ages and I just want to know the age in years from
the
given birthday.

My formula is - Round((Now()-[dob])/356,0), which is fine until the
current date is into the second half of the current birthday year and then
off course they are a shown a year older than they actually are (i.e.
31.6 becomes 32 because it is rounded up).
Rounddown does not appear to be a valid expression.

Help greatly accepted.

Thanks
John

 

[Mike Labosh]

I know in Excel you just have to look in the function list and there they

all are. Is there a definative list where all the access functions etc
are listed. They are very rarely mentioned in any of the off-the-shelf
books you use to learn access.

Unfortunately, Office in general, and Access in particular, the Help files
are the *most unhelpful* that I have ever seen. Off the top of my head,
here's a list of the stuff that I have used most often (off the top of my
head):

Trim() -- removes leading and trailing spaces
LTrim() -- only from the left
RTrim() -- only from the right
UCase() -- convert to upper case
LCase() -- convert to lower case
Chr() -- return a character by ascii code
Asc() -- return the ascii code of a character
Right() -- return the rightmost n characters of a string
Left() -- return the leftmost n characters of a string
Mid() -- return n characters from the middle of a string, beginning at the
m'th position
DatePart() -- returns part of a date
DateAdd() -- add n of a specified interval to a date and return the result
DateDiff() -- distance between two dates in terms of a specified interval
Rnd() -- this is fun for giving error numbers to users
CInt() -- convert something to an integer
CStr() -- convert something to a string
CLng, CDbl, CCur, etc... you get the idea
Instr() -- return the position at which one string occurs in another
InstrRev() -- same thing but searches from the end backwards
Replace() -- replace all occurrances of one string within another
Year() Month() Day() Hour() Minute() Second()
DateSerial() -- give it three numbers for year, month and day. Returns that
date. It should be noted that DateSerial is absolutely indispensible when
someone hands you some crummy import file where the dates are like yyyymmdd
in text columns: 20040917 = Sep 17, 2004, but the DateTime data type chokes
on it.

Remember that Access queries are allowed to reference VBA functions. This
is very powerful. But don't go overboard with it, because it can turn into
spaghetti. Two days ago I spent the whole day porting some legacy Access
stuff to .NET / SQL Server, and it went something like this:

In Query1: ...WHERE [aColumn] = Foo([AnotherColumn])

In VBA:

Public Function Foo(value As String) As String
...
...
Set rs = db.OpenRecordset("Query2")
...
End Function

and query two referenced some other function that ran a query that called a
function and it was all a great big giant mess.

And get yourself a copy of "Access xxx Developer's Handbook" by Sybex (xxx =
your version)

Also spend some time to learn SQL, DAO and ADO.
--
Peace & happy computing,

Mike Labosh, MCSD

Feed the children!
Save the whales!
Free the mallocs!

 

[Douglas J. Steele]

Try poking around "The Access Web" http://www.mvps.org/access/

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks again, yes that works fine.
Re my point about where do we novices learn about these different formula, I
understand them once shown them, as you have Douglas, but its the bit to
kick-start me that is missing. Any pointers please.

Thanks
John

Actually, the correct way to determine age is

Age=DateDiff("yyyy", [dob], Date()) - _
IIf(Format(Date(),"mmdd") < Format([dob],"mmdd"), 1, 0)

DateDiff("yyyy", [dob], Date()) isn't sufficient, because it's too
literal.
DateDiff("yyyy", #12/31/2003#, #1/1/2004#) will return a 1 year
difference,
even though it's only 1 day. The second half of the formula takes care of
that: it subtracts one from the number of years calculated by DateDiff if
the birthday hasn't occurred yet this year.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I can get the correct year by creating using Year(now()) - Year([dob])
but
would be nice to know if there is a rounddown option that is easier

thanks
John

Hi,

Access 2002

Anyone give me information on how I can get access to Rounddown

numbers
in

a calculated query?

I am calculating ages and I just want to know the age in years from
the
given birthday.

My formula is - Round((Now()-[dob])/356,0), which is fine until the
current date is into the second half of the current birthday year and then
off course they are a shown a year older than they actually are (i.e.
31.6 becomes 32 because it is rounded up).
Rounddown does not appear to be a valid expression.

Help greatly accepted.

Thanks
John

 

[Ken Snell [MVP]]

If you open Help file from database window in ACCESS, and click on Contents
tab, you can find the functions listed under Programming in Visual Basic |
Visual Basic Language Reference | Functions tree (this tree is for ACCESS
2002 Help file).

--

Ken Snell
<MS ACCESS MVP>

I know in Excel you just have to look in the function list and there they
all are. Is there a definative list where all the access functions etc
are listed. They are very rarely mentioned in any of the off-the-shelf
books you use to learn access.

Unfortunately, Office in general, and Access in particular, the Help files
are the *most unhelpful* that I have ever seen. Off the top of my head,
here's a list of the stuff that I have used most often (off the top of my
head):

Trim() -- removes leading and trailing spaces
LTrim() -- only from the left
RTrim() -- only from the right
UCase() -- convert to upper case
LCase() -- convert to lower case
Chr() -- return a character by ascii code
Asc() -- return the ascii code of a character
Right() -- return the rightmost n characters of a string
Left() -- return the leftmost n characters of a string
Mid() -- return n characters from the middle of a string, beginning at the
m'th position
DatePart() -- returns part of a date
DateAdd() -- add n of a specified interval to a date and return the result
DateDiff() -- distance between two dates in terms of a specified interval
Rnd() -- this is fun for giving error numbers to users
CInt() -- convert something to an integer
CStr() -- convert something to a string
CLng, CDbl, CCur, etc... you get the idea
Instr() -- return the position at which one string occurs in another
InstrRev() -- same thing but searches from the end backwards
Replace() -- replace all occurrances of one string within another
Year() Month() Day() Hour() Minute() Second()
DateSerial() -- give it three numbers for year, month and day. Returns that
date. It should be noted that DateSerial is absolutely indispensible when
someone hands you some crummy import file where the dates are like yyyymmdd
in text columns: 20040917 = Sep 17, 2004, but the DateTime data type chokes
on it.

Remember that Access queries are allowed to reference VBA functions. This
is very powerful. But don't go overboard with it, because it can turn into
spaghetti. Two days ago I spent the whole day porting some legacy Access
stuff to .NET / SQL Server, and it went something like this:

In Query1: ...WHERE [aColumn] = Foo([AnotherColumn])

In VBA:

Public Function Foo(value As String) As String
...
...
Set rs = db.OpenRecordset("Query2")
...
End Function

and query two referenced some other function that ran a query that called a
function and it was all a great big giant mess.

And get yourself a copy of "Access xxx Developer's Handbook" by Sybex (xxx =
your version)

Also spend some time to learn SQL, DAO and ADO.
--
Peace & happy computing,

Mike Labosh, MCSD

Feed the children!
Save the whales!
Free the mallocs!

 

[Jamie Collins]

If you open Help file from database window in ACCESS, and click on Contents
tab, you can find the functions listed under Programming in Visual Basic |
Visual Basic Language Reference | Functions tree

Do you know of documentation on which functions Jet supports, as
distinct from functions provided by the MS Access UI (VBA functions,
MS Access functions such as NZ, etc)? From testing, most if not all
VBA6 functions (as distinct from methods) listed in the object browser
are supported by Jet but it would be good to get confirmation.

Many thanks,
Jamie.

--

 

[Ken Snell [MVP]]

The only VBA function where I've noted that Jet had a problem is Replace
function in ACCESS 2000 prior to Jet 4.0 SP6. Other than that, Jet has been
able to use any VBA function that I give it (so long as I remember that VB
intrinsic constants need to be changed to numbers!).

 

[Brendan Reynolds]

I'm working on it. Not finished yet, but see the blog in my sig line below.
When I finish working through the list, I'll write up a summary and provide
a permanent link to it.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[Ken Snell [MVP]]

Impressive, Brendan!

 

[Jamie Collins]

The only VBA function where I've noted that Jet had a problem is Replace
function in ACCESS 2000 prior to Jet 4.0 SP6. Other than that, Jet has been
able to use any VBA function that I give it (so long as I remember that VB
intrinsic constants need to be changed to numbers!).

I think the Replace function may be being provided by MS Access UI's
VBA6 function (Replace was not in VBA5). When I try Replace on a Jet
4.0 .mdb (Jet 4.0 SP8, OLEDB 4.0 provider, ADO 2.7) I get an error,
'Undefined function 'REPLACE' in expression.'

Presumably the same applies to functions new to VBA6 e.g. StrReverse.

Jamie.

--

 

[Ken Snell [MVP]]

I don't have more specific knowledge about this situation than what I've
already given. I know that ACCESS 2000 files without SP6 don't work for
putting Replace in queries, but that the same file after patching then will
work. ACCESS 2002 and up have worked from the beginning.
--

Ken Snell
<MS ACCESS MVP>

 

[Douglas J. Steele]

I think the Replace function may be being provided by MS Access UI's
VBA6 function (Replace was not in VBA5). When I try Replace on a Jet
4.0 .mdb (Jet 4.0 SP8, OLEDB 4.0 provider, ADO 2.7) I get an error,
'Undefined function 'REPLACE' in expression.'

Presumably the same applies to functions new to VBA6 e.g. StrReverse.

I'm not sure I agree with Ken. My understanding is that if you're attempting
to connect to a Jet database from outside of Access (eg. from VB, VC++, ASP,
etc.), you can't use any VBA functions in your queries at all, because
you're strictly going through the Jet Engine, which doesn't know about VBA
functions.

On the other hand, if you're trying to run your queries from within Access
and you're getting this error, I'd suspect a References problem. These are
common if you've installed new software, or if you're trying to run on a
machine that has different software installed than the machine where the
database was developed.

Open any code module, then select Tools | References from the menu bar.
Examine all of the selected references.

If any of the selected references have "MISSING:" in front of them, unselect
them, and back out of the dialog. If you really need the reference(s) you
just unselected (you can tell by doing a Compile All Modules), go back in
and reselect them.

If none have "MISSING:", select an additional reference at random, back out
of the dialog, then go back in and unselect the reference you just added. If
that doesn't solve the problem, try to unselect as many of the selected
references as you can (Access may not let you unselect them all), back out
of the dialog, then go back in and reselect the references you just
unselected. (NOTE: write down what the references are before you delete
them, because they'll be in a different order when you go back in)

 

[Brendan Reynolds]

These are the functions that I have so far found to work in Jet queries
executed outside of the Microsoft Access environment. I'm working through
the list of functions in the VBA help file in alphabetical order, and have
still to test functions with names beginning T to Z.

Abs, Asc, AscB, AscW, Atn, CBool, CByte, CCur, CDate, CDbl, Choose, Chr,
Chr$, ChrW, CInt, CLng, Cos, CSng, CStr, CVar, CVDate, Date, Date$, DateAdd,
DateDiff, DatePart, DateSerial, DateValue, Day, DBB, Exp, Fix, Format,
Format$, FV, Hex, Hex$, Hour, IIf, IMEStatus, InStr, InStrB, Int, IPmt,
IsDate, IsEmpty, IsError, IsMissing, IsNull, IsNumeric, IsObject, LCase,
LCase$, Left, Left$, LeftB, LeftB$, Len, LenB, Log, LTrim, LTrim$, Mid,
Mid$, MidB, MidB$, Minute, Month, Now, NPer, Oct, Oct$, Partition, PMT,
PPMT, PV, QBColor, Rate, RGB, Right, Right$, RightB, RightB$, Rnd, Round,
RTrim, RTrim$, Second, Sgn, Sin, SLN, Space, Sqr, Str, Str$, StrComp,
StrConv, String, String$, Switch, SYD.

Note that some of these functions, while they can be used and will not cause
an error, are unlikely to ever return a meaningful result in a query. I
can't, for example, think of any situation in which the IsEmpty, IsMissing,
or IsObject functions can ever return anything other than False in a query.

There is a KB article at the following URL which lists functions available
in Jet queries. However, the results of my tests differ from that list. For
example, the list in the KB article includes the ChrB function, but in my
tests this function fails with an undefined function error message even when
the query is executed within the Microsoft Access environment. I wish I
could trust the KB article, as the testing is really not a whole lot of fun,
but what the heck, the end is in sight now!

http://support.microsoft.com/default.aspx?kbid=294698

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[Brendan Reynolds]

I used to think so too, Doug, but I learned otherwise. The list of functions
that I've posted elsewhere in this thread have all been tested in queries
executed via C# and ADO.NET.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[Douglas J. Steele]

Thanks, Brendan. That list'll get copied to my FAQ...

 

[Ken Snell [MVP]]

Jamie -

Brendan's work shows that Replace fails when called from outside ACCESS. So
I would guess that this is consistent with what you're seeing.