Getting the value from a table

[wojtasik6]

Hi there! I've got a bit of a problem with determining the max value in the table. I have a table called "Functions" and a column: "Function_KSW".
There are values as follows:
281A01
421A01
281A02
etc.
I want to get the max value of them but under the condition that first three chars are e.g 281 so the max function will take only max from a narrowed criterion.
The chars are to be selected from combolist in a form.
I have written something like this:

Private Sub Click1()
Dim max As String
max = DMax("Function_KSW", "Functions", "Function_KSW like " * 281 * "")  // here 281 for the test's need
// should be: like " * [Forms]![Form1]!
[ListBox0]*"? 
End Sub

But, unfortunately, I am getting Run Time error 13, Type mismatch.
Don't know what is wrong with it.

I have also come up with this: But also doesn't work- the same error

Private Sub Click12()
Dim rs As ADODB.Recordset
Dim strSQL As String

Set rs = New ADODB.Recordset
strSQL = "SELECT Max(Function_KSW) FROM Functions WHERE Function_KSW like " * 281 * ""
rs.Open strSQL, CurrentProject.Connection
result = rs("Funkcja_KSW")
MsgBox result
rs.Close

End Sub

Any suggestions?

BTW. When I want to find the max by the SQL in the query and based on the value to sort in combolist in the form, sorting doesn't work as well. When the dirtect value (here 281) is written in the code it is working. Seems that those ** stars unable to retrive the vaule from the combolist

 

[Galaxiom]

Nested quotes are the problem.

DMax("Function_KSW", "Functions", "Function_KSW like ' * 281 * '")

 

[Galaxiom]

BTW When you want to substitute a variable into the expression:

DMax("Function_KSW", "Functions", "Function_KSW like ' *" & somevariable & " * '"

 

[wojtasik6]

Already tried it with Dmax(), and it helped to eliminate the error. But, I am getting another, strange: error 94, invalid use of Null

 

[wojtasik6]

Does anybody have any idea how I can get the max value from the table with like condition? (first letters start with e.g 281)

 

[Galaxiom]

Somehow my earlier post ended up with spaces where it should not have had them.

DMax("Function_KSW", "Functions", "Function_KSW Like '281*'")

 

[Galaxiom]

Somehow my earlier post ended up with spaces where it should not have had them.

The forum put them in again and I had to edit the post and take them out. I will report the odd behaviour to the site owner.

 

[wojtasik6]

Thank you @Galaxiom. Now everything works perfect.
Tell me one more thing, how can I stop getting the error when there is no value found in the table? When I use if after the funtion is run, it is not solving the problem bacause the errors pops up immediately and it doesn't go to the end of the code

 

[wojtasik6]

Why does not the programm conduct condition if when there is sql= null? It simply skips it and go directly to else?

sql = DMax("Function_KSW", "Functions", "Function_KSW like '" & ksw & "*'")
If sql = Null Then
MsgBox "There are no records found"
sql_new = [Forms]![Add_new]![Combi0] + "A00"
sql_new = [Forms]![Add_new]![Text38]
GoTo exit_if
Else
(...)

 

[Galaxiom]

Firstly, Null never equates to anything. Even (Null = Null) will return False.

A string variable cannot be made to equal Null. The variable would need to be a Variant for this.

Alternatively convert the Null returned by the DMax to an empty string using Nz().