Stripping Slashes

[roystreet]

Hello,
I have a form in for recording some staff meeting notes and other various information. In the form I have the user enter in the date of the meeting and then the unique_number (which is staff meeting one, two, three - however many you have in that day) I ultimately want the database to identify that staff meeting with an ID. Which is built by the txtMeetingDate + txtUniqueID, but I want the form to strip away the slashes in the date. Example: user enters in 12/17/74 and it's meeting 01. The StaffMeetingID would be 121774-01 (yes I do want the dash in the number as well) OK, I can get the values to concatenate just fine, but when the user reviews that record again, it removes the slashes in the date as well. So, they come back and see the date they entered as 121774. I want the user to see the slashes in the date, but in the id number no dashes. Any ideas?

Thanks,
---roystreet

 

[PHV]

Provided txtMeetingDate is a date:
txtStaffMeetingID = Format(txtMeetingDate, "mmddyy") & "-" & txtUniqueID

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[randy700]

How about....

IDNum = left(txtMeetingDate,2) & mid(txtMeetingDate,4,2) & right(txtMeetingDate,2) & "-" & txtUniqueID



Randy

 

[roystreet]

OK, I chose PHV's code for right now. But here's my next delima...I have a button on the form that allows them onclick generate a new uniqe_Number (earlier I accidentaly referred to it as Unique_ID incorrectly) I have the following code of which completes this process just fine, except one thing...It doesn't show the user what that new number is? If I leave the record and come back the value is there. Here is my code in the module - The button in the form calls this code:

DoCmd.GoToControl "txtUnique_Number"

    frm.Unique_Number = Format(Nz(DMax("[tbl_StaffMeetings].Unique_Number", "tbl_StaffMeetings", "txtMeetingDate"), 0) + 1, "00")
   
    DoCmd.GoToControl "txtProject_ID"

Thanks,
---roystreet

 

[randy700]

If I leave the record and come back the value is there.
Which means if you do a Me.Refresh it should be there as well.



Randy

 

[roystreet]

Well, this ain't no fun....My original code isn't working the way I had originally had hoped. The code will add one to the number, but it doesn't take into account that each date starts over that number. Example: 101704-01 is the first number - So I enter in another date and I click the button, it just adds one to the original number(unique_number) instead of starting over at one. So now I have a new date, but it ends now with two. Do I make sense??

Here is the original code that I drew from.

    frm.Unique_Number = Format(Nz(DMax("[tbl_MasterProjectInformation].Unique_Number", "tbl_MasterProjectInformation", "Standard_Number = '" & frm.Standard_Number & "' AND FY = '" & frm.FY & "'"), 0) + 1, "000")

It is a code that I use in another form that deals with different data. It works 100% correctly. So, I adapted it to this purpose and found now it doesn't do what it should. When I run the original code in the other form, it does display the new data as it should. I know I'm missing something here.

What's not right here??

---roystreet

 

[randy700]

It would probably be easier if you split your id number into 2 fields and concatenate them for display/report purposes. I'll call them idDate and idNumber. Then, when creating a new record, lookup the max idNumber where idDate equals today's date. If found, add 1 for the new record. If no match, start with 1.


Randy

 

[roystreet]

They are two fields in this specific table...MeetingDate and Unique_Number. I would have no problem keeping them that way, except - There is a relationship between this table and another. The other table refers to the data in this table by it's complete number. Unfortunately, the relationship does not work correctly without the complete number. I have also tried to create a relationship based on a query that concatenates the number into a new field, but that doesn't seem to work. I would be sitting very happy if this weren't a problem. Maybe there is another way to go about this? I could go without the number generator If I Have To.


---roystreet