从表中创建下一个ID [英] Create Next ID from Table

问题描述

如果案例编号在同一个月和一年中，我只希望assID报告最高编号，否则我希望它分配0.由于某种原因，我的代码不起作用。

lsi案例编号应该是类似于407-112

让我们说在07年4月份我们的最高案例数是112，那么assID应该报告112，这样，当我在4月份为新案例分配LSI案件编号时，它将读取407-113。


我还有另一个问题，因为在DMAX声明的标准部分我正在看LEFT最多3个变量，如果我们在10月11日或12个月它可能是4个。


所以，我希望dmax函数扫描我的数据库本月和年份格式化为myy（可能是2个月的长数）并报告最大的最右边的数字（在上面的示例中它将是112，但如果是新的月份，它将找不到数字。最正确的部分LSI案例编号始终为3数字长。

之后，我想将最新的案例分配给我在代码中编写的内容。

I only want the assID to report the highest number if the case number is in the same month and year, otherwise I want it to assign 0. For some reason, my code isn''t working.
The lsi case number should read something like 407-112
Lets say that in the month of April of 07 the highest case number we had was 112, then the assID should report 112, this way, when I assign the LSI Case Number for a new case in the month of april it will read 407-113.

I also have another problem because in the criteria part of the DMAX statement I am looking at the LEFT most 3 variables, it could be 4 if we are in month 10 11 or 12.

So, I want the dmax function to scan my database for the month and year formatted as myy (could be a 2 number long month) and report back the largest right most number (in the example above it would be 112, but if it was a new month it would find no number. The right most part of the LSI Case number is always 3 digits long.
After that I want to assign the newest case to be exactly as it is written in my code.

展开 | 选择 | Wrap | 行号

推荐答案

试试这个...

Try this ...

< span class =codeLinkonclick =Blur（this，this.parentNode.parentNode，getChildren（this），true）;>展开 | 选择 | Wrap | 行号

首先，我强烈建议您将[LSI案例编号]字段的格式更改为yyyymm-nnn。

目前你有什么肯定会让事情变得复杂，对于任何试图使用它的人来说，几乎可以肯定会更难以理解和理解。它也会在您的表格中以有趣且不可预测的顺序出现。本世纪的这个阶段可能有两位数的年份，但不是一个明智的选择。


假设我的建议被遵循，这段代码应该有所帮助：

Firstly, I would strongly suggest that you change the format of your [LSI Case Number] field to yyyymm-nnn.
What you have at the moment will certainly complicate matters for you and almost certainly will be harder to work with and understand for anyone trying to use it. It will also appear in a funny and unpredictable order in your tables. A two digit year is probably usable at this stage of the century, but is not a wise choice nevertheless.

Assuming my recommendations are followed, this code should help :

展开 | 选择 | Wrap | 行号

如何使用记录集来处理它。在多用户环境中处理锁定问题也可能更好。只是一个想法


例如


assID = DMax（" Right（[LSI案件编号]，3）" ，Master Log，Left（[LSI Case Number]，3）= Format（Now，m）& Format（Now，yy））

Me.LSI_Case_Number =格式（现在，m）&格式（现在，yy）& " - " &安培;格式（assID + 1，" 000"）

End Sub

What about using a recordset to handle it. Might also be better in multi user environments to handle locking issues. Just a thought

eg


assID = DMax("Right([LSI Case Number],3)", "Master Log", Left([LSI Case Number], 3) = Format(Now, "m") & Format(Now, "yy"))
Me.LSI_Case_Number = Format(Now, "m") & Format(Now, "yy") & "-" & Format(assID + 1, "000")
End Sub

展开 | 选择 | 换行 | 行号

这篇关于从表中创建下一个ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！