Query stops working with InStr null value

[Marcel2586]

Hello,

I have a question about errors on null value.
I have made a small database for tryout, it has to be implemented in another one.
And the small database is working.

I have one table where there is one field called BatchInput.
I scan a barcode into it and let two query’s breaking it apart.
I scan this batch into the table field BatchInput:
20 MAY 2004H149-082-79 A4147011A05

Then I have my first query (Qrybreak1) extracting the date and deleting H14
Date: Left([BatchInput];11)
PartCertNr: Right([BatchInput];Len([BatchInput])-14)
The result is this:
Field date: 20 MAY 2004
Field PartCertNr: 9-082-79 A4147011A05

the second query (Qrybreak2) I look for the first space:
space: InStr([PartCertNr];" ")
then with the result I cut it into two pieces
PartNumber: Trim(Left([PartCertNr];[space]-1))
CertNumber: Trim(Right([PartCertNr];[space]+2))
Resulting into:
Field Partnumber: 9-082-79
Field Certnumber: A4147011A05
Then I have a third query (QryResult) where all is built together:
Field Date: 20 MAY 2004
Field PartNumber: 9-082-79
Field CertNumber: A4147011A05
This works great. (very happy)

But then I have a barcode without a Certnumber, then everything grinds to a halt.
014-11-2000 H14137-00-277-69FS
Giving me one error on the query (Qrybreak2) #Func!
And query (QryResult) even won’t start, giving a popup with “Invalid procedure call”
How could I handle Null on the part where there is no space after the partnumber (missing Certnumber)?

 

[jdraw]

Can you test for the "condition"; get the record; either correct the issue on input, or skip that record?

 

[Marcel2586]

No, we can't, this are all Boeing parts that we need to keep track of, even the parts without certificates. and with thousands of parts it is hard to check

 

[jdraw]

I didn't understand the barcode without certnumber - maybe you could describe in detail with an example.

Here's a routine with some test data based on your post to parse your input data and print out the results.

'---------------------------------------------------------------------------------------
' Procedure : Boeing
' Author    : mellon
' Date      : 07/02/2015
' Purpose   : From http://www.access-programmers.co.uk/forums/showthread.php?t=274592
' This proc uses the following Type:
'Type BoeingNormal       ' How to read the Boeing input data
' InDate As String       ' 11 Chars
' PartNo As String       ' 10 Chars
' CertNumber As String   ' 11 Chars
'End Type

'
'Attempt to parse incoming data that may or may not have a space in position 23
'data represents a Date, PartNumber and Certification Number
'
' The idea is to not depend on the space or lack of space in position 23.
'
' I have formatted the InDate as per the post, but it could be in other formats as needed.
' It could be used as a Date datatype if needed.
'---------------------------------------------------------------------------------------
'
Sub Boeing()
          Dim i As Integer
          Dim InData As String
          Dim testData(4) As String
10       On Error GoTo Boeing_Error

         ' ---some test data ----------------------------------------
20        testData(0) = "20 MAY 2004H149-082-79 A4147011A05"    'space
30        testData(1) = "17 SEP 2004H888-082-79A4147011C99"  'no space
40        testData(2) = "09 JUN 2007G555-999-21 b555999Z222"    'space
50        testData(3) = " 9 JUL 2017G333-999-21T566899Z333"  'no space
60        testData(4) = "09 DEC 2006G444-999-21 x555909Z444"    'space
          
          'sample  record layout
          Dim xx As BoeingNormal 'thus relates to the Type statement see comment above

70        For i = 0 To UBound(testData)
80            InData = testData(i)
90            xx.InDate = CVDate(Left(InData, 11))
100           xx.CertNumber = Right(InData, 11)
110           xx.PartNo = Mid(InData, 12, 10)

120           Debug.Print Format(xx.InDate, "DD MMM YYYY") & "  " & xx.PartNo & "   " & xx.CertNumber
130       Next i

140      On Error GoTo 0
150      Exit Sub

Boeing_Error:

