Not like

[Charles via AccessMonster.com]

I cannot get the Not Like criteria to work in the Absence field, I try to
delete the filed and replace it with another name to no avail I try other
filed and it works fine.

Why?

Charles


SELECT [Daily Assignments].Absent, NameComb.Names1, [Daily Assignments].Date,
[Daily Assignments].Seniority, [Daily Assignments].RDO, [Daily Assignments].
DriverID, [Daily Assignments].Hrs, [Daily Assignments].StartTime, [Daily
Assignments].Day, [Daily Assignments].EndTime, [Daily Assignments].Route,
[Daily Assignments].Nextel
FROM [Daily Assignments] INNER JOIN NameComb ON [Daily Assignments].DriverID
= NameComb.ID
WHERE ((([Daily Assignments].Absent) Not Like '*Exc*') AND (([Daily
Assignments].Date)=[Forms]![Operation]![ServDate]) AND (([Daily Assignments].
Day) Not Like "*Off*"))
ORDER BY [Daily Assignments].StartTime, [Daily Assignments].Day;

 

[Duane Hookom]

You might want to tell us why you don't think it is working. From our
perspective, everything looks great. The [Absent] field isn't a lookup field
is it?

 

[quinto via AccessMonster.com]

It was a look up field and I delete it, renamed it and still the same
problem, when I enter a Not Like criteria it will not select that record but
it will only select other records with entries and not one with nulls in the
same field.. I wan to exclude absences from the final report. "LOA", "Vac",
"Sick" etc. when I enter one criteria only the fileds with the other entries
are selected and if enter all of the criterias no records are selected. In
the same Query I have a nother fiels with the Criteria Not Like "Off "and it
works fine. I tested other fields and they work but not the one that I want
to use


Thanks

Charles

You might want to tell us why you don't think it is working. From our
perspective, everything looks great. The [Absent] field isn't a lookup field
is it?

I cannot get the Not Like criteria to work in the Absence field, I try to
delete the filed and replace it with another name to no avail I try other

[quoted text clipped - 15 lines]

Day) Not Like "*Off*"))
ORDER BY [Daily Assignments].StartTime, [Daily Assignments].Day;

 

[Rick Brandt]

It was a look up field and I delete it, renamed it and still the same
problem, when I enter a Not Like criteria it will not select that
record but it will only select other records with entries and not one
with nulls in the same field..

Two issues. If you eliminated the lookup field does your field now contain
the actual text words that you are testing for or does it contain numeric ID
values? Your criteria has to test for what is actually stored, not what was
previously displayed from the lookup.

When testing for inequality (<, >, <>, <=, >=, Not Like) you ALWAYS have to
include a separate test for Null values. Null values will not be excluded
automatically from any of those tests. In your case you would need
something like...

WHERE SomeField (Not Like "LOA" AND Is Not Null)

 

[Rick Brandt]

Two issues. If you eliminated the lookup field does your field now
contain the actual text words that you are testing for or does it
contain numeric ID values? Your criteria has to test for what is
actually stored, not what was previously displayed from the lookup.

When testing for inequality (<, >, <>, <=, >=, Not Like) you ALWAYS
have to include a separate test for Null values. Null values will
not be excluded automatically from any of those tests. In your case
you would need something like...

WHERE SomeField (Not Like "LOA" AND Is Not Null)

Sorry, that should be...

WHERE SomeField (Not Like "LOA" OR Is Null)

 

[Charles via AccessMonster.com]

The field is text and is used to store notations or employee status and
stored in a table then I want to print a report but not including a dozen or
so criterias such as "*Exc*"; "*Vac*", "*LOA*" "*Tra*" "*No Wo*" "*Comp*" Etc.
these are reason that an emloyees would not be scheduled to work their
regualar shift and I used a combo box in a form for the selection. Other
notations would include See GM, See Safety, Lic Expire on 5/5/08, Time off
denied, etc. and nothing or blank or null. and these flields would not be
excluded in the selection of the records. I used this before and I must have
done something that I'm not aware of for this not to give me the desired
results. In the same table there is another field that will list the shift
number and if not scheduled it will have the word Off and that criteria work
for that field to exclude the employee Off or not scheduled.

Thanks

Charles

 

[quinto via AccessMonster.com]

