Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Need helping formatting text field in SQL statement

Posted on 2013-01-18
4
Medium Priority
?
343 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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

916 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