"Type mismatch in expression" (1 Viewer)

[maggiemaxima]

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??

 

[pbaldy]

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]

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]

Is the form based on a table or a query? If a query, does it open without the error?

 

[Pat Hartman]

Check the control properties and make sure that any default values are the correct data type.

 

[Megacitizen]

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]

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]

Can you post the db here?

 

[Megacitizen]

Can you post the db here?

Here it is. I tried to save it as a "2003 or earlier" version as I know some people can't access the later programs as I'm using, (I'm in Accdb 2010) however, my system would only let me save it as 2010 as it states I'm using files etc only found in that version.

View attachment Megacities Statistics1.accdb

 

[pbaldy]

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]

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 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]

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]

Thanks for your reply, but now I'm really lost 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]

See attached, which is the source query of your form (its Record Source).

 

[Megacitizen]

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]

Happy to help!