Converting Oracle SQL functions to Access built-in functions

[GVR_Mike]

I have some Oracle SQL code that was sent to me by one of our
programmers. We have an Oracle back-end. We use Access to query Oracle
but the programmers use Oracle directly obviously. I'm trying to
convert his Oracle functions to Access built-in functions without
having to delve into the VBA. I got most of them converted (UPPER,
TO_CHAR, DECODE, etc) but am having trouble finding a way to convert
the LAST_DAY function. Is there a way to use Access built-in functions
to obtain the last day of a given month? I've seen ways to do it in VBA
but want to avoid that if possible.

Thanks.

 

[Douglas J. Steele]

I don't know Oracle, so I don't know what you pass to the LAST_DAY
function.

If you've got a date, and you want the last day of the month that that day
is in, use

DateSerial(Year(MyDateValue), Month(MyDateValue) + 1, 0)

If you've only got a month number, you have a bit of a problem. For all
months but February, you can use DateSerial(2005, MonthValue + 1, 0).
However, what do you want for February: 28 or 29?

 

[Albert D.Kallal]

You do realize that if you use a pass-through query from ms-access, and then
all of your oracle syntax and functions will work from the query in
ms-access?

We are talking about linked tables from ms-access to oracle...right?

If yes, then can you use a pass-through....you will NOT have to modify the
sql, and can leave it in oracle syntax....

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.members.shaw.ca/AlbertKallal

"

 

[GVR_Mike]

Hmmm....interesting, have never used pass-through queries so I didn't
know what they did. I'll research them a little and see if I can get it
to work. Thanks!