math problem with dates.....

[marcokrechting]

Hi All,

I have a rather complex math problem concerning dates.
I will try to explain my problem.

I have a table with the fields SUBJECT (text), DUE DATE (date) and
CHECKED (yes/no). In this table I have some files made up of a name and
date. For example: SUBJECT = "THIS.IS.MY.FIL E", DUE DATE = "20050909",
CHECKED = "false".

Now I want to run a comparison query with another table. In certain
cases the field CHECKED will be marked TRUE and/or the DUE DATE will be
edited.

Example:
Today it is 13 september 2005, so TODAY = Date()
My record has these values:
THIS.IS.MY.FILE 20050909

I run the query and in my second table I have a record with:
THIS.IS.MY.FILE 20050910

The SUBJECT is the same so that's OK. Now the date is bigger than the
DUE DATE.
So I want the DUE DATE changed to this new one 20050910. But the
CHECKED stays false because today it is 20050913. So there might come
other files that day.

Finally when I have a file with TODAY so 20050913 the DUE DATE gets
edited and the CHECKED will become True.

What I need is the If code to do this, for example:

If date in filename > DUE DATE then
change DUE DATE
elseif date in filename < DUE DATE then
do nothing
elseif date in filename > DUE DATE and date in filename = TODAY then
change DUE DATE and CHECKED = True
etc......

I know this is not easy to explain but maybe one of you guys get it....

regards
Marco
THe Netherlands

[Wayne Morgan]

First, this wouldn't be done in a table, it would need to be done in an
update query or form. I assume that there is a common ID field that both
tables use so that you know which record in table 1 goes with which record
in table 2.

If so, it would be possible to run an update query to do this.

UPDATE Table2 INNER JOIN Table1 ON Table2.ID = Table1.ID SET Table1.DueDate
=
IIf([Table2].[DueDate]>[Table1].[DueDate],[Table2].[DueDate],[Table1].[DueDate]),
Table1.[Check] = IIf(([Table2].[DueDate]>[Table1].[DueDate]) And
[Table2].[DueDate]=Date(),True,[Table1].[Check]);
--
Wayne Morgan
MS Access MVP
<ma************ @zonnet.nl> wrote in message
news:11******** **************@ g14g2000cwa.goo glegroups.com.. .

Hi All,

I have a rather complex math problem concerning dates.
I will try to explain my problem.

I have a table with the fields SUBJECT (text), DUE DATE (date) and
CHECKED (yes/no). In this table I have some files made up of a name and
date. For example: SUBJECT = "THIS.IS.MY.FIL E", DUE DATE = "20050909",
CHECKED = "false".

Now I want to run a comparison query with another table. In certain
cases the field CHECKED will be marked TRUE and/or the DUE DATE will be
edited.

Example:
Today it is 13 september 2005, so TODAY = Date()
My record has these values:
THIS.IS.MY.FILE 20050909

I run the query and in my second table I have a record with:
THIS.IS.MY.FILE 20050910

The SUBJECT is the same so that's OK. Now the date is bigger than the
DUE DATE.
So I want the DUE DATE changed to this new one 20050910. But the
CHECKED stays false because today it is 20050913. So there might come
other files that day.

Finally when I have a file with TODAY so 20050913 the DUE DATE gets
edited and the CHECKED will become True.

What I need is the If code to do this, for example:

If date in filename > DUE DATE then
change DUE DATE
elseif date in filename < DUE DATE then
do nothing
elseif date in filename > DUE DATE and date in filename = TODAY then
change DUE DATE and CHECKED = True
etc......

I know this is not easy to explain but maybe one of you guys get it....

regards
Marco
THe Netherlands

[marcokrechting]

Wayne,

It's not exactly what I meant. The record in table 1 will be run
through table two until it finds the record with the same SUBJECT or
name. If it finds this the DUE DATE in table 2 will be compared with
the date in the filename of the record in table 1.
Depending on this comparison the record in table 2 will either be
CHECKED=TRUE and/or the DUE DATE will be changed.

