Frustrating Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all

There should be a prize for this one.

I have two identical queries, each one referencing a different table. That
is the only difference. Below are the queries. The 1st one works nice and
dandy the second just ignores the where clause altogether. n u help??

INSERT INTO ArchiveEmployeeVacations
SELECT EmployeeVacations.*
FROM (Employees INNER JOIN EmployeeVacations ON Employees.EmployeeID =
EmployeeVacations.EmployeeID) INNER JOIN [Temporary] ON
EmployeeVacations.EmployeeID = Temporary.EmployeeID
WHERE (((EmployeeVacations.From)<=([employees].[archivecalcdate])));

INSERT INTO ArchiveHolidayNotTaken
SELECT HolidayNotTaken.*
FROM (Employees INNER JOIN HolidayNotTaken ON Employees.EmployeeID =
HolidayNotTaken.EmployeeID) INNER JOIN [Temporary] ON
HolidayNotTaken.EmployeeID = Temporary.EmployeeID
WHERE (((HolidayNotTaken.HolDate)<=([employees].[archivecalcdate])));

Thanks
 
Is HolidayNotTaken.HolDate an actual date field in the table or just
something that looks like a date in a text field?
 
Hi Jerry

HolDate is a lookup field to a holiday table's date field. In the structure
it is shown as a long Integer

Thanks
Jerry Whittle said:
Is HolidayNotTaken.HolDate an actual date field in the table or just
something that looks like a date in a text field?
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


alex said:
Hi all

There should be a prize for this one.

I have two identical queries, each one referencing a different table. That
is the only difference. Below are the queries. The 1st one works nice and
dandy the second just ignores the where clause altogether. n u help??

INSERT INTO ArchiveEmployeeVacations
SELECT EmployeeVacations.*
FROM (Employees INNER JOIN EmployeeVacations ON Employees.EmployeeID =
EmployeeVacations.EmployeeID) INNER JOIN [Temporary] ON
EmployeeVacations.EmployeeID = Temporary.EmployeeID
WHERE (((EmployeeVacations.From)<=([employees].[archivecalcdate])));

INSERT INTO ArchiveHolidayNotTaken
SELECT HolidayNotTaken.*
FROM (Employees INNER JOIN HolidayNotTaken ON Employees.EmployeeID =
HolidayNotTaken.EmployeeID) INNER JOIN [Temporary] ON
HolidayNotTaken.EmployeeID = Temporary.EmployeeID
WHERE (((HolidayNotTaken.HolDate)<=([employees].[archivecalcdate])));

Thanks
Click to expand...
Click to expand...
 
Hi again Jerry

your question made me think about the field type. So I converted the field
to a date without being a lookup filed and the querry worked fine. But now a
problem arises, that field must be a lookup to be able to populate that table
since I have holiday table. I created a dummy field in the table that is of
date type, but setting it up to receive data from the original holdate field
brings in the wrong dates, (they all come up with the wrong manth and day and
year 1900 which is what the querry was probably doing that's why it was still
going through and bringing in everything)

Any suggestions?


Jerry Whittle said:
Is HolidayNotTaken.HolDate an actual date field in the table or just
something that looks like a date in a text field?
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


alex said:
Hi all

There should be a prize for this one.

I have two identical queries, each one referencing a different table. That
is the only difference. Below are the queries. The 1st one works nice and
dandy the second just ignores the where clause altogether. n u help??

INSERT INTO ArchiveEmployeeVacations
SELECT EmployeeVacations.*
FROM (Employees INNER JOIN EmployeeVacations ON Employees.EmployeeID =
EmployeeVacations.EmployeeID) INNER JOIN [Temporary] ON
EmployeeVacations.EmployeeID = Temporary.EmployeeID
WHERE (((EmployeeVacations.From)<=([employees].[archivecalcdate])));

INSERT INTO ArchiveHolidayNotTaken
SELECT HolidayNotTaken.*
FROM (Employees INNER JOIN HolidayNotTaken ON Employees.EmployeeID =
HolidayNotTaken.EmployeeID) INNER JOIN [Temporary] ON
HolidayNotTaken.EmployeeID = Temporary.EmployeeID
WHERE (((HolidayNotTaken.HolDate)<=([employees].[archivecalcdate])));

Thanks
Click to expand...
Click to expand...
 
Back
Top