I have created a form [frmSampleLogIn] that will be used to enter records into a table [tblSampleLogIn]. Two of the fields in the table are:
Code:
[AccessionNo] AutoNumber Format 0000
[DateEntered] Text; Default Value =Format(Date(),"yymmdd")
What I am trying to do is to have a Text Box on the form that will display a combination of the two fields, and then record this value into the table when a new record is created. For example:
Say the next number for the [AccessionNo] autonumber field is 0045, and the value of the [DateEntered] field is 120229 (based upon the format), then I would like to have the Text Box on the form [frmSampleLogIn] to return something similar to: "0045120229". Then, to have that value stored in another field in the same table [tblSampleLogIn].
However, there is another small formatting change that needs to take place before the value is displayed & stored. I have created a query for such a format:
Code:
LabID: Format(Right("0000" & [DateEntered],4),"0000") & "-" & Format(Right("000" & [AccessionNo],3),"000")
Thus, with the above values for the [AccessionNo] and [DateEntered] fields, the end result is the query returning "0229-045" into the LabID field.
I have found a topic that talks a little bit about what I am trying to do.
Being new to Access, I tried to put the query/formula:
Code:
LabID: Format(Right("0000" & [DateEntered],4),"0000") & "-" & Format(Right("000" & [AccessionNo],3),"000")
directly into the Control Source of the textbox, hoping that it would return the combined values, and then insert them into a field in the table when a new record is created.
I think that I am on the wrong track with what I want to achieve. Any insight or suggestions are welcome. Thanks!