DMAX and Linked tables

[George Too]

Anyone has encountered a problem with Dmax and linked tables? Or is it my code? The following line generates a run-time error '62506' only when my table is linked but not when the table resides in the db. Any ideas as to why?

NextNum = DMax("Val(Ticket)", "tblHolds")

The 'Ticket' field is a text field that holds alpha-numeric data in this format:
4567A, 2125B...etc.

As always, any help is much appreciated.
Thank you.

 

[MarkK]

Is there an error description in addition to the number?
Would this work?

NextNum = Val(DMax("Ticket", "tblHolds"))

 

[George Too]

"Run-time error '62506': Data type mismatch in criteria expression." This occurs only when the "tblHolds" table is linked.

NextNum = Val(DMax("Ticket", "tblHolds"))
does not work as I'm trying to get the numbers from a string tho it does not generate the error as mentioned above.

 

[Simon_MT]

What happens if you use:

NextNum = DMax("Ticket", "tblHolds")

Simon

 

[George Too]

See the 'Ticket' field is a text field so dmax returns '999' as the highest value which is not true. In order for me to strip the 'a' and 'b' from the string I'm using Val() function.

 

[Simon_MT]

Is there anything strange in this file I can't get it to fail try this in SQL

SELECT Max(Val([Ticket]))+1 AS Test
FROM tblHolds;

Simon

 

[George Too]

It works only if my table is not a linked table otherwise it fails with a type mismatched error.

 

[Simon_MT]

All my tables are linked and I'm running these against the FE Version.

Simon

 

[George Too]

I have tried several approaches but still no luck. Could the problem be coming from the Val() function? Should I inspect my table design or something? Any other suggestions?

 

[Simon_MT]

I would look at the table do a Max on Ticket

Simon

 

[George Too]

Dmax and Max work on the Ticket field in the linked table, but again, that is a text field and I need to strip the alpha characters from the string. Unless there is a work around it?

 

[boblarson]

I have tried several approaches but still no luck. Could the problem be coming from the Val() function? Should I inspect my table design or something? Any other suggestions?

Well, here are my observations:

1. Yes, I believe the Val is causing you problems

2. If you needed to get the max number then you should have stored the value as two separate fields; the base number, and the suffix.

3. What you might be able to use is the Left Function to strip the suffix and get to the number and THEN use the DMAX on it.

 

[George Too]

Well, databases are evolving and the one I'm currently working on was designed eons ago without much in mind. As for the Left function, I was afraid it had to come to that just because I wanted a quick simple way without much code involve.

Thanks both for your inputs. Much appreciated.