SELECT NameComb.Names1, [Daily Assignments].Date, [Daily Assignments].
Seniority, [Daily Assignments].RDO, [Daily Assignments].DriverID, [Daily
Assignments].Hrs, [Daily Assignments].StartTime, [Daily Assignments].Day,
[Daily Assignments].EndTime, [Daily Assignments].Route, [Daily Assignments].
Nextel, [Daily Assignments].Status
FROM [Daily Assignments] INNER JOIN NameComb ON [Daily Assignments].DriverID
= NameComb.ID
WHERE ((([Daily Assignments].Date)=[Forms]![Operation]![ServDate]) AND ((
[Daily Assignments].Day) Not Like "*Off*") AND (([Daily Assignments].Status)
Not Like "*Exc*" And ([Daily Assignments].Status) Not Like "*Open*" And (
[Daily Assignments].Status) Not Like "*Vac*" And ([Daily Assignments].Status)
Not Like "*Sick*" And ([Daily Assignments].Status) Not Like "*No Work*" And (
[Daily Assignments].Status) Not Like "*comp*" And ([Daily Assignments].Status)
Not Like "*Light*" And ([Daily Assignments].Status) Not Like "Held Out*" And
([Daily Assignments].Status) Not Like "*resigned*" And ([Daily Assignments].
Status) Not Like "*no driv*" And ([Daily Assignments].Status) Not Like
"*Jury*" And ([Daily Assignments].Status) Not Like "*On Loan*" And ([Daily
Assignments].Status) Not Like "* LOA*" And ([Daily Assignments].Status) Not
Like "CDOO"))
ORDER BY [Daily Assignments].StartTime, [Daily Assignments].Day;

The field is text and is used to store notations or employee status and
stored in a table then I want to print a report but not including a dozen or
so criterias such as "*Exc*"; "*Vac*", "*LOA*" "*Tra*" "*No Wo*" "*Comp*" Etc.
these are reason that an emloyees would not be scheduled to work their
regualar shift and I used a combo box in a form for the selection. Other
notations would include See GM, See Safety, Lic Expire on 5/5/08, Time off
denied, etc. and nothing or blank or null. and these flields would not be
excluded in the selection of the records. I used this before and I must have
done something that I'm not aware of for this not to give me the desired
results. In the same table there is another field that will list the shift
number and if not scheduled it will have the word Off and that criteria work
for that field to exclude the employee Off or not scheduled.

Thanks

Charles
[quoted text clipped - 12 lines]

WHERE SomeField (Not Like "LOA" AND Is Not Null)

 

[Duane Hookom]

I would put all the Not Like values into a table. You should be maintaining
data in tables rather than in complex expressions.

Read http://weblogs.sqlteam.com/jeffs/archive/2006/02/10/9002.aspx.

--
Duane Hookom
Microsoft Access MVP

SELECT NameComb.Names1, [Daily Assignments].Date, [Daily Assignments].
Seniority, [Daily Assignments].RDO, [Daily Assignments].DriverID, [Daily
Assignments].Hrs, [Daily Assignments].StartTime, [Daily Assignments].Day,
[Daily Assignments].EndTime, [Daily Assignments].Route, [Daily Assignments].
Nextel, [Daily Assignments].Status
FROM [Daily Assignments] INNER JOIN NameComb ON [Daily Assignments].DriverID
= NameComb.ID
WHERE ((([Daily Assignments].Date)=[Forms]![Operation]![ServDate]) AND ((
[Daily Assignments].Day) Not Like "*Off*") AND (([Daily Assignments].Status)
Not Like "*Exc*" And ([Daily Assignments].Status) Not Like "*Open*" And (
[Daily Assignments].Status) Not Like "*Vac*" And ([Daily Assignments].Status)
Not Like "*Sick*" And ([Daily Assignments].Status) Not Like "*No Work*" And (
[Daily Assignments].Status) Not Like "*comp*" And ([Daily Assignments].Status)
Not Like "*Light*" And ([Daily Assignments].Status) Not Like "Held Out*" And
([Daily Assignments].Status) Not Like "*resigned*" And ([Daily Assignments].
Status) Not Like "*no driv*" And ([Daily Assignments].Status) Not Like
"*Jury*" And ([Daily Assignments].Status) Not Like "*On Loan*" And ([Daily
Assignments].Status) Not Like "* LOA*" And ([Daily Assignments].Status) Not
Like "CDOO"))
ORDER BY [Daily Assignments].StartTime, [Daily Assignments].Day;

The field is text and is used to store notations or employee status and
stored in a table then I want to print a report but not including a dozen or
so criterias such as "*Exc*"; "*Vac*", "*LOA*" "*Tra*" "*No Wo*" "*Comp*" Etc.
these are reason that an emloyees would not be scheduled to work their
regualar shift and I used a combo box in a form for the selection. Other
notations would include See GM, See Safety, Lic Expire on 5/5/08, Time off
denied, etc. and nothing or blank or null. and these flields would not be
excluded in the selection of the records. I used this before and I must have
done something that I'm not aware of for this not to give me the desired
results. In the same table there is another field that will list the shift
number and if not scheduled it will have the word Off and that criteria work
for that field to exclude the employee Off or not scheduled.

