Simple query with "like" not working >:(

[Bruce Lawrence]

Ok, I'm baffled...

I'm making a query in access 97 between 2 tables. There is a field in
both tables called "DWGNO".

OPENORD has a record with a DWGNO of "00000012345"
DIEDATA has a record with a DWGNO of "12345"

I'm not doing this with VBA right now. I'm doing it through the query
gui.

My query:
Both tables are in the upper section with no links between them.
I have selected 4 fields. Only 3 are told to display results.
The field that is not showing results is where the criteria is entered.
The field is called DieData.DWGNO and the criteria is Like "*" &
[openord]![dwgno]

This shows no query results.
Please help me

[Allen Browne]

If you open these tables in design view, what is the Data Type of the
fields? If they are Text, the text does not match because the string
"00000012345" is nothing like the string "12345"

If they are Number type fields, be aware that Like is a string operator, so
you are still performing a string match. Open the Immediate Window (Ctrl+G)
and enter these lines:
? "12345" Like "*" & "00000012345"
? "00000012345" Like "*" & "12345"
The first returns False, while the second returns True.
The example you gave is like the first one, which does not match.

The other think to keep in mind is that Nulls don't match with Like "*".

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Bruce Lawrence" <BL*****@gmail.comwrote in message
news:11*********************@74g2000cwt.googlegrou ps.com...

Ok, I'm baffled...

I'm making a query in access 97 between 2 tables. There is a field in
both tables called "DWGNO".

OPENORD has a record with a DWGNO of "00000012345"
DIEDATA has a record with a DWGNO of "12345"

I'm not doing this with VBA right now. I'm doing it through the query
gui.

My query:
Both tables are in the upper section with no links between them.
I have selected 4 fields. Only 3 are told to display results.
The field that is not showing results is where the criteria is entered.
The field is called DieData.DWGNO and the criteria is Like "*" &
[openord]![dwgno]

This shows no query results.
Please help me

[Bruce Lawrence]

I see.

Well, then I guess I need a VBA module to convert this to a regular
number or remove the leading zeros.

Unless you have some quick code to copy/paste here, I'll start hunting
for a resolution.

Thanks Allen

[Allen Browne]

Make a backup copy of the mdb file (while it is NOT in use.)

Open the Relationships window (Tools menu), and remove any relationships
that use these fields.

Open the tables in design view, and change the Data Type to Number. Save.
Verify the numbers are still correct (sans the leading digits.)

Close the tables. Go back to the Relationships window again, and create the
relations with the Referential Integrity box checked.

They should now match correctly.

BTW, you can still display them with leading zeros if you wish, even after
converting them to numbers. Just set the Format property of the fields, to:
00000000000

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Bruce Lawrence" <BL*****@gmail.comwrote in message
news:11**********************@h48g2000cwc.googlegr oups.com...

>I see.

Well, then I guess I need a VBA module to convert this to a regular
number or remove the leading zeros.

Unless you have some quick code to copy/paste here, I'll start hunting
for a resolution.

Thanks Allen

[Bruce Lawrence]

Theres a small snag with that. These aren't always going to be
numeric fields. We could have "00012345R1"

The problem is the lady who updated the records in the DieData table
left the 0's out of it. The openord records come out of our SAP system
and I import those automatically through import specs.

I'm going to need some type of function or whatever to take out the
preceding zeros and then compare the 2 fields.

I'm not having much luck with that part

[Bri]

Bruce Lawrence wrote:

Ok, I'm baffled...

I'm making a query in access 97 between 2 tables. There is a field in
both tables called "DWGNO".

OPENORD has a record with a DWGNO of "00000012345"
DIEDATA has a record with a DWGNO of "12345"

I'm not doing this with VBA right now. I'm doing it through the query
gui.

My query:
Both tables are in the upper section with no links between them.
I have selected 4 fields. Only 3 are told to display results.
The field that is not showing results is where the criteria is entered.
The field is called DieData.DWGNO and the criteria is Like "*" &
[openord]![dwgno]

This shows no query results.
Please help me

The problem is that you are putting the wildcard on the field WITH the
leading zeros. Try it the other way around:
openord!dwgno Like "*" & DIEDATA!dwgno

Another alternative is (assuming that these always are numbers):
CLng(openord!dwgno) = CLng(DIEDATA!dwgno)
or if they really will get into the nine or ten digit length:
CDec(openord!dwgno) = CDec(DIEDATA!dwgno)

The wildcard criteria and thes conversion criteria could be slow if
there are a lot of records since none of them will use indexes on the
dwgno fields. If these are truely numbers, then maybe you should store
them as numbers and indes them. Then you could format the number to add
in the leading zeros on output. This would be much faster and also more
efficient in storage.

--
Bri

[Allen Browne]

Len() gives you the length of a string.
String() gives you a number of characters.

So you may be able to use an Update query to pad a field named F1 with
leading zeros so it is 11 characters long. The expression would be something
like this:
String(11-Len([F1]), "0") & [F1]

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Bruce Lawrence" <BL*****@gmail.comwrote in message
news:11**********************@h48g2000cwc.googlegr oups.com...

Theres a small snag with that. These aren't always going to be
numeric fields. We could have "00012345R1"

The problem is the lady who updated the records in the DieData table
left the 0's out of it. The openord records come out of our SAP system
and I import those automatically through import specs.

I'm going to need some type of function or whatever to take out the
preceding zeros and then compare the 2 fields.

I'm not having much luck with that part

[Bruce Lawrence]

Sorry for dragging this out but I'm getting confused with which
solution I use or even search for.

They are text fields because of the alphanumeric values put into DWGNO.
DWGNO in the OpenOrd table is automatically filled in with an import.
DWGNO in DieData table is manually updated.

In my database, I ask the user for an order number. That order number
and the DWGNO is looked up in the OpenOrd table. I then take the DWGNO
and try to find it in the DieData table so I can get information from
that table as well.

The challenge is finding the DGWNO in DieData. DieData's is smaller
than OpenOrd's.

I have posted an image of my query online so you can see what I'm
doing.

http://www.c-o-g.com/img/qryprob.jpg

[Smartin]

Bruce Lawrence wrote:

Ok, I'm baffled...

I'm making a query in access 97 between 2 tables. There is a field in
both tables called "DWGNO".

OPENORD has a record with a DWGNO of "00000012345"
DIEDATA has a record with a DWGNO of "12345"

I'm not doing this with VBA right now. I'm doing it through the query
gui.

My query:
Both tables are in the upper section with no links between them.
I have selected 4 fields. Only 3 are told to display results.
The field that is not showing results is where the criteria is entered.
The field is called DieData.DWGNO and the criteria is Like "*" &
[openord]![dwgno]

This shows no query results.
Please help me

?

Like "*" & trim(str([openord]![dwgno])) & "*"

--
Smartin

[Allen Browne]

Bruce, let's identify the problem. It is that some fields have a fixed
number of leading zeros, and others are variable length, and therefore the
fields don't match?

Possible approaches:
A. Use an Update query to change the data so it does match. You can then
create a relationship between the table, and know that they will match for
sure. Use the information in the last post to pad everything with leading
zeros to (say) 11 characters in both tables. To make an update query, use
Update on the Query menu in query design. Then once the data matches, choose
Relationships on the Tools menu to create the relation, and check the box
for Referential Integrity. Problem permanently solved.

B. If the data is constantly being dumped and the bad data being imported
again, the permanent solution doesn't apply. You might solve it by repeading
the criteria under PRODORD on the next line again (the "Or" line in query
design), and adding this to the Or line under DWGNO from OPENORD:
Like "*" & [DieData].[Dwgno]
Now it doesn't matter which of the 2 tables has the leading zeros, the text
should match.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Bruce Lawrence" <BL*****@gmail.comwrote in message
news:11**********************@m73g2000cwd.googlegr oups.com...

Sorry for dragging this out but I'm getting confused with which
solution I use or even search for.

They are text fields because of the alphanumeric values put into DWGNO.
DWGNO in the OpenOrd table is automatically filled in with an import.
DWGNO in DieData table is manually updated.

In my database, I ask the user for an order number. That order number
and the DWGNO is looked up in the OpenOrd table. I then take the DWGNO
and try to find it in the DieData table so I can get information from
that table as well.

The challenge is finding the DGWNO in DieData. DieData's is smaller
than OpenOrd's.

I have posted an image of my query online so you can see what I'm
doing.

http://www.c-o-g.com/img/qryprob.jpg

[Bri]

Bruce Lawrence wrote:

Sorry for dragging this out but I'm getting confused with which
solution I use or even search for.

They are text fields because of the alphanumeric values put into DWGNO.
DWGNO in the OpenOrd table is automatically filled in with an import.
DWGNO in DieData table is manually updated.

In my database, I ask the user for an order number. That order number
and the DWGNO is looked up in the OpenOrd table. I then take the DWGNO
and try to find it in the DieData table so I can get information from
that table as well.

The challenge is finding the DGWNO in DieData. DieData's is smaller
than OpenOrd's.

I have posted an image of my query online so you can see what I'm
doing.

http://www.c-o-g.com/img/qryprob.jpg

You have a decision to make. Either the field with the leading zeros
needs to lose those leading zeros or the other field needs to gain those
leading zeros. Otherwise you are trying to compare things that will
never be the same. I showed you a method for removing the leading zeros
by converting the field to a number, but it seems that this s an
alphanumeric field, so that won't work. Allen Browne showed you a
formula that will add in the leading zeros to the field without them.
His solution involves permanently changing the data in that field. The
problem with that is that if your data entry allows for the leading
zeros to not be entered, then the problem will recreate itself. Putting
the formula into the query will work, but it will be slow.

SELECT Openord.Prodord FROM Openord, DieData
WHERE openord!dwgno = String(11-Len(DIEDATA!dwgno), "0") & DIEDATA!dwgno

This does make the assumption that the fields are always 11 characters
long, but in your examples you've shown both 10 and 11 character fields.
I'm hoping that one of them was a typo.

--
Bri

[Bruce Lawrence]

Just an update....

The problem has been resolved. The issue was that the specs I was
provided for importing the SAP report .txt file into Access was wrong.
I was importing 5 columns to the left too many which was giving me
preceding 0's in the field.

The access import spec was adjusted and no records come in with 0's any
longer so I can do direct comparisons now.

My apologies for dragging you guys through the mud on something as
silly as this. I did learn though so some good came of it

Thanks again guys