Ato generated control number

[MyMel]

Hi , Thank you for any and all assistance.
I have a customer entry form(CustEntryForm) the fields that I want to auto
populate are CreationDate, ControlNumber. The CreationDate date/time stamps
when the customer request is created. The ControlNumber is my request
tracking field. The CreationDate field works. What I am having a problem
with is getting the ControlNumber field to auto generate. The ControlNumber
is being created based on the CreationDate field by grabbing the year portion
of the date. The date is structured like yy-001(10-001) I have the following
SQL statement below in my create request button after the CreationDate field
is populated. After the request data is entered you press a button it
timestamps it and it is suppose to assign it a ControlNumber, it not is
working. Please help.

stDocName = "SELECT CAST(substring(cast(datepart(yy,getdate()) as
char(4)),3,2)) + '-' + " & _
"replace(str((SELECT CASE WHEN(select count(*) from
Forms![Request_log] where datepart(yy,Me![CreationDate]) =
datepart(yy,getdate()) group by datepart(yy,Me![CreationDate])) " & _
"IS NULL THEN 0 ELSE (select count(*) from Forms![Request_log]
where datepart(yy,Me![CreationDate]) = datepart(yy,getdate()) group by
datepart(yy,Me![CreationDate])) END)+1,4),' ','0') ;"

 

[Steve Sanford]

You cannot just assign a SQL statement to a string variable and have a value
returned. Without the code for your "create request button", I don't know if
you have opened a recordset to get a value.

Also, the syntax is wrong for MS Access SQL. IFAIK, there is no "CAST"
function.


You said *what* you want, but didn't explain *how* you create the control
number.

yy-001(10-001)

The "yy" is the last two digits of the year.
What is "001" and how is it created or where does it come from?
What is (10-001)? How is it created or where does it come from?


HTH