Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Need helping formatting text field in SQL statement

Posted on 2013-01-18
4
Medium Priority
?
338 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Technology Partners: 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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

715 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