Example:

Today it is: 09 sep 2005.

table1: MYFILENAME.2005 0909
table 2: MYFILENAME ---------- 08 sep ---------- FALSE

table1 filename.date > table2.duedate -----> change table2.duedate to
09 sep and
set table2.checked = true

Example Two:

Today it is: 11 sep 2005.

table1: MYFILENAME.2005 0909
table 2: MYFILENAME ---------- 08 sep ---------- FALSE

table1 filename.date > table2.duedate -----> change table2.duedate to
09 sep and
leave table2.checked = false

This time the table2.due.date will be changed because we have a newer
file but since today is 11 sep we leave checked FALSE. We are still
missing MYFILENAME.2005 0910 and MYFILENAME.2005 0911.

And now in programming code all the possible options.
HTH

Marco

[Wayne Morgan]

Well, the basic concept should still be the same, I just misunderstood which
of the two tables you're wanting to update. Also, it appears that the "date"
in FileName isn't a date, but instead is a string of numbers. If so, this
will need to be converted to a date to make the comparison.

I have swapped the table getting the update and converted the text string to
a date to do the comparisons.

UPDATE Table2 INNER JOIN Table1 ON Table2.Subject = Table1.Subject SET
Table2.DueDate =
IIf(DateSerial( Left([Table1].[MyFileName],4),Mid([Table1].[MyFileName],5,2),Right([Table1].[MyFileName],2))>[Table2].[DueDate],DateSerial(Lef t([Table1].[MyFileName],4),Mid([Table1].[MyFileName],5,2),Right([Table1].[MyFileName],2)),[Table2].[DueDate]),
Table2.[Check] =
(DateSerial(Lef t([Table1].[MyFileName],4),Mid([Table1].[MyFileName],5,2),Right([Table1].[MyFileName],2))>[Table2].[DueDate])
And [Table1].[DueDate]=Date();

The statement that Table2.Check is being set to will return True or False,
not a date.

--
Wayne Morgan
MS Access MVP
<ma************ @zonnet.nl> wrote in message
news:11******** *************@g 43g2000cwa.goog legroups.com...

Wayne,

It's not exactly what I meant. The record in table 1 will be run
through table two until it finds the record with the same SUBJECT or
name. If it finds this the DUE DATE in table 2 will be compared with
the date in the filename of the record in table 1.
Depending on this comparison the record in table 2 will either be
CHECKED=TRUE and/or the DUE DATE will be changed.

Example:

Today it is: 09 sep 2005.

table1: MYFILENAME.2005 0909
table 2: MYFILENAME ---------- 08 sep ---------- FALSE

table1 filename.date > table2.duedate -----> change table2.duedate to
09 sep and
set table2.checked = true

Example Two:

Today it is: 11 sep 2005.

table1: MYFILENAME.2005 0909
table 2: MYFILENAME ---------- 08 sep ---------- FALSE

table1 filename.date > table2.duedate -----> change table2.duedate to
09 sep and
leave table2.checked = false

This time the table2.due.date will be changed because we have a newer
file but since today is 11 sep we leave checked FALSE. We are still
missing MYFILENAME.2005 0910 and MYFILENAME.2005 0911.

And now in programming code all the possible options.
HTH

Marco

[marcokrechting]

I get two errors on this query:

1. Table1.subject doesn't exist but it would be the left part of the
field MyFileName without the string representing the date so:
MYFILENAME.

2. table1.DueDate doesn't exist; shouldn't it be table2.DueDate?

Regards
Marco

[Wayne Morgan]

#1 Ok, so the Subject and Date are in one text field. So, create another
query based on the first table. Include all of the fields and create a
calcualted field called Subject, using the equation above. Use this query in
the previous query instead of Table1. Also, create a calculated field in
this query called DueDate. This will be the "date" extracted from
MyFileName. While we're at it, let's go ahead and break down the "date" into
month, year, and day to simplify the second query so that we don't have to
do this there.

