[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 320
  • Last Modified:

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


0
bowsere
Asked:
bowsere
  • 4
  • 3
1 Solution
 
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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
 
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now