SQL or VB??

[elhit]

I have pondered the following teaser for some time without getting a really acceptable solution:

Creating a parameter query using SQL for a MS Access (97) Database where I want to use wild cards in the parameter.

E.G. :

WHERE (((tblusage.DATE) Between [Type Start Date] And [Type End Date:]))

SQL does not seem to allow the use of wildcards in a parameter query involving dates.

I.E. If I wanted to search a table of records containing any day in the month of January in any year
Syntax using **/01/**** or ??/01/????

or any combination of static parameters and wildcards.

SQL throws up an error saying the query is too complex and has to be simplified or the syntax in the expression is incorrect.

I have checked through all the possible numeric wildcards that (Access97) SQL allows to be used and I can only find * and ? as legal characters.

My question is can this be done using SQL and if so does anyone know the correct expression?

If not, do I need to go down the VB route (VB for Applications code - MS Access)

Could any one help me with the VB expression to achieve this?




------------------
My best advice?
RTFM.
What?
Read The F***ing Manual.

[QSECOFR]

I'll have to dig up some information when I go back to the office (tomorrow).

------------------
OS/400...At least it's not Microsoft.

[elhit]

Any help will be most graciously received.

http://forums.windrivers.com/cgi-bin/forum/smilies/cwm1.gif

------------------
My best advice?
RTFM.
What?
Read The F***ing Manual.

[QSECOFR]

I was looking at some of my Access projects/materials, and I was having the same problem with another database. The only information I found was the same you have. Make them enter 2 dates. I'll keep looking.

------------------
OS/400...At least it's not Microsoft.

[Archangel_of_Underworld]

I am taking an SQL class right now and I think that you can use % as a wild character.

[elhit]

Sorry Archangel

See my original post:

% can be used as a wildcard character in normal queries but is not accepted for parameter queries.

:confused:

[SubZero]

I figured it out!

in your criteria on the Access query, put in "1/*/*" (including the quotes) and it will work!

Here is the SQL view of the same statement:

SELECT SAMPLES.LogDate
FROM SAMPLES
WHERE (((SAMPLES.LogDate) Like "3/*/*"));


But making the month a paramater, now I'll ahve to play with that.

[elhit]

Originally posted by QSECOFR:
I figured it out!

in your criteria on the Access query, put in "1/*/*" (including the quotes) and it will work!

But making the month a paramater, now I'll ahve to play with that.

Thanks for trying QSECOFR.

Thats my "work around" for the databases I use currently. But the "Like" statement only works for "static" queries that you input the parameters into as you build the query. This syntax and wildcards is not accepted by Access for parameter queries (refer to my original post).

So if you had a table with a date field containing a number of years the only way to query using the Like "*/01/*" syntax is to write a query for every possible date combo which would be a lot of queries to write!! Also you would still not be able to select records for more than one year at a time!

This needs a parameter type of query so that any possible date (or any part of a date) can be queried?

Thanks for your help all the same

;)

[SubZero]

The static paramater works for multiple years. I tested that on a database that I work on. I posted a SQL example for you in the previous post. That one Selected for multiple years.

Originally posted by ELHIT:
Thanks for trying QSECOFR.

Thats my "work around" for the databases I use currently. But the "Like" statement only works for "static" queries that you input the parameters into as you build the query. This syntax and wildcards is not accepted by Access for parameter queries (refer to my original post).

So if you had a table with a date field containing a number of years the only way to query using the Like "*/01/*" syntax is to write a query for every possible date combo which would be a lot of queries to write!! Also you would still not be able to select records for more than one year at a time!

This needs a parameter type of query so that any possible date (or any part of a date) can be queried?

Thanks for your help all the same

;)

[elhit]

Originally posted by QSECOFR:
The static paramater works for multiple years. I tested that on a database that I work on. I posted a SQL example for you in the previous post. That one Selected for multiple years.

Sorry ;) Your absoloutely right - I phrased that badly, the static parameter does work for multiple years. The point I was trying to make (badly!) was that the requirement is to be able to query a table of dates in any possible combo. So the database user could be querying for say any records in January ("*/01/*" - UK Date format) for any year. Or they may want to query for any record for any date in January for a specific year only ("*/01/2001") or they may want to query for records between a range of dates (Like in mey original post).

The variations on the querys they end user may wish to run are too numerous for me to build a query for each possible situation - hence the need for dynamic querying. What I did not say originally and should of done (very remiss of me!!) is that it is not me using the databases and the user does not have any knowledge (nor do they need to) of building queries in Access all they do is input and report via forms.

So as the records grow and more year dates are added the need for more static queries would grow.

So as you quite rightly point out - static queries using the Like "*/*/*" statement do work for multiple years, months, whatever - they are just not feasible to use in this instance when the query parameters are potentially numerous.

Thanks.

[SubZero]

I'll keep hunting.