search a table using part of a field

[Maclain]

Afternoon all,

We have a form with a field Drawing No from the Table Job Register

I want to be able to take the first part of the drawing number and display all matching items from the quote details table.

Aesthetics I can sort later.

I've created a query Item History.

My intention is to have a command button on the form Job Register that runs the query qryQuoteHistory displaying matching results.

My issues are as follows:
drawing numbers are sporadic in length and information ie:
kk80
a2-19520
a2-19520 rev a
e8
1128215 issue d

Retrospectively I would have had issue or revision number in a separate field, or better yet have a products table and link the info there. Unfortunately this isn't the case.

Any clues?

 

[BlueIshDan]

Come again?

 

[Maclain]

Haha ok that's a fair comment.

Let's say I have two tables that similar information is entered into.

Quotes & Orders

When a customer sends in an enquiry we usually get a copy of the drawing. so We record all the information relating to the drawing number in the "Drawing No" field. This will usually include a revision or issue number.

At some point we may get an order. Part of the data entry for the orders table is the field "Drawing No". However, we will not always receive an issue number or suffix like at the quotation stage, or the issue number may have changed between quotes and orders.

An example of a range of drawing numbers would be:

kk80
118849/RED
118849/CRYO
118849/600
a2-19520 rev a
e8
1128215 issue d

What I'm trying to do at order entry stage is have a command button take the drawing No field from the form order entry and display a list of matching entries in the quotes table.

Eg order entry would be 118849/red I'd want the search to display all entries in the quotes table that match 118849*. And for "A2-19520 Rev A" the match query would be "A2-19520*"

I've experimented with selecting the first 3 or 4 characters of the search in a pseudo field and trying to use that, but to be honest, I can't get it to work.

Hope this explains the issue a little better.

 

[RainLover]

You must understand what you want but you have failed to portray this in writing.

e.g. We record all the information relating to the drawing number in the "Drawing No" field. This will usually include a revision or issue number.

So you want all the information, or you are currently doing this, placed into ONE only field.

You Have "I've created a query Item History."
You also have " the query qryQuoteHistory "

One is prefixed with qry the other with naught.

I have to agree with BlueIshDan. Come Again?

If you go to the FAQs there is a section on how to ask a question. I suggest you give it a read.

 

[Maclain]

Apologies, I was attempting to simplify the dismal naming of the items within our database.

I'm hoping my follow up response made more sense?

 

[vbaInet]

So you want to extract the number part from a Drawing Number field just entered and use the result to search within the Quotes table?

What field will you be searching against in the Quotes table?

 

[Maclain]

Yes!

where the drawing number entered onto the orders table is in the format :
xxxx xx xx
or
x-xxxxx xx xxx
or
xxxxx/xxx/x xxx x

I need everything before the first space.

the field in quotes is "Drawing No"

 

[RainLover]

Apologies, I was attempting to simplify the dismal naming of the items within our database.

I'm hoping my follow up response made more sense?

One needs all the information in order to solve the problem. If it is so dismal then FIX it before proceeding. You will be happy you did so down the track.

It looks like VBAInet has come to your rescue.

 

[RainLover]

So you want to extract the number part from a Drawing Number field just entered and use the result to search within the Quotes table?

What field will you be searching against in the Quotes table?

How do you do that. I must be reading things upside down.

 

[vbaInet]

I suppose it'll be two steps:

1. Extract the numeric part of the string using a function, i.e. cycle through each character of the string, find a number and stop if the next char isn't a number.
2. Perform a LIKE check using the numeric part.

I wonder if there's any point of this exercise since the order is being entered by someone and s/he can simply enter the numeric part into the textbox for a search to be done!

 

[Maclain]

HI vbaInet.

It's not always going to be done at order entry. It's likely to be done after the initial order entry.

I don't need just the numeric. Ideally I need everything before the initial space.

 Like "*" & Left([Forms]![Job Register and Report Log]![Drawing No],(InStr(1,[Drawing No]," "))-1) & "*"

is the lines on which I'm thinking. But, assuming I have the code correct, wouldn't this still throw an error if there is no space?

 

[vbaInet]

I see. If there's no space it will return all records because Instr() will return 0, Len() will return the empty string "" and the resulting Like expression will translate to:

LIKE "**"

So you want to write a function for this instead.

 

[Maclain]

 Like "*" & Left([Forms]![Job Register and Report Log]![Drawing No],(InStr(1,[Forms]![Job Register and Report Log]![Drawing No]," "))-1) & "*"

gives me what I want, as long as there is a space in the search term. If there is no space it errors on Property not found.

How would I add a handler in to cater for entries without a space?

 

[vbaInet]

I thought I had already hinted in my last post what needed to be done?

 

