By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
449,247 Members | 1,223 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 449,247 IT Pros & Developers. It's quick & easy.

math problem with dates.....

P: n/a
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.FILE", 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

Nov 13 '05 #1
Share this Question
Share on Google+
14 Replies


P: n/a
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.googlegr oups.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.FILE", 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

Nov 13 '05 #2

P: n/a
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.20050909
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.20050909
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.20050910 and MYFILENAME.20050911.

And now in programming code all the possible options.
HTH

Marco

Nov 13 '05 #3

P: n/a
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(Left([Table1].[MyFileName],4),Mid([Table1].[MyFileName],5,2),Right([Table1].[MyFileName],2)),[Table2].[DueDate]),
Table2.[Check] =
(DateSerial(Left([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*********************@g43g2000cwa.googlegro ups.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.20050909
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.20050909
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.20050910 and MYFILENAME.20050911.

And now in programming code all the possible options.
HTH

Marco

Nov 13 '05 #4

P: n/a
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

Nov 13 '05 #5

P: n/a
#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.DueDate =
IIf(DateSerial(Query33.DueYear,Query33.DueMonth,Qu ery33.DueDay)>Table16a.DueDate,DateSerial(Query33. DueYear,Query33.DueMonth,Query33.DueDay),Table16a. DueDate),
Table16a.[Check] =
(DateSerial(Query33.DueYear,Query33.DueMonth,Query 33.DueDay)>Table16a.DueDate)
And DateSerial(Query33.DueYear,Query33.DueMonth,Query3 3.DueDay)=Date();

--
Wayne Morgan
MS Access MVP
<ma************@zonnet.nl> wrote in message
news:11**********************@g47g2000cwa.googlegr oups.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

Nov 13 '05 #6

P: n/a
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

Nov 13 '05 #7

P: n/a
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.MyFileName SET
Table2.DueDate =
IIf(DateSerial(Query1.DueYear,Query1.DueMonth,Quer y1.DueDay)>Table2.DueDate,DateSerial(Query1.DueYea r,Query1.DueMonth,Query1.DueDay),Table2.DueDate),
Table2.[Check] =
(DateSerial(Query1.DueYear,Query1.DueMonth,Query1. DueDay)>Table2.DueDate)
And DateSerial(Query1.DueYear,Query1.DueMonth,Query1.D ueDay)=Date();

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.googlegr oups.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

Nov 13 '05 #8

P: n/a
Wayne,

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

Nov 13 '05 #9

P: n/a
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

Nov 13 '05 #10

P: n/a
Do you want to make sure that such a date doesn't exist or do you want to
exclude it from processing if it does exist? If the latter, you could just
add the restriction as a WHERE clause to the query.

To do the first, use DLookup() and see what you can find. DLookup() will
return Null if it doesn't find anything.

If IsNull(DLookup("DueDate", "Table2", "DueDate>#" & Date() & "#")) Then
'there isn't one, do what you want here
Else
'there is one, do what you want here
End If

--
Wayne Morgan
MS Access MVP
<ma************@zonnet.nl> wrote in message
news:11*********************@g47g2000cwa.googlegro ups.com...
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

Nov 13 '05 #11

P: n/a
I want to exclude it and change it.
Let's say someone, by accident put the date of tomorrow in the DueDate
field.
That way the queries would do the wrong things. So I want to have a
WHERE but if possible the possibility to set the date back to Today
since that's the biggest date it can possibly have.

Regards
Marco

Nov 13 '05 #12

P: n/a
It would be a simple matter to run an Update query on the table checking for
a DueDate greater than today and changing it prior to running the other
queries.

Example:
UPDATE Table2 SET DueDate = Date() WHERE DueDate > Date();

--
Wayne Morgan
MS Access MVP
<ma************@zonnet.nl> wrote in message
news:11**********************@g49g2000cwa.googlegr oups.com...
I want to exclude it and change it.
Let's say someone, by accident put the date of tomorrow in the DueDate
field.
That way the queries would do the wrong things. So I want to have a
WHERE but if possible the possibility to set the date back to Today
since that's the biggest date it can possibly have.

Regards
Marco

Nov 13 '05 #13

P: n/a
Wayne,

There is one thing I had forgotten.
The filename will always have an extension like:
THIS.IS.MY.FILE.20050909.doc or .xls or .pdf etc...
How should I change the query to do this right?

Marco

Nov 13 '05 #14

P: n/a
That will change the length of the string by 4 characters. It will require
changing the first of the 2 query set in order to get the correct 8
characters for the date and to end getting the name part of the string at
the correct point.

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

Of course, this assumes it will always be a period with a three character
extension. It can be made more complicated if that won't be the case, but it
can be set up to find the last period and work off of that. Also, the part
being extracted as the name will include the period prior to the numbers.

--
Wayne Morgan
MS Access MVP
<ma************@zonnet.nl> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...
Wayne,

There is one thing I had forgotten.
The filename will always have an extension like:
THIS.IS.MY.FILE.20050909.doc or .xls or .pdf etc...
How should I change the query to do this right?

Marco

Nov 13 '05 #15

This discussion thread is closed

Replies have been disabled for this discussion.