Shorten a 7 digit number in a text field

[DLT]

I have a text field with 7 digit numbers in it (none starting with zero)
called 'USE_DCAT'. I want to run a query that shortens the 7 digits to the
first 3. I am using: Left ([qryRPI.USE_DCAT], 3) but this returns nothing.
Any help would be greatly appreciated.

 

[(PeteCresswell)]

Per DLT:

I have a text field with 7 digit numbers in it (none starting with zero)
called 'USE_DCAT'. I want to run a query that shortens the 7 digits to the
first 3. I am using: Left ([qryRPI.USE_DCAT], 3) but this returns nothing.
Any help would be greatly appreciated.

The syntax looks unfamiliar to me.

How are you referencing a field within a query?

I think in terms of opening up a recordset which is populated by
a query.

Set myRS = CurrentDB("SELECT * FROM tblWhatever")

Then I'd expect Left$(myRS!USE_DCAT, 3) to work.

 

[DLT]

A little more info:

The field 'USER_DCAT' is in a table. I'm using a query to sum the square
footage of the 'USER_DCAT' field but I want to sum the SF from the three
digit not the seven digit.

For instance,
USER_DCAT = 1312000 with a SF of 10
USER_DCAT = 1313000 with a SF of 10
USER_DCAT = 4422000 with a SF of 50

The result I want to see is:

USER_DCAT:
131= 20
442 = 50
'7 digits shortened to three, and the SF summed. The SUM part I get and that
works fine. It's getting the 7 digits to 3 that doesn't work.

Does this help? Sorry for the confusion.

 

[Conan Kelly]

DLT,

Your table qualifyer and your column name both need square brackets around
them. You don't put one pair around both. If this is a typo, ignore this
suggestion.

Change...

Left ([qryRPI.USE_DCAT], 3)

....to...

Left ([qryRPI].[USE_DCAT], 3)

If it is in there like Left ([qryRPI.USE_DCAT], 3), I would expect Access to
propmt you for the value of [qryRPI.USE_DCAT] each time it is run.

If that doesn't solve your problems, please write back.

HTH,

Conan

 

[(PeteCresswell)]

Per DLT:

Does this help? Sorry for the confusion.

Paste the query's SQL into a reply and somebody'll nail it in a
heartbeat.

 

[DLT]

Here is the SQL right now.

SELECT RPI.USE_DCAT AS FCG, Sum(RPI.ASSET_CONFIG_PRIMARY_QTY) AS AREA,
RPI.PRIMARY_UM AS SF
FROM RPI
GROUP BY RPI.USE_DCAT, RPI.PRIMARY_UM
HAVING (((RPI.USE_DCAT)=Left([RPI]![USE_DCAT],3)) AND ((RPI.PRIMARY_UM)="sf"))
ORDER BY RPI.USE_DCAT;

Thanks for any help ahead of time.

 

[Douglas J. Steele]

Your SQL is saying to limit the output to those rows where RPI.USE_DCAT is
equal to only the first three characters of RPI.USE_DCAT

Try

SELECT Left(RPI.USE_DCAT, 3) AS FCG, Sum(RPI.ASSET_CONFIG_PRIMARY_QTY) AS
AREA,
RPI.PRIMARY_UM AS SF
FROM RPI
WHERE RPI.PRIMARY_UM="sf"
GROUP BY Left(RPI.USE_DCAT, 3), RPI.PRIMARY_UM
ORDER BY RPI.USE_DCAT;

Note that I changed the HAVING clause to a WHERE clause. WHERE clauses get
applied before the calculations get done. In other words, it's only going to
aggregate values where PRIMARY_UM is "sf". HAVING clauses get applied after
the calculations. The onlyl time you need a HAVING clause is when you're
trying to limit based on the calculation (such as only return details where
the sum is greater than a certain threshold value)

 

[DLT]

Issue resolved. I was trying to "Build" a function in the criteria rather
than using the function in the Field Name of the query design view.

Thanks for you help, all of you.

DLT