Convert Oracle function INSTRC in MS ACCESS or SQL Server

  • Thread starter Thread starter Steph_canoe
  • Start date Start date
S

Steph_canoe

Hi,

I'm trying to convert the following function from Oracle SQL to MS Access.
I'm using Access 2003.

The "poscarmap" is a double from 1 to 98. My P_FILTREREGION field is a memo
field containing the letter "Y" which is placed at different places in the
field. I assume that if there is 12 spaces before tbe "Y" and the "Y" is at
the 13 position, it will relate to the "poscarmap" 13.

I have no clue how to do something similair in Access.

Here is my Oracle function :

r.poscarmap = INSTRC(P_FILTREREGION, 'Y', 1, 1)

My from and where statements:

from
com_grp_client g,
com_clients c,
poste p,
for_tracking_log t ,
refregion r,
creneau cr
where
cr.numero = p.p_creneau and
c.no_grp_client_parent = g.no_grp_client and
g.no_grp_client_corpo in ('G121159') and
p.p_noemployeur = c.no_client and
c.no_client = t.numemp and
c.no_grp_client_forfait = t.no_grp_client and
t.no_formation = p.p_noposte and
r.poscarmap = INSTRC(P_FILTREREGION, 'Y', 1, 1) and
r.langue = 'F' and
t.date_log > to_date('2009-01-01','YYYY-MM-DD') and
t.date_log < to_date('2009-01-31','YYYY-MM-DD')+1

Any help will be appreciated

Thanks

Steph_canoe
 
You might try to describe what that function does. For instance, are
you trying to see if a specific character "Y" is in a specific position
in P_FiltreRegion field? Or are you trying to see if the "Y" is at the
position defined by posCarMap?

If you are looking for the position of the first "Y" in P_FiltreRegion
then you are probably looking for the INSTR function (a VBA function).

Text compare returns position of first Y or y
Instr(1,P_FiltreRegion,"Y",1)

Binary compare (case sensitive - returns position of first upper case Y.
If you are looking for lowercase Y then the 3rd argument would be "y".
Instr(1,P_FiltreRegion,"Y",0)
'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
Try


from
com_grp_client g,
com_clients c,
poste p,
for_tracking_log t ,
refregion r,
creneau cr
where
cr.numero = p.p_creneau and
c.no_grp_client_parent = g.no_grp_client and
g.no_grp_client_corpo in ('G121159') and
p.p_noemployeur = c.no_client and
c.no_client = t.numemp and
c.no_grp_client_forfait = t.no_grp_client and
t.no_formation = p.p_noposte and
r.poscarmap = INSTR(P_FILTREREGION, 'Y') and
r.langue = 'F' and
t.date_log > #01/01/2009# and
t.date_log < #01/31/2009#



So, the only thing you need change is the date format, and the instrC
function becomes instr()
 
Back
Top