? Which Function 1

[WingandaPrayer]

I have a field with 9 digits.
I want to show the first 5 digits.

000010055
to
00001

Thank you

David

 

[tlbroadbent]

If the column is a text filed use the LEFT function.

SELECT LEFT(colname,5) As NewColname

If the column is numeric, you will need a combination of functions - FORMAT and LEFT.

Select LEFT(FORMAT(colname,"000000000&quot,5)

Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[WingandaPrayer]

Terry,
Thanks for the reply.

It is a text field,imported from excel into a table.
Text field due to a few references being aplhanumeric.

The query
SELECT lsvtprop.PropRef, Left([PropRef],5) AS RefOnly
FROM lsvtprop;

It comes up compile error
What's this idiot doing wrong !!!!!


David

 

[tlbroadbent]

What's the error? And which version of Access are you using? Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[tlbroadbent]

You may be missing References. Check this article.

http://support.microsoft.com/support/kb/articles/Q208/2/18.ASP

Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[WingandaPrayer]

Access97.
compile error in query expression 'Left([PropRef],5)'

error 3075 ........are field names and punctuation correct.



Thanks

David

 

[WingandaPrayer]

Terry,

Regarding missing references.
I know Calendar control? has disappeared since the hard drive has been re-formatted.Waiting for disk from IT.
Would this cause the problem?
Other PC's in the office also missing Calendar control.

Thanks

David

 

[tlbroadbent]

Maybe Access 97 requires the use of LEFT$ rather than LEFT. I can't remember. I used to use the $ on these functions all the time but now rutinely omit it.

Sounds like you may actually be missing an OCX or DLL file for the calendar control. Have you tried reinstalling OFFICE to correct any problems? Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[WingandaPrayer]

Terry,

Thanks for your time.
I'll have to wait for our IT department to reload office.
Just looking for original excel workbook to change to 5 digits then import.

Thanks again

David