Attn: Allen Browne - Old Question - Verify If Start Date Exists In Another Record Before Write

Allen -

I haven't tried doing the response that you gave me regarding the question 
below.  I'm having a major senior moment and can't figure out what the 
DLookup syntax would be.  Any help?

BTW the table name is tblContractorProjects...

Thanks

Jeff
-----------------------------------------


Use the BeforeUpdate event procedure of the form to perform the validation.

Use DLookup() to see if an overlapping entry exists in the table.

Assuming contractStartDate and contractEndDate are required fields (so you
don't have to handle overlapping dates when one of the fields is blank), the
dates overlap if:
    A starts before B ends, AND
    B starts before A ends.
and presumably it's the same contractor and/or project number as well.

So, the Criteria for your DLookup() will contain several phrases. Don't
forget ot exclude the contractID (i.e. an existing record does not clash
with itself.)

It may help to draw example events on paper like this:
A:        StartDate-----------EndDate
B:  StartDate-----------EndDate
C:                                             StartDate------EndDate
to get the idea of how they overlap.

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Jeff Garrison" <JGarrison@bmcsolutions.com> wrote in message
news:Ol9%23sNHyHHA.276@TK2MSFTNGP06.phx.gbl...
> Hello all...
>
> I trying to do some validation of a record before it writes to be
> database. Here's the scenario:
>
> My table includes the following fields:
>
> contractID                        Autonumber Field
> contractContractor            The Contractor's ID
> contractProjectNumber    The Project Number
> contractStartDate            The Start Date of the Project
> contractEndDate                The End Date of the Project
>
>
> I have the entry done on a form with those fields.  What I want to do is
> when the next record is entered, BEFORE the record is written to the
> database, check to see if the new record falls within any previous Start
> or End Dates.
>
> Any help would be much appreciated.
>
> Jeff G


0
Jeff
8/9/2007 3:46:30 PM
access 16762 articles. 3 followers. Follow

11 Replies
991 Views

Similar Articles

[PageSpeed] 59

Something along these lines:

DLookup("ContractID", "tblContract",
"(contractStartDate < " & Format([contractEndDate], "\#mm\/dd\/yyyy\#") &
") AND (" & Format([contractStartDate], "\#mm\/dd\/yyyy\#") &
" < contractEndDate) AND (contractContractor = " & [contractContractor] & 
")")

You may need to match the quotes/brackets etc.

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Jeff Garrison" <JGarrison@bmcsolutions.com> wrote in message
news:%23sg4Iyp2HHA.4476@TK2MSFTNGP06.phx.gbl...
> Allen -
>
> I haven't tried doing the response that you gave me regarding the question 
> below.  I'm having a major senior moment and can't figure out what the 
> DLookup syntax would be.  Any help?
>
> BTW the table name is tblContractorProjects...
>
> Thanks
>
> Jeff
> -----------------------------------------
>
>
> Use the BeforeUpdate event procedure of the form to perform the 
> validation.
>
> Use DLookup() to see if an overlapping entry exists in the table.
>
> Assuming contractStartDate and contractEndDate are required fields (so you
> don't have to handle overlapping dates when one of the fields is blank), 
> the
> dates overlap if:
>    A starts before B ends, AND
>    B starts before A ends.
> and presumably it's the same contractor and/or project number as well.
>
> So, the Criteria for your DLookup() will contain several phrases. Don't
> forget ot exclude the contractID (i.e. an existing record does not clash
> with itself.)
>
> It may help to draw example events on paper like this:
> A:        StartDate-----------EndDate
> B:  StartDate-----------EndDate
> C:                                             StartDate------EndDate
> to get the idea of how they overlap.
>
> "Jeff Garrison" <JGarrison@bmcsolutions.com> wrote in message
> news:Ol9%23sNHyHHA.276@TK2MSFTNGP06.phx.gbl...
>> Hello all...
>>
>> I trying to do some validation of a record before it writes to be
>> database. Here's the scenario:
>>
>> My table includes the following fields:
>>
>> contractID                        Autonumber Field
>> contractContractor            The Contractor's ID
>> contractProjectNumber    The Project Number
>> contractStartDate            The Start Date of the Project
>> contractEndDate                The End Date of the Project
>>
>>
>> I have the entry done on a form with those fields.  What I want to do is
>> when the next record is entered, BEFORE the record is written to the
>> database, check to see if the new record falls within any previous Start
>> or End Dates.
>>
>> Any help would be much appreciated.
>>
>> Jeff G 

0
Allen
8/10/2007 1:04:01 AM
On 10 Aug, 02:04, "Allen Browne" <AllenBro...@SeeSig.Invalid> wrote:
> > Use DLookup() to see if an overlapping entry exists in the table.
>
> > Assuming contractStartDate and contractEndDate are required fields (so you
> > don't have to handle overlapping dates when one of the fields is blank),
> > the
> > dates overlap if:
> >    A starts before B ends, AND
> >    B starts before A ends.

That is correct only when A containts B. For A overIaps B you surely
meant 'OR'. It makes a big difference! :)

Also note it is common to use the NULL value (presumably what you mean
by 'blank date') for an end date to signify the period in the current
state, when would be appropriate to use the current timestamp NOW() in
place of the NULL value i.e. NULLs should indeed be considered when
testing for overlapping periods. Again, this is a significant point.

> > and presumably it's the same contractor and/or project number as well.
>
> > So, the Criteria for your DLookup() will contain several phrases. Don't
> > forget ot exclude the contractID (i.e. an existing record does not clash
> > with itself.)
>
> > It may help to draw example events on paper like this:
> > A:        StartDate-----------EndDate
> > B:  StartDate-----------EndDate
> > C:                                             StartDate------EndDate
> > to get the idea of how they overlap.
>
> Something along these lines:
>
> DLookup("ContractID", "tblContract",
> "(contractStartDate < " & Format([contractEndDate], "\#mm\/dd\/yyyy\#") &
> ") AND (" & Format([contractStartDate], "\#mm\/dd\/yyyy\#") &
> " < contractEndDate) AND (contractContractor = " & [contractContractor] &
> ")")
>
Another approach which is IMO more intuitive is to use a Calendar
table with one row per day (within a large range) and use GROUP BY to
count the clashes between key value and day e.g. someting like:

.... WHERE NOT EXISTS
(
SELECT E1.employee_number, C1.dt
FROM EarningsHistory AS E1, Calendar AS C1
WHERE C1.dt BETWEEN E1.start_date AND E1.end_date
GROUP BY E1.employee_number, C1.dt
HAVING COUNT(*) > 1
);

Jamie.

--


0
Jamie
8/13/2007 11:33:26 AM
"Jamie Collins" <jamiecollins@xsmail.com> wrote in message 
news:1187004806.607202.323590@r34g2000hsd.googlegroups.com...
> On 10 Aug, 02:04, "Allen Browne" <AllenBro...@SeeSig.Invalid> wrote:
>> > Use DLookup() to see if an overlapping entry exists in the table.
>>
>> > Assuming contractStartDate and contractEndDate are required fields (so 
>> > you
>> > don't have to handle overlapping dates when one of the fields is 
>> > blank),
>> > the
>> > dates overlap if:
>> >    A starts before B ends, AND
>> >    B starts before A ends.
>
> That is correct only when A containts B. For A overIaps B you surely
> meant 'OR'. It makes a big difference! :)

I believe Allen's correct in what he states.

Let's say A starts 2007-08-13 at 09:00 and ends 2007-08-13 at 12:00.

If B starts at 2007-08-13 at 10:00 and ends 2007-08-13 at 14:00, you've got 
A starting before B ends and B starting before A ends, but A does not 
contain B.

Heck, A starting before B ends would catch any B starting after 2007-08-13 
09:00. In other words, using OR would result in a B starting and ending on 
2007-08-15 being flagged as an overlap.

-- 
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



0
Douglas
8/13/2007 1:40:43 PM
On 13 Aug, 14:40, "Douglas J. Steele"
<NOSPAM_djsteele@NOSPAM_canada.com> wrote:
> > > > the
> > > > dates overlap if:
> > > >    A starts before B ends, AND
> > > >    B starts before A ends.
>
> I believe Allen's correct in what he states.

Yes, you're correct of course. Thanks for picking me up on the point!

> Let's say A starts 2007-08-13 at 09:00 and ends 2007-08-13 at 12:00.
>
> If B starts at 2007-08-13 at 10:00 and ends 2007-08-13 at 14:00, you've got
> A starting before B ends and B starting before A ends, but A does not
> contain B.
>
> Heck, A starting before B ends would catch any B starting after 2007-08-13
> 09:00. In other words, using OR would result in a B starting and ending on
> 2007-08-15 being flagged as an overlap.

Question: if A starts #2007-08-13 09:00:00# and ends #2007-08-13
12:00:00#
and B starts #2007-08-13 12:00:00# and ends #2007-08-13 14:00:00#,
is this an overlap?

Jamie.

--


0
Jamie
8/13/2007 2:18:06 PM
"Jamie Collins" <jamiecollins@xsmail.com> wrote in message 
news:1187014686.252083.108660@g4g2000hsf.googlegroups.com...
>
> Question: if A starts #2007-08-13 09:00:00# and ends #2007-08-13
> 12:00:00#
> and B starts #2007-08-13 12:00:00# and ends #2007-08-13 14:00:00#,
> is this an overlap?

My answer is that's a business rule that needs to be set.

Sometimes consecutive events like that are fine, sometimes they aren't.

If you're in a situation where you need to do maintenance between events 
(such as a meeting facility where seating setup is required), then it's 
possible that you'd want A ending at #2007-08-13 12:00:00# and B starting 
#2007-08-13 14:00:00# considered to be an overlap.

-- 
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



0
Douglas
8/13/2007 2:32:17 PM
On 13 Aug, 15:32, "Douglas J. Steele"
<NOSPAM_djsteele@NOSPAM_canada.com> wrote:
> > Question: if A starts #2007-08-13 09:00:00# and ends #2007-08-13
> > 12:00:00#
> > and B starts #2007-08-13 12:00:00# and ends #2007-08-13 14:00:00#,
> > is this an overlap?
>
> My answer is that's a business rule that needs to be set.
>
> Sometimes consecutive events like that are fine, sometimes they aren't.
>
> If you're in a situation where you need to do maintenance between events
> (such as a meeting facility where seating setup is required), then it's
> possible that you'd want A ending at #2007-08-13 12:00:00# and B starting
> #2007-08-13 14:00:00# considered to be an overlap.

It wasn't a trick question :)

Note that in my question (different from the OP's), period A ends
#2007-08-13 12:00:00# and B starts #2007-08-13 12:00:00# i.e. the same
DATETIME value

I could have asked whether you use closed-closed representation (A
overlaps B by almost a second) or closed-open representation (A meets
B i.e. are contiguous periods) or something else, but I was trying to
avoid jargon.

Rather than 'business rules', it is a question on how to handle the
nature of time. The way I see it, closed-open representation fits the
floating point nature of Access/Jet's DATETIME, however I personally
find it unintuitive e.g. if you told me the end date for submitting my
homework was 14 August I'd turn up on the 14 August, homework in hand,
to learn I was late. Tell me the last available time granule for
submission is #2007-08-13 23:59:59# and I'd be in no doubt :)

PS Who am I kidding? I *always* hand in my homework late. Miss, the
dog ate my end date, Miss!

Jamie.

--


0
Jamie
8/13/2007 3:04:24 PM
Douglas J. Steele wrote:
> "Jamie Collins" <jamiecollins@xsmail.com> wrote in message 
> news:1187014686.252083.108660@g4g2000hsf.googlegroups.com...
> 
>>Question: if A starts #2007-08-13 09:00:00# and ends #2007-08-13
>>12:00:00#
>>and B starts #2007-08-13 12:00:00# and ends #2007-08-13 14:00:00#,
>>is this an overlap?
> 
> 
> My answer is that's a business rule that needs to be set.
> 
> Sometimes consecutive events like that are fine, sometimes they aren't.
> 
> If you're in a situation where you need to do maintenance between events 
> (such as a meeting facility where seating setup is required), then it's 
> possible that you'd want A ending at #2007-08-13 12:00:00# and B starting 
> #2007-08-13 14:00:00# considered to be an overlap.
> 

If you can calculate the amount of overlap precisely then your seating 
setup example just needs enough overlap for the seating setup.  After 
reading the Snodgrass' pdf article posted by Jamie 
(http://www.cs.arizona.edu/people/rts/tdbbook.pdf), I adopted the 
convention that all time endpoints are at the left-hand side of the 
second interval.  I often use a TimeIntersection function to hide some 
of the details until I get the SQL running, then replace the 
TimeIntersection with equivalent SQL.

My TimeIntersection function is shown here:

http://groups.google.com/group/microsoft.public.access/msg/9ce7997df8677fca

I'm posting the TimeIntersection function again in the hope that it will 
highlight some of the issues.  Note: It returns the number of hours 
overlapping two time intervals (including decimal part), but can be 
changed easily to display minutes or seconds of overlap.  This doesn't 
address Null values.  It's not even my answer to the problem.

The seemingly small issue of checking if dates overlap masks a larger 
one.  I believe that someday, when :-) (if) Access catches up to the 
latest SQL standards, that separate tables will be the norm for handling 
temporal data because the built-in mechanisms for handling them that way 
will greatly simplify everything.  Under current conditions, without 
those advanced SQL capabilites present in Access, the SQL required to 
maintain a separate temporal table suggested by Jamie can get way out of 
hand quickly with only a few slight changes to the requirements.  Only a 
few Access programmers can keep up with the complexity of the queries 
shown in Snodgrass' book until Access gains newer SQL capabilities.  In 
the meantime, we have to get by in a way that can't quickly exceed our 
SQL capacities.  Handling temporal data properly is much tougher than it 
looks.  My advice for anyone venturing down the temporal table path is 
to warn their employers that small design changes can potentially create 
a huge amount of effort.  I think it's great that Jamie is helping 
prepare Access programmers for a more standardized future, but I do not 
believe that Access provides us with a realistic means to achieve that 
vision yet.

James A. Fortune
MPAPoster@FortuneJames.com
0
James
8/13/2007 4:10:13 PM
On Aug 13, 5:10 pm, "James A. Fortune" <MPAPos...@FortuneJames.com>
wrote:

Good post.

> I think it's great that Jamie is helping
> prepare Access programmers for a more standardized future

Erm, not intentionally <g>. I'm trying to get Access programmers to
face reality.

> I believe that someday, when :-) (if) Access catches up to the
> latest SQL standards, that separate tables will be the norm for handling
> temporal data because the built-in mechanisms for handling them that way
> will greatly simplify everything.
>
> without
> those advanced SQL capabilites present in Access, the SQL required to
> maintain a separate temporal table suggested by Jamie can get way out of
> hand quickly with only a few slight changes to the requirements.

I don't know what "advanced SQL capabilities" you are hoping for
(SQL-92's OVERLAPS operator? SQL3's period constructor?) but I know
don't share your optimism about future change. I think what we today
have is a good as we'll ever get in this product.

We will not get improved temporal functionality because Access is in
maintenance mode. Sorry! but it is. What did we get as new features in
the Access 2007 engine? Multi-valued data types (a.k.a. First Normal
Form violation) without the multi-valued operators etc to go with it;
I don't think even Albert D. Kallal Access MVP(MPV = Multi-Value
Promoter <g>) had multi-valued data types on his wish list. Similarly,
Attachment fields are document management without the operators, full
text search etc. With the new MEMO columns you can "view a history" of
revisions: I hope this is not the advanced capabilities you wish for.

