?
Solved

Need helping formatting text field in SQL statement

Posted on 2013-01-18
4
Medium Priority
?
344 Views
Last Modified: 2013-01-18
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.
0
Comment
Question by:PBLack
  • 2
4 Comments
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 800 total points
ID: 38793932
where replace(rf_sect,'0','') = replace(sect,'0','')
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 1200 total points
ID: 38793940
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
 

Author Closing Comment

by:PBLack
ID: 38793996
Thanks
0
 
LVL 32

Expert Comment

by:awking00
ID: 38794172
Would it matter if where 20 = 20 becomes where 2 = 2? Just curious.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Implementing simple internal controls in the Microsoft Access application.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

615 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question