MS Query

[Martin_King]

Hi all,

I don't know if I'm in the right area for this but I use your site all the time and you manage to answer 99% of my questions. However I've become a bit stuck with setting a parameter in MS Query through excel, and any help would be great.

Basically we query SAGE, and have account codes begining xx-xxx. We use the project ledger which attaches a letter on the end depending on the works, i.e. 00-001A, 00-001B, etc

I need to set a paramater to say any that any that begin/contain (i.e. LIKE %) 00-001 filter out, regardless of the letter.

I know the basics of [paramater] or ?, but the ? doubles as a wildcard in the like criteria. Whenever I try to put a paramter value in it shows nothing. I can get it to work for exact, >< etc but not contains or begins. Even if I play with the SQL, nothing!

Any ideas guys? I'm really at a loose end here??

Thanks for all the help

M

 

[Richard Schollar]

Hi Martin & Welcome to the Board!

The criteria in SQl should look like:

AccountCodeField Like '00-001_'

It is the underscore that functions as a single character wildcard (usually) when using MSQuery. This may depend on what (if any) specific SAGE ODBC driver is being employed though

 

[RoryA]

Try using something like:
WHERE Left(

,6) = ?
and then you can just use your 00-001 as the parameter.

 

[Martin_King]

Thanks for the quick reply.

Richard; I understand about the wildcard, and thats similair to what I am using now to include all the 00-001 and works. What I want is to have a cell where I can enter 00-001, 00-002 etc and it extracts all the info for these codes. At the moment I have to manually edit the query and change the code to 00-002 etc. I can use parameters normally just not in this case

Rorya; can you explain a little more? I have looked at the sql and cant seem to incorporate the Left function.Do you mean WHERE (prtrm.project=LEFT(

,6)=?)
I have tried this and can't seem to get it to work? I may be missing something, I don't know.
 
Thanks for all the help so far!! Fingers crossed

 

[RoryA]

Nope:
WHERE left(prtrm.project,6)=?
should work if I understand you correctly

 

[Martin_King]

Rorya, your a diamond!! Works like a charm. I had know idea you can combine formula with the criteria. This may help solve some minor bugs in my other queries where I have had to use a work around solution.

Thanks for all the time and help all. You can close the thread now (or however it works)

 

[Richard Schollar]

Using Like can offer you a deal of flexibility though - you can set your criteria up as:

WHERE AccountCode Like ?

and then you should get the dialog asking you to fill in the criteria. You just need to remember to use _ as the wildcard for single character

 

[Martin_King]

Richard,

Ive tried combinations of what you suggect but It wont accept the parameter. This is my sql

SELECT prtrm.project, prtrm.expense_code, prtrm.period, prtrm.trans_reference, prtrm.description, prtrm.amount, prtrm.glcode
FROM ffclubs.scheme.prtrm prtrm
WHERE (prtrm.glcode Like '00-001%') AND (prtrm.period<='200903')

This works with 00-001
If i try replacing this with;
'?'
'

'
'[code?]
or 
(prtrm.glcode='Like?') or 'Like ?' i get nothing. It doesn't even ask me to enter anything, just returns blanks. Im adament there is a way to det a like parameter but I've googled and googled to no joy.
 
Rorya's solution is working but if you know where I'm going wrong then it would be a great help as I have many other minor issues that this can help with

 

[RoryA]

WHERE (prtrm.glcode Like ?)
but you would then have to include the % (or underscore) when entering the parameter.

 

[Martin_King]

Rorya

Got it to work! Genius. Ive adapted the cell ref to include the wildcards so the user can just type the code.

Thanks for all the help