Criteria Statement in a query

[Gntlhnds]

I have a combo box named Rank on my form, and it is based on a table that
lists all the ranks and has a column that has the associated paygrade (which
has a column width of 0" so it can't be seen). I want to create a few
queries based on the paygrade (i.e. E5 and below or E6 and above or 01 and
above). What is the syntax that I would use in the criteria statement for
the query? I have tried <"E5", <"E6", but I keep getting weird results when
I run the query. What is the syntax that I need to be using?

 

[Jeff Boyce]

You know what you mean by "paygrade above/below E5", but imagine you were
explaining it to someone who doesn't have your background.

Now multiply that times 10 -- Access knows even less! You'll have to give
Access some way to know what the proper sort order is.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Amy Blankenship]

I have a combo box named Rank on my form, and it is based on a table that
lists all the ranks and has a column that has the associated paygrade
(which
has a column width of 0" so it can't be seen). I want to create a few
queries based on the paygrade (i.e. E5 and below or E6 and above or 01 and
above). What is the syntax that I would use in the criteria statement for
the query? I have tried <"E5", <"E6", but I keep getting weird results
when
I run the query. What is the syntax that I need to be using?

I think < and > only work with numbers and dates. Assuming that the
progression in rank is the same as the numbers involved, you could do
something like

SELECT YourInfo From YourTable Where CINT(Right([Rank], Len([Rank]) - 1) < 5

HTH;

Amy

 

[Gntlhnds]

I was hoping that by providing that extra column giving the paygrade and then
using some type of statement that it would be smart enough to know that I
want all records where the associated paygrade is "E" and all 5 and below

 

[Gntlhnds]

I was hoping that by providing that extra column giving the paygrade and then
using some type of statement that it would be smart enough to know that I
want all records where the associated paygrade is "E" and all 5 and below

 

[Gntlhnds]

Hmm, posted twice for some reason. Anyways, If I use 'Like "CPL" OR Like
"PFC" OR Like "....(until I've inlcuded them all)', it works. If I use 'Like
"E1" OR Like "E2" Or Like "...', it doesn't work. I'm assuming that is
because the bound column of the combo box is the second column, which has the
Rank, not the Paygrade (the rank column is the one that is visible). How
would I write the criteria statment so that it would check the Paygrade
column of the combo box without having to change the bound column?

 

[Jeff Boyce]

One approach would be to use the .Column() property of the combobox -- but
watch out! This is zero-based, and starts counting columns with "0, 1,
2...". Check Access HELP on Column()

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Michael Gramelspacher]

Hmm, posted twice for some reason. Anyways, If I use 'Like "CPL" OR Like
"PFC" OR Like "....(until I've inlcuded them all)', it works. If I use 'Like
"E1" OR Like "E2" Or Like "...', it doesn't work. I'm assuming that is
because the bound column of the combo box is the second column, which has the
Rank, not the Paygrade (the rank column is the one that is visible). How
would I write the criteria statment so that it would check the Paygrade
column of the combo box without having to change the bound column?

this works for me:

SELECT Soldiers.soldier_name, Soldiers.soldier_rank
FROM Soldiers
WHERE (((Soldiers.soldier_rank)<='E5'))
ORDER BY Soldiers.soldier_rank DESC;

 

[Gntlhnds]

The problem that I have is that Rank is a combo box with three columns. The
visible column is the middle column which shows the rank, and the right
column is the paygrade column which shows the paygrade. I was trying to use
the .column() property in the Where statement, but I kept getting an
"Undefined function..." error. What I did do was create a new column in my
table called paygrade, and then set the "After Update" event of the "Rank"
field to Me![Paygrade] = Me![Rank].column(2), which populates the Paygrade
field (I know, causing redundant data), but there again, it's on the after
update, which means I have to go back through all my 700 records and manually
re-update. I also run into the problem of when I import records I have to
manually update every record indivdually again. Is there a way to have it
automatically populate the paygrade field so my query will work right?

 

[Michael Gramelspacher]

The problem that I have is that Rank is a combo box with three columns. The
visible column is the middle column which shows the rank, and the right
column is the paygrade column which shows the paygrade. I was trying to use
the .column() property in the Where statement, but I kept getting an
"Undefined function..." error. What I did do was create a new column in my
table called paygrade, and then set the "After Update" event of the "Rank"
field to Me![Paygrade] = Me![Rank].column(2), which populates the Paygrade
field (I know, causing redundant data), but there again, it's on the after
update, which means I have to go back through all my 700 records and manually
re-update. I also run into the problem of when I import records I have to
manually update every record indivdually again. Is there a way to have it
automatically populate the paygrade field so my query will work right?

MilitaryRanks
rank_order rank_title pay_grade
1 PVT E1
2 PV2 E2
3 PFC E3
4 CPL E4
5 SGT E5
6 SSG E6
7 SFC E7
8 MSG E8
9 1SG E8
10 SGM E9
11 CSM E9

soldier_name rank_order rank_title
David 8 MSG
Jim 7 SFC
Joe 9 1SG
Larry 5 SGT
Martha 6 SSG
Mary 3 PFC
mike 4 CPL
Sam 2 PV2
Susan 4 CPL

SELECT Soldiers.soldier_name,
MilitaryRanks.pay_grade
FROM MilitaryRanks
INNER JOIN Soldiers
ON (MilitaryRanks.rank_title = Soldiers.rank_title)
AND (MilitaryRanks.rank_order = Soldiers.rank_order)
WHERE (((MilitaryRanks.pay_grade) <= 'E5'))
ORDER BY MilitaryRanks.pay_grade DESC;

 

[Gntlhnds]

I thank you for your help, but that doesn't work for me. I'm working with a
multi-agency database (therefore several different ranks associated with a
paygrade) and my table that contains the paygrades has an autonumber as the
primary key. If I could find some way to programmatically have the paygrade
field in my main contacts table be updated with the paygrade associate with
the rank field, that would work. Right now it's set up to only do it in the
after update event of the form that I use to enter/edit data into the table.
Like I mentioned before, that doesn't exactly work because some of the
records get imported sometimes (and don't have a paygrade field), so I need
to find some code to write that will update the paygrade field as soon as the
records are imported.

The problem that I have is that Rank is a combo box with three columns. The
visible column is the middle column which shows the rank, and the right
column is the paygrade column which shows the paygrade. I was trying to use
the .column() property in the Where statement, but I kept getting an
"Undefined function..." error. What I did do was create a new column in my
table called paygrade, and then set the "After Update" event of the "Rank"
field to Me![Paygrade] = Me![Rank].column(2), which populates the Paygrade
field (I know, causing redundant data), but there again, it's on the after
update, which means I have to go back through all my 700 records and manually
re-update. I also run into the problem of when I import records I have to
manually update every record indivdually again. Is there a way to have it
automatically populate the paygrade field so my query will work right?

Hmm, posted twice for some reason. Anyways, If I use 'Like "CPL" OR Like
"PFC" OR Like "....(until I've inlcuded them all)', it works. If I use 'Like
"E1" OR Like "E2" Or Like "...', it doesn't work. I'm assuming that is
because the bound column of the combo box is the second column, which has the
Rank, not the Paygrade (the rank column is the one that is visible). How
would I write the criteria statment so that it would check the Paygrade
column of the combo box without having to change the bound column?

:

I was hoping that by providing that extra column giving the paygrade and then
using some type of statement that it would be smart enough to know that I
want all records where the associated paygrade is "E" and all 5 and below

:

You know what you mean by "paygrade above/below E5", but imagine you were
explaining it to someone who doesn't have your background.

Now multiply that times 10 -- Access knows even less! You'll have to give
Access some way to know what the proper sort order is.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a combo box named Rank on my form, and it is based on a table that
lists all the ranks and has a column that has the associated paygrade
(which
has a column width of 0" so it can't be seen). I want to create a few

MilitaryRanks
rank_order rank_title pay_grade
1 PVT E1
2 PV2 E2
3 PFC E3
4 CPL E4
5 SGT E5
6 SSG E6
7 SFC E7
8 MSG E8
9 1SG E8
10 SGM E9
11 CSM E9

soldier_name rank_order rank_title
David 8 MSG
Jim 7 SFC
Joe 9 1SG
Larry 5 SGT
Martha 6 SSG
Mary 3 PFC
mike 4 CPL
Sam 2 PV2
Susan 4 CPL

SELECT Soldiers.soldier_name,
MilitaryRanks.pay_grade
FROM MilitaryRanks
INNER JOIN Soldiers
ON (MilitaryRanks.rank_title = Soldiers.rank_title)
AND (MilitaryRanks.rank_order = Soldiers.rank_order)
WHERE (((MilitaryRanks.pay_grade) <= 'E5'))
ORDER BY MilitaryRanks.pay_grade DESC;

 

[Gntlhnds]

I incorporated that into the after update event on the form to populate a
field I added to my table called paygrade. However, that doesn't help me
with records that currently exist or records that will be imported. I tried
using the column() property in and update query and select query, but I kept
getting an undefined function error, so I can't use it there. Do you know of
some code that I could write that would update all my records with the
appropriate paygrade for the rank selected? I was thinking of using the
update function of the recordset, but I have no clue about writing code
dealing with recordsets, and I don't know if the column() property would work
there, either.

 

[Michael Gramelspacher]

I thank you for your help, but that doesn't work for me. I'm working with a
multi-agency database (therefore several different ranks associated with a
paygrade) and my table that contains the paygrades has an autonumber as the
primary key. If I could find some way to programmatically have the paygrade
field in my main contacts table be updated with the paygrade associate with
the rank field, that would work. Right now it's set up to only do it in the
after update event of the form that I use to enter/edit data into the table.
Like I mentioned before, that doesn't exactly work because some of the
records get imported sometimes (and don't have a paygrade field), so I need
to find some code to write that will update the paygrade field as soon as the
records are imported.

The problem that I have is that Rank is a combo box with three columns. The
visible column is the middle column which shows the rank, and the right
column is the paygrade column which shows the paygrade. I was trying to use
the .column() property in the Where statement, but I kept getting an
"Undefined function..." error. What I did do was create a new column in my
table called paygrade, and then set the "After Update" event of the "Rank"
field to Me![Paygrade] = Me![Rank].column(2), which populates the Paygrade
field (I know, causing redundant data), but there again, it's on the after
update, which means I have to go back through all my 700 records and manually
re-update. I also run into the problem of when I import records I have to
manually update every record indivdually again. Is there a way to have it
automatically populate the paygrade field so my query will work right?

:

Hmm, posted twice for some reason. Anyways, If I use 'Like "CPL" OR Like
"PFC" OR Like "....(until I've inlcuded them all)', it works. If I use 'Like
"E1" OR Like "E2" Or Like "...', it doesn't work. I'm assuming that is
because the bound column of the combo box is the second column, which has the
Rank, not the Paygrade (the rank column is the one that is visible). How
would I write the criteria statment so that it would check the Paygrade
column of the combo box without having to change the bound column?

:

I was hoping that by providing that extra column giving the paygrade and then
using some type of statement that it would be smart enough to know that I
want all records where the associated paygrade is "E" and all 5 and below

:

You know what you mean by "paygrade above/below E5", but imagine you were

If I rememer correctly some ranks are the same between branches but the pay
grade is different. That tells me that rank can only be used to determine the
pay grade when the branch is known. Once the branch and rank are known, pay
grade is known.

If a new record contains the name, service number, branch, and rank, then
paygrade is known. The problem is how to best set it in the form. This assumes
there is a MilitaryRanks table: autonumber, branch, rank, paygrade, so as to
look it up.

 

[Amy Blankenship]

I incorporated that into the after update event on the form to populate a
field I added to my table called paygrade. However, that doesn't help me
with records that currently exist or records that will be imported. I
tried
using the column() property in and update query and select query, but I
kept
getting an undefined function error, so I can't use it there. Do you know
of
some code that I could write that would update all my records with the
appropriate paygrade for the rank selected? I was thinking of using the
update function of the recordset, but I have no clue about writing code
dealing with recordsets, and I don't know if the column() property would
work
there, either.

If you have a table that includes the pay grades for each rank, then you
don't need to store it for each person.