Wildcard

[slaga9]

I am trying to run a parameter query (w/ access),
I am having major problems trying to insert the wildcard clause, mainly because my inexpierence with SQL.

Now...my first question is, Do I need to insert a line which will permit null values?
or will just putting in the wild card clause with whatever other parameters the user selects do?

My code is listed below. What do I need to get rid of? and where do I need to install "*" and is there anything else?

Any help is GREATLY appreciated!
thanks for reading,
-Sean


SELECT [Employee_ID].[Account Name], [Employee_ID].[Gen Ledg Acnt No], [Timesheettable1].[JobNumbers], [Employee_ID].[Type], [Employee_ID].[Recovery No], [Employee_ID].[Ref No], [Timesheettable1].[Employee], [Employee_ID].[Rate], [Timesheettable1].[Hours Worked], [Timesheettable1].[Hours Paid]
FROM [Job Number] INNER JOIN (Employee_ID INNER JOIN Timesheettable1 ON [Employee_ID].[Employee]=[Timesheettable1].[Employee]) ON [Job Number].[Job Number]=[Timesheettable1].[JobNumbers]
WHERE ((([Timesheettable1].[JobNumbers])=[Forms]![srchfrm]!jobno) And (([Employee_ID].[Recovery No])=[Forms]![srchfrm]![recovnumber]) And (([Timesheettable1].[Employee])=[Forms]![srchfrm]![employee]) And (([Job Number].[Job Site])=[Forms]![srchfrm]![site]) And (([Timesheettable1].[PayPeriodEnd])=[Forms]![srchfrm]![payday]));

 

[slaga9]

sorry...I posted an older version of the code...
this is the current one, my appologies

SELECT Employee_ID.[Recovery No], Employee_ID.[Account Name], Employee_ID.[Gen Ledg Acnt No], Timesheettable1.JobNumbers, Employee_ID.Type, Employee_ID.unknown, Employee_ID.[Ref No], Timesheettable1.Employee, Employee_ID.Rate, Timesheettable1.[Hours Worked], Timesheettable1.[Hours Paid]
FROM [Job Number] INNER JOIN (Employee_ID INNER JOIN Timesheettable1 ON Employee_ID.Employee = Timesheettable1.Employee) ON [Job Number].[Job Number] = Timesheettable1.JobNumbers
WHERE (((Employee_ID.[Recovery No])=[Forms]![srchfrm]![recovnumber]) AND ((Timesheettable1.JobNumbers)=[Forms]![srchfrm]![jobnumbersrchfrm]) AND ((Timesheettable1.Employee)=[Forms]![srchfrm]![employee]) AND ((Timesheettable1.PayPeriodEnd)=[Forms]![srchfrm]![payday]) AND (([Job Number].[Job Site])=[Forms]![srchfrm]![site]));

thanks again

 

[PHV]

And what is the problem ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[slaga9]

I wish to insert a wild card clause...
Currently the user sees a form, there are several drop down boxes, if they only wish to search by 1 or 2 criteria instead of all of them in order to return results I need some sort of wildcard clause...which I believe is "*" I'm hopeing that you might know where I go about inserting that into the code provided above (on my second post)

also... as you may have guessed I'm not very good with this VB thing, so perhaps I am missing some steps. If this is the case I would really appreciate it if you could put me in the right direction.

thanks
Sean

 

[PHV]

Like this ?
SELECT ...
FROM ...
WHERE (Employee_ID.[Recovery No]=[Forms]![srchfrm]![recovnumber] OR [Forms]![srchfrm]![recovnumber] Is Null)
AND (Timesheettable1.JobNumbers=[Forms]![srchfrm]![jobnumbersrchfrm] OR [Forms]![srchfrm]![jobnumbersrchfrm] Is Null)
AND (Timesheettable1.Employee=[Forms]![srchfrm]![employee] OR [Forms]![srchfrm]![employee] Is Null)
AND (Timesheettable1.PayPeriodEnd=[Forms]![srchfrm]![payday] OR [Forms]![srchfrm]![payday] Is Null)
AND ([Job Number].[Job Site]=[Forms]![srchfrm]![site] OR [Forms]![srchfrm]![site] Is Null);

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[slaga9]

thanks a lot for the response!
I think this is moving towards what I am looking for, however I do have some questions.

I am not certain, but in order for a wildcard search to be permitted...can I have "=" installed?
Do I need to install like a wildcard operator into the code... I am not certain, but I think it may be "*" (with the quotes). It is entirely possible I am wrong, but have you heard of anything like this before? If so, where do you think I should try to install that clause? I was thinking in place of =, but I really do not know. (I am going to try that now)

I should apologize as I am sure I may have some awful nomenclature for terms, I definitely I don’t make it any easier for you experts to help me!!!
However, Thanks so much for the post! The helps is really appreciated.
-Sean

 

[PHV]

Wildcards are meaningful with the Like operator.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[slaga9]

Thanks for the direction PHV, you’ve helped me out a few times before!

but if you have the time, I have more questions for you.

So in the design view of my query named 'paraquery' I have changed my criteria to the like operator format

example.
Like "*" & [Jobsite] & "*"

forgetting that this would make the drop down list on my form irrelevant.
I dont want the users to have to respond to the pop up prompt,
so how can I tie the like operator to the dropdown lists on my search form named 'srchfrm'

the following line is the original command from SQL view for the site field

(([Job Number].[Job Site])=[Forms]![srchfrm]![site]));


If possible could you please show me how to properly tie the like operator into the site combo box?

thanks again, and sorry for all the questions! Your help is always really appreciated!

 

[PHV]

You wanted this ?
(([Job Number].[Job Site]) Like '*' & [Forms]![srchfrm]![site] & '*'));

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886