Append Query Problem

[Guest]

I have an append query that will not work. I can't figure out where the
problem is. When I run the query, an error message about type conversion
failure, or key violation, or lock violation, or validation rule violation
appears.

Here is the SQL statment for the append query:

INSERT INTO tblWeeklyWorkedHours ( UnitID, VacationHours, HolidayHours,
CompHoursUsed, InjuryHours, IllnessHours, WorkWeek )
SELECT tblDailyWorkLog.UnitID, Sum(tblDailyWorkLog.VacationHours) AS
SumOfVacationHours, Sum(tblDailyWorkLog.HolidayHours) AS SumOfHolidayHours,
Sum(tblDailyWorkLog.CompHoursUsed) AS SumOfCompHoursUsed,
Sum(tblDailyWorkLog.InjuryHours) AS SumOfInjuryHours,
Sum(tblDailyWorkLog.IllnessHours) AS SumOfIllnessHours, Format([Date],"ww")
AS Week
FROM tblDailyWorkLog
WHERE (((tblDailyWorkLog.WorkStatusCode)>"2") AND
((tblDailyWorkLog.DateWorked) Between [Enter Sunday of Week Again] And [Enter
Saturday of the Reporting Week]))
GROUP BY tblDailyWorkLog.UnitID;

Here is a list of the field names for my table to append to:

UnitID Text Size=4
WorkWeek Date/Time Format= ww
DailyHours Worked Number Double
VacationHours Number Double
HolidayHours Number Double
CompHoursUsed Number Double
InjuryHours Number Double
IllnessHours Number Double

Currently no Primay Key set. It will be UNITID + WORKWEEK.

The fields used in the query have the same data type and format as the table
to be appended to. The WorkWeek is coming from the DateWorked which is a
Date/Time field.

I can get the query to return the values I need when the query is shown in
the Datasheet view, but receive the errror message when I RUN the append
query.

Thanks for your help!

 

[John Vinson]

I have an append query that will not work. I can't figure out where the
problem is. When I run the query, an error message about type conversion
failure, or key violation, or lock violation, or validation rule violation
appears.

The WorkWeek field in your target table is a Date/Time field; its
format is completely irrelevant - it's still a Date/Time, and the only
acceptable values for it are dates. Format(<datefield>, "ww") returns
a two-character String, not a date/time. I'd suggest just appending
the date; displaying it with the ww format will *show* you the week
regardless of the actual content.

John W. Vinson[MVP]

 

[Guest]

John,

I changed Format(<datefield>, "ww") TO Format(<datefield>, "Short Date") and
the datasheet view now shows the date, but when I RUN the query the message
states 3 validation rule violations and doesn't append the records. There
are only three records in the datasheet view.

I then tried taking the Format statment out and using the DateWorked field
which is currently the WHERE statement. I then tried to append DateWorked to
WorkWeek. DateWorked is a Date/Time field showing Short Date Format.
However, I received an error stating "you tried to execute a query that does
not include the specified expression "DateWorked" as part of an aggregate
function.

What can you suggest that will help me past this delima?

Thanks for your patience!

 

[John Vinson]

John,

I changed Format(<datefield>, "ww") TO Format(<datefield>, "Short Date") and
the datasheet view now shows the date, but when I RUN the query the message
states 3 validation rule violations and doesn't append the records. There
are only three records in the datasheet view.

If you're inserting into a Date/TIme field you need the syntactially
required # delimiters. Try changing it to

Format(<datefield>, "\#mm/dd/yyyy\#")


John W. Vinson[MVP]