Query not working properly using wildcard parameters

[Botfly]

Hello fellow programmers. So, I have been working on a several month long project and a client has discovered this issue with adding up some values. I have figured out the issue but I am unsure why it does not work. Everywhere I look online suggests the exact same solution (to which I already have implemented) but it does not work properly.

So, here's what is going on: A user can submit a purchase order under a job number. Let's say they submit this under job number '123-12345' for a purchase order of $5. Now, the user submits another purchase order a week later for let's say $1 under the same job number, however, since then the job number has changed to '123-12345x'. This is perfectly fine as the job number sometimes slightly changes. There is a form which they can type a job number and it should not care that there is an 'x' after it. When they type in '123-12345', the total it gives them is $5 when it should be $6.

So here is the way the code works. TableX has fields called 'Job Number' and 'Price'. A form (let's call it FormX) has a field to enter a job number, and when they do, they press a button so the total price of every object in TableX with the same job number as they entered above gets its price added up for a sum total. Sometimes the job number can slightly change but still be the same job number. Sometimes a letter gets added to the end so there are slight different spellings of the job number in this case. Upon button press, a query looks at TableX and its field called "Job Number" and compares it to the field in FormX using this code:

Like "*" & [Forms]![FormX]![JobNumber] & "*"

(In this code above, [JobNumber] is the field on FormX that the user enters the job number they want the total purchase order $ amt for that job)



So, (assuming the user enters '123-12345') with using these wildcards, it should not matter if TableX has an object with the job number 123-12345xyz or 123-12345x or 123-12345. However, if there are multiple entries in TableX that have variations of the job number, then it only queries the objects with (in this case) the simpler spelling.

Finally, everything about adding up the total Price works just fine. Everything about this works well except with the line of code above. I was under the impression that using "*" would tell the query to not care what comes before/after the "JobNumber" string. However, this is clearly not the case here. I have tried everything I can think of and used different syntax with no luck. Perhaps someone understands what I am doing wrong here?:confused: Any help would be greatly appreciated.

[ranman256]

like "*" & [Forms]![formX]![JobNumber] & "*"

works just fine in a query.
But since we cannot see your sql, we cannot debug.

[Botfly]

I have taken out all irrelevant SQL and heres all of it that affects it:

SELECT [Purchase Orders Table].[Job Number]
FROM [Purchase Orders Table]
WHERE ((([Purchase Orders Table].[Job Number]) Like "*" & [Forms]![FormX]![JobNumber] & "*"));

[ranman256]

I tried it, it works.

[June7]

Me too.

If you want to provide db for analysis, follow instructions at bottom of my post.

[Botfly]

Alright since 2 of you said it worked for you, I figured the difference for why mine doesnt work is something I am overlooking that I think wouldnt affect it. I analyzed everything I have in my query that I didnt mention here. I had a one-to-one relationship between 2 different tables that both had a Job Number field. I do not remember my logic behind doing this. Anyways, I deleted the relationship between those 2 and to my surprise that fixed the problem. It seriously is sometimes the stupid little things that break the code. Thank you June7 and ranman256 for responding so quickly!