160       MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Boeing of Module AWF_Related"
End Sub

Following are the results:

20 May 2004  H149-082-7   A4147011A05
17 Sep 2004  H888-082-7   A4147011C99
09 Jun 2007  G555-999-2   b555999Z222
09 Jul 2017  G333-999-2   T566899Z333
09 Dec 2006  G444-999-2   x555909Z444

 

[Marcel2586]

Thank you very much, i will test this as soon as possible.

Here is a example with certificate number and one without.

With:
13-MAR-2007H101150002-604-010 220920

Without:
014-11-2000H14137-00-277-69FS
Also my database with error on 014-11-2000H14137-00-277-69FS

 

[jdraw]

You have an issue with your business process in my view.

It appears that you are receiving data in differing formats.
Difference in date format eg "11 Mar 2004" and "014-11-2000".

Note: It appears that SKYPE on my machine is trying to make 0 1 4 - 11 -2 0 0 0 into a phone number

Some inputs have field values missing - No certification number.

Business processes have to be more disciplined than this. You can not program or build something you can't define.


Note:
I added your input data records to my test cases.

  testData(2) = "20 MAY 2004H149-082-79                 A4147011A05"
  testData(3) = "014-11-2000 H14137-00-277-69FS"
  testData(4) = " 2 OCT 2001H140-111-0048-1000          A1567423A06"

I modified the procedure because of the number of contiguous spaces in your input data.

Here is the revised code:

Option Compare Database

Type BoeingNormal       ' How to read the Boeing input data
 InDate As String       ' 11 Chars
 PartNo As String       ' 10 Chars
 CertNumber As String   ' 11 Chars
 End Type

'---------------------------------------------------------------------------------------
' Procedure : Boeing
' Author    : mellon
' Date      : 07/02/2015
' Purpose   : From http://www.access-programmers.co.uk/forums/showthread.php?t=274592
' This proc uses the following Type:
'Type BoeingNormal       ' How to read the Boeing input data
' InDate As String       ' 11 Chars
' PartNo As String       ' 10 Chars
' CertNumber As String   ' 11 Chars
'End Type

'
'Attempt to parse incoming data that may or may not have a space in position 23
'data represents a Date, PartNumber and Certification Number
'
' The idea is to not depend on the space or lack of space in position 23.
'
' I have formatted the InDate as per the post, but it could be in other formats as needed.
' It could be used as a Date datatype if needed.
'---------------------------------------------------------------------------------------
'
Sub Boeing()
          Dim i As Integer
          Dim InData As String
          Dim testData(4) As String
10       On Error GoTo Boeing_Error

         ' ---some test data ----------------------------------------
20        testData(0) = "20 MAY 2004H149-082-79 A4147011A05"    'space
30        testData(1) = "17 SEP 2004H888-082-79A4147011C99"  'no space

'add ops data here
          testData(2) = "20 MAY 2004H149-082-79                 A4147011A05"
          testData(3) = "014-11-2000 H14137-00-277-69FS"
          testData(4) = " 2 OCT 2001H140-111-0048-1000          A1567423A06"


40       ' testData(2) = "09 JUN 2007G555-999-21 b555999Z222"    'space
50       ' testData(3) = "014-11-2000H14137-00-277-69FS"  'no space
60       ' testData(4) = "09 DEC 2006G444-999-21 x555909Z444"    'space
          
          'sample  record layout
          Dim xx As BoeingNormal 'thus relates to the Type statement see comment above

70        For i = 0 To UBound(testData)
80            InData = Replace(testData(i), "  ", " ")
90            xx.InDate = CVDate(Left(InData, 11))
100           xx.CertNumber = Right(InData, 11)
110           xx.PartNo = Mid(InData, 12, 10)

120           Debug.Print Format(xx.InDate, "DD MMM YYYY") & "  " & xx.PartNo & "   " & xx.CertNumber
130       Next i

140      On Error GoTo 0
150      Exit Sub

Boeing_Error:

