Ms-access – Query that compares dates

database-designms accesstable

I'm trying to create a query that shows if students are mature or not, based on their date of birth. However when the query is executed it returns all students as not mature. Any students that are born before 1/10/1971 should be classed as mature.

Shown below is the IIf() statement I have used to show if students are mature.

When executed the table returns the following results

Can anyone give any idea as to why this is happening?

Shown below is the full page of the Query

Best Answer

Most likely because the expression 1/10/1971 is interpreted as a numeric expression, i.e. 1 divided by 10 divided by 1971 which is 0 (with integer division rounding values down). Telling Access that this is a date can be done using quotes (not really recommended) or by using a date function, like this:

IIf([DOB]<DateSerial(1971, 10, 1), "Mature", "Not mature")

The above DateSerial() VBA function will return October 1, 1971. The parameters are (Year, Month, Day).

Also, the following will work (thanks, @ypercube!)

IIf([DOB]<#1971-10-01#, "Mature", "Not mature")

Related Solutions

Ms-access – Microsoft Access constrain view by date in outer query

How about:

SELECT a.orderid,
       a.ocustomerid
FROM   orders AS a
       INNER JOIN (SELECT b.orderid,
                          b.ocustomerid
                          b.opromisedshipdate
                   FROM   orders AS b
                   ) AS c
       ON c.orderid = a.orderid 
WHERE  c.opromisedshipdate = a.opromisedshipdate

Displaying Only the Most Recent Activity of an Item in MS Access

Try this:

    SELECT a.MaxOfShipID,
           a.Serial_number,
           a.[Recent Date],
           a.[Send_Recieve]
    FROM <tablename> a
    INNER JOIN 
    (
    SELECT serial_number,
           MAX([Recent Date]) AS [Recent Date]
    FROM <tablename>
    GROUP BY serial_number
    ) b
         ON a.serial_number = b.serial_number
         AND a.[Recent Date] = b.[Recent Date]

Update:

In your specific case:

SELECT a.MaxOfShipID,
           a.Serial_number,
           a.[Recent Date],
           a.[Send_Recieve]
    FROM tblSend_Recieve a
    INNER JOIN 
    (
    SELECT serial_number,
           MAX([Recent Date]) AS [Recent Date]
    FROM tblSend_Recieve
    GROUP BY serial_number
    ) b
         ON a.serial_number = b.serial_number
         AND a.[Recent Date] = b.[Recent Date]

Best Answer

Related Solutions

Ms-access – Microsoft Access constrain view by date in outer query

Displaying Only the Most Recent Activity of an Item in MS Access

Related Question