Solved Type Mismatch in Expression / Invalid Procedure Call (1 Viewer)

[rodski]

Greetings,

This one has me stumped! I have a database with a memo/long text field and in this field there are a bunch of time stamps and some text (for example, "2022-04-15 0945 In Production"). I have successfully extracted the time stamp in a query (using "Mid([Status],InStr([Status],"In Production")-18,11)") but I am not able to filter the result with either string or date criteria. I was trying to filter the data between 2 dates or a date greater than or equal to one of the dates but no matter what I do, I get the "Type mismatch in expression" or "Invalid Procedure Call". I've tried dissecting the date and rebuilding it as a string, changing the format in the field to date, used the # before & after the value etc., but to no avail even when I copy one of the results and placing it in the query criteria.

The data type in the original field is of course long text but I am stumped to figure out what is going wrong. It seems like filtering the resulting extraction is not possible? I hope some brilliant person can help solve this issue or explain why it is not possible. Thanks in advance!!!

Cheers,
Rod

 

[MajP]

any chance you can post a sample of the data? 20 or more rows of just the memo field would probably be fine.

 

[The_Doc_Man]

If you are trying to treat a time-string as a time, you need to enclose it in #, as #2022-04-15#. You said you tried that but it didn't work. So the question is, when you get the "Invalid Procedure Call" does it highlight a specific line? Type mismatch probably occurs because the spacing isn't right so your "Mid" sequence is picking the wrong characters for the date. But the procedure call error might tell us more.

Wouldn't hurt to see the full query either.

 

[rodski]

Hi MajP,

It is a huge database, so I created a smaller one (attached) with only a few records in the table with the query I was using. Odd, in this database, I am easily able to filter it. So I went back to the one I'm working on and still get the errors.

I'm tempted to export everything into a new database and see if that makes a difference. Frustrating!

Thanks for your quick response!

-Rod

 

[rodski]

If you are trying to treat a time-string as a time, you need to enclose it in #, as #2022-04-15#. You said you tried that but it didn't work. So the question is, when you get the "Invalid Procedure Call" does it highlight a specific line? Type mismatch probably occurs because the spacing isn't right so your "Mid" sequence is picking the wrong characters for the date. But the procedure call error might tell us more.

Wouldn't hurt to see the full query either.

Thanks The_Doc_Man,

As I just posted to MajP, the query seems to work in the stripped down database. So I will go back to the drawing board to see why it isn't working in the database it came from. There are a large number of records in the original database (+212,000) but that shouldn't make a difference because I am able to filter other fields with no problems.

Cheers,
-Rod

 

[MajP]

Then it is probably failing on cases that return null values. You will need to filter out the bad data.

 

[The_Doc_Man]

I can only second MajP's opinion. Nulls eat your lunch for you if you don't take steps to handle them when they occur OR to remove them (mitigate them) before trying to use the dataset.

 

[rodski]

Hi MajP,

You were absolutely correct! I just needed an IIF statement before extracting the date. It's amazing what a good night's sleep will do when you go down the odd rabbit hole (I should have known better).

Thanks for all your help!

-Rod