Remove leading zero

[gjproctor]

I am trying to remove the leading zeros from a 16 character text field. I
have tried using the val () funtion as well as the right () function. Each
time I run the query it asks for me to enter the "Parameter Value" and lists
the field name. I think I am querying an Oracle database if that makes a
difference.

 

[Guest]

Show us the SQL. Open the query in design view. Next go to View, SQL View
and copy and past it here.

 

[gjproctor]

SELECT Right([LOCATION].[CAR1_CAV_LOCINFODETL],8) AS LOC, CAR1_CAV_METERDETL.
METER, CAR1_CAV_MBRSEPMSTR.NAME, CAR1_CAV_METERDETL.MULT,
CAR1_CAV_LOCINFODETL.PHASE, CAR1_CAV_METERDETL.VOLTS, CAR1_CAV_MBRSEPMSTR.
ACCTSTATUS AS Status, CAR1_CAV_MBRSEPMSTR.MBRSEP, CAR1_CAV_LOCINFODETL.
SUBSTATION AS SUB, CAR1_CAV_MEMBERDETL.CONNDATE
FROM (CAR1_CAV_MEMBERDETL INNER JOIN (CAR1_CAV_METERDETL INNER JOIN
CAR1_CAV_LOCINFODETL ON CAR1_CAV_METERDETL.LOCATION = CAR1_CAV_LOCINFODETL.
LOCATION) ON CAR1_CAV_MEMBERDETL.LOCATION = CAR1_CAV_LOCINFODETL.LOCATION)
INNER JOIN CAR1_CAV_MBRSEPMSTR ON CAR1_CAV_MEMBERDETL.MBRSEP =
CAR1_CAV_MBRSEPMSTR.MBRSEP
WHERE (((CAR1_CAV_MBRSEPMSTR.NAME) Not Like "*SUBSTATION METER*") AND (
(CAR1_CAV_METERDETL.MULT)>"000001") AND ((CAR1_CAV_MBRSEPMSTR.ACCTSTATUS)
Like "*A*") AND ((CAR1_CAV_LOCINFODETL.SUBSTATION)="0007" Or
(CAR1_CAV_LOCINFODETL.SUBSTATION)="0010" Or (CAR1_CAV_LOCINFODETL.SUBSTATION)
="0011" Or (CAR1_CAV_LOCINFODETL.SUBSTATION)="0012" Or (CAR1_CAV_LOCINFODETL.
SUBSTATION)="0013") AND ((CAR1_CAV_METERDETL.IDLECODE)<>"01")) OR ((
(CAR1_CAV_MBRSEPMSTR.NAME) Not Like "*SUBSTATION METER*") AND (
(CAR1_CAV_LOCINFODETL.PHASE)>"03") AND ((CAR1_CAV_MBRSEPMSTR.ACCTSTATUS) Like
"*A*") AND ((CAR1_CAV_LOCINFODETL.SUBSTATION)="0007" Or (CAR1_CAV_LOCINFODETL.
SUBSTATION)="0010" Or (CAR1_CAV_LOCINFODETL.SUBSTATION)="0011" Or
(CAR1_CAV_LOCINFODETL.SUBSTATION)="0012" Or (CAR1_CAV_LOCINFODETL.SUBSTATION)
="0013") AND ((CAR1_CAV_METERDETL.IDLECODE)<>"01")) OR (((CAR1_CAV_MBRSEPMSTR.
NAME) Not Like "*SUBSTATION METER*") AND ((CAR1_CAV_METERDETL.VOLTS)>"0240")
AND ((CAR1_CAV_MBRSEPMSTR.ACCTSTATUS) Like "*A*") AND ((CAR1_CAV_LOCINFODETL.
SUBSTATION)="0007" Or (CAR1_CAV_LOCINFODETL.SUBSTATION)="0010" Or
(CAR1_CAV_LOCINFODETL.SUBSTATION)="0011" Or (CAR1_CAV_LOCINFODETL.SUBSTATION)
="0012" Or (CAR1_CAV_LOCINFODETL.SUBSTATION)="0013") AND ((CAR1_CAV_METERDETL.
IDLECODE)<>"01"))
ORDER BY CAR1_CAV_LOCINFODETL.SUBSTATION, CAR1_CAV_LOCINFODETL.LOCATION;

 

[Guest]

Right([LOCATION].[CAR1_CAV_LOCINFODETL],8)

The above is backwards. It should be table name then field name like below:

Right([CAR1_CAV_LOCINFODETL].[LOCATION],8)
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

