Re: Calculated Field

On 28 Nov 2005 21:12:53 -0800, "rech340" <rech340@xxxxxxxxxxx> wrote:

>Hi,
>
>I have a query with a calculated field called Export.
>
>Export looks like this: Export: "IWTP" & "-" & [CourseID] & "-" &
>[VendorID]
>
>The CourseID is a 4-digit autonumber field with a format of 0000, and
>VendorID is 3-digit autonumber field with a format of 000. They work
>fine when you're looking at individual field in the query. It's when I
>put them together in Export calculated field, it drops the leading
>zeros from CourseID and VendorID. I have tried everything to get this
>to work.
>
>Any help is greatly appreciated.

Try using the Format() function to explicitly set the displayed size:

Export: "IWTP-" & Format([CourseID], "0000") & Format([VendorID],
"\-000")

I'm saving a couple of quoted strings by including the first hyphen in
the text string, and the second as a literal value in the Format.

John W. Vinson[MVP]
.

Relevant Pages

  • Calculated Field
    ... VendorID is 3-digit autonumber field with a format of 000. ... zeros from CourseID and VendorID. ...
    (microsoft.public.access.queries)
  • RE: Sequencing numbers
    ... To have 3 digits the format should be 000 ... >The user wants it to be in a similar format with the zeros ... I also have a hidden Autonumber field in the table that is ...
    (microsoft.public.access.forms)
  • AutoNumber field in VBA
    ... I'm trying to add an AutoNumber field to a spreadsheet I've imported into a ... I'm adding a couple of columns using the following format: ... tatus", adInteger) ...
    (microsoft.public.access.modulesdaovba)
  • Parameter query doesnt work with my format
    ... lab codes to begin with 40000. ... parameter query with this format, or to set up my #format ...
    (microsoft.public.access.queries)
  • Autonumber
    ... I want to add an Autonumber field to a table and its form. ... I want the format of it to be six digits no matter what the number (ie - ...
    (microsoft.public.access.tablesdbdesign)