Search Form Query Problem

  • Thread starter Thread starter Charles D Clayton Jr
  • Start date Start date
C

Charles D Clayton Jr

I have a database of drawing names. Contained within the drawing name
are 'location,' 'size,' 'service,' 'spec' and 'line number.' An
Example would be BPD-10-PF-A-9081-1. Everything is separated by "-"
(Dashes) but not everything is a consistent number of characters.
However, I can generate the queries to extract the information from
the drawing number using "left", "Mid", "Right" & "InStr" functions
without any problems at all. But in trying to develop a search form
for other people I am encountering problems. I keep getting the error
#13 "Type mismatch". Here is some of the code:

Private Sub FindRecords_Click()
On Err GoTo Err_FindRecords_Click
Dim db As Database
Dim q As QueryDef
Dim strSQL As String
Dim line As String

' Create the query based on the information on the form
strSQL = "SELECT isos.* FROM Isos WHERE "

' Extract Size information from Iso
strSQL = strSQL & "(left([iso],3)) Like """ & Me![cLocation] & """"
strSQL = strSQL & " AND "
'strSQL = strSQL & "(Mid([iso],5,2)) Like """ & Me![cSize] & """"
(Test-Passed)
strSQL = strSQL & "AND "
'strSQL = strSQL & "(Mid([iso],((InStr([iso]," -
"))+1),((InStr(InStr([iso]," - ")+1,[iso]," - "))-(InStr([Iso]," -
"))-1))) Like """ & Me![cSize] & """"
strSQL = strSQL & "(Mid([Iso],(InStr(InStr(InStr([Iso]," -
")+1,[Iso]," - ")+1,[iso]," - ")+1),1)) Like """ & Me![cSpec] & """"

' Delete the previous query
db.QueryDefs.Delete "qFindRecordsTest"
Set q = db.CreateQueryDef("qFindRecordsTest", strSQL)

' Open the query
DoCmd.OpenQuery "qFindRecordsTest", acViewNormal, acEdit

Exit_FindRecords_Click:
Exit Sub

I found the location first using "left" and it worked find. I then
added size and that is when I started getting errors. I considered
that there was somthing about using "Mid" so I put a real simple
construction and it worked fine. Does anybody have any suggestions?

Thanks,

Charles D Clayton Jr
 
Charles,

the problem is, you should store all these data in separate fields,
after all they are different and very real properties of the drawing.
Putting them together for display is much more easy than trying to
fish something out of the whole string - as you see now. In that case
you wouldn't have the problems with the varying number of characters
either, plus much more flexibility for sorting, searching etc.

However, a suggestion: try to create the query in the query design
grid first. Start with 1 criteria, then add one more criteria at a
time to find out where's the problem.

And there is something in the code. If you posted the exact copy,
there's a glith in these lines (apart from 'Test Passed' not commented
out):

strSQL = strSQL & "(left([iso],3)) Like """ & Me![cLocation] & """"
strSQL = strSQL & " AND "
'strSQL = strSQL & "(Mid([iso],5,2)) Like """ & Me![cSize] & """"
(Test-Passed)
strSQL = strSQL & "AND "

If the comment is correct, you have at this point two AND operators in
the string:
"(left([iso],3)) Like ... AND AND "

Best regards
Emilia

Emilia Maxim
PC-SoftwareService, Stuttgart
http://www.maxim-software-service.de
I have a database of drawing names. Contained within the drawing name
are 'location,' 'size,' 'service,' 'spec' and 'line number.' An
Example would be BPD-10-PF-A-9081-1. Everything is separated by "-"
(Dashes) but not everything is a consistent number of characters.
However, I can generate the queries to extract the information from
the drawing number using "left", "Mid", "Right" & "InStr" functions
without any problems at all. But in trying to develop a search form
for other people I am encountering problems. I keep getting the error
#13 "Type mismatch". Here is some of the code:

Private Sub FindRecords_Click()
On Err GoTo Err_FindRecords_Click
Dim db As Database
Dim q As QueryDef
Dim strSQL As String
Dim line As String

' Create the query based on the information on the form
strSQL = "SELECT isos.* FROM Isos WHERE "

' Extract Size information from Iso
strSQL = strSQL & "(left([iso],3)) Like """ & Me![cLocation] & """"
strSQL = strSQL & " AND "
'strSQL = strSQL & "(Mid([iso],5,2)) Like """ & Me![cSize] & """"
(Test-Passed)
strSQL = strSQL & "AND "
'strSQL = strSQL & "(Mid([iso],((InStr([iso]," -
"))+1),((InStr(InStr([iso]," - ")+1,[iso]," - "))-(InStr([Iso]," -
"))-1))) Like """ & Me![cSize] & """"
strSQL = strSQL & "(Mid([Iso],(InStr(InStr(InStr([Iso]," -
")+1,[Iso]," - ")+1,[iso]," - ")+1),1)) Like """ & Me![cSpec] & """"

' Delete the previous query
db.QueryDefs.Delete "qFindRecordsTest"
Set q = db.CreateQueryDef("qFindRecordsTest", strSQL)

' Open the query
DoCmd.OpenQuery "qFindRecordsTest", acViewNormal, acEdit

Exit_FindRecords_Click:
Exit Sub

I found the location first using "left" and it worked find. I then
added size and that is when I started getting errors. I considered
that there was somthing about using "Mid" so I put a real simple
construction and it worked fine. Does anybody have any suggestions?

Thanks,

Charles D Clayton Jr
Click to expand...
 
I appreciate your answer and you are right it would be easier to
combine the strings rather than to extract. Unfortunately, that is
how they do it in the fabrication business. So the example I gave is
the actual name of the drawing which then becomes part of the name of
the pipe spool after it is fabricated (which will then be put together
for an offshore drilling platform). Fortunately for me, someone in
the past decided that the name should have some relevant information
in it (which makes things easier).

As I said earlier, what puzzles me is that each query runs fine as a
query. I have one big query that extracts every piece of information
from that drawing name and I never have any problems. There are no
conflicts with any criteria that I pick. But when I put that same
query into code, I get problems. Like so many I know so little that I
cannot begin to figure out what is wrong. The only thing I can think
of as I am now writing this is that there is some confusion over where
something is a number of a letter.

You want to know what the really frustrating part of all this is? I
have almost the exact same database and it works find in there. I do
not know what I have done different in this one to cause the error and
it is driving me up the wall.

However, let me express my appreciate for your time and suggestions.
I hope you have a good weekend

Blessings,

Charles D Clayton jr

Emilia Maxim said:
Charles,

the problem is, you should store all these data in separate fields,
after all they are different and very real properties of the drawing.
Putting them together for display is much more easy than trying to
fish something out of the whole string - as you see now. In that case
you wouldn't have the problems with the varying number of characters
either, plus much more flexibility for sorting, searching etc.

However, a suggestion: try to create the query in the query design
grid first. Start with 1 criteria, then add one more criteria at a
time to find out where's the problem.

And there is something in the code. If you posted the exact copy,
there's a glith in these lines (apart from 'Test Passed' not commented
out):

strSQL = strSQL & "(left([iso],3)) Like """ & Me![cLocation] & """"
strSQL = strSQL & " AND "
'strSQL = strSQL & "(Mid([iso],5,2)) Like """ & Me![cSize] & """"
(Test-Passed)
strSQL = strSQL & "AND "

If the comment is correct, you have at this point two AND operators in
the string:
"(left([iso],3)) Like ... AND AND "

Best regards
Emilia

Emilia Maxim
PC-SoftwareService, Stuttgart
http://www.maxim-software-service.de
I have a database of drawing names. Contained within the drawing name
are 'location,' 'size,' 'service,' 'spec' and 'line number.' An
Example would be BPD-10-PF-A-9081-1. Everything is separated by "-"
(Dashes) but not everything is a consistent number of characters.
However, I can generate the queries to extract the information from
the drawing number using "left", "Mid", "Right" & "InStr" functions
without any problems at all. But in trying to develop a search form
for other people I am encountering problems. I keep getting the error
#13 "Type mismatch". Here is some of the code:

Private Sub FindRecords_Click()
On Err GoTo Err_FindRecords_Click
Dim db As Database
Dim q As QueryDef
Dim strSQL As String
Dim line As String

' Create the query based on the information on the form
strSQL = "SELECT isos.* FROM Isos WHERE "

' Extract Size information from Iso
strSQL = strSQL & "(left([iso],3)) Like """ & Me![cLocation] & """"
strSQL = strSQL & " AND "
'strSQL = strSQL & "(Mid([iso],5,2)) Like """ & Me![cSize] & """"
(Test-Passed)
strSQL = strSQL & "AND "
'strSQL = strSQL & "(Mid([iso],((InStr([iso]," -
"))+1),((InStr(InStr([iso]," - ")+1,[iso]," - "))-(InStr([Iso]," -
"))-1))) Like """ & Me![cSize] & """"
strSQL = strSQL & "(Mid([Iso],(InStr(InStr(InStr([Iso]," -
")+1,[Iso]," - ")+1,[iso]," - ")+1),1)) Like """ & Me![cSpec] & """"

' Delete the previous query
db.QueryDefs.Delete "qFindRecordsTest"
Set q = db.CreateQueryDef("qFindRecordsTest", strSQL)

' Open the query
DoCmd.OpenQuery "qFindRecordsTest", acViewNormal, acEdit

Exit_FindRecords_Click:
Exit Sub

I found the location first using "left" and it worked find. I then
added size and that is when I started getting errors. I considered
that there was somthing about using "Mid" so I put a real simple
construction and it worked fine. Does anybody have any suggestions?

Thanks,

Charles D Clayton Jr
Click to expand...
Click to expand...
 
I find the solution to my type mismatch error. When I divide the
drawing name, I divide by the "-" (dashes). It seems that I needed to
put another set of quotes around the dashes in the Instr function
(e.g. change Instr("-") to Instr (""-""). Now I now longer get the
error but my query does not work. So I will keep trying to figure out
what exactly I need to do.

Thanks,

Charles D Clayton Jr



I appreciate your answer and you are right it would be easier to
combine the strings rather than to extract. Unfortunately, that is
how they do it in the fabrication business. So the example I gave is
the actual name of the drawing which then becomes part of the name of
the pipe spool after it is fabricated (which will then be put together
for an offshore drilling platform). Fortunately for me, someone in
the past decided that the name should have some relevant information
in it (which makes things easier).

As I said earlier, what puzzles me is that each query runs fine as a
query. I have one big query that extracts every piece of information
from that drawing name and I never have any problems. There are no
conflicts with any criteria that I pick. But when I put that same
query into code, I get problems. Like so many I know so little that I
cannot begin to figure out what is wrong. The only thing I can think
of as I am now writing this is that there is some confusion over where
something is a number of a letter.

You want to know what the really frustrating part of all this is? I
have almost the exact same database and it works find in there. I do
not know what I have done different in this one to cause the error and
it is driving me up the wall.

However, let me express my appreciate for your time and suggestions.
I hope you have a good weekend

Blessings,

Charles D Clayton jr

Emilia Maxim said:
Charles,

the problem is, you should store all these data in separate fields,
after all they are different and very real properties of the drawing.
Putting them together for display is much more easy than trying to
fish something out of the whole string - as you see now. In that case
you wouldn't have the problems with the varying number of characters
either, plus much more flexibility for sorting, searching etc.

However, a suggestion: try to create the query in the query design
grid first. Start with 1 criteria, then add one more criteria at a
time to find out where's the problem.

And there is something in the code. If you posted the exact copy,
there's a glith in these lines (apart from 'Test Passed' not commented
out):

strSQL = strSQL & "(left([iso],3)) Like """ & Me![cLocation] & """"
strSQL = strSQL & " AND "
'strSQL = strSQL & "(Mid([iso],5,2)) Like """ & Me![cSize] & """"
(Test-Passed)
strSQL = strSQL & "AND "

If the comment is correct, you have at this point two AND operators in
the string:
"(left([iso],3)) Like ... AND AND "

Best regards
Emilia

Emilia Maxim
PC-SoftwareService, Stuttgart
http://www.maxim-software-service.de
I have a database of drawing names. Contained within the drawing name
are 'location,' 'size,' 'service,' 'spec' and 'line number.' An
Example would be BPD-10-PF-A-9081-1. Everything is separated by "-"
(Dashes) but not everything is a consistent number of characters.
However, I can generate the queries to extract the information from
the drawing number using "left", "Mid", "Right" & "InStr" functions
without any problems at all. But in trying to develop a search form
for other people I am encountering problems. I keep getting the error
#13 "Type mismatch". Here is some of the code:

Private Sub FindRecords_Click()
On Err GoTo Err_FindRecords_Click
Dim db As Database
Dim q As QueryDef
Dim strSQL As String
Dim line As String

' Create the query based on the information on the form
strSQL = "SELECT isos.* FROM Isos WHERE "

' Extract Size information from Iso
strSQL = strSQL & "(left([iso],3)) Like """ & Me![cLocation] & """"
strSQL = strSQL & " AND "
'strSQL = strSQL & "(Mid([iso],5,2)) Like """ & Me![cSize] & """"
(Test-Passed)
strSQL = strSQL & "AND "
'strSQL = strSQL & "(Mid([iso],((InStr([iso]," -
"))+1),((InStr(InStr([iso]," - ")+1,[iso]," - "))-(InStr([Iso]," -
"))-1))) Like """ & Me![cSize] & """"
strSQL = strSQL & "(Mid([Iso],(InStr(InStr(InStr([Iso]," -
")+1,[Iso]," - ")+1,[iso]," - ")+1),1)) Like """ & Me![cSpec] & """"

' Delete the previous query
db.QueryDefs.Delete "qFindRecordsTest"
Set q = db.CreateQueryDef("qFindRecordsTest", strSQL)

' Open the query
DoCmd.OpenQuery "qFindRecordsTest", acViewNormal, acEdit

Exit_FindRecords_Click:
Exit Sub

I found the location first using "left" and it worked find. I then
added size and that is when I started getting errors. I considered
that there was somthing about using "Mid" so I put a real simple
construction and it worked fine. Does anybody have any suggestions?

Thanks,

Charles D Clayton Jr
Click to expand...
Click to expand...
Click to expand...
 
I find the solution to my type mismatch error. When I divide the
drawing name, I divide by the "-" (dashes). It seems that I needed to
put another set of quotes around the dashes in the Instr function
(e.g. change Instr("-") to Instr (""-""). Now I now longer get the
error but my query does not work. So I will keep trying to figure out
what exactly I need to do.
Click to expand...

Charles,

if you stick to this data structure (called denormalized by
professionals) , you'll always have problems. I'd still strongly
recommend to create a field for each string component if you're
allowed to lay hands on the table design.

Best regards
Emilia

Emilia Maxim
PC-SoftwareService, Stuttgart
http://www.maxim-software-service.de
 
Back
Top