Query with lookup table

[Guest]

I have a table which has a field(column) as a lookup field from the lookup
table. I am trying to do a query on that field and for some odd reason it is
returning no records, not a null but an error like return. I can see the
data in the field, it is there, can someone help me?

Here is the table
DrillID Shift Drill_Date Drill_Identifiers Location-lookup field from

Location Table
ID Location-this field
I want to do a query that pulls specific data...ie. whether it was at one
buidlding or another.

Thank you for your help in advance

 

[Guest]

Now I know why there are so many post about tables with look-up fields being
so bad.

That field contains the ID of the location, not the actual location text.
You have to enter the ID or add the Location look-up table joined on the ID
and put your criteria for the location field.

 

[Marshall Barton]

I have a table which has a field(column) as a lookup field from the lookup
table. I am trying to do a query on that field and for some odd reason it is
returning no records, not a null but an error like return. I can see the
data in the field, it is there, can someone help me?

Here is the table
DrillID Shift Drill_Date Drill_Identifiers Location-lookup field from

Location Table
ID Location-this field
I want to do a query that pulls specific data...ie. whether it was at one
buidlding or another.

With a look up field, what you see is **not** what you get.
Either change your criteria to use the location ID value or
change the query to join to the location table and add the
location name field to the query so you can use the name as
the criteria.

 

[Guest]

Marshall,

I am very new to this ACCESS database stuff. So I needs some help
understanding what you wrote.

change the query to join to the location table and add the
location name field to the query so you can use the name as
the criteria. (I am not sure what is you mean with this part).

As I stated, I am new and confused.

Thank you for your help!
JudyT

 

[Marshall Barton]

I am very new to this ACCESS database stuff. So I needs some help
understanding what you wrote.



