selecting a value based on a date

[Guest]

More date related problems in ACCESS 2003

My table has a Date field called "DiveDate" where we store the date a dive
was done and a Date field called "DateChart" where we store the date the
chart pertaining to that dive was received. I need to track that all charts
are received within 28 days following the end of the month the dive took
place.
I use 2 query
In the first query, I have a function that allows me to figure out the last
day of the month for that date and i add 28 days so i know the date i should
have received the chart. It looks like this:
DueDateChart: DateAdd("d",28,(lastofMonth([HarvestDate])))
Everything works beautifully.
I use that query as an input to the second query that i need to know how
many days elapsed between the duedatechart and the day the dive took place.
The calculation I do looks like this:
WaitDays: DateDiff("d",[duedateChart],[DateChart])

It works beautifully. The waitDays show positive numbers if chart were sent
in the allowed time frame , and minus numbers when they were not. All i need
are the Minus number. But i can't get that part to work.
If is use a criteria of > 0 in the waitDays column, i get the message: "you
tried and execute a query that doesn't include the specified expression
datedif etc..
as part of an aggregate function".

So I try adding a WHERE clause and the error message now reads:
"DataMismatch in Criteria expression"

The SQL with the WHERE clause is:

if I add a where clause so now the SQL is
SELECT qryChartDueDate.Vessel, qryChartDueDate.Tab,
qryChartDueDate.DiveDate, qryChartDueDate.DateChart,
qryChartDueDate.DueDateChart, DateDiff("d",[duedateChart],[DateChart]) AS
WaitDays
FROM tblLicense INNER JOIN qryChartDueDate ON tblLicense.LicenseTab =
qryChartDueDate.Tab
WHERE (((DateDiff("d",[duedateChart],[DateChart]))>0))
GROUP BY qryChartDueDate.Vessel, qryChartDueDate.Tab,
qryChartDueDate.DiveDate, qryChartDueDate.DateChart,
qryChartDueDate.DueDateChart, DateDiff("d",[duedateChart],[DateChart]);

I don't use a report to output the query. I just copy the datasheet view
into Excel and do some calculations.

SORRY for such a long and confusing message. But i'm so frustrated i can't
summarize my thoughts.

Thank you.

 

[Guest]

SELECT DISTINCT qryChartDueDate.Vessel,
qryChartDueDate.Tab,
qryChartDueDate.DiveDate,
qryChartDueDate.DateChart,
qryChartDueDate.DueDateChart,
DateDiff("d", [duedateChart], [DateChart]) AS WaitDays
FROM tblLicense INNER JOIN qryChartDueDate ON tblLicense.LicenseTab =
qryChartDueDate.Tab
WHERE Fix([DateChart] - [duedateChart])>0 ;

I saw no reason for the Group By's unless there are duplicates so I put in a
DISTINCT clause to get rid of dupes. You might need to swap the two fields in
the Where clause.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

More date related problems in ACCESS 2003

My table has a Date field called "DiveDate" where we store the date a dive
was done and a Date field called "DateChart" where we store the date the
chart pertaining to that dive was received. I need to track that all charts
are received within 28 days following the end of the month the dive took
place.
I use 2 query
In the first query, I have a function that allows me to figure out the last
day of the month for that date and i add 28 days so i know the date i should
have received the chart. It looks like this:
DueDateChart: DateAdd("d",28,(lastofMonth([HarvestDate])))
Everything works beautifully.
I use that query as an input to the second query that i need to know how
many days elapsed between the duedatechart and the day the dive took place.
The calculation I do looks like this:
WaitDays: DateDiff("d",[duedateChart],[DateChart])

It works beautifully. The waitDays show positive numbers if chart were sent
in the allowed time frame , and minus numbers when they were not. All i need
are the Minus number. But i can't get that part to work.
If is use a criteria of > 0 in the waitDays column, i get the message: "you
tried and execute a query that doesn't include the specified expression
datedif etc..
as part of an aggregate function".

So I try adding a WHERE clause and the error message now reads:
"DataMismatch in Criteria expression"

The SQL with the WHERE clause is:

