field with date formatted as string

[john lawlor]

I'm new (1 month) to Access and SQL.
I have a query that works:

SELECT tbl_Insertion.Date_Completed,
Format([Date_Completed],"yy mmm") AS ins_yymmm
FROM tbl_Insertion;

It returns e.g. 09 Jan.

How do I Insert this field into the same table?

Thanks in advance,
John

 

[John W. Vinson]

I agree - but I inherited this report/task which involves a series of
workarounds in Access, Ascii and data manipulation and charting Excel, even
though the data is entered in SQL Server.

The variable is required for the rest of it.

Um... No. It's not.

You can base "the rest of it" on a Query just exactly as easily as you can
base it on a Table. Storing the data redundantly in the table will give you no
benefit whatsoever.

 

[john lawlor]

John,

I agree, but while I am learning the sql to do that, I still need to put out
a monthly report this week.

Could you please answer the question asked.

Thank you,
John L

 

[John W. Vinson]

Could you please answer the question asked.

UPDATE tbl_Insertion
SET RedundantDuplicateDate= Format([Date_Completed],"yy mmm");

You'll need two fields in the table, a Text field named RedundantDuplicateDate
and a Date/Time field named Date_Completed.

 

[john lawlor]

First - Thank You!

Second, I had got as far as

ALTER TABLE tbl_Insertion
ADD COLUMN ins_yymm text(6);

to add the column.

New Question: am I correct in that it is not possible to insert the blank
variable and calculate the 'redundant' string in one query?

Is there a way of running several queries in one saved query? For example,
perform this stupidity in 3 tables?

John L

Could you please answer the question asked.

UPDATE tbl_Insertion
SET RedundantDuplicateDate= Format([Date_Completed],"yy mmm");

You'll need two fields in the table, a Text field named RedundantDuplicateDate
and a Date/Time field named Date_Completed.

 

[John W. Vinson]

First - Thank You!

Second, I had got as far as

ALTER TABLE tbl_Insertion
ADD COLUMN ins_yymm text(6);

to add the column.

New Question: am I correct in that it is not possible to insert the blank
variable and calculate the 'redundant' string in one query?

You are correct. You're not inserting a "blank variable"; you're altering the
actual structure of your table. Changing the values in the newly structured
table is a separate operation.

Is there a way of running several queries in one saved query? For example,
perform this stupidity in 3 tables?

Nope. You can add the new field in the Table design view rather than running
your DDL query if you prefer.

 

[john lawlor]

The 'stupidity' worked and the report goes out today, on time - thank you! I
used my first queries, first macro and first VBA module to automate the
current UGH! - Access to Access import, form design date2 insert, copy date
to excel and format as yy/mm, copy to ascii and back into Access as string
yy/mm.

Now, I want to do this properly e.g.

this query uses the ab-normal string yy/mm Date_Completed2 variable.

EXACTLY how do I use format(Date_Completed,yy/mm) to replace
Date_Completed2 in the following query:

TRANSFORM Avg([**Maintenance Compliance By Hospital for Charts].c) AS AvgOfc
SELECT [**Maintenance Compliance By Hospital for Charts].Date_Completed2,
Avg([**Maintenance Compliance By Hospital for Charts].c) AS [Total Of c]
FROM [**Maintenance Compliance By Hospital for Charts]
GROUP BY [**Maintenance Compliance By Hospital for Charts].Date_Completed2
PIVOT [**Maintenance Compliance By Hospital for Charts].UnitID;


Thanks in advance again,
John