Formatting the results of a Make Table Query when running it

N

Night Owl

Is it possible to do this, or does it make better sense to format the fields
in a form based on the table?

The code I'm using is...

SELECT DISTINCT tblLinkedData.FSRef, tblLinkedData.Premises,
tblLinkedData.Type INTO tblFileReference
FROM tblLinkedData
GROUP BY tblLinkedData.FSRef, tblLinkedData.Premises, tblLinkedData.Type
HAVING (((tblLinkedData.Premises) Is Not Null))
ORDER BY Premises;

....but I'd prefer the FSRef field formatted as 123/12345/123

TIA,

Pete
 
F

fredg

Is it possible to do this, or does it make better sense to format the fields
in a form based on the table?

The code I'm using is...

SELECT DISTINCT tblLinkedData.FSRef, tblLinkedData.Premises,
tblLinkedData.Type INTO tblFileReference
FROM tblLinkedData
GROUP BY tblLinkedData.FSRef, tblLinkedData.Premises, tblLinkedData.Type
HAVING (((tblLinkedData.Premises) Is Not Null))
ORDER BY Premises;

...but I'd prefer the FSRef field formatted as 123/12345/123

TIA,

Pete

SELECT DISTINCT Format([FSRef],"@@@\/@@@@@\/@@@") AS FSRef2,
tblLinkedData.Premises, tblLinkedData.Type INTO tblFileReference
FROM tblLinkedData
GROUP BY tblLinkedData.FSRef, tblLinkedData.Premises,
tblLinkedData.Type
HAVING (((tblLinkedData.Premises) Is Not Null))
ORDER BY Premises;

After the new table is made, change the name of the field from FSRef2
back to FSRef.
 
N

Night Owl

Excellent, thank you.

Pete

fredg said:
Is it possible to do this, or does it make better sense to format the
fields
in a form based on the table?

The code I'm using is...

SELECT DISTINCT tblLinkedData.FSRef, tblLinkedData.Premises,
tblLinkedData.Type INTO tblFileReference
FROM tblLinkedData
GROUP BY tblLinkedData.FSRef, tblLinkedData.Premises, tblLinkedData.Type
HAVING (((tblLinkedData.Premises) Is Not Null))
ORDER BY Premises;

...but I'd prefer the FSRef field formatted as 123/12345/123

TIA,

Pete

SELECT DISTINCT Format([FSRef],"@@@\/@@@@@\/@@@") AS FSRef2,
tblLinkedData.Premises, tblLinkedData.Type INTO tblFileReference
FROM tblLinkedData
GROUP BY tblLinkedData.FSRef, tblLinkedData.Premises,
tblLinkedData.Type
HAVING (((tblLinkedData.Premises) Is Not Null))
ORDER BY Premises;

After the new table is made, change the name of the field from FSRef2
back to FSRef.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top