Access query

[Guest]

I have an employee database that includes two fields (Allocated job class and
U/F Class). The Allocated Job Class field entry needs to be overridden with
the entry in U/F Class if U/F Class is not null. Here is what we have now:

SELECT Staff.NAME, Staff.ID, Staff.[FT/PT], Staff.[POS #], Staff.[M/S],
Staff.LOCATION, Staff.[ACCT CODE], Staff.[ALLOCATED JOB CLASS], Staff.[U/F
CLASS], Staff.STEP, Staff.NOTES, Staff.[HIRE DATE]
FROM Staff;

Any suggestions?

 

[Guest]

UPDATE Staff SET Staff.[ALLOCATED JOB CLASS] = [U/F CLASS]
WHERE (((Staff.[U/F CLASS]) Is Not Null));

 

[Guest]

Try:

UPDATE Staff SET [Staff].[ALLOCATED JOB CLASS] = Staff.[U/F CLASS]
WHERE ((([Staff].[U/F CLASS]) Is Not Null));

 

[Guest]

Can someone help me please? I can't post a new message by using "New" and
"General Comment" in the drop down list.

I have a question about query criteria exceeding character limit. I need to
extract data where the critera row has a lot characters such as: not like
"916*" and not like "968*" and not like "986*".... Access has a limit of
1,042 character. How do I get around with this?

Thanks!

 

[Duane Hookom]

You can set a criteria like:

Left([YourField],3) Not In ("916","968","986",...)

Better yet, you should not be "hard-coding" values like this into an
expression. There should be values stored in tables which allow you to set
your criteria. Picture a table with a single field and values like: 916,
968, 986,...

Your criteria could then be something like
Left([YourField],3) Not In (SELECT ExcludeCodes FROM tblCodesToExclude)
When your list of codes changes, edit your data and not your expression.

 

[John Vinson]

Can someone help me please? I can't post a new message by using "New" and
"General Comment" in the drop down list.

I have a question about query criteria exceeding character limit. I need to
extract data where the critera row has a lot characters such as: not like
"916*" and not like "968*" and not like "986*".... Access has a limit of
1,042 character. How do I get around with this?

Thanks!

One way would be to set up a table named Exclude, with values 916,
968, 986 etc.

Use a Query joining this table with a non-equi join rather than a
criterion:

SELECT <whatever>
FROM YourTable
LEFT JOIN Exclude
ON yourtable.fieldname LIKE Exclude.ExcludeThis & "*"
WHERE Exclude.ExcludeThis IS NULL;


John W. Vinson[MVP]