SQL query against Access date field using wildcard(s)

Below is a portion of an sql query.  I am attempting to pull a date from an Access database using wildcards.  The below syntax does not work in my asp sql statement but it works in access.  I know it has something to do with the use of the # sign when retrieving and writing date fields to an Access database.  I just can't seem to get the syntax correct.

HAVING (((Training.TrainingID)>405) AND ((Training.strtDate)>=Now()-20) AND ((Training.strtDate) Not Like "1/14/*"))
ORDER BY Training.strtDate;

I know I could code the page as follows but then I would need to recode the page each year.

HAVING (((Training.TrainingID)>405) AND ((Training.strtDate)>=Now()-20) AND ((Training.strtDate) Not Like #1/14/2005#))
ORDER BY Training.strtDate;

You assistance is greatly appreciated.

elb


bowsereAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sciber_dudeCommented:
You could try this..

HAVING (((Training.TrainingID)>405) AND ((Training.strtDate)>=Now()-20)
    AND (month(Training.strtDate) <>1) AND (day(Training.strtDate) <>14) )
ORDER BY Training.strtDate;
0
bowsereAuthor Commented:
That's close but it excludes everything with a month of 1 and with a day of 14.  I have other dates in January and on the 14th of other months that I don't want to exclude.
0
aprestoCommented:
store the current date in a variable:

dim currdate
currsate = date()

then to get the date and month do this:

...<> 01/14/year(currdate)...


this is saying:

12 january of the current year

it might not work but you might get some use out of this method, store the current daye in a variable and to call a particular day do this:

day(currdate)
month(currdate) etc

might not be of anu use but might help some how, sorry if its a vague one
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

sciber_dudeCommented:
try this...

HAVING (
    ((Training.TrainingID)>405) AND
    ((Training.strtDate)>=Now()-20) AND
    ((month(Training.strtDate) <>1) AND (day(Training.strtDate) <>14))
)
ORDER BY Training.strtDate;
0
sciber_dudeCommented:
Probably this might work..

HAVING (
    ((Training.TrainingID)>405) AND
    ((Training.strtDate)>=Now()-20) NOT
    ((month(Training.strtDate) =1) AND (day(Training.strtDate) =14))
)
ORDER BY Training.strtDate;
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bowsereAuthor Commented:
Very close I added an AND before the NOT and it worked fine.  Thanks for the assistance.

HAVING (
    ((Training.TrainingID)>405) AND
    ((Training.strtDate)>=Now()-20) AND NOT
    ((month(Training.strtDate) =1) AND (day(Training.strtDate) =14))
)
ORDER BY Training.strtDate;
0
sciber_dudeCommented:
An "AND" cost me an A. :((((((((((((((((((((

No worries dude! glad to help. thanks for the points

:) SD
0
bowsereAuthor Commented:
Sorry about that.  I don't post here real often and I'm not very familiar with the grading stuff.  I guess I should read up on that.

elb

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.