Comparing different date types in a query

[Fred Barnes]

Hi,

How do you use two different date types in a query?

For example, one table has a date stored as 'dd/mm/yy' but
a second table holds only 'yyyy'

I am trying to build a query that selects records where
the year (and only the year) in the two tables is equal.

Help please!

Thanks

 

[Ken Snell [MVP]]

DateCompare: Year([DDMMYYfield]) = CLng(YYYYfield])

Test for True on the above calculated field.

 

[Rick B]

The date is actually stored as a serial number that represents the date.
The format you mention is only for how the date displays.

You can pull a query and compare the year of a field by using the Datepart
function. The helpfile should give you more details.

Rick B

 

[Guest]

Thanks.

How do I put this into the query design grid?

F.

-----Original Message-----
DateCompare: Year([DDMMYYfield]) = CLng(YYYYfield])

Test for True on the above calculated field.

--

Ken Snell
<MS ACCESS MVP>

Hi,

How do you use two different date types in a query?

For example, one table has a date stored as 'dd/mm/yy' but
a second table holds only 'yyyy'

I am trying to build a query that selects records where
the year (and only the year) in the two tables is equal.

Help please!

Thanks

.

 

[Michel Walsh]

Hi,


You mean the format, or the data. The data can be formatted in many
different ways, the format may differ or look different, but that is just
format, not the data. If the datatype is DateTime, forget about the format.
If one data type is string, you MAY be able to get something working with


SELECT whatever
FROM table1, table2
WHERE YEAR(CDATE(table1.ddmmyy)) = val (table2.yyyy)


I say "may" because if your default date format is yyyy/mm/dd, and you
present "dd/mm/yy" to be interpreted as a date... well, day become read as
year, etc. On the other hand, if ddmmyy field is already a date_time data
type, not a string, then:


WHERE YEAR(table1.ddmmyy) = val (table2.yyyy)


is enough. That assume yyyy is a string, or a number, but not a date. If it
is a date, then, use:

WHERE Year(table1.ddmmyy) = Year( table2.yyyy )


You see, the IMPORTANT point is to relay on the DATA TYPE of the fields
(table design) , NOT ON THE FORMAT they are displayed, at the moment.



Hoping it may help,
Vanderghast, Access MVP

 

[Tonín]

If data type of your YYYY field is integer, then

SELECT Your1stTable.*, Your2ndTable.*
FROM Your1stTable INNER JOIN Your2ndTable ON Year(Your1stTable.DateField) =
Your2ndTable.YearField;


If your YYYY field is a date, then

SELECT Your1stTable.*, Your2ndTable.*
FROM Your1stTable INNER JOIN Your2ndTable ON Year(Your1stTable.DateField) =
Year(Your2ndTable.YearField);


Hope being helpful

Tonín

 

[Ken Snell [MVP]]

Open the query in design view. Paste the expression in the "Field" cell.
Change the generic field names to your real names. Then put True in the
"Criteria" cell.

--

Ken Snell
<MS ACCESS MVP>

Thanks.

How do I put this into the query design grid?

F.

-----Original Message-----
DateCompare: Year([DDMMYYfield]) = CLng(YYYYfield])

Test for True on the above calculated field.

--

Ken Snell
<MS ACCESS MVP>

Hi,

How do you use two different date types in a query?

For example, one table has a date stored as 'dd/mm/yy' but
a second table holds only 'yyyy'

I am trying to build a query that selects records where
the year (and only the year) in the two tables is equal.

Help please!

Thanks

.

 

[Fred Barnes]

Hi,

Thanks very much for this (you are pushing the limits of
my understanding........)

To clarify what I'm trying to do (and taking your advice
to be more specific about the data types being compared.

The first date has a data type of 'date/time' with a
format of 'short date' (dd/mm/yy)

The second 'date' is in fact a number (long integer).

So in essence I'm trying to compare dd/mm/yy (held as
date/time) with yyyy (held as a number).

Is there a way?

Thanks again.

Fred.

 

[Douglas J. Steele]

Michel gave you the answer:

WHERE YEAR(table1.ddmmyy) = VAL(table2.yyyy)

However, since table2.yyyy is now known to be an Integer, and not text, you
can simplify that slightly to

WHERE YEAR(table1.ddmmyy) = table2.yyyy