simple syntax problem

[markjkubicki]

trying to return the record where:
txtType = me.txtType (there will most likely be several records)
and
that record (of those that match) that has the lowest value for the field lngPrintOrder

strSearch = "[txtType] = '" & Me.txtType & "'" <--- works!

strSearch = "[txtType] = '" & Me.txtType & "' and [lngPrintOrder] = min([lngPrintOrder])" <--- this does NOT !

with thanks in advance,


Mark

[ranman256]

Dmin("[field]", "table")

strSearch = "[txtType] = '" & Me.txtType & "' and [lngPrintOrder] =" & Dmin("[field]", "table")

[orange]

What exactly is lngPrintOrder?

strSearch = "[txtType] = '" & Me.txtType & "' and [lngPrintOrder] = min([lngPrintOrder])"

needs to be something such as

strSearch = "[txtType] = '" & Me.txtType & "' and [lngPrintOrder] =" & min([lngPrintOrder])

The stuff in purple has to be rendered to a value, then substituted into the expression. So it can not be in quotes


min will not be recognized. We need some context for what lngPrintOrder means and where it gets populated and which values you need for your search.

Update: As Ranman has posted, DMin() could be the answer.

[markjkubicki]

within table tblPages are fields:
txtType - string
lngPrintOrder - long number
PageLink - hyperlink

this table is linked in a many-to-one relationship to another table where the relationship is thru txtType
so there may be many txtTypes in tblPages, and I am looking for the first page of txtType...

Ranman256's DMin() almost works, but it yields the lowest value for lngPrint Order in the table;
what I need is the lowest lngPrintOrder where among the records where txtType = me.txtType

[June7]

lngPrintOrder is not a unique identifier in the table?

strSearch = "txtType='" & Me.txtType & "' AND lngPrintOrder=" & DMin("lngPrintOrder", "tblPages", "txtType='" & Me.txtType & "'")

[markjkubicki]

(for very curious reasons... lngPrintOrder is not a unique identifier)

never-the-less,
your syntax is exactly right

big thnx
new lesson learned!

mark