Need helping formatting text field in SQL statement

I have a query:

strPID = "DELETE * FROM tblPID WHERE PID = '" & !RF_PID & "' AND SECT = '" & Format(!RF_SECT, "0") & "';"

Fields RF_SECT and SECT are each two characters wide and can accept values from 01 - 99. Each have to be a text field.

Works fine most of the time, however sometimes the field SECT has data in it formatted with a zero place holder.

Unfortunately there is no way that I can access all sources of data and change it to remove the place holder Zero.

How could I rewrite this statement so that if the leading character in the field SECT is a zero that it would be ignored? RF_PID always has the zero place place holder.

To explain by example:

SECT | RF_SECT
---------------------
1  | 01
02 | 02
3 | 03
4 | 04
05 | 05

Each one has to match the other and I do no know how to catch it.
PBLackAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
this will do it

where replace(rf_sect,"0","",1,1)= replace(sect,"0","",1,1)

it will replace ONLY the first "0" in the First position and only one 0
0
 
awking00Connect With a Mentor Commented:
where replace(rf_sect,'0','') = replace(sect,'0','')
0
 
PBLackAuthor Commented:
Thanks
0
 
awking00Commented:
Would it matter if where 20 = 20 becomes where 2 = 2? Just curious.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.