Dmax type mismatch error

[Greyowlsl]

Hi,

What is wrong with this code?

Private Sub Text91_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
Dim work As String
work = DMax("[document number]", "Drawings", "[Category] = 'Mechanical*'" And "[product] = 'ls*'")
End Sub

- Drawings is a table
- Document number, category and product are columns in the table
- Mechanical and Ls are record fields in the table

thanks,

Leon

 

[RuralGuy]

Did you mean:

work = DMax("[document number]", "Drawings", [COLOR="Red"]"[Category] = 'Mechanical*' And [product] = 'ls*'"[/COLOR])
End Sub

...or were you trying to use wildcards...

work = DMax("[document number]", "Drawings", [COLOR="Red"]"[Category] Like 'Mechanical*' And [product] Like 'ls*'"[/COLOR])
End Sub

 

[Greyowlsl]

Yea i was trying to use wild cards, i put the new code in but now its doesn't do anything at all when i move my mouse over it.

 

[RuralGuy]

Do one test at a time until you get it working.

work = DMax("[document number]", "Drawings", "[Category] Like 'Mechanical*'")

work = DMax("[document number]", "Drawings", "[product] Like 'ls*'")

work = DMax("[document number]", "Drawings", "[Category] Like 'Mechanical*' And [product] Like 'ls*'")

 

[Greyowlsl]

Ive test the code and now i have tried another which also does not work:

Runtime error '62436': Syntax error in date in query expression '[text7] = #20099 #'.

The "20099" is the correct max value that i want displayed in the textbox (text103)

Private Sub Command107_Click()
Dim work As String
    work = DMax("[Text103]", "Drawings", "[text7] = #" _
        & Forms!frmDmechanical!Text7 & "#")
End Sub

 

[gemma-the-husky]

in your last expression, surely text103 and text7 are not right for field names in your table.

if you want variables inserting then you have to use for both text103 and the first text7

"[" & text103 &"]" etc, to wrap the variable into the correct expression.

create the condition part as a separate textstring and display it first, to see if it looks right

eg
wherestrg = "[text7] = #" & Forms!frmDmechanical!Text7 & "#"
msgbox(wherestrg)

then use wherestrg as your condition.

 

[Greyowlsl]

+1 rep 4 u

Hey thanks i got it working thanks to you, the code looks like this:

Private Sub Command107_Click()
Dim work As String
wherestrg = " " & Forms!frmDmechanical!Text7 & " "
Me.Text103 = wherestrg
End Sub