Help needed with Date Add function

C

CP

I have managed to get myself completey confused please help
I am trying to work out my recall time on listed equipment (when is it
overdue)

Here are my fields
[Plant] - with unique ID
[Date] - date of last service
[Scale] - time in months of next recall (ie 6 or 12)

So lets assume its the 1st Jan, I need something to show anything that is
due/overdue from the end of the calender month (Jan 31st) using the original
service date+time scale which will also pick up those still not sorted from
previous months (Dec/Nov etc) - if that makes sense

many thanks
 
J

John Spencer

SELECT Plant, [Date], Scale
FROM [Your Table]
WHERE DateSerial(Year([Date]), Month([Date]) + 1 + [Scale],0) <
DateSerial(Year(Date()),Month(Date())+1,0)

In the query grid, you need to add a calculated field
Field: DateSerial(Year([Date]), Month([Date]) + 1 + [Scale],0)
Criteria: < DateSerial(Year(Date()),Month(Date())+1,0)

By the way Date is a bad name for a field. It is the name of the function
Date() and as such can get Access confused as to whether you want the Date()
function (returns today's date) or the field named date. If possible I
would suggest you rename the field ServiceDate or LastServiceDate or some
other variation.

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
C

CP

Ok I can see it how its getting there but it failed!!

this is my starting point

SELECT Equipment.PlantNo, Equipment.LastCal, Equipment.Scale
FROM Equipment
WHERE DateSerial(Year([LastCal]), Month([LastCal]) + 1 + [Scale],0) <
DateSerial(Year(Date()),Month(Date())+1,0);

The missing colon caused one or two issues, but with the above I get the
error "Data Type Mismatch"

John Spencer said:
SELECT Plant, [Date], Scale
FROM [Your Table]
WHERE DateSerial(Year([Date]), Month([Date]) + 1 + [Scale],0) <
DateSerial(Year(Date()),Month(Date())+1,0)

In the query grid, you need to add a calculated field
Field: DateSerial(Year([Date]), Month([Date]) + 1 + [Scale],0)
Criteria: < DateSerial(Year(Date()),Month(Date())+1,0)

By the way Date is a bad name for a field. It is the name of the function
Date() and as such can get Access confused as to whether you want the Date()
function (returns today's date) or the field named date. If possible I
would suggest you rename the field ServiceDate or LastServiceDate or some
other variation.

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

CP said:
I have managed to get myself completey confused please help
I am trying to work out my recall time on listed equipment (when is it
overdue)

Here are my fields
[Plant] - with unique ID
[Date] - date of last service
[Scale] - time in months of next recall (ie 6 or 12)

So lets assume its the 1st Jan, I need something to show anything that is
due/overdue from the end of the calender month (Jan 31st) using the
original
service date+time scale which will also pick up those still not sorted
from
previous months (Dec/Nov etc) - if that makes sense

many thanks
Click to expand...
Click to expand...
 
J

John Spencer

You could have problems if LastCal or Scale is null. I also assumed that
LastCal is a datetime field and Scale is a number field. If they are not
then this could cause problems.

QueryOne:
SELECT Equipment.PlantNo, Equipment.LastCal, Equipment.Scale
FROM Equipment
WHERE LastCal is Not Null AND Scale is not Null

SELECT PlantNo, LastCal, Scale
FROM QueryOne
WHERE DateSerial(Year([LastCal]), Month([LastCal]) + 1 + [Scale],0) <
DateSerial(Year(Date()),Month(Date())+1,0)

You might be able to do that all in one query with

SELECT PlantNo, LastCal, Scale
FROM (
SELECT Equipment.PlantNo, Equipment.LastCal, Equipment.Scale
FROM Equipment
WHERE LastCal is Not Null AND Scale is not Null
) as Temp
WHERE DateSerial(Year([LastCal]), Month([LastCal]) + 1 + [Scale],0) <
DateSerial(Year(Date()),Month(Date())+1,0)

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

CP said:
Ok I can see it how its getting there but it failed!!

this is my starting point

SELECT Equipment.PlantNo, Equipment.LastCal, Equipment.Scale
FROM Equipment
WHERE DateSerial(Year([LastCal]), Month([LastCal]) + 1 + [Scale],0) <
DateSerial(Year(Date()),Month(Date())+1,0);

The missing colon caused one or two issues, but with the above I get the
error "Data Type Mismatch"

John Spencer said:
SELECT Plant, [Date], Scale
FROM [Your Table]
WHERE DateSerial(Year([Date]), Month([Date]) + 1 + [Scale],0) <
DateSerial(Year(Date()),Month(Date())+1,0)

In the query grid, you need to add a calculated field
Field: DateSerial(Year([Date]), Month([Date]) + 1 + [Scale],0)
Criteria: < DateSerial(Year(Date()),Month(Date())+1,0)

By the way Date is a bad name for a field. It is the name of the
function
Date() and as such can get Access confused as to whether you want the
Date()
function (returns today's date) or the field named date. If possible I
would suggest you rename the field ServiceDate or LastServiceDate or some
other variation.

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

CP said:
I have managed to get myself completey confused please help
I am trying to work out my recall time on listed equipment (when is it
overdue)

Here are my fields
[Plant] - with unique ID
[Date] - date of last service
[Scale] - time in months of next recall (ie 6 or 12)

So lets assume its the 1st Jan, I need something to show anything that
is
due/overdue from the end of the calender month (Jan 31st) using the
original
service date+time scale which will also pick up those still not sorted
from
previous months (Dec/Nov etc) - if that makes sense

many thanks
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

Another way to handle the problem of nulls in Date or Scale. Again the
assumption is that [Date] is a datetime field and Scale is a number field of
some type (preferably Integer, Long Integer, or Byte).

Field: IIF([Date] is not null and Scale is Not Null,
DateSerial(Year([Date]), Month([Date]) + 1 + [Scale],0),Null)
Criteria: < DateSerial(Year(Date()),Month(Date())+1,0)

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

John Spencer said:
You could have problems if LastCal or Scale is null. I also assumed that
LastCal is a datetime field and Scale is a number field. If they are not
then this could cause problems.

QueryOne:
SELECT Equipment.PlantNo, Equipment.LastCal, Equipment.Scale
FROM Equipment
WHERE LastCal is Not Null AND Scale is not Null

SELECT PlantNo, LastCal, Scale
FROM QueryOne
WHERE DateSerial(Year([LastCal]), Month([LastCal]) + 1 + [Scale],0) <
DateSerial(Year(Date()),Month(Date())+1,0)

You might be able to do that all in one query with

SELECT PlantNo, LastCal, Scale
FROM (
SELECT Equipment.PlantNo, Equipment.LastCal, Equipment.Scale
FROM Equipment
WHERE LastCal is Not Null AND Scale is not Null
) as Temp
WHERE DateSerial(Year([LastCal]), Month([LastCal]) + 1 + [Scale],0) <
DateSerial(Year(Date()),Month(Date())+1,0)

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
.

CP said:
Ok I can see it how its getting there but it failed!!

this is my starting point

SELECT Equipment.PlantNo, Equipment.LastCal, Equipment.Scale
FROM Equipment
WHERE DateSerial(Year([LastCal]), Month([LastCal]) + 1 + [Scale],0) <
DateSerial(Year(Date()),Month(Date())+1,0);

The missing colon caused one or two issues, but with the above I get the
error "Data Type Mismatch"

John Spencer said:
SELECT Plant, [Date], Scale
FROM [Your Table]
WHERE DateSerial(Year([Date]), Month([Date]) + 1 + [Scale],0) <
DateSerial(Year(Date()),Month(Date())+1,0)

In the query grid, you need to add a calculated field
Field: DateSerial(Year([Date]), Month([Date]) + 1 + [Scale],0)
Criteria: < DateSerial(Year(Date()),Month(Date())+1,0)

By the way Date is a bad name for a field. It is the name of the
function
Date() and as such can get Access confused as to whether you want the
Date()
function (returns today's date) or the field named date. If possible I
would suggest you rename the field ServiceDate or LastServiceDate or
some
other variation.

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I have managed to get myself completey confused please help
I am trying to work out my recall time on listed equipment (when is it
overdue)

Here are my fields
[Plant] - with unique ID
[Date] - date of last service
[Scale] - time in months of next recall (ie 6 or 12)

So lets assume its the 1st Jan, I need something to show anything that
is
due/overdue from the end of the calender month (Jan 31st) using the
original
service date+time scale which will also pick up those still not sorted
from
previous months (Dec/Nov etc) - if that makes sense

many thanks
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help with julian date? 2
help with syntax for a date range criteria please 3
Problem with between date function 2
help with formula to calculate what column to use 2
Problem with Calculated Date 7
calender help, Pls, for tracking contractual items 0
Help with a date calculation 1
If no records, value = 0 2

Top