Type mismatch in expression / Query

[Brian62]

I have a from the has two sub-form tables that share StudyID in link child and link master. The form works perfectly. But the information stored in the tables is creating problem when I need to create a report off of that information.

I have a query that runs off of three tables. Each have their own unique ID such as EntryID and Entry2ID and the main ID is StudyID. All three tables share the the main table StudyID. Since the information is coming from the form which keeps a history and in the query I have to use the last set of information. In the total I entered Last which works if I only link two tables. When I link the three tables is where I have the problem. It will pull the unique Entry2ID with information from a different record set to include with the other table information.

I hope I explained it correctly.

 

[Galaxiom]

Last is not a very predicitable aggregate since tables are unordered lists.
Use Max on a field such as a date or sequence number.

Similarly use Min rather than First.

 

[boblarson]

Agree with Galaxiom -

First and Last are essentially useless. They are supposed to give you the first or last item in an ordered set of data. But it doesn't always work the way you think it would.

Best to avoid those.

 

[Brian62]

I tried both your suggestions but same out come. I have the one-to-many linked to studyid. If I remove one of the three tables from studyid to entryid linked to studyid it works but gives me wrong information from that table.

I don't think Last has anything to do with my issue but I can be wrong. I think it is more how it links in the query.

Picture of query attached.

 

[boblarson]

Based on your picture (although a little hard to read as it isn't big enough) it looks like you need to link the third table shown to the First table shown and NOT through the second table shown. So the second and third would each be linked to the first.

 

[Brian62]

I tried that as well with the same result.

 

[boblarson]

Okay, not too clear on what it is that is in each table and how each relate to each other. Also what are you trying to do here - display records or what?

 

[boblarson]

And by "Doesn't work" what does that mean exactly?

 

[Brian62]

I created a form that keeps a history of inputted information for research studies. I had to keep it into three sections to be able to do other things that work fine.

Yes I am trying to display the last record set for the particular PI and StudyID.

Dosen't work meaning that I get the same error. Type mismatch in expression.

 

[boblarson]

Dosen't work meaning that I get the same error. Type mismatch in expression.

Does that happen when you go to RUN the query or when you do something in the form?

I might suggest for expediency's sake that you upload a copy of your database here so we can take a quick look.

 

[Brian62]

When I run the query. It is connected to a report.

I can upload the DB tomorrow since I'm about ready to go to a meeting.

 

[Brian62]

I had one of the StudID's as a Text and Not Number which caused my problems. Something I overlooked. Thanks for your help!

 

[boblarson]

good to hear you got it sorted.

 

[ColinEssex]

good to hear you got it sorted.

Yes, so good.

Col