Using DMax with a restricted set of records

[Guest]

In a table "tbl_Projects" I have two fields (amongst others) called Region
and SerialNo. I can use DMax to increment SerialNo by one when entering new
records

Me.SerialNo = DMax("[SerialNo]", "tbl_Projects", "SerialNo") + 1

but I cannot restrict the set of records DMax looks at to a particular region.

Any ideas?

 

[Rick Brandt]

In a table "tbl_Projects" I have two fields (amongst others) called
Region and SerialNo. I can use DMax to increment SerialNo by one when
entering new records

Me.SerialNo = DMax("[SerialNo]", "tbl_Projects", "SerialNo") + 1

but I cannot restrict the set of records DMax looks at to a
particular region.

Any ideas?

Your third argument in meaningless. It is supposed to be a valid SQL WHERE
clause (without the word "where").

If Region is text...
Me.SerialNo = DMax("[SerialNo]", "tbl_Projects", "Region = 'SomeRegion'") + 1

If Region is numeric...
Me.SerialNo = DMax("[SerialNo]", "tbl_Projects", "Region = 123") + 1

 

[Guest]

In a table "tbl_Projects" I have two fields (amongst others) called
Region and SerialNo. I can use DMax to increment SerialNo by one when
entering new records

Me.SerialNo = DMax("[SerialNo]", "tbl_Projects", "SerialNo") + 1

but I cannot restrict the set of records DMax looks at to a
particular region.

Any ideas?

Your third argument in meaningless. It is supposed to be a valid SQL WHERE
clause (without the word "where").

If Region is text...
Me.SerialNo = DMax("[SerialNo]", "tbl_Projects", "Region = 'SomeRegion'") + 1

If Region is numeric...
Me.SerialNo = DMax("[SerialNo]", "tbl_Projects", "Region = 123") + 1