Thanks

Charles

It was a look up field and I delete it, renamed it and still the same
problem, when I enter a Not Like criteria it will not select that

[quoted text clipped - 12 lines]

WHERE SomeField (Not Like "LOA" AND Is Not Null)

 

[quinto via AccessMonster.com]

Thank you, I really appreciate all of the help I get from this forum, I have
learned many procedures here.
Because of shifts variations that can be different on some day, the shifts
may be starting at all odd times 24 hours per day seven days per week by
about 350 employees I cannot adopt your suggestion.

I really appreaciate the time everyone invest in giving advice and suggestion.


Quinto

I would put all the Not Like values into a table. You should be maintaining
data in tables rather than in complex expressions.

Read http://weblogs.sqlteam.com/jeffs/archive/2006/02/10/9002.aspx.

SELECT NameComb.Names1, [Daily Assignments].Date, [Daily Assignments].
Seniority, [Daily Assignments].RDO, [Daily Assignments].DriverID, [Daily

[quoted text clipped - 39 lines]

 

[Duane Hookom]

If you have lots of variations, then you should be maintaining this is data,
not complex expressions.
--
Duane Hookom
Microsoft Access MVP

Thank you, I really appreciate all of the help I get from this forum, I have
learned many procedures here.
Because of shifts variations that can be different on some day, the shifts
may be starting at all odd times 24 hours per day seven days per week by
about 350 employees I cannot adopt your suggestion.

I really appreaciate the time everyone invest in giving advice and suggestion.


Quinto

I would put all the Not Like values into a table. You should be maintaining
data in tables rather than in complex expressions.

Read http://weblogs.sqlteam.com/jeffs/archive/2006/02/10/9002.aspx.

SELECT NameComb.Names1, [Daily Assignments].Date, [Daily Assignments].
Seniority, [Daily Assignments].RDO, [Daily Assignments].DriverID, [Daily

[quoted text clipped - 39 lines]

WHERE SomeField (Not Like "LOA" AND Is Not Null)

 

[NewAccessDude]

Just a quick question, when you have this information in a table, how do you
design a query to pull all the information based on what you have in the
variation table.

for example, table A has the variation info, i want a query that will pull
everything from a misc table that does not match with table A and be able to
look at them in a report or export them or something.

thanks!

If you have lots of variations, then you should be maintaining this is data,
not complex expressions.
--
Duane Hookom
Microsoft Access MVP

Thank you, I really appreciate all of the help I get from this forum, I have
learned many procedures here.
Because of shifts variations that can be different on some day, the shifts
may be starting at all odd times 24 hours per day seven days per week by
about 350 employees I cannot adopt your suggestion.

I really appreaciate the time everyone invest in giving advice and suggestion.


Quinto

I would put all the Not Like values into a table. You should be maintaining
data in tables rather than in complex expressions.

Read http://weblogs.sqlteam.com/jeffs/archive/2006/02/10/9002.aspx.

SELECT NameComb.Names1, [Daily Assignments].Date, [Daily Assignments].
Seniority, [Daily Assignments].RDO, [Daily Assignments].DriverID, [Daily
[quoted text clipped - 39 lines]

WHERE SomeField (Not Like "LOA" AND Is Not Null)

 

[Duane Hookom]

You should be able to use an unmatched query. There is a wizard for this.

--
Duane Hookom
Microsoft Access MVP

Just a quick question, when you have this information in a table, how do you
design a query to pull all the information based on what you have in the
variation table.

for example, table A has the variation info, i want a query that will pull
everything from a misc table that does not match with table A and be able to
look at them in a report or export them or something.

thanks!

If you have lots of variations, then you should be maintaining this is data,
not complex expressions.
--
Duane Hookom
Microsoft Access MVP

Thank you, I really appreciate all of the help I get from this forum, I have
learned many procedures here.
Because of shifts variations that can be different on some day, the shifts
may be starting at all odd times 24 hours per day seven days per week by
about 350 employees I cannot adopt your suggestion.

I really appreaciate the time everyone invest in giving advice and suggestion.


Quinto

Duane Hookom wrote:
I would put all the Not Like values into a table. You should be maintaining
data in tables rather than in complex expressions.

Read http://weblogs.sqlteam.com/jeffs/archive/2006/02/10/9002.aspx.

SELECT NameComb.Names1, [Daily Assignments].Date, [Daily Assignments].
Seniority, [Daily Assignments].RDO, [Daily Assignments].DriverID, [Daily
[quoted text clipped - 39 lines]

WHERE SomeField (Not Like "LOA" AND Is Not Null)