Search not resulting in expected outcome

[hughess7]

Hi all

I am trying to add a routine to my system to check previous history when
creating a new record and it doesn't seem to be working? When an Activity of
VV is entered I want the system to see if there is an existing Audit in the
Table: Itinerary and if not the AuditNo = 0, else = 99. I have tested it with
an example I know has a previous Audit therefore it should set it to 99 but
it sets it to 0. I've tried debugging and I can't see where it is going
wrong... can anyone help please?

I've added the code below and the data returned from adding a watch to
Jetwhere.

(Activity and DealerCode are text fields, CountryCode is Numeric)

Jetwhere = "[Activity] = ""Audit""" & " And [CountryCode] = " &
Me![CountryCode] & " And [DealerCode] = """ & Me![Dealer] & """"
Exists = (DCount("*", "Itinerary", Jetwhere) = 0)
If Exists = False Then

"[Activity] = "Audit" And [CountryCode] = 36 And [DealerCode] = "1377""

Thanks in advance for any help.
Sue

 

[Douglas J. Steele]

You don't actually explain what's going wrong...

No offense, but might it just be a logic issue? I notice that you've name
the variable Exists, but your test actually implies that Exists will be
False if an audit exists, and True if it doesn't. (presumably DCount will
only equal 0 when no audit exists). What's the code that follows the If
Exists = False Then statement? (Incidentally, I don't know why you bother
assigning the comparison of DCount to 0 to a variable. Why not just put the
comparison in the If statement?)

 

[hughess7]

Hi Doug

Thanks for the quick reply! I've used this code before elsewhere in my
system to test for other search criteria and it works fine. Therefore I just
copied it and changed the fieldnames etc, but I'm sure you are right and it
is probably a logic problem ;-)

I did it this way using someone else's example a long time ago. I am only a
novice so welcome any better suggestions

The bit after that code is:

If Exists = False Then
[AuditNo] = 0 ' no audit found
Else
[AuditNo] = 99 ' audit found
End If


Thanks in advance for any help.
Sue

You don't actually explain what's going wrong...

No offense, but might it just be a logic issue? I notice that you've name
the variable Exists, but your test actually implies that Exists will be
False if an audit exists, and True if it doesn't. (presumably DCount will
only equal 0 when no audit exists). What's the code that follows the If
Exists = False Then statement? (Incidentally, I don't know why you bother
assigning the comparison of DCount to 0 to a variable. Why not just put the
comparison in the If statement?)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi all

I am trying to add a routine to my system to check previous history when
creating a new record and it doesn't seem to be working? When an Activity
of
VV is entered I want the system to see if there is an existing Audit in
the
Table: Itinerary and if not the AuditNo = 0, else = 99. I have tested it
with
an example I know has a previous Audit therefore it should set it to 99
but
it sets it to 0. I've tried debugging and I can't see where it is going
wrong... can anyone help please?

I've added the code below and the data returned from adding a watch to
Jetwhere.

(Activity and DealerCode are text fields, CountryCode is Numeric)

Jetwhere = "[Activity] = ""Audit""" & " And [CountryCode] = " &
Me![CountryCode] & " And [DealerCode] = """ & Me![Dealer] & """"
Exists = (DCount("*", "Itinerary", Jetwhere) = 0)
If Exists = False Then

"[Activity] = "Audit" And [CountryCode] = 36 And [DealerCode] = "1377""

Thanks in advance for any help.
Sue

 

[Douglas J. Steele]

Is my interpretation of Exists correct: it should be True if there are any
Audit records in the Itinerary table for that Country and Dealer? If so, it
implies to me that you want:

Jetwhere = "[Activity] = ""Audit""" & " And [CountryCode] = " & _
Me![CountryCode] & " And [DealerCode] = """ & Me![Dealer] & """"

If DCount("*", "Itinerary", Jetwhere) > 0 Then
[AuditNo] = 99
Else
[AuditNo] = 0
End If

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Doug

Thanks for the quick reply! I've used this code before elsewhere in my
system to test for other search criteria and it works fine. Therefore I
just
copied it and changed the fieldnames etc, but I'm sure you are right and
it
is probably a logic problem ;-)

I did it this way using someone else's example a long time ago. I am only
a
novice so welcome any better suggestions

The bit after that code is:

If Exists = False Then
[AuditNo] = 0 ' no audit found
Else
[AuditNo] = 99 ' audit found
End If


Thanks in advance for any help.
Sue

You don't actually explain what's going wrong...

No offense, but might it just be a logic issue? I notice that you've name
the variable Exists, but your test actually implies that Exists will be
False if an audit exists, and True if it doesn't. (presumably DCount will
only equal 0 when no audit exists). What's the code that follows the If
Exists = False Then statement? (Incidentally, I don't know why you bother
assigning the comparison of DCount to 0 to a variable. Why not just put
the
comparison in the If statement?)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi all

I am trying to add a routine to my system to check previous history
when
creating a new record and it doesn't seem to be working? When an
Activity
of
VV is entered I want the system to see if there is an existing Audit in
the
Table: Itinerary and if not the AuditNo = 0, else = 99. I have tested
it
with
an example I know has a previous Audit therefore it should set it to 99
but
it sets it to 0. I've tried debugging and I can't see where it is going
wrong... can anyone help please?

I've added the code below and the data returned from adding a watch to
Jetwhere.

(Activity and DealerCode are text fields, CountryCode is Numeric)

Jetwhere = "[Activity] = ""Audit""" & " And [CountryCode] = " &
Me![CountryCode] & " And [DealerCode] = """ & Me![Dealer] & """"
Exists = (DCount("*", "Itinerary", Jetwhere) = 0)
If Exists = False Then

"[Activity] = "Audit" And [CountryCode] = 36 And [DealerCode] = "1377""

Thanks in advance for any help.
Sue

 

[hughess7]

Thanks, yes you are right my logic was wrong and that works. That is what I
get from trying to copy a slightly different example .

Thanks again
Sue

Is my interpretation of Exists correct: it should be True if there are any
Audit records in the Itinerary table for that Country and Dealer? If so, it
implies to me that you want:

Jetwhere = "[Activity] = ""Audit""" & " And [CountryCode] = " & _
Me![CountryCode] & " And [DealerCode] = """ & Me![Dealer] & """"

If DCount("*", "Itinerary", Jetwhere) > 0 Then
[AuditNo] = 99
Else
[AuditNo] = 0
End If

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Doug

Thanks for the quick reply! I've used this code before elsewhere in my
system to test for other search criteria and it works fine. Therefore I
just
copied it and changed the fieldnames etc, but I'm sure you are right and
it
is probably a logic problem ;-)

I did it this way using someone else's example a long time ago. I am only
a
novice so welcome any better suggestions

The bit after that code is:

If Exists = False Then
[AuditNo] = 0 ' no audit found
Else
[AuditNo] = 99 ' audit found
End If


Thanks in advance for any help.
Sue

You don't actually explain what's going wrong...

No offense, but might it just be a logic issue? I notice that you've name
the variable Exists, but your test actually implies that Exists will be
False if an audit exists, and True if it doesn't. (presumably DCount will
only equal 0 when no audit exists). What's the code that follows the If
Exists = False Then statement? (Incidentally, I don't know why you bother
assigning the comparison of DCount to 0 to a variable. Why not just put
the
comparison in the If statement?)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi all

I am trying to add a routine to my system to check previous history
when
creating a new record and it doesn't seem to be working? When an
Activity
of
VV is entered I want the system to see if there is an existing Audit in
the
Table: Itinerary and if not the AuditNo = 0, else = 99. I have tested
it
with
an example I know has a previous Audit therefore it should set it to 99
but
it sets it to 0. I've tried debugging and I can't see where it is going
wrong... can anyone help please?

I've added the code below and the data returned from adding a watch to
Jetwhere.

(Activity and DealerCode are text fields, CountryCode is Numeric)

Jetwhere = "[Activity] = ""Audit""" & " And [CountryCode] = " &
Me![CountryCode] & " And [DealerCode] = """ & Me![Dealer] & """"
Exists = (DCount("*", "Itinerary", Jetwhere) = 0)
If Exists = False Then

"[Activity] = "Audit" And [CountryCode] = 36 And [DealerCode] = "1377""

Thanks in advance for any help.
Sue

 

[hughess7]

Hi, one further question related to this I wondered if you could help with
please?... This works when anyone enters an Activity of VV, but how would I
write code to sort out all the 1000s of existing records? ie to change the
Audit No of a VV to 999 if an audit exists in the Itinerary table prior to
the VV date?

Kind Regards
Sue

Is my interpretation of Exists correct: it should be True if there are any
Audit records in the Itinerary table for that Country and Dealer? If so, it
implies to me that you want:

Jetwhere = "[Activity] = ""Audit""" & " And [CountryCode] = " & _
Me![CountryCode] & " And [DealerCode] = """ & Me![Dealer] & """"

If DCount("*", "Itinerary", Jetwhere) > 0 Then
[AuditNo] = 99
Else
[AuditNo] = 0
End If

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Doug

Thanks for the quick reply! I've used this code before elsewhere in my
system to test for other search criteria and it works fine. Therefore I
just
copied it and changed the fieldnames etc, but I'm sure you are right and
it
is probably a logic problem ;-)

I did it this way using someone else's example a long time ago. I am only
a
novice so welcome any better suggestions

The bit after that code is:

If Exists = False Then
[AuditNo] = 0 ' no audit found
Else
[AuditNo] = 99 ' audit found
End If


Thanks in advance for any help.
Sue

You don't actually explain what's going wrong...

No offense, but might it just be a logic issue? I notice that you've name
the variable Exists, but your test actually implies that Exists will be
False if an audit exists, and True if it doesn't. (presumably DCount will
only equal 0 when no audit exists). What's the code that follows the If
Exists = False Then statement? (Incidentally, I don't know why you bother
assigning the comparison of DCount to 0 to a variable. Why not just put
the
comparison in the If statement?)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi all

I am trying to add a routine to my system to check previous history
when
creating a new record and it doesn't seem to be working? When an
Activity
of
VV is entered I want the system to see if there is an existing Audit in
the
Table: Itinerary and if not the AuditNo = 0, else = 99. I have tested
it
with
an example I know has a previous Audit therefore it should set it to 99
but
it sets it to 0. I've tried debugging and I can't see where it is going
wrong... can anyone help please?

I've added the code below and the data returned from adding a watch to
Jetwhere.

(Activity and DealerCode are text fields, CountryCode is Numeric)

Jetwhere = "[Activity] = ""Audit""" & " And [CountryCode] = " &
Me![CountryCode] & " And [DealerCode] = """ & Me![Dealer] & """"
Exists = (DCount("*", "Itinerary", Jetwhere) = 0)
If Exists = False Then

"[Activity] = "Audit" And [CountryCode] = 36 And [DealerCode] = "1377""

Thanks in advance for any help.
Sue

 

[Douglas J. Steele]

You'd use a query. Sorry, but without details of what's in your table, it's
difficult for me to be more specific.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi, one further question related to this I wondered if you could help with
please?... This works when anyone enters an Activity of VV, but how would
I
write code to sort out all the 1000s of existing records? ie to change the
Audit No of a VV to 999 if an audit exists in the Itinerary table prior to
the VV date?

Kind Regards
Sue

Is my interpretation of Exists correct: it should be True if there are
any
Audit records in the Itinerary table for that Country and Dealer? If so,
it
implies to me that you want:

Jetwhere = "[Activity] = ""Audit""" & " And [CountryCode] = " & _
Me![CountryCode] & " And [DealerCode] = """ & Me![Dealer] & """"

If DCount("*", "Itinerary", Jetwhere) > 0 Then
[AuditNo] = 99
Else
[AuditNo] = 0
End If

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Doug

Thanks for the quick reply! I've used this code before elsewhere in my
system to test for other search criteria and it works fine. Therefore I
just
copied it and changed the fieldnames etc, but I'm sure you are right
and
it
is probably a logic problem ;-)

I did it this way using someone else's example a long time ago. I am
only
a
novice so welcome any better suggestions

The bit after that code is:

If Exists = False Then
[AuditNo] = 0 ' no audit found
Else
[AuditNo] = 99 ' audit found
End If


Thanks in advance for any help.
Sue


:

You don't actually explain what's going wrong...

No offense, but might it just be a logic issue? I notice that you've
name
the variable Exists, but your test actually implies that Exists will
be
False if an audit exists, and True if it doesn't. (presumably DCount
will
only equal 0 when no audit exists). What's the code that follows the
If
Exists = False Then statement? (Incidentally, I don't know why you
bother
assigning the comparison of DCount to 0 to a variable. Why not just
put
the
comparison in the If statement?)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi all

I am trying to add a routine to my system to check previous history
when
creating a new record and it doesn't seem to be working? When an
Activity
of
VV is entered I want the system to see if there is an existing Audit
in
the
Table: Itinerary and if not the AuditNo = 0, else = 99. I have
tested
it
with
an example I know has a previous Audit therefore it should set it to
99
but
it sets it to 0. I've tried debugging and I can't see where it is
going
wrong... can anyone help please?

I've added the code below and the data returned from adding a watch
to
Jetwhere.

(Activity and DealerCode are text fields, CountryCode is Numeric)

Jetwhere = "[Activity] = ""Audit""" & " And [CountryCode] = " &
Me![CountryCode] & " And [DealerCode] = """ & Me![Dealer] & """"
Exists = (DCount("*", "Itinerary", Jetwhere) = 0)
If Exists = False Then

"[Activity] = "Audit" And [CountryCode] = 36 And [DealerCode] =
"1377""

Thanks in advance for any help.
Sue

 

[hughess7]

I have a query which finds the latest date of activity per dealer but if the
last activity was a VV, I am not sure how to combine this with a check to see
if an audit exists. I thought it would have to be done in code.

What details do you need?

Table is Itinerary

Primary key is ReviewDate and Specialist (the same person can not have more
than one activity on any one date).

Fields are:

ItineraryID (autono.)
ReviewDate (start of activity)
ReviewDays (length of activity)
Activity (type eg audit, VV, holiday, etc)
Specialist (name of person)
DealerCode (only entered if it is a dealer activity eg audit/VV)
..... (don't think any other fields are relevant for this task)

Thanks, Sue

You'd use a query. Sorry, but without details of what's in your table, it's
difficult for me to be more specific.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi, one further question related to this I wondered if you could help with
please?... This works when anyone enters an Activity of VV, but how would
I
write code to sort out all the 1000s of existing records? ie to change the
Audit No of a VV to 999 if an audit exists in the Itinerary table prior to
the VV date?

Kind Regards
Sue

Is my interpretation of Exists correct: it should be True if there are
any
Audit records in the Itinerary table for that Country and Dealer? If so,
it
implies to me that you want:

Jetwhere = "[Activity] = ""Audit""" & " And [CountryCode] = " & _
Me![CountryCode] & " And [DealerCode] = """ & Me![Dealer] & """"

If DCount("*", "Itinerary", Jetwhere) > 0 Then
[AuditNo] = 99
Else
[AuditNo] = 0
End If

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi Doug

Thanks for the quick reply! I've used this code before elsewhere in my
system to test for other search criteria and it works fine. Therefore I
just
copied it and changed the fieldnames etc, but I'm sure you are right
and
it
is probably a logic problem ;-)

I did it this way using someone else's example a long time ago. I am
only
a
novice so welcome any better suggestions

The bit after that code is:

If Exists = False Then
[AuditNo] = 0 ' no audit found
Else
[AuditNo] = 99 ' audit found
End If


Thanks in advance for any help.
Sue


:

You don't actually explain what's going wrong...

No offense, but might it just be a logic issue? I notice that you've
name
the variable Exists, but your test actually implies that Exists will
be
False if an audit exists, and True if it doesn't. (presumably DCount
will
only equal 0 when no audit exists). What's the code that follows the
If
Exists = False Then statement? (Incidentally, I don't know why you
bother
assigning the comparison of DCount to 0 to a variable. Why not just
put
the
comparison in the If statement?)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi all

I am trying to add a routine to my system to check previous history
when
creating a new record and it doesn't seem to be working? When an
Activity
of
VV is entered I want the system to see if there is an existing Audit
in
the
Table: Itinerary and if not the AuditNo = 0, else = 99. I have
tested
it
with
an example I know has a previous Audit therefore it should set it to
99
but
it sets it to 0. I've tried debugging and I can't see where it is
going
wrong... can anyone help please?

I've added the code below and the data returned from adding a watch
to
Jetwhere.

(Activity and DealerCode are text fields, CountryCode is Numeric)

Jetwhere = "[Activity] = ""Audit""" & " And [CountryCode] = " &
Me![CountryCode] & " And [DealerCode] = """ & Me![Dealer] & """"
Exists = (DCount("*", "Itinerary", Jetwhere) = 0)
If Exists = False Then

"[Activity] = "Audit" And [CountryCode] = 36 And [DealerCode] =
"1377""

Thanks in advance for any help.
Sue

 

[Douglas J. Steele]

I may be missing something, but

SELECT Specialist, Max(ReviewDate) AS LastDate
FROM Itinerary
WHERE Activity = "Audit"
GROUP BY Specialist

will give you a list of the last audit conducted by each specialist.

You could then check for any activity after that date:

SELECT I.Specialist, I.ReviewDate, I.Activity, I.DealerCode
FROM Itinerary INNER JOIN
(
SELECT Specialist, Max(ReviewDate) AS LastDate
FROM Itinerary
WHERE Activity = "Audit"
GROUP BY Specialist
) AS S
ON I.Specialist = S.Specialist
AND I.ReviewDate > S.LastDate

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have a query which finds the latest date of activity per dealer but if
the
last activity was a VV, I am not sure how to combine this with a check to
see
if an audit exists. I thought it would have to be done in code.

What details do you need?

Table is Itinerary

Primary key is ReviewDate and Specialist (the same person can not have
more
than one activity on any one date).

Fields are:

ItineraryID (autono.)
ReviewDate (start of activity)
ReviewDays (length of activity)
Activity (type eg audit, VV, holiday, etc)
Specialist (name of person)
DealerCode (only entered if it is a dealer activity eg audit/VV)
.... (don't think any other fields are relevant for this task)

Thanks, Sue

You'd use a query. Sorry, but without details of what's in your table,
it's
difficult for me to be more specific.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi, one further question related to this I wondered if you could help
with
please?... This works when anyone enters an Activity of VV, but how
would
I
write code to sort out all the 1000s of existing records? ie to change
the
Audit No of a VV to 999 if an audit exists in the Itinerary table prior
to
the VV date?

Kind Regards
Sue


:

Is my interpretation of Exists correct: it should be True if there are
any
Audit records in the Itinerary table for that Country and Dealer? If
so,
it
implies to me that you want:

Jetwhere = "[Activity] = ""Audit""" & " And [CountryCode] = " & _
Me![CountryCode] & " And [DealerCode] = """ & Me![Dealer] & """"

If DCount("*", "Itinerary", Jetwhere) > 0 Then
[AuditNo] = 99
Else
[AuditNo] = 0
End If

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi Doug

Thanks for the quick reply! I've used this code before elsewhere in
my
system to test for other search criteria and it works fine.
Therefore I
just
copied it and changed the fieldnames etc, but I'm sure you are right
and
it
is probably a logic problem ;-)

I did it this way using someone else's example a long time ago. I am
only
a
novice so welcome any better suggestions

The bit after that code is:

If Exists = False Then
[AuditNo] = 0 ' no audit found
Else
[AuditNo] = 99 ' audit found
End If


Thanks in advance for any help.
Sue


:

You don't actually explain what's going wrong...

No offense, but might it just be a logic issue? I notice that
you've
name
the variable Exists, but your test actually implies that Exists
will
be
False if an audit exists, and True if it doesn't. (presumably
DCount
will
only equal 0 when no audit exists). What's the code that follows
the
If
Exists = False Then statement? (Incidentally, I don't know why you
bother
assigning the comparison of DCount to 0 to a variable. Why not just
put
the
comparison in the If statement?)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi all

I am trying to add a routine to my system to check previous
history
when
creating a new record and it doesn't seem to be working? When an
Activity
of
VV is entered I want the system to see if there is an existing
Audit
in
the
Table: Itinerary and if not the AuditNo = 0, else = 99. I have
tested
it
with
an example I know has a previous Audit therefore it should set it
to
99
but
it sets it to 0. I've tried debugging and I can't see where it is
going
wrong... can anyone help please?

I've added the code below and the data returned from adding a
watch
to
Jetwhere.

(Activity and DealerCode are text fields, CountryCode is Numeric)

Jetwhere = "[Activity] = ""Audit""" & " And [CountryCode] = "
&
Me![CountryCode] & " And [DealerCode] = """ & Me![Dealer] & """"
Exists = (DCount("*", "Itinerary", Jetwhere) = 0)
If Exists = False Then

"[Activity] = "Audit" And [CountryCode] = 36 And [DealerCode] =
"1377""

Thanks in advance for any help.
Sue

 

[hughess7]

Thanks very much, I did forget two vital bits of info though sorry! It needs
to be grouped by Customer and Country rather than Specialist, we deal with
approx 15 different countries and some have the same dealer codes. Currently
we only do this for custid=3. The Specialist is not always the same person
every time who visits the dealer. I assume I would just change the groupby
clause to reflect these columns instead?

The dealer can have several audits though or different activities, I only
need to check the history if the LAST activity was a VV. If there wasn't an
Audit before it (which usually there isn't) the AuditNo remains at zero, but
if there was an audit I want to update the AuditNo to 99.

Thanks...

I may be missing something, but

SELECT Specialist, Max(ReviewDate) AS LastDate
FROM Itinerary
WHERE Activity = "Audit"
GROUP BY Specialist

will give you a list of the last audit conducted by each specialist.

You could then check for any activity after that date:

SELECT I.Specialist, I.ReviewDate, I.Activity, I.DealerCode
FROM Itinerary INNER JOIN
(
SELECT Specialist, Max(ReviewDate) AS LastDate
FROM Itinerary
WHERE Activity = "Audit"
GROUP BY Specialist
) AS S
ON I.Specialist = S.Specialist
AND I.ReviewDate > S.LastDate

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have a query which finds the latest date of activity per dealer but if
the
last activity was a VV, I am not sure how to combine this with a check to
see
if an audit exists. I thought it would have to be done in code.

What details do you need?

Table is Itinerary

Primary key is ReviewDate and Specialist (the same person can not have
more
than one activity on any one date).

Fields are:

ItineraryID (autono.)
ReviewDate (start of activity)
ReviewDays (length of activity)
Activity (type eg audit, VV, holiday, etc)
Specialist (name of person)
DealerCode (only entered if it is a dealer activity eg audit/VV)
.... (don't think any other fields are relevant for this task)

Thanks, Sue

You'd use a query. Sorry, but without details of what's in your table,
it's
difficult for me to be more specific.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi, one further question related to this I wondered if you could help
with
please?... This works when anyone enters an Activity of VV, but how
would
I
write code to sort out all the 1000s of existing records? ie to change
the
Audit No of a VV to 999 if an audit exists in the Itinerary table prior
to
the VV date?

Kind Regards
Sue


:

Is my interpretation of Exists correct: it should be True if there are
any
Audit records in the Itinerary table for that Country and Dealer? If
so,
it
implies to me that you want:

Jetwhere = "[Activity] = ""Audit""" & " And [CountryCode] = " & _
Me![CountryCode] & " And [DealerCode] = """ & Me![Dealer] & """"

If DCount("*", "Itinerary", Jetwhere) > 0 Then
[AuditNo] = 99
Else
[AuditNo] = 0
End If

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi Doug

Thanks for the quick reply! I've used this code before elsewhere in
my
system to test for other search criteria and it works fine.
Therefore I
just
copied it and changed the fieldnames etc, but I'm sure you are right
and
it
is probably a logic problem ;-)

I did it this way using someone else's example a long time ago. I am
only
a
novice so welcome any better suggestions

The bit after that code is:

If Exists = False Then
[AuditNo] = 0 ' no audit found
Else
[AuditNo] = 99 ' audit found
End If


Thanks in advance for any help.
Sue


:

You don't actually explain what's going wrong...

No offense, but might it just be a logic issue? I notice that
you've
name
the variable Exists, but your test actually implies that Exists
will
be
False if an audit exists, and True if it doesn't. (presumably
DCount
will
only equal 0 when no audit exists). What's the code that follows
the
If
Exists = False Then statement? (Incidentally, I don't know why you
bother
assigning the comparison of DCount to 0 to a variable. Why not just
put
the
comparison in the If statement?)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi all

I am trying to add a routine to my system to check previous
history
when
creating a new record and it doesn't seem to be working? When an
Activity
of
VV is entered I want the system to see if there is an existing
Audit
in
the
Table: Itinerary and if not the AuditNo = 0, else = 99. I have
tested
it
with
an example I know has a previous Audit therefore it should set it
to
99
but
it sets it to 0. I've tried debugging and I can't see where it is
going
wrong... can anyone help please?

I've added the code below and the data returned from adding a
watch
to
Jetwhere.

(Activity and DealerCode are text fields, CountryCode is Numeric)

Jetwhere = "[Activity] = ""Audit""" & " And [CountryCode] = "
&
Me![CountryCode] & " And [DealerCode] = """ & Me![Dealer] & """"
Exists = (DCount("*", "Itinerary", Jetwhere) = 0)
If Exists = False Then

"[Activity] = "Audit" And [CountryCode] = 36 And [DealerCode] =
"1377""

Thanks in advance for any help.
Sue

 

[Douglas J. Steele]

Basically.

Try something like:

SELECT I.Specialist, I.ReviewDate, I.Activity, I.DealerCode
FROM Itinerary INNER JOIN
(
SELECT CountryCode, DealerCode, Max(ReviewDate) AS LastDate
FROM Itinerary
WHERE Activity = "Audit"
GROUP BY CountryCode, DealerCode
) AS S
ON I.CountryCode = S.CountryCode
ON I.DealerCode = S.DealerCode
AND I.ReviewDate > S.LastDate

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks very much, I did forget two vital bits of info though sorry! It
needs
to be grouped by Customer and Country rather than Specialist, we deal with
approx 15 different countries and some have the same dealer codes.
Currently
we only do this for custid=3. The Specialist is not always the same person
every time who visits the dealer. I assume I would just change the groupby
clause to reflect these columns instead?

The dealer can have several audits though or different activities, I only
need to check the history if the LAST activity was a VV. If there wasn't
an
Audit before it (which usually there isn't) the AuditNo remains at zero,
but
if there was an audit I want to update the AuditNo to 99.

Thanks...

I may be missing something, but

SELECT Specialist, Max(ReviewDate) AS LastDate
FROM Itinerary
WHERE Activity = "Audit"
GROUP BY Specialist

will give you a list of the last audit conducted by each specialist.

You could then check for any activity after that date:

SELECT I.Specialist, I.ReviewDate, I.Activity, I.DealerCode
FROM Itinerary INNER JOIN
(
SELECT Specialist, Max(ReviewDate) AS LastDate
FROM Itinerary
WHERE Activity = "Audit"
GROUP BY Specialist
) AS S
ON I.Specialist = S.Specialist
AND I.ReviewDate > S.LastDate

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have a query which finds the latest date of activity per dealer but if
the
last activity was a VV, I am not sure how to combine this with a check
to
see
if an audit exists. I thought it would have to be done in code.

What details do you need?

Table is Itinerary

Primary key is ReviewDate and Specialist (the same person can not have
more
than one activity on any one date).

Fields are:

ItineraryID (autono.)
ReviewDate (start of activity)
ReviewDays (length of activity)
Activity (type eg audit, VV, holiday, etc)
Specialist (name of person)
DealerCode (only entered if it is a dealer activity eg audit/VV)
.... (don't think any other fields are relevant for this task)

Thanks, Sue


:

You'd use a query. Sorry, but without details of what's in your table,
it's
difficult for me to be more specific.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi, one further question related to this I wondered if you could
help
with
please?... This works when anyone enters an Activity of VV, but how
would
I
write code to sort out all the 1000s of existing records? ie to
change
the
Audit No of a VV to 999 if an audit exists in the Itinerary table
prior
to
the VV date?

Kind Regards
Sue


:

Is my interpretation of Exists correct: it should be True if there
are
any
Audit records in the Itinerary table for that Country and Dealer?
If
so,
it
implies to me that you want:

Jetwhere = "[Activity] = ""Audit""" & " And [CountryCode] = " & _
Me![CountryCode] & " And [DealerCode] = """ & Me![Dealer] &
""""

If DCount("*", "Itinerary", Jetwhere) > 0 Then
[AuditNo] = 99
Else
[AuditNo] = 0
End If

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi Doug

Thanks for the quick reply! I've used this code before elsewhere
in
my
system to test for other search criteria and it works fine.
Therefore I
just
copied it and changed the fieldnames etc, but I'm sure you are
right
and
it
is probably a logic problem ;-)

I did it this way using someone else's example a long time ago. I
am
only
a
novice so welcome any better suggestions

The bit after that code is:

If Exists = False Then
[AuditNo] = 0 ' no audit found
Else
[AuditNo] = 99 ' audit found
End If


Thanks in advance for any help.
Sue


:

You don't actually explain what's going wrong...

No offense, but might it just be a logic issue? I notice that
you've
name
the variable Exists, but your test actually implies that Exists
will
be
False if an audit exists, and True if it doesn't. (presumably
DCount
will
only equal 0 when no audit exists). What's the code that follows
the
If
Exists = False Then statement? (Incidentally, I don't know why
you
bother
assigning the comparison of DCount to 0 to a variable. Why not
just
put
the
comparison in the If statement?)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi all

I am trying to add a routine to my system to check previous
history
when
creating a new record and it doesn't seem to be working? When
an
Activity
of
VV is entered I want the system to see if there is an existing
Audit
in
the
Table: Itinerary and if not the AuditNo = 0, else = 99. I have
tested
it
with
an example I know has a previous Audit therefore it should set
it
to
99
but
it sets it to 0. I've tried debugging and I can't see where it
is
going
wrong... can anyone help please?

I've added the code below and the data returned from adding a
watch
to
Jetwhere.

(Activity and DealerCode are text fields, CountryCode is
Numeric)

Jetwhere = "[Activity] = ""Audit""" & " And [CountryCode] =
"
&
Me![CountryCode] & " And [DealerCode] = """ & Me![Dealer] &
""""
Exists = (DCount("*", "Itinerary", Jetwhere) = 0)
If Exists = False Then

"[Activity] = "Audit" And [CountryCode] = 36 And [DealerCode]
=
"1377""

Thanks in advance for any help.
Sue

 

[hughess7]

Thanks for this, will try to get it working. At the moment I get syntax error
(missing operator) in query expression 'I.CountryCode = S.CountryCode ON
I.DealerCode = S.DealerCode. If I try to fix this I then get an error on the
From Clause.

Sue

Basically.

Try something like:

SELECT I.Specialist, I.ReviewDate, I.Activity, I.DealerCode
FROM Itinerary INNER JOIN
(
SELECT CountryCode, DealerCode, Max(ReviewDate) AS LastDate
FROM Itinerary
WHERE Activity = "Audit"
GROUP BY CountryCode, DealerCode
) AS S
ON I.CountryCode = S.CountryCode
ON I.DealerCode = S.DealerCode
AND I.ReviewDate > S.LastDate

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks very much, I did forget two vital bits of info though sorry! It
needs
to be grouped by Customer and Country rather than Specialist, we deal with
approx 15 different countries and some have the same dealer codes.
Currently
we only do this for custid=3. The Specialist is not always the same person
every time who visits the dealer. I assume I would just change the groupby
clause to reflect these columns instead?

The dealer can have several audits though or different activities, I only
need to check the history if the LAST activity was a VV. If there wasn't
an
Audit before it (which usually there isn't) the AuditNo remains at zero,
but
if there was an audit I want to update the AuditNo to 99.

Thanks...

I may be missing something, but

SELECT Specialist, Max(ReviewDate) AS LastDate
FROM Itinerary
WHERE Activity = "Audit"
GROUP BY Specialist

will give you a list of the last audit conducted by each specialist.

You could then check for any activity after that date:

SELECT I.Specialist, I.ReviewDate, I.Activity, I.DealerCode
FROM Itinerary INNER JOIN
(
SELECT Specialist, Max(ReviewDate) AS LastDate
FROM Itinerary
WHERE Activity = "Audit"
GROUP BY Specialist
) AS S
ON I.Specialist = S.Specialist
AND I.ReviewDate > S.LastDate

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have a query which finds the latest date of activity per dealer but if
the
last activity was a VV, I am not sure how to combine this with a check
to
see
if an audit exists. I thought it would have to be done in code.

What details do you need?

Table is Itinerary

Primary key is ReviewDate and Specialist (the same person can not have
more
than one activity on any one date).

Fields are:

ItineraryID (autono.)
ReviewDate (start of activity)
ReviewDays (length of activity)
Activity (type eg audit, VV, holiday, etc)
Specialist (name of person)
DealerCode (only entered if it is a dealer activity eg audit/VV)
.... (don't think any other fields are relevant for this task)

Thanks, Sue


:

You'd use a query. Sorry, but without details of what's in your table,
it's
difficult for me to be more specific.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi, one further question related to this I wondered if you could
help
with
please?... This works when anyone enters an Activity of VV, but how
would
I
write code to sort out all the 1000s of existing records? ie to
change
the
Audit No of a VV to 999 if an audit exists in the Itinerary table
prior
to
the VV date?

Kind Regards
Sue


:

Is my interpretation of Exists correct: it should be True if there
are
any
Audit records in the Itinerary table for that Country and Dealer?
If
so,
it
implies to me that you want:

Jetwhere = "[Activity] = ""Audit""" & " And [CountryCode] = " & _
Me![CountryCode] & " And [DealerCode] = """ & Me![Dealer] &
""""

If DCount("*", "Itinerary", Jetwhere) > 0 Then
[AuditNo] = 99
Else
[AuditNo] = 0
End If

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi Doug

Thanks for the quick reply! I've used this code before elsewhere
in
my
system to test for other search criteria and it works fine.
Therefore I
just
copied it and changed the fieldnames etc, but I'm sure you are
right
and
it
is probably a logic problem ;-)

I did it this way using someone else's example a long time ago. I
am
only
a
novice so welcome any better suggestions

The bit after that code is:

If Exists = False Then
[AuditNo] = 0 ' no audit found
Else
[AuditNo] = 99 ' audit found
End If


Thanks in advance for any help.
Sue


:

You don't actually explain what's going wrong...

No offense, but might it just be a logic issue? I notice that
you've
name
the variable Exists, but your test actually implies that Exists
will
be
False if an audit exists, and True if it doesn't. (presumably
DCount
will
only equal 0 when no audit exists). What's the code that follows
the
If
Exists = False Then statement? (Incidentally, I don't know why
you
bother
assigning the comparison of DCount to 0 to a variable. Why not
just
put
the
comparison in the If statement?)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi all

I am trying to add a routine to my system to check previous
history
when
creating a new record and it doesn't seem to be working? When
an
Activity
of
VV is entered I want the system to see if there is an existing
Audit
in
the
Table: Itinerary and if not the AuditNo = 0, else = 99. I have
tested
it
with
an example I know has a previous Audit therefore it should set
it
to
99
but
it sets it to 0. I've tried debugging and I can't see where it
is
going
wrong... can anyone help please?

I've added the code below and the data returned from adding a
watch
to
Jetwhere.

(Activity and DealerCode are text fields, CountryCode is
Numeric)

Jetwhere = "[Activity] = ""Audit""" & " And [CountryCode] =
"
&
Me![CountryCode] & " And [DealerCode] = """ & Me![Dealer] &
""""
Exists = (DCount("*", "Itinerary", Jetwhere) = 0)
If Exists = False Then

"[Activity] = "Audit" And [CountryCode] = 36 And [DealerCode]
=
"1377""

Thanks in advance for any help.
Sue

 

[hughess7]

I have got the following sql working which returns all the last audit dates
per country and dealer for customer 3. Now I need to add to this to see if
there is a VV date later than the last audit date, can it be done in the same
query?

SELECT I1.CountryCode, I1.ReviewDate AS LastAudit, I1.DealerCode,
I1.Specialist, I1.AuditNo, I1.CustID, I1.Activity
FROM Specialists INNER JOIN (ZONELIST INNER JOIN Itinerary AS I1 ON
(ZONELIST.Dealer = I1.DealerCode) AND (ZONELIST.CustID = I1.CustID) AND
(ZONELIST.CountryID = I1.CountryCode)) ON Specialists.SpecialistID =
I1.Specialist
WHERE (((I1.ReviewDate)=(SELECT MAX(ReviewDate) From Itinerary As I2

WHERE (I2.Activity="Audit") AND I2.CustID=3 AND I2.DealerCode =
I1.DealerCode AND I2.CountryCode = I1.CountryCode AND ZONELIST.Live=True)))
ORDER BY I1.CountryCode, I1.ReviewDate DESC;

Thanks in advance for any help.
Sue

Thanks for this, will try to get it working. At the moment I get syntax error
(missing operator) in query expression 'I.CountryCode = S.CountryCode ON
I.DealerCode = S.DealerCode. If I try to fix this I then get an error on the
From Clause.

Sue

Basically.

Try something like:

SELECT I.Specialist, I.ReviewDate, I.Activity, I.DealerCode
FROM Itinerary INNER JOIN
(
SELECT CountryCode, DealerCode, Max(ReviewDate) AS LastDate
FROM Itinerary
WHERE Activity = "Audit"
GROUP BY CountryCode, DealerCode
) AS S
ON I.CountryCode = S.CountryCode
ON I.DealerCode = S.DealerCode
AND I.ReviewDate > S.LastDate

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks very much, I did forget two vital bits of info though sorry! It
needs
to be grouped by Customer and Country rather than Specialist, we deal with
approx 15 different countries and some have the same dealer codes.
Currently
we only do this for custid=3. The Specialist is not always the same person
every time who visits the dealer. I assume I would just change the groupby
clause to reflect these columns instead?

The dealer can have several audits though or different activities, I only
need to check the history if the LAST activity was a VV. If there wasn't
an
Audit before it (which usually there isn't) the AuditNo remains at zero,
but
if there was an audit I want to update the AuditNo to 99.

Thanks...

:

I may be missing something, but

SELECT Specialist, Max(ReviewDate) AS LastDate
FROM Itinerary
WHERE Activity = "Audit"
GROUP BY Specialist

will give you a list of the last audit conducted by each specialist.

You could then check for any activity after that date:

SELECT I.Specialist, I.ReviewDate, I.Activity, I.DealerCode
FROM Itinerary INNER JOIN
(
SELECT Specialist, Max(ReviewDate) AS LastDate
FROM Itinerary
WHERE Activity = "Audit"
GROUP BY Specialist
) AS S
ON I.Specialist = S.Specialist
AND I.ReviewDate > S.LastDate

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have a query which finds the latest date of activity per dealer but if
the
last activity was a VV, I am not sure how to combine this with a check
to
see
if an audit exists. I thought it would have to be done in code.

What details do you need?

Table is Itinerary

Primary key is ReviewDate and Specialist (the same person can not have
more
than one activity on any one date).

Fields are:

ItineraryID (autono.)
ReviewDate (start of activity)
ReviewDays (length of activity)
Activity (type eg audit, VV, holiday, etc)
Specialist (name of person)
DealerCode (only entered if it is a dealer activity eg audit/VV)
.... (don't think any other fields are relevant for this task)

Thanks, Sue


:

You'd use a query. Sorry, but without details of what's in your table,
it's
difficult for me to be more specific.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi, one further question related to this I wondered if you could
help
with
please?... This works when anyone enters an Activity of VV, but how
would
I
write code to sort out all the 1000s of existing records? ie to
change
the
Audit No of a VV to 999 if an audit exists in the Itinerary table
prior
to
the VV date?

Kind Regards
Sue


:

Is my interpretation of Exists correct: it should be True if there
are
any
Audit records in the Itinerary table for that Country and Dealer?
If
so,
it
implies to me that you want:

Jetwhere = "[Activity] = ""Audit""" & " And [CountryCode] = " & _
Me![CountryCode] & " And [DealerCode] = """ & Me![Dealer] &
""""

If DCount("*", "Itinerary", Jetwhere) > 0 Then
[AuditNo] = 99
Else
[AuditNo] = 0
End If

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi Doug

Thanks for the quick reply! I've used this code before elsewhere
in
my
system to test for other search criteria and it works fine.
Therefore I
just
copied it and changed the fieldnames etc, but I'm sure you are
right
and
it
is probably a logic problem ;-)

I did it this way using someone else's example a long time ago. I
am
only
a
novice so welcome any better suggestions

The bit after that code is:

If Exists = False Then
[AuditNo] = 0 ' no audit found
Else
[AuditNo] = 99 ' audit found
End If


Thanks in advance for any help.
Sue


:

You don't actually explain what's going wrong...

No offense, but might it just be a logic issue? I notice that
you've
name
the variable Exists, but your test actually implies that Exists
will
be
False if an audit exists, and True if it doesn't. (presumably
DCount
will
only equal 0 when no audit exists). What's the code that follows
the
If
Exists = False Then statement? (Incidentally, I don't know why
you
bother
assigning the comparison of DCount to 0 to a variable. Why not
just
put
the
comparison in the If statement?)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi all

I am trying to add a routine to my system to check previous
history
when
creating a new record and it doesn't seem to be working? When
an
Activity
of
VV is entered I want the system to see if there is an existing
Audit
in
the
Table: Itinerary and if not the AuditNo = 0, else = 99. I have
tested
it
with
an example I know has a previous Audit therefore it should set
it
to
99
but
it sets it to 0. I've tried debugging and I can't see where it
is
going
wrong... can anyone help please?

I've added the code below and the data returned from adding a
watch
to
Jetwhere.

(Activity and DealerCode are text fields, CountryCode is
Numeric)

Jetwhere = "[Activity] = ""Audit""" & " And [CountryCode] =
"
&
Me![CountryCode] & " And [DealerCode] = """ & Me![Dealer] &
""""
Exists = (DCount("*", "Itinerary", Jetwhere) = 0)
If Exists = False Then

"[Activity] = "Audit" And [CountryCode] = 36 And [DealerCode]
=
"1377""

Thanks in advance for any help.
Sue

 

[Douglas J. Steele]

Sorry: my typo

SELECT I.Specialist, I.ReviewDate, I.Activity, I.DealerCode
FROM Itinerary INNER JOIN
(
SELECT CountryCode, DealerCode, Max(ReviewDate) AS LastDate
FROM Itinerary
WHERE Activity = "Audit"
GROUP BY CountryCode, DealerCode
) AS S
ON I.CountryCode = S.CountryCode
AND I.DealerCode = S.DealerCode
AND I.ReviewDate > S.LastDate

The perils of copy and paste!

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks for this, will try to get it working. At the moment I get syntax
error
(missing operator) in query expression 'I.CountryCode = S.CountryCode ON
I.DealerCode = S.DealerCode. If I try to fix this I then get an error on
the
From Clause.

Sue

Basically.

Try something like:

SELECT I.Specialist, I.ReviewDate, I.Activity, I.DealerCode
FROM Itinerary INNER JOIN
(
SELECT CountryCode, DealerCode, Max(ReviewDate) AS LastDate
FROM Itinerary
WHERE Activity = "Audit"
GROUP BY CountryCode, DealerCode
) AS S
ON I.CountryCode = S.CountryCode
ON I.DealerCode = S.DealerCode
AND I.ReviewDate > S.LastDate

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks very much, I did forget two vital bits of info though sorry! It
needs
to be grouped by Customer and Country rather than Specialist, we deal
with
approx 15 different countries and some have the same dealer codes.
Currently
we only do this for custid=3. The Specialist is not always the same
person
every time who visits the dealer. I assume I would just change the
groupby
clause to reflect these columns instead?

The dealer can have several audits though or different activities, I
only
need to check the history if the LAST activity was a VV. If there
wasn't
an
Audit before it (which usually there isn't) the AuditNo remains at
zero,
but
if there was an audit I want to update the AuditNo to 99.

Thanks...

:

I may be missing something, but

SELECT Specialist, Max(ReviewDate) AS LastDate
FROM Itinerary
WHERE Activity = "Audit"
GROUP BY Specialist

will give you a list of the last audit conducted by each specialist.

You could then check for any activity after that date:

SELECT I.Specialist, I.ReviewDate, I.Activity, I.DealerCode
FROM Itinerary INNER JOIN
(
SELECT Specialist, Max(ReviewDate) AS LastDate
FROM Itinerary
WHERE Activity = "Audit"
GROUP BY Specialist
) AS S
ON I.Specialist = S.Specialist
AND I.ReviewDate > S.LastDate

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have a query which finds the latest date of activity per dealer but
if
the
last activity was a VV, I am not sure how to combine this with a
check
to
see
if an audit exists. I thought it would have to be done in code.

What details do you need?

Table is Itinerary

Primary key is ReviewDate and Specialist (the same person can not
have
more
than one activity on any one date).

Fields are:

ItineraryID (autono.)
ReviewDate (start of activity)
ReviewDays (length of activity)
Activity (type eg audit, VV, holiday, etc)
Specialist (name of person)
DealerCode (only entered if it is a dealer activity eg audit/VV)
.... (don't think any other fields are relevant for this task)

Thanks, Sue


:

You'd use a query. Sorry, but without details of what's in your
table,
it's
difficult for me to be more specific.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi, one further question related to this I wondered if you could
help
with
please?... This works when anyone enters an Activity of VV, but
how
would
I
write code to sort out all the 1000s of existing records? ie to
change
the
Audit No of a VV to 999 if an audit exists in the Itinerary table
prior
to
the VV date?

Kind Regards
Sue


:

Is my interpretation of Exists correct: it should be True if
there
are
any
Audit records in the Itinerary table for that Country and
Dealer?
If
so,
it
implies to me that you want:

Jetwhere = "[Activity] = ""Audit""" & " And [CountryCode] = "
& _
Me![CountryCode] & " And [DealerCode] = """ & Me![Dealer] &
""""

If DCount("*", "Itinerary", Jetwhere) > 0 Then
[AuditNo] = 99
Else
[AuditNo] = 0
End If

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi Doug

Thanks for the quick reply! I've used this code before
elsewhere
in
my
system to test for other search criteria and it works fine.
Therefore I
just
copied it and changed the fieldnames etc, but I'm sure you are
right
and
it
is probably a logic problem ;-)

I did it this way using someone else's example a long time
ago. I
am
only
a
novice so welcome any better suggestions

The bit after that code is:

If Exists = False Then
[AuditNo] = 0 ' no audit found
Else
[AuditNo] = 99 ' audit found
End If


Thanks in advance for any help.
Sue


:

You don't actually explain what's going wrong...

No offense, but might it just be a logic issue? I notice that
you've
name
the variable Exists, but your test actually implies that
Exists
will
be
False if an audit exists, and True if it doesn't. (presumably
DCount
will
only equal 0 when no audit exists). What's the code that
follows
the
If
Exists = False Then statement? (Incidentally, I don't know
why
you
bother
assigning the comparison of DCount to 0 to a variable. Why
not
just
put
the
comparison in the If statement?)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message
Hi all

I am trying to add a routine to my system to check previous
history
when
creating a new record and it doesn't seem to be working?
When
an
Activity
of
VV is entered I want the system to see if there is an
existing
Audit
in
the
Table: Itinerary and if not the AuditNo = 0, else = 99. I
have
tested
it
with
an example I know has a previous Audit therefore it should
set
it
to
99
but
it sets it to 0. I've tried debugging and I can't see where
it
is
going
wrong... can anyone help please?

I've added the code below and the data returned from adding
a
watch
to
Jetwhere.

(Activity and DealerCode are text fields, CountryCode is
Numeric)

Jetwhere = "[Activity] = ""Audit""" & " And
[CountryCode] =
"
&
Me![CountryCode] & " And [DealerCode] = """ & Me![Dealer] &
""""
Exists = (DCount("*", "Itinerary", Jetwhere) = 0)
If Exists = False Then

"[Activity] = "Audit" And [CountryCode] = 36 And
[DealerCode]
=
"1377""

Thanks in advance for any help.
Sue

 

[hughess7]

. If I simply copy and paste this into sql view I still get a syntax join
error.

Sue

Sorry: my typo

SELECT I.Specialist, I.ReviewDate, I.Activity, I.DealerCode
FROM Itinerary INNER JOIN
(
SELECT CountryCode, DealerCode, Max(ReviewDate) AS LastDate
FROM Itinerary
WHERE Activity = "Audit"
GROUP BY CountryCode, DealerCode
) AS S
ON I.CountryCode = S.CountryCode
AND I.DealerCode = S.DealerCode
AND I.ReviewDate > S.LastDate

The perils of copy and paste!

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks for this, will try to get it working. At the moment I get syntax
error
(missing operator) in query expression 'I.CountryCode = S.CountryCode ON
I.DealerCode = S.DealerCode. If I try to fix this I then get an error on
the
From Clause.

Sue

Basically.

Try something like:

SELECT I.Specialist, I.ReviewDate, I.Activity, I.DealerCode
FROM Itinerary INNER JOIN
(
SELECT CountryCode, DealerCode, Max(ReviewDate) AS LastDate
FROM Itinerary
WHERE Activity = "Audit"
GROUP BY CountryCode, DealerCode
) AS S
ON I.CountryCode = S.CountryCode
ON I.DealerCode = S.DealerCode
AND I.ReviewDate > S.LastDate

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Thanks very much, I did forget two vital bits of info though sorry! It
needs
to be grouped by Customer and Country rather than Specialist, we deal
with
approx 15 different countries and some have the same dealer codes.
Currently
we only do this for custid=3. The Specialist is not always the same
person
every time who visits the dealer. I assume I would just change the
groupby
clause to reflect these columns instead?

The dealer can have several audits though or different activities, I
only
need to check the history if the LAST activity was a VV. If there
wasn't
an
Audit before it (which usually there isn't) the AuditNo remains at
zero,
but
if there was an audit I want to update the AuditNo to 99.

Thanks...

:

I may be missing something, but

SELECT Specialist, Max(ReviewDate) AS LastDate
FROM Itinerary
WHERE Activity = "Audit"
GROUP BY Specialist

will give you a list of the last audit conducted by each specialist.

You could then check for any activity after that date:

SELECT I.Specialist, I.ReviewDate, I.Activity, I.DealerCode
FROM Itinerary INNER JOIN
(
SELECT Specialist, Max(ReviewDate) AS LastDate
FROM Itinerary
WHERE Activity = "Audit"
GROUP BY Specialist
) AS S
ON I.Specialist = S.Specialist
AND I.ReviewDate > S.LastDate

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have a query which finds the latest date of activity per dealer but
if
the
last activity was a VV, I am not sure how to combine this with a
check
to
see
if an audit exists. I thought it would have to be done in code.

What details do you need?

Table is Itinerary

Primary key is ReviewDate and Specialist (the same person can not
have
more
than one activity on any one date).

Fields are:

ItineraryID (autono.)
ReviewDate (start of activity)
ReviewDays (length of activity)
Activity (type eg audit, VV, holiday, etc)
Specialist (name of person)
DealerCode (only entered if it is a dealer activity eg audit/VV)
.... (don't think any other fields are relevant for this task)

Thanks, Sue


:

You'd use a query. Sorry, but without details of what's in your
table,
it's
difficult for me to be more specific.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi, one further question related to this I wondered if you could
help
with
please?... This works when anyone enters an Activity of VV, but
how
would
I
write code to sort out all the 1000s of existing records? ie to
change
the
Audit No of a VV to 999 if an audit exists in the Itinerary table
prior
to
the VV date?

Kind Regards
Sue


:

Is my interpretation of Exists correct: it should be True if
there
are
any
Audit records in the Itinerary table for that Country and
Dealer?
If
so,
it
implies to me that you want:

Jetwhere = "[Activity] = ""Audit""" & " And [CountryCode] = "
& _
Me![CountryCode] & " And [DealerCode] = """ & Me![Dealer] &
""""

If DCount("*", "Itinerary", Jetwhere) > 0 Then
[AuditNo] = 99
Else
[AuditNo] = 0
End If

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi Doug

Thanks for the quick reply! I've used this code before
elsewhere
in
my
system to test for other search criteria and it works fine.
Therefore I
just
copied it and changed the fieldnames etc, but I'm sure you are
right
and
it
is probably a logic problem ;-)

I did it this way using someone else's example a long time
ago. I
am
only
a
novice so welcome any better suggestions

The bit after that code is:

If Exists = False Then
[AuditNo] = 0 ' no audit found
Else
[AuditNo] = 99 ' audit found
End If


Thanks in advance for any help.
Sue


:

You don't actually explain what's going wrong...

No offense, but might it just be a logic issue? I notice that
you've
name
the variable Exists, but your test actually implies that
Exists
will
be
False if an audit exists, and True if it doesn't. (presumably
DCount
will
only equal 0 when no audit exists). What's the code that
follows
the
If
Exists = False Then statement? (Incidentally, I don't know
why
you
bother
assigning the comparison of DCount to 0 to a variable. Why
not
just
put
the
comparison in the If statement?)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message
Hi all

I am trying to add a routine to my system to check previous
history
when
creating a new record and it doesn't seem to be working?
When
an
Activity
of
VV is entered I want the system to see if there is an
existing
Audit

 

[John Spencer]

Douglas missed assigning the Alias to Itinerary

SELECT I.Specialist, I.ReviewDate, I.Activity, I.DealerCode
FROM Itinerary AS I INNER JOIN
(
SELECT CountryCode, DealerCode, Max(ReviewDate) AS LastDate
FROM Itinerary
WHERE Activity = "Audit"
GROUP BY CountryCode, DealerCode
) AS S
ON I.CountryCode = S.CountryCode
AND I.DealerCode = S.DealerCode
AND I.ReviewDate > S.LastDate

The perils of copy and paste!

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[hughess7]

Thanks John! I have got this working with your amendment and altered slightly
to group by CUSTID too. This won't let me edit the query in design view as I
get an error but it produces the data ok when executed . Thanks guys for
all your help, much appreciated...

Sue

SELECT I.CountryCode, I.CustID, I.Specialist, I.ReviewDate, I.Activity,
I.DealerCode, I.AuditNo
FROM Itinerary AS I INNER JOIN
(
SELECT CustID, CountryCode, DealerCode, Max(ReviewDate) AS LastDate
FROM Itinerary
WHERE Activity = "Audit" and CustID = 3
GROUP BY CustID, CountryCode, DealerCode
) AS S
ON I.CountryCode = S.CountryCode
AND I.CustID = S.CustID
AND I.DealerCode = S.DealerCode
AND I.ReviewDate > S.LastDate
ORDER BY I.CountryCode, I.DealerCode,I.ReviewDate Desc;

 

[hughess7]

One small problem, I did this exercise so I could change all the AuditNo's in
the queries results but I can't? I tried saving this query as a select query
and doing an update query on it, but it told me it wasn't updateable.

Can you tell me how I can achieve this without having to manually do the
updates since there are over a 100 records to modify?
Thanks
Sue

 

[John Spencer]

Perhaps by using a correlated subquery in the where clause you can make
this an updateable query.

SELECT I.CountryCode, I.CustID, I.Specialist, I.ReviewDate, I.Activity,
I.DealerCode, I.AuditNo
FROM Itinerary AS I
WHERE I.ReviewDate = (
SELECT Max(ReviewDate) AS LastDate
FROM Itinerary as X
WHERE X.Activity = "Audit" and X.CustID = 3
AND X.CountryCode = I.CountryCode
AND X.DealerCode = I.DealerCode)

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[hughess7]

Hi John

This query doesn't give me the same set of data though? It gives me all the
last audit records, whereas the other query gave me any VV records that
occured after the last audit date for that dealer.

Kind Regards
Sue