SELECT Right([LOCATION].[CAR1_CAV_LOCINFODETL],8) AS LOC, CAR1_CAV_METERDETL.
METER, CAR1_CAV_MBRSEPMSTR.NAME, CAR1_CAV_METERDETL.MULT,
CAR1_CAV_LOCINFODETL.PHASE, CAR1_CAV_METERDETL.VOLTS, CAR1_CAV_MBRSEPMSTR.
ACCTSTATUS AS Status, CAR1_CAV_MBRSEPMSTR.MBRSEP, CAR1_CAV_LOCINFODETL.
SUBSTATION AS SUB, CAR1_CAV_MEMBERDETL.CONNDATE
FROM (CAR1_CAV_MEMBERDETL INNER JOIN (CAR1_CAV_METERDETL INNER JOIN
CAR1_CAV_LOCINFODETL ON CAR1_CAV_METERDETL.LOCATION = CAR1_CAV_LOCINFODETL.
LOCATION) ON CAR1_CAV_MEMBERDETL.LOCATION = CAR1_CAV_LOCINFODETL.LOCATION)
INNER JOIN CAR1_CAV_MBRSEPMSTR ON CAR1_CAV_MEMBERDETL.MBRSEP =
CAR1_CAV_MBRSEPMSTR.MBRSEP
WHERE (((CAR1_CAV_MBRSEPMSTR.NAME) Not Like "*SUBSTATION METER*") AND (
(CAR1_CAV_METERDETL.MULT)>"000001") AND ((CAR1_CAV_MBRSEPMSTR.ACCTSTATUS)
Like "*A*") AND ((CAR1_CAV_LOCINFODETL.SUBSTATION)="0007" Or
(CAR1_CAV_LOCINFODETL.SUBSTATION)="0010" Or (CAR1_CAV_LOCINFODETL.SUBSTATION)
="0011" Or (CAR1_CAV_LOCINFODETL.SUBSTATION)="0012" Or (CAR1_CAV_LOCINFODETL.
SUBSTATION)="0013") AND ((CAR1_CAV_METERDETL.IDLECODE)<>"01")) OR ((
(CAR1_CAV_MBRSEPMSTR.NAME) Not Like "*SUBSTATION METER*") AND (
(CAR1_CAV_LOCINFODETL.PHASE)>"03") AND ((CAR1_CAV_MBRSEPMSTR.ACCTSTATUS) Like
"*A*") AND ((CAR1_CAV_LOCINFODETL.SUBSTATION)="0007" Or (CAR1_CAV_LOCINFODETL.
SUBSTATION)="0010" Or (CAR1_CAV_LOCINFODETL.SUBSTATION)="0011" Or
(CAR1_CAV_LOCINFODETL.SUBSTATION)="0012" Or (CAR1_CAV_LOCINFODETL.SUBSTATION)
="0013") AND ((CAR1_CAV_METERDETL.IDLECODE)<>"01")) OR (((CAR1_CAV_MBRSEPMSTR.
NAME) Not Like "*SUBSTATION METER*") AND ((CAR1_CAV_METERDETL.VOLTS)>"0240")
AND ((CAR1_CAV_MBRSEPMSTR.ACCTSTATUS) Like "*A*") AND ((CAR1_CAV_LOCINFODETL.
SUBSTATION)="0007" Or (CAR1_CAV_LOCINFODETL.SUBSTATION)="0010" Or
(CAR1_CAV_LOCINFODETL.SUBSTATION)="0011" Or (CAR1_CAV_LOCINFODETL.SUBSTATION)
="0012" Or (CAR1_CAV_LOCINFODETL.SUBSTATION)="0013") AND ((CAR1_CAV_METERDETL.
IDLECODE)<>"01"))
ORDER BY CAR1_CAV_LOCINFODETL.SUBSTATION, CAR1_CAV_LOCINFODETL.LOCATION;

Show us the SQL. Open the query in design view. Next go to View, SQL View
and copy and past it here.

 

[gjproctor]

This works...Many thanks for the help.

Right([LOCATION].[CAR1_CAV_LOCINFODETL],8)

The above is backwards. It should be table name then field name like below:

Right([CAR1_CAV_LOCINFODETL].[LOCATION],8)

SELECT Right([LOCATION].[CAR1_CAV_LOCINFODETL],8) AS LOC, CAR1_CAV_METERDETL.
METER, CAR1_CAV_MBRSEPMSTR.NAME, CAR1_CAV_METERDETL.MULT,

[quoted text clipped - 33 lines]