Spot the missing article in this series:

New Features in Microsoft Jet Version 3.0:
http://support.microsoft.com/kb/137039

Description of the new features that are included in Microsoft Jet
4.0:
http://support.microsoft.com/kb/275561

New features in Microsoft Access 2007 engine:
[don't bother looking, you won't find one; it would be an
embarrassingly short piece]

SQL Server 2008 will be getting four new temporal data types DATE,
TIME, DATETIME2 (scaled decimal accurate to 100 nanoseconds) and
DATETIMEOFFSET (time zones etc). AFAIK no new operators, constructors,
etc for the new temporal types (probably because they are merely
variations on the DATETIME and SMALLDATETIME theme). SQL Server seems
to be giving a node to the standards but is picking and choosing e.g.
implementing 'full SQL-99' features before 'core SQL-99' compliance
has been achieved :(

In the good old days, the Access engine (Jet) was owned by the SQL
Server team and we often saw compatible functionality 'migrating'
between products. These days, no such luck: the Access team now own a
'private fork' of the code base.

But I don't think the current situation is all that bad. I think valid-
time state models are within the capability of the *product*. We have
table-level CHECK constraints in the engine. Using two instants
(DATETIME) to model a period works well enough. Sure, the SQL is a bit
more complex than a simple JOIN on two INTEGER columns (as I
demonstrated in this thread with my unsound challenge!) but I think it
is within the capability of most SQL coders. "I'm not saying that
you'd be able to do it without some hard work, some honest hard
work ...and possibly some medicine" (Nicholas Cage in 'Family Man').

The problem is one of awareness: the need for a sequenced key, the
existence of engine-level functionality and how to use it. However,
the average Access user doesn't give a hoot about engine-level
constraints and logical keys. How do you convey the concept of a
sequenced primary key to a group who thinks, "I've added an AutoNumber
and given it the PRIMARY KEY designation for the table. My work here
is done."

Jamie.

--

> After
> reading the Snodgrass' pdf article posted by Jamie
> (http://www.cs.arizona.edu/people/rts/tdbbook.pdf), I adopted the
> convention that all time endpoints are at the left-hand side of the
> second interval.  I often use a TimeIntersection function to hide some
> of the details until I get the SQL running, then replace the
> TimeIntersection with equivalent SQL.
>
> My TimeIntersection function is shown here:
>
> http://groups.google.com/group/microsoft.public.access/msg/9ce7997df8...
>
> I'm posting the TimeIntersection function again in the hope that it will
> highlight some of the issues.  Note: It returns the number of hours
> overlapping two time intervals (including decimal part), but can be
> changed easily to display minutes or seconds of overlap.  This doesn't
> address Null values.  It's not even my answer to the problem.
>
> The seemingly small issue of checking if dates overlap masks a larger
> one.  I believe that someday, when :-) (if) Access catches up to the
> latest SQL standards, that separate tables will be the norm for handling
> temporal data because the built-in mechanisms for handling them that way
> will greatly simplify everything.  Under current conditions, without
> those advanced SQL capabilites present in Access, the SQL required to
> maintain a separate temporal table suggested by Jamie can get way out of
> hand quickly with only a few slight changes to the requirements.  Only a
> few Access programmers can keep up with the complexity of the queries
> shown in Snodgrass' book until Access gains newer SQL capabilities.  In
> the meantime, we have to get by in a way that can't quickly exceed our
> SQL capacities.  Handling temporal data properly is much tougher than it
> looks.  My advice for anyone venturing down the temporal table path is
> to warn their employers that small design changes can potentially create
> a huge amount of effort.  I think it's great that Jamie is helping
> prepare Access programmers for a more standardized future, but I do not
> believe that Access provides us with a realistic means to achieve that
> vision yet.


0
Jamie
8/14/2007 9:27:21 AM
Allen -

Thanks for the reply...I didn't realize that it was going to set off a 
firestorm of discussions.

My question is...

Now that I have the syntax, I put it in the Before Update on the form, but 
is there something I need to do via VBA, such as If...Then?  Also, how do I 
exclude the current record?  I plugged in the dlookup and made the changes 
needed for table name, etc., and did an If...Then by saying If 
dlookup...then msgbox "Overlapping Projects", End If.  When I opened an item 
the had only 1 record, I cycled through the record and when I exited the 
item and went to the new one, the Message Box popped up.  Am I correct in 
the logic, especially the VBA part?  As far as the exclusion, there won't be 
an autonumber assinged to it to be used for exclusion because the record 
hasn't been written to the table and therefore isn't a record ID assgined to 
it.

Thanks.

Jeff

"Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message 
news:euFxXpu2HHA.4584@TK2MSFTNGP03.phx.gbl...
> Something along these lines:
>
> DLookup("ContractID", "tblContract",
> "(contractStartDate < " & Format([contractEndDate], "\#mm\/dd\/yyyy\#") &
> ") AND (" & Format([contractStartDate], "\#mm\/dd\/yyyy\#") &
> " < contractEndDate) AND (contractContractor = " & [contractContractor] & 
> ")")
>
> You may need to match the quotes/brackets etc.
>
> -- 
> Allen Browne - Microsoft MVP.  Perth, Western Australia
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Jeff Garrison" <JGarrison@bmcsolutions.com> wrote in message
> news:%23sg4Iyp2HHA.4476@TK2MSFTNGP06.phx.gbl...
>> Allen -
>>
>> I haven't tried doing the response that you gave me regarding the 
>> question below.  I'm having a major senior moment and can't figure out 
>> what the DLookup syntax would be.  Any help?
>>
>> BTW the table name is tblContractorProjects...
>>
>> Thanks
>>
>> Jeff
>> -----------------------------------------
>>
>>
>> Use the BeforeUpdate event procedure of the form to perform the 
>> validation.
>>
>> Use DLookup() to see if an overlapping entry exists in the table.
>>
>> Assuming contractStartDate and contractEndDate are required fields (so 
>> you
>> don't have to handle overlapping dates when one of the fields is blank), 
>> the
>> dates overlap if:
>>    A starts before B ends, AND
>>    B starts before A ends.
>> and presumably it's the same contractor and/or project number as well.
>>
>> So, the Criteria for your DLookup() will contain several phrases. Don't
>> forget ot exclude the contractID (i.e. an existing record does not clash
>> with itself.)
>>
>> It may help to draw example events on paper like this:
>> A:        StartDate-----------EndDate
>> B:  StartDate-----------EndDate
>> C:                                             StartDate------EndDate
>> to get the idea of how they overlap.
>>
>> "Jeff Garrison" <JGarrison@bmcsolutions.com> wrote in message
>> news:Ol9%23sNHyHHA.276@TK2MSFTNGP06.phx.gbl...
>>> Hello all...
>>>
>>> I trying to do some validation of a record before it writes to be
>>> database. Here's the scenario:
>>>
>>> My table includes the following fields:
>>>
>>> contractID                        Autonumber Field
>>> contractContractor            The Contractor's ID
>>> contractProjectNumber    The Project Number
>>> contractStartDate            The Start Date of the Project
>>> contractEndDate                The End Date of the Project
>>>
>>>
>>> I have the entry done on a form with those fields.  What I want to do is
>>> when the next record is entered, BEFORE the record is written to the
>>> database, check to see if the new record falls within any previous Start
>>> or End Dates.
>>>
>>> Any help would be much appreciated.
>>>
>>> Jeff G
> 


0
Jeff
8/14/2007 2:35:35 PM
On 14 Aug, 10:27, Jamie Collins <jamiecoll...@xsmail.com> wrote:
> I don't think the current situation is all that bad. I think valid-
> time state models are within the capability of the *product*. We have
> table-level CHECK constraints in the engine. Using two instants
> (DATETIME) to model a period works well enough.

Since posting last, a number of significant limitations have sprung to
mind:

Sequenced operations (update, insert, delete) involve multiple SQL
statements and in a Access/Jet PROCEDURE can only execute a single SQL
statements, therefore you must code your sequenced operations in VBA.

Access/Jet SQL does not support deferrable constraints so those VBA
procedures will have to DROP the sequenced key then recreate them in a
transaction that will inevitably cause lock the table for the
duration.

Perhaps Access/Jet isn't fit for purpose after all. How to prevent it
being used for these purposes <g>?

Jamie.

--


0
Jamie
8/14/2007 3:33:31 PM
Jamie Collins wrote:
> I don't know what "advanced SQL capabilities" you are hoping for
> (SQL-92's OVERLAPS operator? SQL3's period constructor?) but I know
> don't share your optimism about future change. I think what we today
> have is a good as we'll ever get in this product.

I was referring to SQL3.  SQL-92 compliance is not enough.  Without it, 
Snodgrass' SQL will be a nightmare to create and maintain.

James A. Fortune
MPAPoster@FortuneJames.com
0
James
8/14/2007 6:13:25 PM
Reply:

Similar Artilces:

Please help. Question about querying a subform
I asked this yesterday and see all kinds of questions around me being answered and suppose i just did not word this correctly. I have a table of lab results. It is related to my patient table. It is a one-to-many relationship. I have a date field, which is the date of last labs. I am trying to build a query that will pull only the most previous lab from each patient, with the criteria of it having been more than 6 months ago. I do not want to pull lab dates from 10 months ago, if there is one within the last 6 months. This is for a Dr and it is a priority that I get it d...

Office apps won't open, report Office in use by another use
Version: 2004 Operating System: Mac OS X 10.4 (Tiger) Processor: Intel A few days ago, our Office applications started showing the report that "An Office program is being used by "user." your installation exceeds the number of installations permitted by thelicense agreement. Click More Information to learn about Microsoft Office licensing." But OFFICE ISN'T BEING USED BY ANYONE. All computers have been restarted to no avail. HELP! <sakas@officeformac.com> wrote: > A few days ago, our Office applications started showing the report that > "An Office p...

Clustering question
Win2k3 domain, Exch 2003 w/sp2. In an emergency can one cluster node hold two EVS(s)? Or will only one start up? Any docs. to back it up? -- TIA, Clayton P.S.: I wrote an iTunes podcast tutorial and just want to publicize it. You can find it at: http://www.nikoli.net/itunepod ******************* Active/Active clusters in a 2-node configuration - when one node fails the other can host 2 EVSes. -- Bharat Suneja MVP - Exchange www.zenprise.com NEW blog location: exchangepedia.com/blog ------------------------------ "Clayton Sutton" <none@none.com> wrote in...

Outlook 2007 question
Outlook 2007 We have had an issue when we set a user to change password at next logon that it causes them to lose their settiings - they have to complete the Outlook wizard to configure the mailbox etc. Do the loose the settings just for Outlook or do they loose all the settings for the profile also? "goundhog" wrote: > Outlook 2007 > > We have had an issue when we set a user to change password at next logon > that it causes them to lose their settiings - they have to complete the > Outlook wizard to configure the mailbox etc. ...

my first question
hi friend...am new to this group....currently am doing Mtech in MIT, chennai. I want to know details regarding compression technique for my final year project...... my question: 1, I want to know any available content based compression technique, presently? content based in the sense...want to consider text, numeric and special characters as separate.......not audio and video.... "lin" <kcjlingesh@gmail.com> wrote in message news:1c767221-36fa-4b58-9731-122673b89463@y12g2000prb.googlegroups.com... > hi friend...am new to this group....

My report contains multiple copies of each record
I have three tables (parent/guardian, children, otheradults). that are all related with left joins (i.e. Include ALL records from 'Parent/Guardian' and only those records from 'Adults' where the joined fields are equal.) by ContactID (which is the parent/guardian ID) same for children. I created a query called Family which successfully combined three queries of each table where I combined the first and last names into one field. When I create my report, it creates an entry for EACH child rather than listing the children and related adults under Parent/Guardia...

How to update one form when closing another ?
I have two forms open. frmPeople has the Focus frmAccount does not. frmAccount has subFormPeopleList I add a new person to frmPeople (which is linked to frmAccount) Question 1: When I close frmPeople I want to update frmAccount so the new person shows in subFormPeopleList Quetion 2: But I'm also wondering if I add code to the "close event" for frmPeople to update frmAccount What will this do if frmAccount is not open? Will it add the person twice? I'm guessing not as updating a form doesn't really add data to the table... right? Thanks for any help. Mel Thanks...

Windows xp updates question
When I try to update Windows XP IE will not load page. It's as if the page didn't exist. I go click on 'Tools/Window Update' but the MS update page will not be found. How can I fix this? Tony Alpha wrote: > When I try to update Windows XP IE will not load page. It's as if > the page didn't exist. I go click on 'Tools/Window Update' but the > MS update page will not be found. > > How can I fix this? Windows XP _____ Edition with Service Pack _ __-Bit? Internet Explorer version _? Assuming some 32-bit version of Windows XP...

SumProduct with date and time
I have finally found that with my formulas the calulations are based on the time. Can someone help me find the best way to correct this without using macros to strip the time out of the data. Bob 09/08/2007 01:05 13/08/2007 00:00 Bob Bill 10/08/2007 23:39 Bill Kyle 11/08/2007 07:36 Kyle Bob 16/07/2007 20:39 Bill 27/07/2007 19:59 Kyle 18/07/2007 16:51 Bob 14/07/2007 16:31 Bill 21/06/2007 16:46 Kyle 30/06/2007 16:55 Bob 12/06/2007 01:05 Bill 13/06/2007 23:39 1 2 0 1 1 Kyle 14/06/2007 07:36 1 1 1 1 1 Bob 12/01/2007 20:39 1 1 2 0 1 Bill 13/08/2006 19:59 Kyle 14/05/1999 16:51 These are t...

Another unclear e-learning question
Hi! The question is: "If you were creating a culture of your own using the CultureAndRegionInfoBuilder class, which value would you set for the CultureAndRegionModifiers parameter ?" 1. None 2. Neutral. 3. Replacement In this question it's not quite clear if the question mean if a new culture from scratch should be created or if an existing culture should be changed. If the question mean that a competely new culture from scratch than alternative 1 or 2 should be used. Alternative 1 for a neutral culture and 2 for a specific culture. If the question mean...

Incoming mails from another external domain are lost
Hi, I have a Exchange Front-End & Back-End combination. Front-End Server also has SMTP Relay server and Intelligent Message Filtering. Front-end server is in a DMZ Zone. I have a pix firewall. I got a peculiar problem. One user has complained me saying he has not received a email sent from another company (external domain say xyz.com). I have checked the server logs and the filtered spam emails. I could not see any email from xyz.com in the server logs. Also the sender from xyz.com did not receive any NDR for this mail also. Can anybody help me on this? Regards Ganapathy Is the...

View Customization Question
My director came to me the other day and asked about customizing his view of his accounts. He had two requests. I figured out the first one, but I'm having a little trouble figuring out if he can do the second one. When he opens up a single account, instead of it opening automatically to the Information section, he would like it to automatically open to the Contacts section. Help is much appreciated! Cammie You can make a new tab pn Account form (contact view and make it the first one on account form). Now you use Iframe . you can get idea from here http://icu-mscrm.blogspot....

standard color dialog box inside another dialog box
Hi, does anybody know if it is possible to place a standard color dialog box inside another dialog box? I'm using the MFC library. Any snippets of code are welcomed. Pawel ...

OLE Object Data Type Question
Hi, a friend of mine supports a very simple Access 2000 database for keeping tracks of his .JPG file. The database has a table with several field, and one of them is of OLE Object data typy. He opens that table, rightclicks OLE Object field, selects Insert Oject and creates a link to a .JPG file. After finishing insertion the OLE Object field contains kind of "MSPicture3" or "MSImage3" (I don't remember exactly the text). Before a month ago he moved his database to another computer and now after insertion into the OLE Object field a link, it contains "...

Question on Attachment
I want Attachment field below Subject field When i attach any file or folder the attachment should shown below subject as a seprate field like subject or Bcc Which version of OL are you using? This works in OL2000. Try changing the message format from "Rich Text" to "HTML" or "Plain text." This works for me but not every one likes receiving HTML emails. I use strictly plain text. To get at this setting go to Tools, Options then click "Mail Format" tab. >-----Original Message----- >I want Attachment field below Subject field > >When ...

Week starts on Friday
Hi Groupies I have created a calculated field that is supposed to specify the week a job occurred. The week starts on Friday and goes through the following Thursday. My calculated field looks like this: WorkPeriod: Format(DateAdd("d",-1*(DatePart("w",[Ticket Date])+1),[Ticket Date]),"mmm dd""/""yy") & " - " & Format((DateAdd("d",-1*(DatePart("w",[Ticket Date])+1),[Ticket Date])+6),"mmm dd""/""yy") It returns a period formatted as: Dec 04/09 - Dec 10/09 (for e...

Getting started with GP10.0
So, now I've installed it, what do I do? If I open the application, I get a list of servers (means nothing to me) and a username and password box. Again, I haven't a clue what I could put in here. Is this software supposed to be for lay-people? It seems horribly complex if you're not a programmer. Where is your reseller? You need a good VAR to help you implement this product. I don't know of any product in the price range of Dynamics GP that is "super-friendly" the first time you use it. A good rule of the thumb: more functionality = more complexity. -- Japhe...

searching for a given record
I have to think there is a very easy way to do this, but I've been struggling for a couple of days now and I could use some help. All I want to do is search for a particular record using a form. I don't want my users to have to right click and even think about filters or anything like that. I want them to be able to type in a partial name and press a button on the form and have the matching records pulled into the form. What's the best way to accomplish this? --b Open the relevant form in design mode and make some room in the form's header. With the wizards enabled, draw ...

Outlook 2000 Error 550 Sender verify failed
Hello all We have one machine on our network that cannot send mail for some reason. Everytime this machine attempts to send mail using Outlook 2000 it fails, reporting the following error: "The message could not be sent because one of the recipients was rejected by the server. Server response: '550 Sender verify failed'. (Account: 'Fred', smtp server: 'relay.clara.net', error number: 0x8000ccc79)." It is not a simple matter of setting the authentication on the outgoing mail server, as all the other machines on the network (also using Outlook 2000) do...

Java vs. C++ (Date class)
I'm porting some Java code into C++. Which class I should use instead Date class? Note that Date support miliseconds. Also, which class I should use instead of Vector class? std::vector? "Petar Popara" <my.fake@mail.net> wrote in message news:uG6VGpUfFHA.2424@TK2MSFTNGP09.phx.gbl... > > I'm porting some Java code into C++. Which class I should use instead Date > class? Note that Date support miliseconds. Oh dear. Both the Java and the C++ date classes do assorted odd things with time zones and "daylight saving"; neither is documented fully or ...

how to create a calendar to select a date in access 2007
I want to select a date from a pop up calendar on the form. Access 2007 automatically adds a date picker to a Date/Time field. -- Lynn Trapp MCP, MOS, MCAS "keithteri" wrote: > I want to select a date from a pop up calendar on the form. ...

RMS Database Questions
We are working on a RMS project with some customization for data extraction and reporting. The data extraction and reporting mandates maximum number of characters for certain fields, such as ItemLookupCode, which cannot excede 14 characters. We are evaluating some of the possible options to meet this requirement. One of the possible solutions appears to be changing the field lengths in the RMS database tables. We have the following questions and appreciate your help if you have any answer for us. a) Is this a viable option? b) Can you resize any or all the columns? c) Can you extend...

Creation date
Hi, I would like a way to insert the creation date of worksheet, not the creation date of the template. -- Thank you in Advance Merci a l'Avance Martin Hi you'll need VBA for this. Find below a UDF for this task: Public Function Get_Creation_date() As Date Get_Creation_date = ActiveWorkbook.BuiltinDocumentProperties("Creation Date") End Function Enter the following in your target cell: =Get_Creation_date() and format the result as date -- Regards Frank Kabel Frankfurt, Germany Martin Racette wrote: > Hi, > > I would like a way to insert the creation d...

Outlook starting problem
"Cannot start Microsoft Outlook. A dialog box is open. Close it and try again." ...

Help: Can I change the date formula from mm/dd/yyyy to "mmm-yy" et
Hi, If the date was input as mm/dd/yyyy (12/31/2004), can I convert the format to "yyyy-mm-dd"(2004-12-31) or "mmm-yy"(Dec-2004)? i tried to change date format from "Format Cells-- Number tab--Category--choose "date" or "custom" but just failed. While Strangely, If i imput the data as "2004-12-31", i can change it to whatever date format i like. Appreicate your advice at earliest convenience. Thanks -- wintersunshine Hi sounds like your imported dates are not recognised as 'date' values but are stored as 'Text&...