VBA code to check if there "/" in the Reference

[aman]

Hi All

VBA code to check if there is / in the Reference. If there is "/" in the Reference then display Yes otherwise No.

e.g Reference=C123OIQ3/1200

NOW THE ANSWER FOR THIS SHOULD BE YES.

Thanks

 

[Minty]

Use the InStr Function
https://support.office.com/en-us/article/InStr-Function-85d3392c-3b1c-4232-bb18-77cd0cb8a55b

 

[aman]

I have a table with Field name "Reference". The Reference format is as below:

First two characters Initial of the name
Next 6 digits for todays Date (ddmmyy)
a character i.e "/"
then Unique number at the end.

so e.g AK280416/1200

SO if there is "/" in the code then the following code works fine and give me next DocCode in the textbox AK280416/1201

But sometimes we get codes from the company that are without "/" e.g CW21904 so in that case the table field will store CW21904 but when the form is loaded then it gives me AK280416/1 in the DocCode textbox but instead I want Code to do the following things:

1. If the top record of the table stores DocCode without "/" e.g CW21904
2. Then vba code should check for the next record with "/" and give me the Unique code after "/" that I can add by 1 to give me the next DocCode

 ' Find max ID from the table
 i = DMax("ID", "tbl_Mas_DocLog")
 MsgBox "Max id=" & i
 
' Look for Doccode against that ID
 a = DLookup("DocCode", "tbl_Mas_DocLog", "ID=" & i)
 MsgBox "DocCode=" & a
 'Find the Unique Code after "/"
 j = Val(Mid(a, InStr(a, "/") + 1)) + 1
 
' Look for Staff Name corresponding to the Staff Number
 b = DLookup("[Staff Name]", "tblstaff", "[Staff Number]=" & Right(Environ("username"), 6) & "")
 ' Find Initials using the below formula
 initial = Left(b, 1) & Mid(b, InStr(1, b & " ", " ") + 1, 1)
 ' The Unique DocCode will be as below:
 Ref = initial & Format(Date, "ddmmyy") & "/" & j
 txtCode = Ref

 

[Grumm]

Well what you tell here : "But sometimes we get codes from the company that are without "/" e.g CW21904 so in that case the table field will store CW21904 but when the form is loaded then it gives me AK280416/1 in the DocCode "
is correct when I look your code.

if there is no "/" : j = 1
Then later you do Ref = initial & date & "/" & j. So you will ALWAYS get either /xxx if there is a "/" found in the text or /1

So not sure what you want ? Since you are surprised that the correct result is actually not correct, I am confused too.

Maybe it is beter to start again and explain in detail what you need.

 

[arnelgp]

there is a flaw in your function.
1. firstly you must first, get the initial and date.
2. after you lookup for the max value of the above concatenation.
3. and then add 1.

after adding new record, you save the value you get on step three to a table.
this is the table you want to lookup for max value (step 2).

 

[aman]

I have resolved it. Thanks anyway everyone!!