In Query...Data type mismatch in criteria expression 1

[fredka]

I tried searching but could not find an answer... I have a table that holds a primary key which is a text field. (opportunity ID) is the name of the PK.

The data comes from an external source so I had no control over the format of it (it has numbers and text fields)

I have a query that concatinates a number (1 through 6) according to another field to this primary key field - For example:

1-X90A98 is a primary key - it becomes 61-X90A98 (named maxsalesstage)

The reason this is done is to remove duplicates (by duplicates I mean I want a unique account name, region, market segment and effective date that has the most advanced sales stage field.- an if statement takes the most advanced record (based on the above fields) and by using the max function takes the max maxsalesstagefield.

So I have one query taking only the max and eliminates the duplicates... I then want to link the primary key back up to the original table to pull more information.

The problem is that when I use:

Oppty ID: CStr(Right([maxofmax sales stage],(Len([maxofmax sales stage])-1))) and link it back to the original primary key from the table, I get the data type mismatch error.

Hope I explained this ok - any help would be greatly appreciated!!!

Thanks!!!

Fred

 

[dhookom]

I don't think you need to use CStr() when the expression is already a string. Does [maxofmax sales stage] always have a length of at least one character?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[fredka]

Thanks for the reply Duane - I added the cstr when I first got the error thinking that somehow my concat field somehow became numberic - but now that you say that, it could not be numberic because it also contains letters -

Yes, the field will always have @ least one character.

Thanks!!

Fred

 

[fredka]

Duane, you hit the nail on the head (as usual) I had an if statement and my if statement had a mis-spelling..... that was causing some records to have a blank -

As always, your the best .... thanks!!!!