Hi,
So I have a field in a table that will actually store numbers. I have had to make it text field as a serial numbers will be formatted as 1234/9876 which wouldn't work in a number field. Other serial numbers may also be formatted as 5 or 6 digits without any other characters.
The code is below is effectively trying to decide what fields to return depending on the length of the serial number (similar code for Len(abc) = 5 Or Len(abc) = 6) by editing the SQL of an existing query...
The MsgBox is just making sure the InputBox value is receiving all digits.
However after entering "7290/0001" nothing is returned. So I check the query manually to look at the criteria and the criteria is 7290/2? It's removed the zeros. I can understand why but don't know how to stop it from doing that! The second half of the serial number may be "/9435" though so I can't put a format on it to include zeros!
What I feel like I need to do is force the entry into the InputBox to be text data but I'm unsure of how to do that or if that would work.
I feel like I'm right on the edge of fixing this but worked on it for too long now.
Appreciate any and all help in advance.
So I have a field in a table that will actually store numbers. I have had to make it text field as a serial numbers will be formatted as 1234/9876 which wouldn't work in a number field. Other serial numbers may also be formatted as 5 or 6 digits without any other characters.
The code is below is effectively trying to decide what fields to return depending on the length of the serial number (similar code for Len(abc) = 5 Or Len(abc) = 6) by editing the SQL of an existing query...
Code:
Dim abc As String
abc = InputBox("What Serial Number Are You Searching For?", "Specify Serial Number")
If Len(abc) = 9 Then
MsgBox (abc)
Dim SQL As String
SQL = "SELECT Main_T.Serial_Number, Main_T.[SMT_Built?], Main_T.[SMT_Passed?], Main_T.[Seica_PCB_Passed?], Main_T.[PCB_Passed?], Main_T.[Seica_Module_Passed?], Main_T.[Module_Passed?] FROM Main_T WHERE Main_T.Serial_Number = " & abc
CurrentDb.QueryDefs("Temp_Q").SQL = SQL
DoCmd.OpenQuery "Temp_Q"
The MsgBox is just making sure the InputBox value is receiving all digits.
However after entering "7290/0001" nothing is returned. So I check the query manually to look at the criteria and the criteria is 7290/2? It's removed the zeros. I can understand why but don't know how to stop it from doing that! The second half of the serial number may be "/9435" though so I can't put a format on it to include zeros!
What I feel like I need to do is force the entry into the InputBox to be text data but I'm unsure of how to do that or if that would work.
I feel like I'm right on the edge of fixing this but worked on it for too long now.
Appreciate any and all help in advance.