Date Type Question

[Walter Steadman]

Hello all,
I would like to know if anyone can point me to a good place to learn
about date type functions, queries etc... for MS Access.

I am trying to build an update query that is on BirthMonth. In the query I
have placed the following fields from two tables

PilotID (PK, for tblPilot)
BirthMonth (Birthdate in 6/7/2005 format) from tblPilot
Active (YesNo CheckBox) from tblHours
DateFlown (Date field in 6/7/2005 format) from tblHours

I try to get an update query to change all records where the Date is less
than the first day of the month following a persons birthmonth.

I put the following criteria in the Date field

Now()=> DateSerial(Year(Date()),Month("BirthMonth")+1,1)

And in Update to under the ACTIVE field I put FALSE

When I run the query I get the following:
Data type mismatch in query or expression

Because I am not very good with this Date thing, I don't know what to do. I
have changed it to Date() and put the Date() or Now() on the back end. Any
help would be appreciated.

Thanks in Advance

Wally Steadman
US Army in Iraq

 

[Walter Steadman]

One thing I did not add, is that I will run this query when the Database
opens so it will check against todays date.

so if todays date is greater than the last day of the month of the pilots
birth, I want it to change all the records for that pilot equal to or less
than the last day of the birth month to FALSE. Example:

Jones Birthday is on 15 May, so when I open the database today, it will see
Jones Birthday is past and so it will change all records prior to the first
day of the month past Jones birthday to FALSE. These records are no longer
counted so I am chaning their active status to false so they don't show up
in queries on current information.

I hope that was a bit clearer than it sounds to me.

TIA

Wally Steadman
US Army in Iraq

 

[George Nicholson]

Now()=> DateSerial(Year(Date()),Month(DateAdd("m",1,[BirthMonth])),1)

1) Month("BirthMonth") asks VB to return the month of the text string
"BirthMonth". Type mismatch. You want to refer to the date field of that
name, so enclose it in brackets instead.
2) Adding 1 to the month like you are will cause problems with December
birthdates since there is no 13th month. DateSerial will automatically
change the month to January, which is fine, but it will also add 1 to the
year to reflect the "rollover", which is *not* what you want, I'm sure.
Example: DateSerial(Year(Date()),13,1) = 1/1/2006
As you have it, December birthdates would *never* be turned False since they
would *always* evaluate to "next January" (on 1/1/06 your formula would be
comparing Now() to 1/1/07, etc, etc.).

HTH,

 

[Walter Steadman]

George,
Thanks for the help. I started thinking about the whole birthday thing
and realized the years were going to be an issue. I just posted a message
asking about the whole year thing. This date stuff is a bear to me, but I
am learning quickly. If you wouldn't mind, please refer to my post "Date of
Month vs. Now" that I just posted as this is truly where I think I can make
my bread and butter. Again, I appreciate the help

Wally Steadman

Now()=> DateSerial(Year(Date()),Month(DateAdd("m",1,[BirthMonth])),1)

1) Month("BirthMonth") asks VB to return the month of the text string
"BirthMonth". Type mismatch. You want to refer to the date field of that
name, so enclose it in brackets instead.
2) Adding 1 to the month like you are will cause problems with December
birthdates since there is no 13th month. DateSerial will automatically
change the month to January, which is fine, but it will also add 1 to the
year to reflect the "rollover", which is *not* what you want, I'm sure.
Example: DateSerial(Year(Date()),13,1) = 1/1/2006
As you have it, December birthdates would *never* be turned False since
they would *always* evaluate to "next January" (on 1/1/06 your formula
would be comparing Now() to 1/1/07, etc, etc.).

HTH,
--
George Nicholson

Remove 'Junk' from return address.

One thing I did not add, is that I will run this query when the Database
opens so it will check against todays date.

so if todays date is greater than the last day of the month of the pilots
birth, I want it to change all the records for that pilot equal to or
less than the last day of the birth month to FALSE. Example:

Jones Birthday is on 15 May, so when I open the database today, it will
see Jones Birthday is past and so it will change all records prior to the
first day of the month past Jones birthday to FALSE. These records are
no longer counted so I am chaning their active status to false so they
don't show up in queries on current information.

I hope that was a bit clearer than it sounds to me.

TIA

Wally Steadman
US Army in Iraq