Many thanks for the correction. Can I use the Region field on the form to
autopopulate the third argument? (I am thinking like ...... , "Region =
'Me.Region'")+1

 

[Guest]

Yes, here is the correct syntax. Notice also that I have added functionality
so you wont throw an error if this is the first serial number for a region:

Me.SerialNo = Nz(DMax("[SerialNo]", "tbl_Projects", "Region = '" &
Me.Region & "'"),0) + 1

--
Dave Hargis, Microsoft Access MVP

Keith Woollacott wrote:
In a table "tbl_Projects" I have two fields (amongst others) called
Region and SerialNo. I can use DMax to increment SerialNo by one when
entering new records

Me.SerialNo = DMax("[SerialNo]", "tbl_Projects", "SerialNo") + 1

but I cannot restrict the set of records DMax looks at to a
particular region.

Any ideas?

Your third argument in meaningless. It is supposed to be a valid SQL WHERE
clause (without the word "where").

If Region is text...
Me.SerialNo = DMax("[SerialNo]", "tbl_Projects", "Region = 'SomeRegion'") + 1

If Region is numeric...
Me.SerialNo = DMax("[SerialNo]", "tbl_Projects", "Region = 123") + 1

Many thanks for the correction. Can I use the Region field on the form to
autopopulate the third argument? (I am thinking like ...... , "Region =
'Me.Region'")+1

 

[Guest]

I have copied your syntax, but I get an error message "Run-time error '3464'.
Data type mismatch in criteria expression". Is this becaure the Region field
is a lookup to a seperate table?
--
Keith W

Yes, here is the correct syntax. Notice also that I have added functionality
so you wont throw an error if this is the first serial number for a region:

Me.SerialNo = Nz(DMax("[SerialNo]", "tbl_Projects", "Region = '" &
Me.Region & "'"),0) + 1

--
Dave Hargis, Microsoft Access MVP

Keith Woollacott wrote:
In a table "tbl_Projects" I have two fields (amongst others) called
Region and SerialNo. I can use DMax to increment SerialNo by one when
entering new records

Me.SerialNo = DMax("[SerialNo]", "tbl_Projects", "SerialNo") + 1

but I cannot restrict the set of records DMax looks at to a
particular region.

Any ideas?

Your third argument in meaningless. It is supposed to be a valid SQL WHERE
clause (without the word "where").

If Region is text...
Me.SerialNo = DMax("[SerialNo]", "tbl_Projects", "Region = 'SomeRegion'") + 1

If Region is numeric...
Me.SerialNo = DMax("[SerialNo]", "tbl_Projects", "Region = 123") + 1

Many thanks for the correction. Can I use the Region field on the form to
autopopulate the third argument? (I am thinking like ...... , "Region =
'Me.Region'")+1

 

[Guest]

No, it is what it says it is. The syntax assumes Region is a text field. If
it is a numeric field, it should be like this:
Me.SerialNo = Nz(DMax("[SerialNo]", "tbl_Projects", "Region = " &
Me.Region),0) + 1


--
Dave Hargis, Microsoft Access MVP

I have copied your syntax, but I get an error message "Run-time error '3464'.
Data type mismatch in criteria expression". Is this becaure the Region field
is a lookup to a seperate table?
--
Keith W

Yes, here is the correct syntax. Notice also that I have added functionality
so you wont throw an error if this is the first serial number for a region:

Me.SerialNo = Nz(DMax("[SerialNo]", "tbl_Projects", "Region = '" &
Me.Region & "'"),0) + 1

--
Dave Hargis, Microsoft Access MVP

Keith Woollacott wrote:
In a table "tbl_Projects" I have two fields (amongst others) called
Region and SerialNo. I can use DMax to increment SerialNo by one when
entering new records

Me.SerialNo = DMax("[SerialNo]", "tbl_Projects", "SerialNo") + 1

but I cannot restrict the set of records DMax looks at to a
particular region.

Any ideas?

Your third argument in meaningless. It is supposed to be a valid SQL WHERE
clause (without the word "where").

If Region is text...
Me.SerialNo = DMax("[SerialNo]", "tbl_Projects", "Region = 'SomeRegion'") + 1

If Region is numeric...
Me.SerialNo = DMax("[SerialNo]", "tbl_Projects", "Region = 123") + 1

Many thanks for the correction. Can I use the Region field on the form to
autopopulate the third argument? (I am thinking like ...... , "Region =
'Me.Region'")+1

 

[Guest]

Working as it should - my thanks to you both
--
Keith W

No, it is what it says it is. The syntax assumes Region is a text field. If
it is a numeric field, it should be like this:
Me.SerialNo = Nz(DMax("[SerialNo]", "tbl_Projects", "Region = " &
Me.Region),0) + 1


--
Dave Hargis, Microsoft Access MVP

I have copied your syntax, but I get an error message "Run-time error '3464'.
Data type mismatch in criteria expression". Is this becaure the Region field
is a lookup to a seperate table?
--
Keith W

Yes, here is the correct syntax. Notice also that I have added functionality
so you wont throw an error if this is the first serial number for a region:

Me.SerialNo = Nz(DMax("[SerialNo]", "tbl_Projects", "Region = '" &
Me.Region & "'"),0) + 1

--
Dave Hargis, Microsoft Access MVP


:

Keith Woollacott wrote:
In a table "tbl_Projects" I have two fields (amongst others) called
Region and SerialNo. I can use DMax to increment SerialNo by one when
entering new records

Me.SerialNo = DMax("[SerialNo]", "tbl_Projects", "SerialNo") + 1

but I cannot restrict the set of records DMax looks at to a
particular region.

Any ideas?

Your third argument in meaningless. It is supposed to be a valid SQL WHERE
clause (without the word "where").

If Region is text...
Me.SerialNo = DMax("[SerialNo]", "tbl_Projects", "Region = 'SomeRegion'") + 1

If Region is numeric...
Me.SerialNo = DMax("[SerialNo]", "tbl_Projects", "Region = 123") + 1

Many thanks for the correction. Can I use the Region field on the form to
autopopulate the third argument? (I am thinking like ...... , "Region =
'Me.Region'")+1