Data formatting

  • Thread starter Thread starter Pharoh
  • Start date Start date
P

Pharoh

The data that the following query is retrieving includes numerical data
that's formatted as shown here 24:24.12.

Is there a way to get this data formatted without the : . and 12 in it
so it appears as just 2424?

SELECT Extract_Current.[BCI DIN], Extract_Current.[BCI SUBCLASS]
FROM Extract_Current LEFT JOIN CRS_EHB_DIN ON Extract_Current.[BCI DIN]
= CRS_EHB_DIN.DIN
WHERE (((CRS_EHB_DIN.DIN) Is Null))
ORDER BY Extract_Current.[BCI DIN];

Thanking you in advance!
 
The data that the following query is retrieving includes numerical data
that's formatted as shown here 24:24.12.

Is there a way to get this data formatted without the : . and 12 in it
so it appears as just 2424?

SELECT Extract_Current.[BCI DIN], Extract_Current.[BCI SUBCLASS]
FROM Extract_Current LEFT JOIN CRS_EHB_DIN ON Extract_Current.[BCI DIN]
= CRS_EHB_DIN.DIN
WHERE (((CRS_EHB_DIN.DIN) Is Null))
ORDER BY Extract_Current.[BCI DIN];

Thanking you in advance!
Click to expand...


Is it always 2 characters colon 2 characters?
Here is one method:

Select Left([Tablename].[FieldName],2) &
Mid([TableName].[FieldName],4,2) from TableName etc....

-
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
 
yes, it's always 2 characters, colon, 2 characters, period two
characters.

I just want to remove the colon, the period and the last two numbers.

I tried this statement but it didn't work..not sure if I missed
something though.


Select Left([Extract_Current].[BCI Subclass],2) &
Mid([Extract].[BCI Subclass],4,2) from Extract_Current
 
yes, it's always 2 characters, colon, 2 characters, period two
characters.

I just want to remove the colon, the period and the last two numbers.

I tried this statement but it didn't work..not sure if I missed
something though.

Select Left([Extract_Current].[BCI Subclass],2) &
Mid([Extract].[BCI Subclass],4,2) from Extract_Current
Click to expand...

Try it this way.

SELECT Left([BCI Subclass],2) & Mid([BCI Subclass],4,2) AS
NewFieldName FROM [Extract_Current];
 
that definately works in terms of concatenating the numbers the way I
need them but is there a way to keep them where they are? The table has
two columns and each record from colum A relates to the record in
Columb B so I was hoping to keep them where they were and in the same
order.
 
that definately works in terms of concatenating the numbers the way I
need them but is there a way to keep them where they are? The table has
two columns and each record from colum A relates to the record in
Columb B so I was hoping to keep them where they were and in the same
order.
Click to expand...

It's always best to include the relevant text of any previous message
when replying. It makes it easier to understand the current message
(especially as I usually quickly forget what the previous message
was).

Do you mean you wish to include additional fields? Just add them,
separated by a comma.

SELECT [Extract_Current].[SomeField], Left([BCI Subclass],2) &
Mid([BCI Subclass],4,2) AS NewColumnName FROM [Extract_Current];
 
Do you mean you wish to include additional fields? Just add them,
separated by a comma.

SELECT [Extract_Current].[SomeField], Left([BCI Subclass],2) &
Mid([BCI Subclass],4,2) AS NewColumnName FROM [Extract_Current];
Click to expand...

sorry...what I meant was this. I have my original query to retrieve the
data but I want to use the 2nd query to format (concatenate) the BCI
Subclass field at the same time.

here are the two statements.

SELECT Extract_Current.[BCI DIN], Extract_Current.[BCI SUBCLASS],
Extract_Current.[TRADE NAME]
FROM Extract_Current LEFT JOIN CRS_EHB_DIN ON Extract_Current.[BCI DIN]
= CRS_EHB_DIN.DIN
WHERE (((CRS_EHB_DIN.DIN) Is Null))
ORDER BY Extract_Current.[BCI DIN];


SELECT Left([BCI Subclass],2) & Mid([BCI Subclass],4,2) AS
NewFieldName FROM [Extract_Current];
 
Do you mean you wish to include additional fields? Just add them,
separated by a comma.

SELECT [Extract_Current].[SomeField], Left([BCI Subclass],2) &
Mid([BCI Subclass],4,2) AS NewColumnName FROM [Extract_Current];
Click to expand...

sorry...what I meant was this. I have my original query to retrieve the
data but I want to use the 2nd query to format (concatenate) the BCI
Subclass field at the same time.

here are the two statements.

SELECT Extract_Current.[BCI DIN], Extract_Current.[BCI SUBCLASS],
Extract_Current.[TRADE NAME]
FROM Extract_Current LEFT JOIN CRS_EHB_DIN ON Extract_Current.[BCI DIN]
= CRS_EHB_DIN.DIN
WHERE (((CRS_EHB_DIN.DIN) Is Null))
ORDER BY Extract_Current.[BCI DIN];

SELECT Left([BCI Subclass],2) & Mid([BCI Subclass],4,2) AS
NewFieldName FROM [Extract_Current];
*********************************
Do you mean you wish to include additional fields? Just add them,
separated by a comma. <<<<<<
Click to expand...
Click to expand...
Just use the left and mid functions in the first query:

SELECT Extract_Current.[BCI DIN], Extract_Current.[BCI SUBCLASS],
Extract_Current.[TRADE NAME], Left([BCI Subclass],2) & Mid([BCI
Subclass],4,2) AS NewFieldName FROM Extract_Current LEFT JOIN
CRS_EHB_DIN ON Extract_Current.[BCI DIN] = CRS_EHB_DIN.DIN
WHERE (((CRS_EHB_DIN.DIN) Is Null))
ORDER BY Extract_Current.[BCI DIN];
 
Just use the left and mid functions in the first query:

SELECT Extract_Current.[BCI DIN], Extract_Current.[BCI SUBCLASS],
Extract_Current.[TRADE NAME], Left([BCI Subclass],2) & Mid([BCI
Subclass],4,2) AS NewFieldName FROM Extract_Current LEFT JOIN
CRS_EHB_DIN ON Extract_Current.[BCI DIN] = CRS_EHB_DIN.DIN
WHERE (((CRS_EHB_DIN.DIN) Is Null))
ORDER BY Extract_Current.[BCI DIN];
Click to expand...

I tried that and YES it does keep the ordering the way I need it but it
only shows the first two numbers. I've been playing with it trying to
get it to change 24:04.12 into just 2404.
 
I got it (with your help of course)!

SELECT Extract_Current.[BCI DIN], Extract_Current.[BCI SUBCLASS],
Extract_Current.[TRADE NAME], Left([BCI Subclass],2) & Mid([BCI
Subclass],4,2) AS NewFieldName FROM Extract_Current LEFT JOIN
CRS_EHB_DIN ON Extract_Current.[BCI DIN] = CRS_EHB_DIN.DIN
WHERE (((CRS_EHB_DIN.DIN) Is Null))
ORDER BY Extract_Current.[BCI DIN];

There was a problem with the Mid function....I think a space or
something in it. Ideally I would have liked to format the original
field in the table as opposed to creating a new field name but I can
just replace it with the new one I guess.
 
Back
Top