if I add a where clause so now the SQL is
SELECT qryChartDueDate.Vessel, qryChartDueDate.Tab,
qryChartDueDate.DiveDate, qryChartDueDate.DateChart,
qryChartDueDate.DueDateChart, DateDiff("d",[duedateChart],[DateChart]) AS
WaitDays
FROM tblLicense INNER JOIN qryChartDueDate ON tblLicense.LicenseTab =
qryChartDueDate.Tab
WHERE (((DateDiff("d",[duedateChart],[DateChart]))>0))
GROUP BY qryChartDueDate.Vessel, qryChartDueDate.Tab,
qryChartDueDate.DiveDate, qryChartDueDate.DateChart,
qryChartDueDate.DueDateChart, DateDiff("d",[duedateChart],[DateChart]);

I don't use a report to output the query. I just copy the datasheet view
into Excel and do some calculations.

SORRY for such a long and confusing message. But i'm so frustrated i can't
summarize my thoughts.

Thank you.

 

[Guest]

Thank you for your suggestion.
I added the distinct to the select clause.
Then i added the Where > 0 in the DateChart and having >0 in the "WaitDays:
DateDiff("d",[duedateChart],[DateChart])' column
I still get the message "you tried to execute a query that does not include
the specified expression 'WaitDays: DateDiff("d",[duedateChart],[DateChart])>
0' as part of an aggregate function.
SO i tried replacing the having clause by the WHERE clause in the Waitdays
field,
then i get the error 'data type mismatch in criteria expression. Yet
waitDays should be a number, at least the content of the field in the
datasheet view is a number.
I just can't imagine what would the problem would be.

Sylvie

SELECT DISTINCT qryChartDueDate.Vessel,
qryChartDueDate.Tab,
qryChartDueDate.DiveDate,
qryChartDueDate.DateChart,
qryChartDueDate.DueDateChart,
DateDiff("d", [duedateChart], [DateChart]) AS WaitDays
FROM tblLicense INNER JOIN qryChartDueDate ON tblLicense.LicenseTab =
qryChartDueDate.Tab
WHERE Fix([DateChart] - [duedateChart])>0 ;

I saw no reason for the Group By's unless there are duplicates so I put in a
DISTINCT clause to get rid of dupes. You might need to swap the two fields in
the Where clause.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

More date related problems in ACCESS 2003

My table has a Date field called "DiveDate" where we store the date a dive
was done and a Date field called "DateChart" where we store the date the
chart pertaining to that dive was received. I need to track that all charts
are received within 28 days following the end of the month the dive took
place.
I use 2 query
In the first query, I have a function that allows me to figure out the last
day of the month for that date and i add 28 days so i know the date i should
have received the chart. It looks like this:
DueDateChart: DateAdd("d",28,(lastofMonth([HarvestDate])))
Everything works beautifully.
I use that query as an input to the second query that i need to know how
many days elapsed between the duedatechart and the day the dive took place.
The calculation I do looks like this:
WaitDays: DateDiff("d",[duedateChart],[DateChart])

It works beautifully. The waitDays show positive numbers if chart were sent
in the allowed time frame , and minus numbers when they were not. All i need
are the Minus number. But i can't get that part to work.
If is use a criteria of > 0 in the waitDays column, i get the message: "you
tried and execute a query that doesn't include the specified expression
datedif etc..
as part of an aggregate function".

So I try adding a WHERE clause and the error message now reads:
"DataMismatch in Criteria expression"

The SQL with the WHERE clause is:

if I add a where clause so now the SQL is
SELECT qryChartDueDate.Vessel, qryChartDueDate.Tab,
qryChartDueDate.DiveDate, qryChartDueDate.DateChart,
qryChartDueDate.DueDateChart, DateDiff("d",[duedateChart],[DateChart]) AS
WaitDays
FROM tblLicense INNER JOIN qryChartDueDate ON tblLicense.LicenseTab =
qryChartDueDate.Tab
WHERE (((DateDiff("d",[duedateChart],[DateChart]))>0))
GROUP BY qryChartDueDate.Vessel, qryChartDueDate.Tab,
qryChartDueDate.DiveDate, qryChartDueDate.DateChart,
qryChartDueDate.DueDateChart, DateDiff("d",[duedateChart],[DateChart]);

I don't use a report to output the query. I just copy the datasheet view
into Excel and do some calculations.

SORRY for such a long and confusing message. But i'm so frustrated i can't
summarize my thoughts.

Thank you.