What am I doing wrong Query

[chrisguk]

Hi all,

I put the following query together and it keeps returning:

"Type mismatch in criteria expression"

Here is the SQL:

SELECT tbllog.*, tbllog.qstatus
FROM tbllog
WHERE (((tbllog.qstatus)="Closed"));

 

[jdraw]

What is the data type of tbllog.qstatus?

You could run this query to get values and counts as a start

SELECT tbllog.qstatus, Count(tbllog.qstatus) as Cnt
FROM tbllog
Group By qstatus;

 

[chrisguk]

Hi,

Its a number data type as the values are selected from a combo box / Table Query.

Does that help

 

[jdraw]

How can a number = "Closed"?
"Closed" is a string.

 

[chrisguk]

How can a number = "Closed"?
"Closed" is a string.

Yes you are right.

In the table it is displayed like so:

Field name: qstatus
Date Type: Number

In the tabs below in design view:

Lookup =:

Display Control: Combo Box
Row Source Type: Table/Query
Row Source: SELECT [tblstatus].[statusid], [tblstatus].[status] FROM [tblstatus];

Hope that makes sense

 

[jdraw]

I have a feeling you are using a lookup at the table level.

What are the fields in
tbllog?

 

[DCrake]

FGS get rid of the table level lookups. They are oneof the worst things you can use, they are a pain in the backside. This is the root of your problem.

 

[jdraw]

Thanks David. I was about to suggest
http://www.mvps.org/access/lookupfields.htm

but wanted chrisguk to understand what the problem is.

 

[chrisguk]

Thanks David. I was about to suggest
http://www.mvps.org/access/lookupfields.htm

but wanted chrisguk to understand what the problem is.

Ok guys I get it now. Are you able to give me a detailed example of what I should be using instead of lookup fields?

 

[chrisguk]

Well I have had a good read on Normalisation and seem to completely understand what I have been doing wrong.

I am going to rebuild my database from the ground up. With these rules:

Naming convention for tables, queries, forms, reports and modules.
No table level lookups (lets leave that to the forms)
Redefined PK/FK and field name descriptions
Only use number data types for when calculations are required.

What do you think?

 

[jdraw]

Sounds great.
I'd suggest making a data model.
Identify business rules.
Create some representative test data
and some mock outputs.

Play the "stump the model" to make sure your test data
and model reflect your business rules. Adjust as necessary.

Good luck with your project.