Current Date in SQL Query

[dungstar]

This seems like a straight forward function but I don't quite know how to call the current date function in the Access SQL query.

SELECT *
FROM table
WHERE sysdate BETWEEN '4/30/02' AND '5/1/03'

(sysdate: the current date function (Oracle current date))

TIA,
D

 

[Pat Hartman]

Date() is the current date function for VB and NOW() is the current date plus the time of day. In VB, literal dates are surrounded with pound signs (#).

Since you are unfamiliar with Access SQL, use the query builder to help you adjust to the slightly different syntax. For example, if you type a date in a criteria cell, the query builder will automatically surround it with pound signs. Be careful though, if you type a column name, it will assume that you are entering a text string and surround it with quotes. You need to specifically surround column names with square brackets when you enter them in the criteria cell.

PS, Seattle is my favorite city in the US. I lived there many years ago when I had a consulting assignment at the Boeing plant in Kent.

 

[dungstar]

I am fairly familiar with Access SQL. I know the date function for VB, but I was just wondering if there is one for Access SQL it self. If not, I guess I'll have to use VBA to support it.

Thanks, Pat.

Yes, Seattle's my favorite city as well, I've been all over to place and this is the place I will always call home. =) How are you liking working at Boeing?

 

[Pat Hartman]

Access SQL uses VB functions rather than the standard functions that other SQL implementations use. It also supports user defined functions so you can write your own functions and use them in queries. This functionality sort of takes the place of stored procedures.

 

[dungstar]

You're right, Pat. This was easy enough. All I had to do was use the Date() function in the where clause.

SELECT *
FROM table
WHERE Date() BETWEEN '4/30/02' AND '5/1/03'

 

[Pat Hartman]

I hope you actually used # to surround the literal dates.

 

[dungstar]

Thanks for correcting my syntax, Pat. I'm actually using form control objects as the date variables.