How to compare dates in a SELECT query

[leifoet]

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.

 

[ChrisHirst]

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.

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

 

[leifoet]

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.

 

[ChrisHirst]

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.

 

[leifoet]

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

 

[ChrisHirst]

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.

 

[leifoet]

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.

 

[ChrisHirst]

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.

 

[guitarzan]

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.

 

[leifoet]

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 ;-)
=> 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

 

[guitarzan]

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

 

[leifoet]

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)

 

[guitarzan]

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