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.

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]

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:

 
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:

 
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.

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

[Stewart Ross]

Oops! Error in my first reply in the line

 
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

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

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

-Stewart

[NeoPa]

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

-Stewart

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