DLast with If Statement

[Zanstemic]

I'm working on comparing a number that is being calcualting (vbCaseNumber)
with the last number in a table Attendees. If the numbers match, it should
increment by 1.

I'm using DLast but not sure if it's giving the desired result since it's
still giving an error.

chkCaseNumber = DLast([CaseNumber], "Attendees")

If vbCaseNumber = ckCaseNumber Then
vbIncrementNumber = vbIncrementNumber + 1
vbCaseNumber = Format(Date, "yymmdd") & Format(vbIncrementNumber, "000")

Else

 

[John W. Vinson]

I'm working on comparing a number that is being calcualting (vbCaseNumber)
with the last number in a table Attendees. If the numbers match, it should
increment by 1.

I'm using DLast but not sure if it's giving the desired result since it's
still giving an error.

chkCaseNumber = DLast([CaseNumber], "Attendees")

If vbCaseNumber = ckCaseNumber Then
vbIncrementNumber = vbIncrementNumber + 1
vbCaseNumber = Format(Date, "yymmdd") & Format(vbIncrementNumber, "000")

Else

DLast() isn't doing what you think it's doing. It's retrieving the last record
*IN DISK STORAGE ORDER* - and you have no control over disk storage order.
It's not necessarily the most recently added record, or the largest case
number.

Use DMax() instead. However, if the case number is in fact something like
080130002, then incrementing it by 1 won't necessarily give you the desired
result!!!

John W. Vinson [MVP]

 

[John Spencer]

To get the largest case number you should use something like

chkCaseNumber = DMax("CaseNumber", "Attendees")

If vbCaseNumber = ckCaseNumber Then
vbIncrementNumber = vbIncrementNumber + 1
vbCaseNumber = Format(Date, "yymmdd") & Format(vbIncrementNumber, "000")



--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..