#2, No, not table2, it should actually be the date derived from the date
string. Your previous description was if the "date" from table1 was today's
date.

First Query:
SELECT Table16.Field1,
Left([Table16].[MyFileName],Len([Table16].[MyFileName])-8) AS MyFileName,
Left(Right([Table16].[MyFileName],8),4) AS DueYear,
Mid(Right([Table16].[MyFileName],8),5,2) AS DueMonth,
Right([Table16].[MyFileName],2) AS DueDay
FROM Table16;
Second Query (update of query in last message):
UPDATE Table16a INNER JOIN Query33 ON Table16a.Field1 = Query33.Field1 SET
Table16a.DueDat e =
IIf(DateSerial( Query33.DueYear ,Query33.DueMon th,Query33.DueD ay)>Table16a.Du eDate,DateSeria l(Query33.DueYe ar,Query33.DueM onth,Query33.Du eDay),Table16a. DueDate),
Table16a.[Check] =
(DateSerial(Que ry33.DueYear,Qu ery33.DueMonth, Query33.DueDay) >Table16a.DueDa te)
And DateSerial(Quer y33.DueYear,Que ry33.DueMonth,Q uery33.DueDay)= Date();

--
Wayne Morgan
MS Access MVP
<ma************ @zonnet.nl> wrote in message
news:11******** **************@ g47g2000cwa.goo glegroups.com.. .

I get two errors on this query:

1. Table1.subject doesn't exist but it would be the left part of the
field MyFileName without the string representing the date so:
MYFILENAME.

2. table1.DueDate doesn't exist; shouldn't it be table2.DueDate?

Regards
Marco

[marcokrechting]

Why are you using Table16 and Field1?
I'm confused now. I have Table1 and Table2.
Table1 has only MyFileName and Table2 has subject, duedate and checked
as fields.
Why using the name Query33 and Table16a?

Marco

[Wayne Morgan]

Sorry, as mentioned before, I have to change the names when I post into the
message. I pasted, changed, then changed the queries before sending the
message, so I repasted. I forgot to change again. Let me redo this.

First Query:
SELECT Left([Table1].[MyFileName],Len([Table1].[MyFileName])-8) AS
MyFileName,
Left(Right([Table1].[MyFileName],8),4) AS DueYear,
Mid(Right([Table1].[MyFileName],8),5,2) AS DueMonth,
Right([Table1].[MyFileName],2) AS DueDay
FROM Table1;

Second Query (update of query in last message):
UPDATE Table2 INNER JOIN Query33 ON Table2.FileName = Query33.MyFileN ame SET
Table2.DueDate =
IIf(DateSerial( Query1.DueYear, Query1.DueMonth ,Query1.DueDay) >Table2.DueDate ,DateSerial(Que ry1.DueYear,Que ry1.DueMonth,Qu ery1.DueDay),Ta ble2.DueDate),
Table2.[Check] =
(DateSerial(Que ry1.DueYear,Que ry1.DueMonth,Qu ery1.DueDay)>Ta ble2.DueDate)
And DateSerial(Quer y1.DueYear,Quer y1.DueMonth,Que ry1.DueDay)=Dat e();

The query Query1 that is refered to in the second query is the first query.

--
Wayne Morgan
MS Access MVP
<ma************ @zonnet.nl> wrote in message
news:11******** **************@ g44g2000cwa.goo glegroups.com.. .

Why are you using Table16 and Field1?
I'm confused now. I have Table1 and Table2.
Table1 has only MyFileName and Table2 has subject, duedate and checked
as fields.
Why using the name Query33 and Table16a?

Marco

[marcokrechting]

Wayne,

After some small canges I got it to work, this is really great thanks a
lot.

[marcokrechting]

Wayne,

Is it possible to build-in a code that checks first to make sure that
there is no date (DueDate) in table2 which is bigger than Date()?

Marco