VB, DMax with 'And'

[michelle]

I'm trying to make a search in my database with a statement like this

magId = DMax("magasinId", "Photos", "[travelname] = '" & travel & "'"
And "[boxId] = highestBoxId")

pictureId and boxId are integers in the datadase and magasinId is a
string.
In the Sub they are all 'Variant'.

this works
magId = DMax("magasinId", "Photos", "[travelname] = '" & travel &
"'")

-but as soon I add an 'And' it doesn't
magId = DMax("magasinId", "Photos", "[travelname] = '" & travel & "'"
And "[boxId] = '" & highestBoxId & "'")
-nor des this
magId = DMax("magasinId", "Photos", "[travelname] = '" & travel & "'"
And "[boxId] = highestBoxId")

So,my conclusion is that I'm using the statement wrong when I add
the 'And'.

Can anyone help me?
Please
/Michelle

 

[fredg]

I'm trying to make a search in my database with a statement like this

magId = DMax("magasinId", "Photos", "[travelname] = '" & travel & "'"
And "[boxId] = highestBoxId")

pictureId and boxId are integers in the datadase and magasinId is a
string.
In the Sub they are all 'Variant'.

this works
magId = DMax("magasinId", "Photos", "[travelname] = '" & travel &
"'")

-but as soon I add an 'And' it doesn't
magId = DMax("magasinId", "Photos", "[travelname] = '" & travel & "'"
And "[boxId] = '" & highestBoxId & "'")
-nor des this
magId = DMax("magasinId", "Photos", "[travelname] = '" & travel & "'"
And "[boxId] = highestBoxId")

So,my conclusion is that I'm using the statement wrong when I add
the 'And'.

Can anyone help me?
Please
/Michelle

Your AND operator is outside of the quotes.
You need to remove the double quote immediately before and after the
word AND.
Also, the value of HighestBoxID has to be concatenated into the
expression.

From your syntax, it seems as though [TravelName] is a Text datatype
and [BoxID] is a Number datatype?

Try:
magId = DMax("magasinId", "Photos", "[travelname] = '" & [travel] &
"' And [boxId] = " & [highestBoxId])

 

[Rick Brandt]

I'm trying to make a search in my database with a statement like this

magId = DMax("magasinId", "Photos", "[travelname] = '" & travel & "'"
And "[boxId] = highestBoxId")

[snip]

The "And" needs to be inside the quotes as it is part of the syntax for the
WHERE clause. You were also delimiting the second criteria incorrectly.

magId = DMax("magasinId", "Photos", "[travelname] = '" & travel & "' AND [boxId]
= " & highestBoxId & "")

Only your VBA variables should be outside the quotes.

 

[Michel Walsh]

Hi,


If travel and highestBoxld are controls, not variables, you can use:


magId = DMax("magasinId", "Photos", "[travelname] = FORMS!formName!travel
AND boxId = FORMS!FormName!highestBoxId")




Hoping it may help,
Vanderghast, Access MVP