Between Function

  • Thread starter Thread starter meghanwh
  • Start date Start date
M

meghanwh

I have a table that has three fields, one is the group ID, one is a
minimum value and another is a maximum value. I have a query that
counts the number of people who work at a certain company, and I want
to place that company into a group. For example, if there are 5-15
people at a company, they'd be group 4. I have designed a query
function:

LFC Group: DLookUp("[LFCGroup]","[LFCGroups Tbl]","[CountOfUID]"
Between [LFCGroups Tbl]![Minimum] And [LFCGroups Tbl]![Maximum])

It counts the number of people correctly (from UID field), but does not
return the correct group category. It also prompts me for parameters
for [LFCGroups Tbl]![Maximum] and [LFCGroups Tbl]![Minimum]). Help!
 
Create a totals query that counts the number of people, and use it in another
query to show the group ID with these two criteria:

CountOfPeople >= [Minimum Value]
CountOfPeople <= [Maximum Value]

I have a table that has three fields, one is the group ID, one is a
minimum value and another is a maximum value. I have a query that
counts the number of people who work at a certain company, and I want
to place that company into a group. For example, if there are 5-15
people at a company, they'd be group 4. I have designed a query
function:

LFC Group: DLookUp("[LFCGroup]","[LFCGroups Tbl]","[CountOfUID]"
Between [LFCGroups Tbl]![Minimum] And [LFCGroups Tbl]![Maximum])

It counts the number of people correctly (from UID field), but does not
return the correct group category. It also prompts me for parameters
for [LFCGroups Tbl]![Maximum] and [LFCGroups Tbl]![Minimum]). Help!
Click to expand...
 
kingston said:
Create a totals query that counts the number of people, and use it in another
query to show the group ID with these two criteria:

CountOfPeople >= [Minimum Value]
CountOfPeople <= [Maximum Value]

I have a table that has three fields, one is the group ID, one is a
minimum value and another is a maximum value. I have a query that
counts the number of people who work at a certain company, and I want
to place that company into a group. For example, if there are 5-15
people at a company, they'd be group 4. I have designed a query
function:

LFC Group: DLookUp("[LFCGroup]","[LFCGroups Tbl]","[CountOfUID]"
Between [LFCGroups Tbl]![Minimum] And [LFCGroups Tbl]![Maximum])

It counts the number of people correctly (from UID field), but does not
return the correct group category. It also prompts me for parameters
for [LFCGroups Tbl]![Maximum] and [LFCGroups Tbl]![Minimum]). Help!
Click to expand...
Click to expand...

When I do that I get an error "Data type mismatch in criteria
expression"

it says:

"CountOfUID"<=[Maximum]
"CountOfUID">=[Minimum]
 
I have a table that has three fields, one is the group ID, one is a
minimum value and another is a maximum value. I have a query that
counts the number of people who work at a certain company, and I want
to place that company into a group. For example, if there are 5-15
people at a company, they'd be group 4. I have designed a query
function:

LFC Group: DLookUp("[LFCGroup]","[LFCGroups Tbl]","[CountOfUID]"
Between [LFCGroups Tbl]![Minimum] And [LFCGroups Tbl]![Maximum])

It counts the number of people correctly (from UID field), but does not
return the correct group category. It also prompts me for parameters
for [LFCGroups Tbl]![Maximum] and [LFCGroups Tbl]![Minimum]). Help!
Click to expand...

Turn the logic around: try

LFC Group: DLookUp("[LFCGroup]","[LFCGroups Tbl]",
"[Minimum] <= " & [CountOfUID] & " And [Maximum] >= " & [CountOfUID])

John W. Vinson[MVP]
 
CountOfUID should be a field: [CountOfUID]
"CountOfUID" is a string.
Verify that [Maximum] and [Minimum] are numbers.

Create a totals query that counts the number of people, and use it in another
query to show the group ID with these two criteria:
Click to expand...
[quoted text clipped - 19 lines]
Message posted via AccessMonster.com
Click to expand...

When I do that I get an error "Data type mismatch in criteria
expression"

it says:

"CountOfUID"<=[Maximum]
"CountOfUID">=[Minimum]
Click to expand...
 
I have a table that has three fields, one is the group ID, one is a
minimum value and another is a maximum value. I have a query that
counts the number of people who work at a certain company, and I want
to place that company into a group. For example, if there are 5-15
people at a company, they'd be group 4. I have designed a query
function:
Click to expand...
LFC Group: DLookUp("[LFCGroup]","[LFCGroups Tbl]","[CountOfUID]"
Between [LFCGroups Tbl]![Minimum] And [LFCGroups Tbl]![Maximum])
Click to expand...
It counts the number of people correctly (from UID field), but does not
return the correct group category. It also prompts me for parameters
for [LFCGroups Tbl]![Maximum] and [LFCGroups Tbl]![Minimum]). Help!Turn the logic around: try
Click to expand...

LFC Group: DLookUp("[LFCGroup]","[LFCGroups Tbl]",
"[Minimum] <= " & [CountOfUID] & " And [Maximum] >= " & [CountOfUID])

John W. Vinson[MVP]
Click to expand...

Now it prompts me for CountofUID and then says "Syntax error (missing
command) in query expression '[LFCGroups Tbl]![Minimum] <= And
[LFCGroups Tbl]![Maximum]>= '.

I feel like the logic is there and perhaps the syntax is incorrect. The
query is:

SELECT [LFCFirm Count Qry].[Firm ID], [LFCFirm Count Qry].[Firm Name],
[LFCFirm Count Qry].CountOfUID, DLookUp("[LFCGroup]","[LFCGroups
Tbl]","[Minimum] <= " & [CountOfUID] & " And [Maximum] >= " &
[CountOfUID]) AS [LFC Group]
FROM [LFCFirm Count Qry], [LFCGroups Tbl];
 
Now it prompts me for CountofUID and then says "Syntax error (missing
command) in query expression '[LFCGroups Tbl]![Minimum] <= And
[LFCGroups Tbl]![Maximum]>= '.
Click to expand...

Ok, sorry! I misunderstood the context. Try

SELECT [LFCFirm Count Qry].[Firm ID], [LFCFirm Count Qry].[Firm Name],
[LFCFirm Count Qry].CountOfUID, DLookUp("[LFCGroup]",
"[LFCGroups Tbl]", "[Minimum] <= " & [LFCFirm Count Qry].[CountOfUID]
& " And [Maximum] >= " & [LFCFirm Count Qry].[CountOfUID]) AS [LFC
Group]
FROM [LFCFirm Count Qry];

Since you're using DLookUp to lood in [LFCGroups Tbl] you don't need
to - and shouldn't - include it in the Query.

John W. Vinson[MVP]
 
Now it prompts me for CountofUID and then says "Syntax error (missing
command) in query expression '[LFCGroups Tbl]![Minimum] <= And
[LFCGroups Tbl]![Maximum]>= '.Ok, sorry! I misunderstood the context. Try
Click to expand...

SELECT [LFCFirm Count Qry].[Firm ID], [LFCFirm Count Qry].[Firm Name],
[LFCFirm Count Qry].CountOfUID, DLookUp("[LFCGroup]",
"[LFCGroups Tbl]", "[Minimum] <= " & [LFCFirm Count Qry].[CountOfUID]
& " And [Maximum] >= " & [LFCFirm Count Qry].[CountOfUID]) AS [LFC
Group]
FROM [LFCFirm Count Qry];

Since you're using DLookUp to lood in [LFCGroups Tbl] you don't need
to - and shouldn't - include it in the Query.

John W. Vinson[MVP]
Click to expand...

That did it! Thanks!
 
Back
Top