INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

How to compare dates in a SELECT query

How to compare dates in a SELECT query

(OP)
In a SELECT query I want to compare a date (e.g. 10/02/2017) with the dates from the table 'Activity' by using this code :

SELECT ... FROM ... WHERE Members.membernr>=1 and cdate(Activity.Date)=cdate("10/02/2017") ORDER BY ....

I returns the following error :
Microsoft VBScript compilation error '800a0401'
Expected end of statement

Activity.Date = Date/Time field (short notation)

I think the code in bold is not correct?

Thanks for correction tips.

RE: How to compare dates in a SELECT query

Because you can't simply plug VBScript code into a SQL query as a string and hope it works.

You have to concatenate the return values from the function into the SQL query string.

CODE --> vbscript

<% SQLString = "SELECT ... FROM ... WHERE Members.membernr>=1" & [vbscript functions here] & " [ORDER BY ..... ;" %> 

Chris.

Indifference will be the downfall of mankind, but who cares?
Time flies like an arrow, however, fruit flies like a banana.

Never mind this jesus character, stars had to die for me to live.

RE: How to compare dates in a SELECT query

(OP)
I do not understand this SELECT process with VB script functions - excuse me.

What are 'the return values from the function' ?
Can I get some more explanation about this ?

Thanks for help.

RE: How to compare dates in a SELECT query

Okay;

What DO you know about vbscript and querying databases?

Chris.

Indifference will be the downfall of mankind, but who cares?
Time flies like an arrow, however, fruit flies like a banana.

Never mind this jesus character, stars had to die for me to live.

RE: How to compare dates in a SELECT query

(OP)
The combination vbscript and querying databases was not known to me until today - sorry.
Where can I learn about "& [vbscript functions here]" ?
Thanks for help

RE: How to compare dates in a SELECT query

W3Schools and Dev Guru are a couple of the few sites that still have 'classic' ASP tutorials as far as I know.

These are two of the sites I 'cut my teeth' with for ASP scripting, they both also have SQL tutorials. Plus just about everything you need to know has been covered here in the FAQs and in the this forum threads. The thing to get down to a fine art is string concatenation for SQL queries with particular emphasis on getting the double and single quotes, apostrophes and 'back ticks' in the right order and in the right places.

The backtick by the way is that key at the top left corner of the keyboard under the Esc key usually, and one you have probably never, ever used, but is important for SQL because it is the ` (backtick) not the apostrophe/single quote that you should use as a delimiter for database and table names in queries.

eg. SELECT `field` FROM `table` WHERE `column`=value;

So welcome to the world of ASP scripting. :)

Chris.

Indifference will be the downfall of mankind, but who cares?
Time flies like an arrow, however, fruit flies like a banana.

Never mind this jesus character, stars had to die for me to live.

RE: How to compare dates in a SELECT query

(OP)
Thank you for all this information - in particular, for the ASP-FAQ's.

My problem is still :
all my previous SELECT queries functioned with only SQL-notations (in ASP) => I do not understand why I need to use VBScript in this particular application ?
I therefore do not really know what item I should look in the FAQ => through the forest no longer see the trees (or something like that)

To understand also more the use of VBScript in SELECT queries, I would like to start by learning 'by doing'
=> please is it possible to put the correct VBscripting code in my wrong SELECT query, to see how it's dome ?

Thanks.

RE: How to compare dates in a SELECT query

Quote:

I do not understand why I need to use VBScript in this particular application ?

That makes two of us, because I can only guess at what you need to achieve. If you are simply setting record selection criteria using the values from the database, it can be done in the query using functions that are supported by whatever database you are querying.

However if you are using values that are collected from user input, then you need to concatenate (build) a query from variable or functions.

Chris.

Indifference will be the downfall of mankind, but who cares?
Time flies like an arrow, however, fruit flies like a banana.

Never mind this jesus character, stars had to die for me to live.

RE: How to compare dates in a SELECT query

leifoet: Post the actual code that is not working, and what error you get on which line. It's probably just that "Activity.Date" is incorrect.

RE: How to compare dates in a SELECT query

(OP)
Thanks for explanation - now I begin to understand the SELECT/VBscript process.

Until now, I just want to test if my SELECT query displays only this data from the database that I will see.
(The ultimate goal is to select the date(s) from a drop down menu, but so far I'm not yet)

In this test phase, the date (10/02/2017) is a fixed input - so I can still use the functions that are supported by whatever database; is that correct ?

Back to my initial problem winky smile
=> how can I solve this incorrect syntax or function :
SELECT ... FROM ... WHERE Members.membernr>=1 and cdate(Activity.Date)=cdate("10/02/2017") ORDER BY ....

if I delete the bold section, my query works, but without date filter.

Thanks for tips - Leif

RE: How to compare dates in a SELECT query

I asked for the actual code, you just re-posted a snippet... but anyway... what is Activity.Date supposed to be? Is that syntax correct? What database are you using? Possibly replace cdate("10/02/2017") with cdate(#10/02/2017#)

RE: How to compare dates in a SELECT query

(OP)
Thanks Guitarzan for your practical help tips : field Activity.Date renamed and inserted #

This code works now !
... WHERE Members.membernr>=1 AND Activity.ActDat=cdate(#02/10/2017#) ORDER BY ...

Question : why # and not " - difference ?
Another question : my testdate is 10/02/2017 = DD/MM/YYYY - all dates in my Access database: DD/MM/YYYY = Date/Time short notation - session.LCID=OK
In my working SELECT query it must be : MM/DD/YYYY
Something missing in my code ?

Thanks - Leif


('repost a snippet' => I was answering the previous post => not seen your incoming question at ca the same time)

RE: How to compare dates in a SELECT query

>Question : why # and not " - difference ?
Access / Jet engine uses # to surround dates.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close