DateTime comparison problem when use format() or dateValue()

anniebai

In our database, the publication year and month are saved in different columns, the query is to find out the pubs after 7/1/2006. My following query works but not get the desired dataset, e.g. publications during 10/1/2006 to 12/31/2006 was not listed in the result. I am frustrated, please help! Thanks very much.

Expand|Select|Wrap|Line Numbers

 SELECT Publications.PubYear, Publications.PubMonth, format(mid(PubMonth,1,3) & ' 1,' & PubYear, 'short date') AS Expr2,mid(PubMonth,1,3), IsDate(DateValue(format(mid(PubMonth,1,3) & ' 1,' & PubYear, 'short date')))
 
FROM [select * from publications where PubYear is not null and pubMonth is not null and (pubyear=2006 or pubyear=2007)]. as Publications

where format(mid(PubMonth,1,3) & ' 1,' & PubYear, 'short date') >= #2006-07-01#

PS.
I tried to use DataValue(), but it always gives 'Datatype mismatch..."error (there's no null value, all values seem are date type for me).

Stewart Ross Inverness

Quote:

Originally Posted by anniebai

In our database, the publication year and month are saved in different columns, the query is to find out the pubs after 7/1/2006. ... SELECT Publications.PubYear, Publications.PubMonth, format(mid(PubMonth,1,3) & ' 1,' & PubYear, 'short date') AS Expr2,mid(PubMonth,1,3), IsDate(DateValue(format(mid(PubMonth,1,3) & ' 1,' & PubYear, 'short date')))

FROM [select * from publications where PubYear is not null and pubMonth is not null and (pubyear=2006 or pubyear=2007)]. as Publications
where format(mid(PubMonth,1,3) & ' 1,' & PubYear, 'short date') >= #2006-07-01#

Hi Anniebai. The use of Format is not helping you here. Format returns a string representation of a date, not a date as it is internally stored (as a number). You are also using a subquery, which seems a lot for a simple query.
As you already hold the year and month values as discrete columns within your publication table (assuming these are integers), you could compare these directly with the year and month you need to find:

Expand|Select|Wrap|Line Numbers

Select * from publications where (PubYear >= 2006) AND (PubMonth>=7);

If the PubYear and PubMonth values are text rather than Integers or Longs you can convert them to integers using the CInt function (e.g. where (CInt(PubYear) > 2006)...

If you do need to convert your publication year and month to dates, use DateSerial instead of Format:

Expand|Select|Wrap|Line Numbers

  
Select * from publications where (DateSerial(PubYear, PubMonth, 1) >= #07/01/2006#;

Note that the standard Access format of a date value given inside the hashes is mm/dd/yyyy, not yyyy-mm-dd as in your SQL.

Hope this helps.

-Stewart

NeoPa

You can either convert your columns to a number, a string or a date. As you're using manipulation anyway, I'd go for the number. You need to compare the whole item in one go for best results as there are situations where the month can actually be LESS than the required month (if the year is greater). BTW I'm assuming you're meaning ON OR after 1 July 2006.

Expand|Select|Wrap|Line Numbers

WHERE ([PubYear]*100+[PubMonth])>200606

Stewart Ross Inverness

Oops! Error in my first reply in the line

Expand|Select|Wrap|Line Numbers

Select * from publications where (PubYear >= 2006) AND (PubMonth>=7);

This condition is in error as it selects only those months from July onwards. Neopa's combination of year and month is more elegant!

A corrected version of the SQL statement is

Expand|Select|Wrap|Line Numbers

 Select * from publications where (PubYear > 2006) Or ((PubYear = 2006) AND (PubMonth>=7));
 

Anyway, NeoPa's solution is simpler and more elegant...

-Stewart

NeoPa

Quote:

Originally Posted by Stewart Ross Inverness

...Anyway, NeoPa's solution is simpler and more elegant...

-Stewart

Thanks Stewart. I could hardly ask for a higher compliment :)

DateTime comparison problem when use format() or dateValue()

re: DateTime comparison problem when use format() or dateValue()

re: DateTime comparison problem when use format() or dateValue()

re: DateTime comparison problem when use format() or dateValue()

re: DateTime comparison problem when use format() or dateValue()

Similar Microsoft Access / VBA bytes

/bytes/development

/bytes/it

/bytes/about