[Maclain]

Yes you did. But I have to clue where to start

 

[vbaInet]

Well you repeated what you said before which made me to believe you didn't understand my post. If you don't understand, best to ask for guidance rather than repeating yourself.

Since you're not keen on writing functions in VBA you can use the IIF() function to check what the return value of Instr() is then act accordingly. The code will look something like this:

LIKE "*" & IIF[COLOR="red"]([/COLOR]Nz(Instr(), 0)=0, "|", Left([Forms]!...etc, Instr())[COLOR="Red"])[/COLOR] & "*"

Which means, if there's no match it will read:

LIKE *|*

I'm guessing there's no pipe character in your field.

 

[Maclain]

Like "*" & IIf(Nz(InStr(),0)=0,"|",Left([Forms]![Job Register and Report Log]![Drawing No],InStr())) & "*"

This now gives me a "typed incorrectly or too complex" error. It suggests using a variable.

Looking at the code though, am I correct in thinking where a drawing number does not contain a space, it would search for "*|*"?

I'd like to put this into a function, but would needed coaching!

Let me try and break this down and understand it:

The iff statement looks for the Instr function value

Nz(Instr(),0)=0,"|"

This looks to see if there is a null value in the Instr function. I can't see where we give it the information to hunt for in the Instr function. Wouldn't this be written as:

 Nz(Instr(1,[Forms]![Job Register and Report Log]![Drawing No]," "),"|")

Would this not search in ..[Drawing No] for a space, and if it doesn't find one, it will return a null value which the Nz function then replaces with a |?

If this code was changed to:

Nz(Instr(1,[Forms]![Job Register and Report Log]![Drawing No]," "),[Forms]![Job Register and Report Log]![Drawing No])

would it then change the null value to the value of ..[Drawing No]?

I can't get my head around where the argument for the iff statement actually is. Surely the Nz function is taking this away?

So if I use

If 
 Instr(1,[FormInfo]," ")=0 then
 result = [Forms]![Job Register and Report Log]![Drawing No]
 else
 result =  Inst(1,[Forms]![Job Register and Report Log]![Drawing No]," ")-1

The only problem I see with this is Instr doesn't return a 0 if no " " is found?

 

[vbaInet]

Like "*" & IIf(Nz(InStr[COLOR="red"][B]()[/B][/COLOR],0)=0,"|",Left([Forms]![Job Register and Report Log]![Drawing No],InStr[B][COLOR="Red"]()[/COLOR][/B])) & "*"

This now gives me a "typed incorrectly or too complex" error. It suggests using a variable.

I gave you skeleton code for you to fill out the relevant bits. You filled out the Forms! part but didn't complete the Instr() part.

 

[Maclain]

I gathered that in the end. I'm struggling to understand the code, which is annoying me.

Like "*" & IIf(Nz(InStr(1,[Forms]![Job Register and Report Log]![Drawing No]," "),0)=0,"|",Left([Forms]![Job Register and Report Log]![Drawing No],InStr([Forms]![Job Register and Report Log]![Drawing No]," ",-1))) & "*"

This now removes the error when there is no space in the drawing number. But, instead of searching for the drawing number (that doesn't have a space) it searches for |.

Changing it to:

Like "*" & IIf(Nz(InStr(1,[Forms]![Job Register and Report Log]![Drawing No]," "),0)=0,[Forms]![Job Register and Report Log]![Drawing No],Left([Forms]![Job Register and Report Log]![Drawing No],InStr([Forms]![Job Register and Report Log]![Drawing No]," ",-1))) & "*"

Will search for the correct item when there is no space. But now this gives me an error when there is a space in the drawing number. Parameter not found.

Evidently as I'm an incompetent person:

Like "*" & IIf(Nz(InStr(1,[Forms]![Job Register and Report Log]![Drawing No]," "),0)=0,[Forms]![Job Register and Report Log]![Drawing No],Left([Forms]![Job Register and Report Log]![Drawing No],InStr([Forms]![Job Register and Report Log]![Drawing No]," [COLOR=red][COLOR=black]"[/COLOR][COLOR=black],)[/COLOR]-1[/COLOR][COLOR=black]))[/COLOR] & "*"

 

[vbaInet]

IIf(Nz(InStr(1,[Forms]![Job Register and Report Log]![Drawing No]," ")[COLOR="Red"] - 1[/COLOR],0)=0,"|",Left([Forms]![Job Register and Report Log]![Drawing No],InStr([Forms]![Job Register and Report Log]![Drawing No]," ")[COLOR="red"] - 1[/COLOR]))

I've highlighted the parts you missed in red.

It searches for a pipe "|" character (which should be non-existent in your data) if the DrawingNo is blank otherwise it returns the Left()