"Type mismatch in expression" (1 Viewer)

maggiemaxima

dental database
Local time
Today, 04:47
Joined
Nov 11, 2004
Messages
6
i am trying to create a query that combines all my tables together into a new table. It wont let me run the query. It says TYPE MISMATCH IN EXPRESSION. What does that mean?? :confused:
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 04:47
Joined
Aug 30, 2003
Messages
36,118
Generally means a mismatch between a numeric, date and text data. Either in the criteria or you're trying to put text data into a numeric field for example.
 

Megacitizen

Registered User.
Local time
Today, 04:47
Joined
Mar 22, 2012
Messages
130
I get the same statement when I try to open a form to either design, view or input data. All fields in my tables are Data Type AutoNumber (where Primary Key), Number (where Foreign Key - related to IDs of corresponding tables) or text (where a name value is input). As far as I can see there is no crossover of types, all numerical types are set to long integer and text boxes to 50 characters.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 04:47
Joined
Aug 30, 2003
Messages
36,118
Is the form based on a table or a query? If a query, does it open without the error?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:47
Joined
Feb 19, 2002
Messages
42,970
Check the control properties and make sure that any default values are the correct data type.
 

Megacitizen

Registered User.
Local time
Today, 04:47
Joined
Mar 22, 2012
Messages
130
Is the form based on a table or a query? If a query, does it open without the error?


Seeing as I haven't started running or building any queries yet, I assume (dangerous word) it must be table related. We're not really intending to run various queries on our finished product, it will just be each team actions viewed in single sheet form - like a file card in a filing cabinet, one card per individual team.

Where I've got so far is creating tables and getting the relationships right with them, regarding who teams are, where they come from and where they're going, and when I get round to adding the info, when they are there.
 

Megacitizen

Registered User.
Local time
Today, 04:47
Joined
Mar 22, 2012
Messages
130
Check the control properties and make sure that any default values are the correct data type.

Would I be right in thinking you mean a field that is autonumber in its own table (where it's the PK) should be a number in the tables where it's an FK? If so, that is what I have.

I'm wondering if I should be populating particular boxes in the property sheets for each table to get things to work. If that's the case, seeing as I have never worked on DB construction before (only data entry) I haven't a clue as to what goes where, or even where to start.

In other words:- HHHHHHHEEEEEEELLLLLLLLPPPPPPPP!!!!!!!!
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 04:47
Joined
Aug 30, 2003
Messages
36,118
Can you post the db here?
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 04:47
Joined
Aug 30, 2003
Messages
36,118
You have a number of invalid control sources on the form, but the error comes from a join in the source query, from RegionID to TeamName.
 

Megacitizen

Registered User.
Local time
Today, 04:47
Joined
Mar 22, 2012
Messages
130
You have a number of invalid control sources on the form, but the error comes from a join in the source query, from RegionID to TeamName.

Thanks for your reply, but now I'm really lost:confused: Not sure where to access the source query so that the join can be viewed, and when I can, how to rectify the situation.

I initially thought you were referring to relationships. As far as I'm aware, I have the correct setup for the relationships I require. I have NationID as a PK in t_Teams, with the relevant relationship, rather than TeamID as PK in t_Nations. The former gives me a One Region Many Teams option, whereas the latter would set up One Team Many Regions, which I don't want.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 04:47
Joined
Aug 30, 2003
Messages
36,118
Well, it may be in the relationships, I didn't look there. You'd access what I saw by looking at the form's record source property. In that, there was join between those incompatible fields. I'm on an iPad right now and can't look at the db.
 

Megacitizen

Registered User.
Local time
Today, 04:47
Joined
Mar 22, 2012
Messages
130
Thanks for your reply, but now I'm really lost:confused: Not sure where to access the source query so that the join can be viewed, and when I can, how to rectify the situation.

I initially thought you were referring to relationships. As far as I'm aware, I have the correct setup for the relationships I require. I have NationID as a PK in t_Teams, with the relevant relationship, rather than TeamID as PK in t_Nations. The former gives me a One Region Many Teams option, whereas the latter would set up One Team Many Regions, which I don't want.

Just re-reading my own quote from a week or so ago, where it says PK, should read FK (I think) and vice versa. Is that correct?

I want to be able to build some expressions so that we can start testing our data entries, but I don't yet know where or how to make the necessary corrections to be able to access my forms.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 04:47
Joined
Aug 30, 2003
Messages
36,118
See attached, which is the source query of your form (its Record Source).
 

Attachments

  • BadJoin.jpg
    BadJoin.jpg
    79.1 KB · Views: 5,071

Megacitizen

Registered User.
Local time
Today, 04:47
Joined
Mar 22, 2012
Messages
130
Thanks Paul

I though I had already got rid of that join, but my system had kept a record of it somewhere, somehow, and THAT was what was stopping me from proceeding. I just got rid of the entire file and started again, which thanks to numerous previous attempts, only took a couple of minutes to redo.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 04:47
Joined
Aug 30, 2003
Messages
36,118
Happy to help!
 

Users who are viewing this thread

Top Bottom