每年将文件编号重置为零 [英] Reset file number to zero each year

问题描述

我建立了一个文件管理数据库，根据年份生成文件编号。


即2006-PRRS-13



几个月后他们回来说我们提到的文件号需要每年重置为零。


他们点击按钮生成文件编号。


这个sql查询（下面）给出了我想要的结果，但我要么在代码中实现类似的方法或从查询中获取值，添加一个并将其放入表单中。要么解决我的问题。


SELECT Count（左（[file_no]，4））AS Expr1

FROM tbl_item

WHERE（ （（左（[file_no]，4））像年（现在（））））;



我也尝试过：


dim Now_year as Integer

now_year =年（日期）

NumberOfFiles = DCount（" FILE_NO"，" tbl_item"，"左（[file_no]，4）" = now_year）

MsgBox NumberOfFiles


与DMax采取了类似的方法。我没有成功。


我们非常感谢任何建议。


Sean

解决方案

如何你实施了PK + 1吗？目前？

我认为这里的一般策略/逻辑必须是：


1）记录当前年份


2）在表单打开事件中，相应的表单将当前年份与最后记录的年份进行比较


3）如果两者相等，请继续执行现有计划用于设置文件编号


4）如果两者不相等，请更改年份部分并将文件编号部分重置为1


您可以将年份填入实用程序表中的字段中，如果您有一个或只是为此目的组成一个记录/单个字段表。我总是为这样的事情制作一个以及记录公司数据以包括报告的信头等。

感谢您的回复。


目前我通过获取新记录的主键来设置id。


这是一个标签界面。他们在第一个选项卡上输入一些信息，然后移到第二个选项卡以生成文件编号。这个移动到下一个选项卡保存文件并创建主键，然后我从生成文件编号选项卡上的隐藏控件中检索。

展开 | 选择 | Wrap | 行号

I built a file management database which generates a file number based on the year.

i.e. 2006-PRRS-13

The criteria was that each file should be unique. So I took the primary key, added one to it and moved on.

Several months down the road they came back and said did we mention the file number needs to reset to zero each year.

They click a button to generate the file number.

This sql query (below) gives the result I want but I either need to implement a similar approach in code or take the value from the query, add one and put it into the form. Either would solve my problem.


SELECT Count(Left([file_no],4)) AS Expr1
FROM tbl_item
WHERE (((Left([file_no],4)) Like Year(Now())));


I have also tried:

dim Now_year as Integer
now_year = Year(Date)

NumberOfFiles = DCount("FILE_NO", "tbl_item", "Left([file_no],4)" = now_year)
MsgBox NumberOfFiles

Similar approaches were taken with DMax. I was unsuccessful.

Any suggestions would be greatly appreciated.

Sean

解决方案

How do you implement the "PK+1" at present?

I think the general strategy/logic here needs to be:

1) Record the current year somewhere

2) In the form open event the appropriate form compare the current year with the last recorded year

3) If the two are equal, continue your present scheme for setting the file number

4) If the two are not equal, change the year part and reset the file number part to 1

You can stuff the year into a field in a utility table, if you have one or simply make up a single record/single field table for that purpose. I always make one for things like this as well as recording company data for including in letterhead of reports, etc.

Thank you for the responses.

Currently I set the id by getting the primary key of the new record.

It is a tabbed interface. They enter some information on the first tab and move to the second to generate the file number. This movement to the next tab saves the file and creates the primary key which I then retrieve from a hidden control on the generate file number tab.

Expand|Select|Wrap|Line Numbers

这篇关于每年将文件编号重置为零的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！