160       MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Boeing of Module AWF_Related"
End Sub

and the results

20 May 2004  H149-082-7   A4147011A05
17 Sep 2004  H888-082-7   A4147011C99
20 May 2004  H149-082-7   A4147011A05
14 Nov 2000   H14137-00   00-277-69FS
02 Oct 2001  H140-111-0   A1567423A06

Note: I did not try to reject invalids - I just processed them. That kind of check can be added once you get your process "cleaned Up".

My code will take the 2 different date formats I mentioned and handle them properly. But you can not create data when it's missing. The only option you have when you receive improperly valued records is to highlight the record/move it to an invalid records file.

I looked at your database. I really don't understand your queries - with no joins between tables....1

Please tell us more about your knowledge and use of database and Access.

good luck

 

[Marcel2586]

I know that the business process is horrible, there i cannot change one thing. A company with 45000 employees, all working their own way and there you get a lack of structure. This is a project that i was given. The man before me used a excel sheet and just typed all the data by hand. I try to do it a little bit faster by scanning the barcode. On the barcode there is all the information that I need. Only one problem, it scans and just prints the outcome, it cannot handle tabs and spaces properly. That the data is not always the same, that has to do that before 2004 we did not printed the certificate number on the batch sticker. Until the authorities forced us to print it on the batch sticker to trace flying parts back.
My knowledge In access is at learning stage, you have to start somewhere, but I can build databases that work on a crude way.
And as i look at the sticker, i forgot cure date and shelf life in my database.
The database you have is a test to see if i get it to work, so i do not have to go the same way as my colleague before me.

 

[jdraw]

Marcel,

I'm sure things look like they can not be changed. But I can assure you, they can. In your own words

Until the authorities forced us to print it on the batch sticker to trace flying parts back.

Somebody (in authority) said enough is enough, put it on the sticker. That somebody saw a problem, and someone said you need to put "XXX" on the sticker, and behold, management said do it. Unfortunately, so it seems to me, they addressed a symptom that made some group happy(it resolved their immediate problem) but it did not cure the basic problem. The data format, the consistency of the scanner or the information getting to the scanner were, and are still, a problem. Seems nobody stepped back to review the process and get to the heart of the issue.

In my view, you can not

i forgot cure date and shelf life in my database.

. You need to get a handle on the requirements of your proposed database --best approach is to write it down in clear and simple English. Start at the 30,000 ft overview and then work the details. Get others, knowledgeable of what you are trying to do, to review your findings. Be prepared to change things; you can even make an incorrect statement on purpose, just to let others "find the mistake" --they want to be part of the solution and you want a solution and so does management. So don't feel it's all up to you; get others involved. But you do need all the facts before you build the database. Build a model on paper if necessary and make sure the things involved and how they relate to each other satisfy the requirements. Get others involved to gather facts and review the model.
If it's any comfort, Boeing did not build the 747--787 without some models on paper, or collaboration among various teams. They did not all work independently and in one "magic moment" roll off some finished passenger jets.

What exactly do you need on that sticker? Define every piece of information includnig the format. Then determine the source of that info. Determine any anomalies --bad format, missing data,.... and identify the downstream issues because of those anomalies.
Determine which formatting type issues can be resolved with programming/logic. Determine exactly what data is NOT Available to be placed on the sticker.

You can not concoct data or materialize basic data from nothing. But you can identify programmatically that not value has been supplied. Lots of programs, typically called data validation routines, have been written for all sorts of situations.

You say

I can build databases that work on a crude way.

but I can assure you you can not dream up basic data that is missing. You have to adjust the input processes to ensure they collect/supply that data.

Don't get me wrong, I'm aware that nothing is 100% perfect. There is always room for improvement, or at some time you'll find a condition that has not been addressed at design. That's why there are system modifications, enhancements, maintenance, release levels/versions etc. But you can not invent basic data at the database level, if it is not supplied by the process(es) that feed the database.

There are free videos here that may help with some database concepts.

Good luck with your project.