Help in dmax using 2 criterias

[deanvilar]

please can you check my code below it returns an error: TYPE MISMATCH

moveCtr is a variable string
fields in table: assetNo and moveCode

tstJobCode = Nz(DMax("toJobcode", "assetMovementTable", "assetNo LIKE '" & Me.txtAssetNo & "*'" And "moveCode LIKE '" & moveCtr & "*'"), "")

[Seth Schrock]

The word AND in your criteria needs to be inside the quotes.

tstJobCode = Nz(DMax("toJobcode", "assetMovementTable", "assetNo LIKE '" & Me.txtAssetNo & "*' And moveCode LIKE '" & moveCtr & "*'"), "")

[zmbd]

Well, then something has been declaried a different datatype than what you expected.

First: What is the datatype for "tstJobCode"
Second: What does your criteria string resolve to... you'll need to modify the code a tad to trouble shoot the resolved string. This is always the issue when the criteria is built "in-line" with the function because it makes it very deficult to determine what the string resolves to... This is one of my petpeeves

I have not altered your code persay... however this is what I'd suggest you do:

(caution... this is basically air code so you can see what I'm suggesting you do to troubleshoot your code.)

Sub zxmple()
Dim tstJobCode As String
Dim strSQL As String
'
'Setup the criteria string as a seperate string:
strSQL = "assetNo LIKE '" & Me.txtAssetNo & "*'" And "moveCode LIKE '" & moveCtr & "*'"
'
'Now you can debug the string... to see it press <ctrl><g>
Debug.Print strSQL
'
'>>>insert a STOP so you can review the string
Stop
'
'and now here we go.... I suspect that if you did define the tstJobCode as a string then the "" is the error
'i'll just stick a zero in there for now.
tstJobCode = Nz(DMax("toJobcode", "assetMovementTable", strSQL), "0")
'
End Sub
 

[deanvilar]

that solves it wheeew ... thank you again sir Seth!!!

[deanvilar]

thank you for the suggestion sir ZMBD, what sir seth did already worked .. thank you very much!