Query help

[4Wile]

I have qryTest as:
INSERT INTO TestInTbl ( TestinDate )
SELECT DatePart("ww",DateValue(Mid([TestOutDate],5,2) & "/" & Right([TestOutDate],2) & "/" & Left([TestOutDate],4))) AS TestInDate
FROM TestOutTbl;

My Environment is:
TestInTbl: TestInDate Number 0
TestOutLbl: TestOutLbl Text "20000220"

Compile error, in query expression 'DatePart("ww"....

It should try to make a Text date to Number week no.

Bob Foreman
4Wile@mediaone.net

 

[scking]

The format for the INSERT statement is:

INSERT INTO tblname(fld1, fld2) VALUES (fld1Val, fld2Val);

You have excluded the VALUES clause. I believe if you place your SELECT statement inside the VALUES parens it may work. I didn't evaluate the SELECT statement to determine if it was correct.

Steve King Growth follows a healthy professional curiosity

 

[4Wile]

scking.

I'm lost..Here's the query:
INSERT INTO TestInTbl ( TestinDate )
SELECT ( DatePart("ww",DateValue(Mid([TestOutDate],5,2) & "/" & Right([TestOutDate],2) & "/" & Left([TestOutDate],4))) AS TestInDate
FROM TestOutTbl;

Where does the VALUE go and the "()" go..I'm a Newbie
Bob Foreman
4Wile@mediaone.net

 

[scking]

If you are a Newbie how did you come up with such a complex SQL statement?

INSERT INTO TestInTbl (TestinDate)
VALUES(#Date()#);

This would insert the current date into the TestinDate field of the TestInTbl. You are apparently trying to construct a string data value from the TestOutDate, which must be a text field, and getting the week of the year. DateValue apparently tries to convert the string to a date variable but it is converted to a variant and I'm not sure DatePart will work with a variant.

Try your SQL statement in the query builder and when it works paste it between the parens for the VALUES clause.

Steve King

Growth follows a healthy professional curiosity

 

[tlbroadbent]

The basic query structure is correct. The example below is a basic Append query in Access.

INSERT INTO Table2
SELECT Table1.*
FROM table1;

Bob's entire query looks like it should work as long as the column and table names are spelled correctly. Perhaps, the references are broken. Check the following article.

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

Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.