(Where do I change this in the query?

In the Location field's Criteria (i.e. the same place where
you are specifying the location's name).

When asking a question about a query, you should post a
Copy/Paste of your query's SQL view. Lacking that, I will
guess that your query looks something like:

SELECT atable.DrillID, atable.Shift, atable.Drill_Date,
atable.Drill_Identifiers, atable.Location
FROM atable
WHERE atable.Location = "West Texas"

Assuming that the West Texas location has an ID of 123, the
change would be to use this instead:
WHERE atable.Location = 123

(I am not sure what is you mean with this part).

In this case the query would be similar to this:

SELECT atable.DrillID, atable.Shift, atable.Drill_Date,
atable.Drill_Identifiers, atable.Location,
[Location Table].LocationName
FROM atable INNER JOIN [Location Table]
ON atable.Location = [Location Table].[ID Location]
WHERE [Location Table].LocationName = "West Texas"

Because you probably have no idea what all that SQL means, I
suggest that you create a new query. Don't bother selecting
a table. As soon as you see the query design grid, use the
View menu to switch to SQL view. Then Copy/Paste the SQL
statement above over the top of whatever Access had inserted
automatically. Check each of my guesses at the table and
field names and correct them to the names you are actually
using. You can try to switch the query to datasheet view to
see if it runs and, it it does, if it's returning the
correct data. Once the query is performing correctly,
switch to design view to see how it could have been
specified using the query design grid.

All this confusion is caused by your use of the lookup field
in the table. It probably would have been better if you had
left it as (or change it now) a simple text box display so
you could see what's actually in the field. Then it would
be more obvious how to set up your query.

 

[Guest]

SELECT [Drill information].DrillID, [Drill information].Location, [Drill
information].Date, Count([Drill information].Location) AS CountOfLocation
FROM [Drill information] INNER JOIN Location_lookup ON [Drill
information].Location = Location_lookup.Location
GROUP BY [Drill information].DrillID, [Drill information].Location, [Drill
information].Date
HAVING ((([Drill information].Date) Between #1/1/2006# And #12/31/2006#)) OR
((([Drill information].Location) Like "CVN"));

I am very new to this ACCESS database stuff. So I needs some help
understanding what you wrote.



(Where do I change this in the query?

In the Location field's Criteria (i.e. the same place where
you are specifying the location's name).

When asking a question about a query, you should post a
Copy/Paste of your query's SQL view. Lacking that, I will
guess that your query looks something like:

SELECT atable.DrillID, atable.Shift, atable.Drill_Date,
atable.Drill_Identifiers, atable.Location
FROM atable
WHERE atable.Location = "West Texas"

Assuming that the West Texas location has an ID of 123, the
change would be to use this instead:
WHERE atable.Location = 123

(I am not sure what is you mean with this part).

In this case the query would be similar to this:

SELECT atable.DrillID, atable.Shift, atable.Drill_Date,
atable.Drill_Identifiers, atable.Location,
[Location Table].LocationName
FROM atable INNER JOIN [Location Table]
ON atable.Location = [Location Table].[ID Location]
WHERE [Location Table].LocationName = "West Texas"

Because you probably have no idea what all that SQL means, I
suggest that you create a new query. Don't bother selecting
a table. As soon as you see the query design grid, use the
View menu to switch to SQL view. Then Copy/Paste the SQL
statement above over the top of whatever Access had inserted
automatically. Check each of my guesses at the table and
field names and correct them to the names you are actually
using. You can try to switch the query to datasheet view to
see if it runs and, it it does, if it's returning the
correct data. Once the query is performing correctly,
switch to design view to see how it could have been
specified using the query design grid.

All this confusion is caused by your use of the lookup field
in the table. It probably would have been better if you had
left it as (or change it now) a simple text box display so
you could see what's actually in the field. Then it would
be more obvious how to set up your query.

 

[Guest]

Hey Marshall,

My apologies...here is my query in sql view.

SELECT [Drill information].DrillID, [Drill information].Location, [Drill
information].Date
FROM Location_lookup INNER JOIN [Drill information] ON
Location_lookup.Location = [Drill information].Location
GROUP BY [Drill information].DrillID, [Drill information].Location, [Drill
information].Date
HAVING ((([Drill information].Date) Between #1/1/2006# And #12/31/2006#)) OR
((([Drill information].Location)="cvg"));

I am very new to this ACCESS database stuff. So I needs some help
understanding what you wrote.



(Where do I change this in the query?

In the Location field's Criteria (i.e. the same place where
you are specifying the location's name).

When asking a question about a query, you should post a
Copy/Paste of your query's SQL view. Lacking that, I will
guess that your query looks something like:

SELECT atable.DrillID, atable.Shift, atable.Drill_Date,
atable.Drill_Identifiers, atable.Location
FROM atable
WHERE atable.Location = "West Texas"

Assuming that the West Texas location has an ID of 123, the
change would be to use this instead:
WHERE atable.Location = 123

(I am not sure what is you mean with this part).

In this case the query would be similar to this:

SELECT atable.DrillID, atable.Shift, atable.Drill_Date,
atable.Drill_Identifiers, atable.Location,
[Location Table].LocationName
FROM atable INNER JOIN [Location Table]
ON atable.Location = [Location Table].[ID Location]
WHERE [Location Table].LocationName = "West Texas"

Because you probably have no idea what all that SQL means, I
suggest that you create a new query. Don't bother selecting
a table. As soon as you see the query design grid, use the
View menu to switch to SQL view. Then Copy/Paste the SQL
statement above over the top of whatever Access had inserted
automatically. Check each of my guesses at the table and
field names and correct them to the names you are actually
using. You can try to switch the query to datasheet view to
see if it runs and, it it does, if it's returning the
correct data. Once the query is performing correctly,
switch to design view to see how it could have been
specified using the query design grid.

All this confusion is caused by your use of the lookup field
in the table. It probably would have been better if you had
left it as (or change it now) a simple text box display so
you could see what's actually in the field. Then it would
be more obvious how to set up your query.

 

[Marshall Barton]

I am still not sure what fields are in the Location_lookup
table. I think you said that there is an ID field and if
that's correct, then the ON clause should be:
ON Location_lookup.ID = [Drill information].Location
To confirm this, open the Location_lookup table in design
view and check the field names. Then switch the table to
sheet view to see the values in the ID(?) field.

You should be using a WHERE clause not a HAVING clause.
Since you are not using an aggregate function, there is no
real need for the GROUP BY clause.

You probably want to use AND instead of OR, but I can't tell
without knowing exactly what you want the query do.

Putting this together with my suggestion, the query would
be:

SELECT [Drill information].DrillID,
[Drill information].Location,
[Drill information].Date
FROM Location_lookup INNER JOIN [Drill information]
ON Location_lookup.ID = [Drill information].Location
WHERE ([Drill information].Date
Between #1/1/2006# And #12/31/2006#)
AND (Location_lookup.Location="cvg")

Note that Date is a reserved word in Access and you may
eventually run into trouble with that name. Maybe
ReadingDate would be more appropriate?
--
Marsh
MVP [MS Access]

My apologies...here is my query in sql view.

SELECT [Drill information].DrillID, [Drill information].Location, [Drill
information].Date
FROM Location_lookup INNER JOIN [Drill information] ON
Location_lookup.Location = [Drill information].Location
GROUP BY [Drill information].DrillID, [Drill information].Location, [Drill
information].Date
HAVING ((([Drill information].Date) Between #1/1/2006# And #12/31/2006#)) OR
((([Drill information].Location)="cvg"));

I am very new to this ACCESS database stuff. So I needs some help
understanding what you wrote.


Either change your criteria to use the location ID value

(Where do I change this in the query?

In the Location field's Criteria (i.e. the same place where
you are specifying the location's name).

When asking a question about a query, you should post a
Copy/Paste of your query's SQL view. Lacking that, I will
guess that your query looks something like:

SELECT atable.DrillID, atable.Shift, atable.Drill_Date,
atable.Drill_Identifiers, atable.Location
FROM atable
WHERE atable.Location = "West Texas"

Assuming that the West Texas location has an ID of 123, the
change would be to use this instead:
WHERE atable.Location = 123

or change the query to join to the location table and add the
location name field to the query so you can use the name as
the criteria.

(I am not sure what is you mean with this part).

In this case the query would be similar to this:

SELECT atable.DrillID, atable.Shift, atable.Drill_Date,
atable.Drill_Identifiers, atable.Location,
[Location Table].LocationName
FROM atable INNER JOIN [Location Table]
ON atable.Location = [Location Table].[ID Location]
WHERE [Location Table].LocationName = "West Texas"

Because you probably have no idea what all that SQL means, I
suggest that you create a new query. Don't bother selecting
a table. As soon as you see the query design grid, use the
View menu to switch to SQL view. Then Copy/Paste the SQL
statement above over the top of whatever Access had inserted
automatically. Check each of my guesses at the table and
field names and correct them to the names you are actually
using. You can try to switch the query to datasheet view to
see if it runs and, it it does, if it's returning the
correct data. Once the query is performing correctly,
switch to design view to see how it could have been
specified using the query design grid.

All this confusion is caused by your use of the lookup field
in the table. It probably would have been better if you had
left it as (or change it now) a simple text box display so
you could see what's actually in the field. Then it would
be more obvious how to set up your query.

:

JudyT wrote:

I have a table which has a field(column) as a lookup field from the lookup
table. I am trying to do a query on that field and for some odd reason it is
returning no records, not a null but an error like return. I can see the
data in the field, it is there, can someone help me?

Here is the table
DrillID Shift Drill_Date Drill_Identifiers Location-lookup field from

Location Table
ID Location-this field
I want to do a query that pulls specific data...ie. whether it was at one
buidlding or another.


With a look up field, what you see is **not** what you get.
Either change your criteria to use the location ID value or
change the query to join to the location table and add the
location name field to the query so you can use the name as
the criteria.

 

[Guest]

Hey Marshall,

A few things, below you talk about the tables and then the ON clause, is
that in my table somewhere or in the query you are talking about, I am
confused.

Next, I tried the query the way you wrote it and I get a data mismatch
expression, what does that mean?

You talk about the table and for me to go to the design view but you do not
say what to look for. I have and ID field but it is an autonumber field.

I am still not sure what fields are in the Location_lookup
table. I think you said that there is an ID field and if
that's correct, then the ON clause should be:
ON Location_lookup.ID = [Drill information].Location
To confirm this, open the Location_lookup table in design
view and check the field names. Then switch the table to
sheet view to see the values in the ID(?) field.

You should be using a WHERE clause not a HAVING clause.
Since you are not using an aggregate function, there is no
real need for the GROUP BY clause.

You probably want to use AND instead of OR, but I can't tell
without knowing exactly what you want the query do.

Putting this together with my suggestion, the query would
be:

SELECT [Drill information].DrillID,
[Drill information].Location,
[Drill information].Date
FROM Location_lookup INNER JOIN [Drill information]
ON Location_lookup.ID = [Drill information].Location
WHERE ([Drill information].Date
Between #1/1/2006# And #12/31/2006#)
AND (Location_lookup.Location="cvg")

Note that Date is a reserved word in Access and you may
eventually run into trouble with that name. Maybe
ReadingDate would be more appropriate?
--
Marsh
MVP [MS Access]

My apologies...here is my query in sql view.

SELECT [Drill information].DrillID, [Drill information].Location, [Drill
information].Date
FROM Location_lookup INNER JOIN [Drill information] ON
Location_lookup.Location = [Drill information].Location
GROUP BY [Drill information].DrillID, [Drill information].Location, [Drill
information].Date
HAVING ((([Drill information].Date) Between #1/1/2006# And #12/31/2006#)) OR
((([Drill information].Location)="cvg"));

JudyT wrote:
I am very new to this ACCESS database stuff. So I needs some help
understanding what you wrote.


Either change your criteria to use the location ID value

(Where do I change this in the query?

In the Location field's Criteria (i.e. the same place where
you are specifying the location's name).

When asking a question about a query, you should post a
Copy/Paste of your query's SQL view. Lacking that, I will
guess that your query looks something like:

SELECT atable.DrillID, atable.Shift, atable.Drill_Date,
atable.Drill_Identifiers, atable.Location
FROM atable
WHERE atable.Location = "West Texas"

Assuming that the West Texas location has an ID of 123, the
change would be to use this instead:
WHERE atable.Location = 123


or change the query to join to the location table and add the
location name field to the query so you can use the name as
the criteria.

(I am not sure what is you mean with this part).

In this case the query would be similar to this:

SELECT atable.DrillID, atable.Shift, atable.Drill_Date,
atable.Drill_Identifiers, atable.Location,
[Location Table].LocationName
FROM atable INNER JOIN [Location Table]
ON atable.Location = [Location Table].[ID Location]
WHERE [Location Table].LocationName = "West Texas"

Because you probably have no idea what all that SQL means, I
suggest that you create a new query. Don't bother selecting
a table. As soon as you see the query design grid, use the
View menu to switch to SQL view. Then Copy/Paste the SQL
statement above over the top of whatever Access had inserted
automatically. Check each of my guesses at the table and
field names and correct them to the names you are actually
using. You can try to switch the query to datasheet view to
see if it runs and, it it does, if it's returning the
correct data. Once the query is performing correctly,
switch to design view to see how it could have been
specified using the query design grid.

All this confusion is caused by your use of the lookup field
in the table. It probably would have been better if you had
left it as (or change it now) a simple text box display so
you could see what's actually in the field. Then it would
be more obvious how to set up your query.


:

JudyT wrote:

I have a table which has a field(column) as a lookup field from the lookup
table. I am trying to do a query on that field and for some odd reason it is
returning no records, not a null but an error like return. I can see the
data in the field, it is there, can someone help me?

Here is the table
DrillID Shift Drill_Date Drill_Identifiers Location-lookup field from

Location Table
ID Location-this field
I want to do a query that pulls specific data...ie. whether it was at one
buidlding or another.


With a look up field, what you see is **not** what you get.
Either change your criteria to use the location ID value or
change the query to join to the location table and add the
location name field to the query so you can use the name as
the criteria.

 

[Marshall Barton]

I am really, really fed up with these bleeping lookup
fields. Would you please open the drill table and change
the Lookup for Location back to a text box so you can see
what is actually in the field when you display the table in
sheet view.

A type mismatch error means that I don't know as much about
your tables as I thought I did. Please confirm or correct
my assumptions:

table [Drill information]
DrillID (AutoNumber - Primary Key)
Shift (???)
Drill_Date (Date)
Drill_Identifiers (Number - Long)
Location (Number - Long)

table Location_lookup
ID (AutoNumber - Primary Key)
Location (Text - 255)

If you can provide any more information about the error,
like maybe the name of the field or the expression with the
error, it would be a big help.
--
Marsh
MVP [MS Access]

A few things, below you talk about the tables and then the ON clause, is
that in my table somewhere or in the query you are talking about, I am
confused.

Next, I tried the query the way you wrote it and I get a data mismatch
expression, what does that mean?

You talk about the table and for me to go to the design view but you do not
say what to look for. I have and ID field but it is an autonumber field.

I am still not sure what fields are in the Location_lookup
table. I think you said that there is an ID field and if
that's correct, then the ON clause should be:
ON Location_lookup.ID = [Drill information].Location
To confirm this, open the Location_lookup table in design
view and check the field names. Then switch the table to
sheet view to see the values in the ID(?) field.

You should be using a WHERE clause not a HAVING clause.
Since you are not using an aggregate function, there is no
real need for the GROUP BY clause.

You probably want to use AND instead of OR, but I can't tell
without knowing exactly what you want the query do.

Putting this together with my suggestion, the query would
be:

SELECT [Drill information].DrillID,
[Drill information].Location,
[Drill information].Date
FROM Location_lookup INNER JOIN [Drill information]
ON Location_lookup.ID = [Drill information].Location
WHERE ([Drill information].Date
Between #1/1/2006# And #12/31/2006#)
AND (Location_lookup.Location="cvg")

Note that Date is a reserved word in Access and you may
eventually run into trouble with that name. Maybe
ReadingDate would be more appropriate?

My apologies...here is my query in sql view.

SELECT [Drill information].DrillID, [Drill information].Location, [Drill
information].Date
FROM Location_lookup INNER JOIN [Drill information] ON
Location_lookup.Location = [Drill information].Location
GROUP BY [Drill information].DrillID, [Drill information].Location, [Drill
information].Date
HAVING ((([Drill information].Date) Between #1/1/2006# And #12/31/2006#)) OR
((([Drill information].Location)="cvg"));


:

JudyT wrote:
I am very new to this ACCESS database stuff. So I needs some help
understanding what you wrote.


Either change your criteria to use the location ID value

(Where do I change this in the query?

In the Location field's Criteria (i.e. the same place where
you are specifying the location's name).

When asking a question about a query, you should post a
Copy/Paste of your query's SQL view. Lacking that, I will
guess that your query looks something like:

SELECT atable.DrillID, atable.Shift, atable.Drill_Date,
atable.Drill_Identifiers, atable.Location
FROM atable
WHERE atable.Location = "West Texas"

Assuming that the West Texas location has an ID of 123, the
change would be to use this instead:
WHERE atable.Location = 123


or change the query to join to the location table and add the
location name field to the query so you can use the name as
the criteria.

(I am not sure what is you mean with this part).

In this case the query would be similar to this:

SELECT atable.DrillID, atable.Shift, atable.Drill_Date,
atable.Drill_Identifiers, atable.Location,
[Location Table].LocationName
FROM atable INNER JOIN [Location Table]
ON atable.Location = [Location Table].[ID Location]
WHERE [Location Table].LocationName = "West Texas"

Because you probably have no idea what all that SQL means, I
suggest that you create a new query. Don't bother selecting
a table. As soon as you see the query design grid, use the
View menu to switch to SQL view. Then Copy/Paste the SQL
statement above over the top of whatever Access had inserted
automatically. Check each of my guesses at the table and
field names and correct them to the names you are actually
using. You can try to switch the query to datasheet view to
see if it runs and, it it does, if it's returning the
correct data. Once the query is performing correctly,
switch to design view to see how it could have been
specified using the query design grid.

All this confusion is caused by your use of the lookup field
in the table. It probably would have been better if you had
left it as (or change it now) a simple text box display so
you could see what's actually in the field. Then it would
be more obvious how to set up your query.


:

JudyT wrote:

I have a table which has a field(column) as a lookup field from the lookup
table. I am trying to do a query on that field and for some odd reason it is
returning no records, not a null but an error like return. I can see the
data in the field, it is there, can someone help me?

Here is the table
DrillID Shift Drill_Date Drill_Identifiers Location-lookup field from

Location Table
ID Location-this field
I want to do a query that pulls specific data...ie. whether it was at one
buidlding or another.


With a look up field, what you see is **not** what you get.
Either change your criteria to use the location ID value or
change the query to join to the location table and add the
location name field to the query so you can use the name as
the criteria.

 

[Guest]

Marshall,

I have solved the problem another way, I put a yes/no check box to ask the
question and then pulled it into the query that way.

Thanks for your help but I really do like the look up field/table function
and so does the users.

Judy

I am really, really fed up with these bleeping lookup
fields. Would you please open the drill table and change
the Lookup for Location back to a text box so you can see
what is actually in the field when you display the table in
sheet view.

A type mismatch error means that I don't know as much about
your tables as I thought I did. Please confirm or correct
my assumptions:

table [Drill information]
DrillID (AutoNumber - Primary Key)
Shift (???)
Drill_Date (Date)
Drill_Identifiers (Number - Long)
Location (Number - Long)

table Location_lookup
ID (AutoNumber - Primary Key)
Location (Text - 255)

If you can provide any more information about the error,
like maybe the name of the field or the expression with the
error, it would be a big help.
--
Marsh
MVP [MS Access]

A few things, below you talk about the tables and then the ON clause, is
that in my table somewhere or in the query you are talking about, I am
confused.

Next, I tried the query the way you wrote it and I get a data mismatch
expression, what does that mean?

You talk about the table and for me to go to the design view but you do not
say what to look for. I have and ID field but it is an autonumber field.

I am still not sure what fields are in the Location_lookup
table. I think you said that there is an ID field and if
that's correct, then the ON clause should be:
ON Location_lookup.ID = [Drill information].Location
To confirm this, open the Location_lookup table in design
view and check the field names. Then switch the table to
sheet view to see the values in the ID(?) field.

You should be using a WHERE clause not a HAVING clause.
Since you are not using an aggregate function, there is no
real need for the GROUP BY clause.

You probably want to use AND instead of OR, but I can't tell
without knowing exactly what you want the query do.

Putting this together with my suggestion, the query would
be:

SELECT [Drill information].DrillID,
[Drill information].Location,
[Drill information].Date
FROM Location_lookup INNER JOIN [Drill information]
ON Location_lookup.ID = [Drill information].Location
WHERE ([Drill information].Date
Between #1/1/2006# And #12/31/2006#)
AND (Location_lookup.Location="cvg")

Note that Date is a reserved word in Access and you may
eventually run into trouble with that name. Maybe
ReadingDate would be more appropriate?


JudyT wrote:
My apologies...here is my query in sql view.

SELECT [Drill information].DrillID, [Drill information].Location, [Drill
information].Date
FROM Location_lookup INNER JOIN [Drill information] ON
Location_lookup.Location = [Drill information].Location
GROUP BY [Drill information].DrillID, [Drill information].Location, [Drill
information].Date
HAVING ((([Drill information].Date) Between #1/1/2006# And #12/31/2006#)) OR
((([Drill information].Location)="cvg"));


:

JudyT wrote:
I am very new to this ACCESS database stuff. So I needs some help
understanding what you wrote.


Either change your criteria to use the location ID value

(Where do I change this in the query?

In the Location field's Criteria (i.e. the same place where
you are specifying the location's name).

When asking a question about a query, you should post a
Copy/Paste of your query's SQL view. Lacking that, I will
guess that your query looks something like:

SELECT atable.DrillID, atable.Shift, atable.Drill_Date,
atable.Drill_Identifiers, atable.Location
FROM atable
WHERE atable.Location = "West Texas"

Assuming that the West Texas location has an ID of 123, the
change would be to use this instead:
WHERE atable.Location = 123


or change the query to join to the location table and add the
location name field to the query so you can use the name as
the criteria.

(I am not sure what is you mean with this part).

In this case the query would be similar to this:

SELECT atable.DrillID, atable.Shift, atable.Drill_Date,
atable.Drill_Identifiers, atable.Location,
[Location Table].LocationName
FROM atable INNER JOIN [Location Table]
ON atable.Location = [Location Table].[ID Location]
WHERE [Location Table].LocationName = "West Texas"

Because you probably have no idea what all that SQL means, I
suggest that you create a new query. Don't bother selecting
a table. As soon as you see the query design grid, use the
View menu to switch to SQL view. Then Copy/Paste the SQL
statement above over the top of whatever Access had inserted
automatically. Check each of my guesses at the table and
field names and correct them to the names you are actually
using. You can try to switch the query to datasheet view to
see if it runs and, it it does, if it's returning the
correct data. Once the query is performing correctly,
switch to design view to see how it could have been
specified using the query design grid.

All this confusion is caused by your use of the lookup field
in the table. It probably would have been better if you had
left it as (or change it now) a simple text box display so
you could see what's actually in the field. Then it would
be more obvious how to set up your query.


:

JudyT wrote:

I have a table which has a field(column) as a lookup field from the lookup
table. I am trying to do a query on that field and for some odd reason it is
returning no records, not a null but an error like return. I can see the
data in the field, it is there, can someone help me?

Here is the table
DrillID Shift Drill_Date Drill_Identifiers Location-lookup field from

Location Table
ID Location-this field
I want to do a query that pulls specific data...ie. whether it was at one
buidlding or another.


With a look up field, what you see is **not** what you get.
Either change your criteria to use the location ID value or
change the query to join to the location table and add the
location name field to the query so you can use the name as
the criteria.