Wednesday, May 4, 2011

dates / times are stored in Excel and Access as serial numbers.

date and time arithmetic

SELECT Book.Title, Book.PublDate, Book.Copyright, Author.LastName, Publisher.PubName, Date()-60 AS Expr1
FROM Publisher INNER JOIN (Author INNER JOIN Book ON Author.[AuthorID] = Book.[AuthorCode]) ON Publisher.[PUBID] = Book.[PubID]
WHERE Book.PublDate>Date()-60;

we want:
SELECT Book.Title, Book.PublDate, Book.Copyright, Author.LastName, Publisher.PubName, Date()-60 AS Expr1
FROM Publisher INNER JOIN (Author INNER JOIN Book ON Author.[AuthorID] = Book.[AuthorCode]) ON Publisher.[PUBID] = Book.[PubID]
WHERE (((Book.PublDate) Between Date()-60 And Date()));


SELECT Book.Title, Book.EDITION, Author.LastName, Publisher.PubName
FROM Publisher INNER JOIN (Author INNER JOIN Book ON Author.[AuthorID] = Book.[AuthorCode]) ON Publisher.[PUBID] = Book.[PubID]
WHERE (((Book.EDITION) Is Null));

more about wildcards
* match anything, including nothing
? match exactly one character

begins with G
G*
turned it into
Like "G*"

where the title contains Excel
*Excel*

No comments:

Post a Comment