Date Formatting

[Guest]

I have a need to print the alpha name of a month followed by the year on a
report. After I read a bunch of the threads out here, I thought I had it but
not.

First the month and year are created in a query using an interval week. The
output of the query shows 0607 where 06 is the year and 07 is the month.
I then did a Mid to separate them and only take the month into the next
expression on the query. The output is an alpha month but it does not align
with the calendar at all.
1st espression
DateMo: Mid([PassedDate],3,2) This breaks the Month out just fine.
2nd expression
DateMonth: Format([DateMo],"mmmm") This creates an alpha month but does not
correlate to the calendar.

What is wrong?

Thanks

 

[Pieter Wijnen]

Create a date
for instance
dateserial(left(datepassed,2),mid(datepassed,3,2),1)

HTH

Pieter

 

[John W. Vinson]

I have a need to print the alpha name of a month followed by the year on a
report. After I read a bunch of the threads out here, I thought I had it but
not.

First the month and year are created in a query using an interval week. The
output of the query shows 0607 where 06 is the year and 07 is the month.
I then did a Mid to separate them and only take the month into the next
expression on the query. The output is an alpha month but it does not align
with the calendar at all.
1st espression
DateMo: Mid([PassedDate],3,2) This breaks the Month out just fine.
2nd expression
DateMonth: Format([DateMo],"mmmm") This creates an alpha month but does not
correlate to the calendar.

What is wrong?

Thanks

The Format function is expecting a Date/Time value. The text string "07" is
not a date... <g>

It sounds like you're doing a whole lot of conversions back and forth which
may be unnecessary. If PassedDate is a date/time field, you can simply use it
as the control source for a textbox; set the Format of the textbox to

"mmmm yyyy"

to display August 2007. No intermediate fields, no code, no substringing
needed.

John W. Vinson [MVP]

 

[Guest]

John,

Thanks for the heads up on what it is expecting. Unfortunately, I first
take the reservation date and format it to an interval week/yr by this
expression in a prior query.
Res Week&Year: Format([Res-Date],"yy") & Format(Format([Res-Date],"mm"),"00")

Then after I do matching on the interval date of Res Week&Year, I would like
to display on a report the Alpha information as previous discussed. So how
do I get
Access to recognize this as a Date/Time function?

Thanks again!

I have a need to print the alpha name of a month followed by the year on a
report. After I read a bunch of the threads out here, I thought I had it but
not.

First the month and year are created in a query using an interval week. The
output of the query shows 0607 where 06 is the year and 07 is the month.
I then did a Mid to separate them and only take the month into the next
expression on the query. The output is an alpha month but it does not align
with the calendar at all.
1st espression
DateMo: Mid([PassedDate],3,2) This breaks the Month out just fine.
2nd expression
DateMonth: Format([DateMo],"mmmm") This creates an alpha month but does not
correlate to the calendar.

What is wrong?

Thanks

The Format function is expecting a Date/Time value. The text string "07" is
not a date... <g>

It sounds like you're doing a whole lot of conversions back and forth which
may be unnecessary. If PassedDate is a date/time field, you can simply use it
as the control source for a textbox; set the Format of the textbox to

"mmmm yyyy"

to display August 2007. No intermediate fields, no code, no substringing
needed.

John W. Vinson [MVP]

 

[Guest]

Pieter,

Thank you for the response but I do not understand what you are saying.
Could you decode it for me? Still learning a bunch.

Thanks

Create a date
for instance
dateserial(left(datepassed,2),mid(datepassed,3,2),1)

HTH

Pieter

I have a need to print the alpha name of a month followed by the year on a
report. After I read a bunch of the threads out here, I thought I had it
but
not.

First the month and year are created in a query using an interval week.
The
output of the query shows 0607 where 06 is the year and 07 is the month.
I then did a Mid to separate them and only take the month into the next
expression on the query. The output is an alpha month but it does not
align
with the calendar at all.
1st espression
DateMo: Mid([PassedDate],3,2) This breaks the Month out just fine.
2nd expression
DateMonth: Format([DateMo],"mmmm") This creates an alpha month but does
not
correlate to the calendar.

What is wrong?

Thanks

 

[Pieter Wijnen]

Access Won't understand that you by 06 means the sixth month
you therefore have to provide a valid date for Format to work correctly

Dateserial Takes three parameters (year, month, day)
as you allready have the year & month available you have to add a day, and
as all months have a 1st through 28 any of those will do for your purpose
eg
Dateserial(2007,12,28) will produce the date
2007-12-28 (or whatever dateformat you're using
Format(Dateserial(2007,12,28),"mmmm") will produce
December
& so will Format(Dateserial(2007,12,1),"mmmm")

My example just built on yours ;-)
ie
Date_Yr: left(datepassed,2)
Date_Mo:mid(datepassed,3,2)
Date_Da:1
Date_YrMoDa: DateSerial(Date_Yr,Date_Mo,Date_Da)
MonthName: Format(Date_YrMoDa,'mmmm')
=
Format(DateSerial(left(datepassed,2),mid(datepassed,3,2),1),'mmmm')

HTH

Pieter

PS you can also Use WinAPI to retrieve the monthname, but I'll leave that
for now

Pieter,

Thank you for the response but I do not understand what you are saying.
Could you decode it for me? Still learning a bunch.

Thanks

Create a date
for instance
dateserial(left(datepassed,2),mid(datepassed,3,2),1)

HTH

Pieter

I have a need to print the alpha name of a month followed by the year on
a
report. After I read a bunch of the threads out here, I thought I had
it
but
not.

First the month and year are created in a query using an interval week.
The
output of the query shows 0607 where 06 is the year and 07 is the
month.
I then did a Mid to separate them and only take the month into the next
expression on the query. The output is an alpha month but it does not
align
with the calendar at all.
1st espression
DateMo: Mid([PassedDate],3,2) This breaks the Month out just fine.
2nd expression
DateMonth: Format([DateMo],"mmmm") This creates an alpha month but
does
not
correlate to the calendar.

What is wrong?

Thanks

 

[Guest]

Thanks a Bunch!!!
Have a great day!

Access Won't understand that you by 06 means the sixth month
you therefore have to provide a valid date for Format to work correctly

Dateserial Takes three parameters (year, month, day)
as you allready have the year & month available you have to add a day, and
as all months have a 1st through 28 any of those will do for your purpose
eg
Dateserial(2007,12,28) will produce the date
2007-12-28 (or whatever dateformat you're using
Format(Dateserial(2007,12,28),"mmmm") will produce
December
& so will Format(Dateserial(2007,12,1),"mmmm")

My example just built on yours ;-)
ie
Date_Yr: left(datepassed,2)
Date_Mo:mid(datepassed,3,2)
Date_Da:1
Date_YrMoDa: DateSerial(Date_Yr,Date_Mo,Date_Da)
MonthName: Format(Date_YrMoDa,'mmmm')
=
Format(DateSerial(left(datepassed,2),mid(datepassed,3,2),1),'mmmm')

HTH

Pieter

PS you can also Use WinAPI to retrieve the monthname, but I'll leave that
for now

Pieter,

Thank you for the response but I do not understand what you are saying.
Could you decode it for me? Still learning a bunch.

Thanks

Create a date
for instance
dateserial(left(datepassed,2),mid(datepassed,3,2),1)

HTH

Pieter

I have a need to print the alpha name of a month followed by the year on
a
report. After I read a bunch of the threads out here, I thought I had
it
but
not.

First the month and year are created in a query using an interval week.
The
output of the query shows 0607 where 06 is the year and 07 is the
month.
I then did a Mid to separate them and only take the month into the next
expression on the query. The output is an alpha month but it does not
align
with the calendar at all.
1st espression
DateMo: Mid([PassedDate],3,2) This breaks the Month out just fine.
2nd expression
DateMonth: Format([DateMo],"mmmm") This creates an alpha month but
does
not
correlate to the calendar.

What is wrong?

Thanks

 

[John W. Vinson]

John,

Thanks for the heads up on what it is expecting. Unfortunately, I first
take the reservation date and format it to an interval week/yr by this
expression in a prior query.
Res Week&Year: Format([Res-Date],"yy") & Format(Format([Res-Date],"mm"),"00")

Why? You're THROWING AWAY DATA unnecessarily.

Note that if you want to do this (and again, it's not necessary) you could use
a single format statement. For month and year, just use
Format([Res-date], "yymm").

But it *is not necessary* to do this for matching purposes. You can use a
range of dates for matching; e.g. to find all the reservations in June 2007,
you could use a query criterion on [Res-date] of

BETWEEN DateSerial([Enter year:], [Enter month number:], 1) AND
DateSerial([Enter year:], [Enter month number:]+1, 0)

This criterion will make use of indexes on the date field so it will run
faster.

Then after I do matching on the interval date of Res Week&Year, I would like
to display on a report the Alpha information as previous discussed. So how
do I get
Access to recognize this as a Date/Time function?

By applying it to a date/time field (Res-Date) rather than converting the date
to a number, the number to a string, the string to another string, the string
to a number...

I can see how you got painted into this corner, but it is *NOT* necessary to
have all these conversions!

John W. Vinson [MVP]

 

[Susan May]

John: I have a different problem.

I have 660 reps who get birthday choclates each month. The birthday field
is date/time field, so when I do Like "5*", I get all the reps with a
birthday in May, but it sorts by the year they were born. The company wants
all reps that were born each month, sorted by the day of the month.

How can I incorporate that into one expression so it sorts only monthly by
the day of the month?

Many thanks.

Susan

John,

Thanks for the heads up on what it is expecting. Unfortunately, I first
take the reservation date and format it to an interval week/yr by this
expression in a prior query.
Res Week&Year: Format([Res-Date],"yy") & Format(Format([Res-Date],"mm"),"00")

Why? You're THROWING AWAY DATA unnecessarily.

Note that if you want to do this (and again, it's not necessary) you could use
a single format statement. For month and year, just use
Format([Res-date], "yymm").

But it *is not necessary* to do this for matching purposes. You can use a
range of dates for matching; e.g. to find all the reservations in June 2007,
you could use a query criterion on [Res-date] of

BETWEEN DateSerial([Enter year:], [Enter month number:], 1) AND
DateSerial([Enter year:], [Enter month number:]+1, 0)

This criterion will make use of indexes on the date field so it will run
faster.

Then after I do matching on the interval date of Res Week&Year, I would like
to display on a report the Alpha information as previous discussed. So how
do I get
Access to recognize this as a Date/Time function?

By applying it to a date/time field (Res-Date) rather than converting the date
to a number, the number to a string, the string to another string, the string
to a number...

I can see how you got painted into this corner, but it is *NOT* necessary to
have all these conversions!

John W. Vinson [MVP]

 

[John Spencer MVP]

Use the following expression to sort by
FORMAT(DOBField,"mmdd")
That will yield dates like 0409 (April 9th) and 1212 (December 12th)

Note that you don't have to display the field in this manner. You can have an
additional calculated field that is
Format(DOBField,"d mmm")
This will return dates that look like 9 Apr and 12 Dec
and display this calculated field.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

John: I have a different problem.

I have 660 reps who get birthday choclates each month. The birthday field
is date/time field, so when I do Like "5*", I get all the reps with a
birthday in May, but it sorts by the year they were born. The company wants
all reps that were born each month, sorted by the day of the month.

How can I incorporate that into one expression so it sorts only monthly by
the day of the month?

Many thanks.

Susan

John,

Thanks for the heads up on what it is expecting. Unfortunately, I first
take the reservation date and format it to an interval week/yr by this
expression in a prior query.
Res Week&Year: Format([Res-Date],"yy") & Format(Format([Res-Date],"mm"),"00")

Why? You're THROWING AWAY DATA unnecessarily.

Note that if you want to do this (and again, it's not necessary) you could use
a single format statement. For month and year, just use
Format([Res-date], "yymm").

But it *is not necessary* to do this for matching purposes. You can use a
range of dates for matching; e.g. to find all the reservations in June 2007,
you could use a query criterion on [Res-date] of

BETWEEN DateSerial([Enter year:], [Enter month number:], 1) AND
DateSerial([Enter year:], [Enter month number:]+1, 0)

This criterion will make use of indexes on the date field so it will run
faster.

Then after I do matching on the interval date of Res Week&Year, I would like
to display on a report the Alpha information as previous discussed. So how
do I get
Access to recognize this as a Date/Time function?

By applying it to a date/time field (Res-Date) rather than converting the date
to a number, the number to a string, the string to another string, the string
to a number...

I can see how you got painted into this corner, but it is *NOT* necessary to
have all these conversions!

John W. Vinson [MVP]

 

[Susan May]

I don't know what I'm doing wrong John, but I put the expression in the
criteria line and it says data type mismatch in criteria expression.

Format("DOBBirthday", "mmdd") is what it shows after I run the query. I
tried the calculated field too and it says "Enter paramater value. In the
field line I have

BirthdaySort: Format([DOBBirthday],"mmdd")

What am I doing wrong?

Use the following expression to sort by
FORMAT(DOBField,"mmdd")
That will yield dates like 0409 (April 9th) and 1212 (December 12th)

Note that you don't have to display the field in this manner. You can have an
additional calculated field that is
Format(DOBField,"d mmm")
This will return dates that look like 9 Apr and 12 Dec
and display this calculated field.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

John: I have a different problem.

I have 660 reps who get birthday choclates each month. The birthday field
is date/time field, so when I do Like "5*", I get all the reps with a
birthday in May, but it sorts by the year they were born. The company wants
all reps that were born each month, sorted by the day of the month.

How can I incorporate that into one expression so it sorts only monthly by
the day of the month?

Many thanks.

Susan

John,

Thanks for the heads up on what it is expecting. Unfortunately, I first
take the reservation date and format it to an interval week/yr by this
expression in a prior query.
Res Week&Year: Format([Res-Date],"yy") & Format(Format([Res-Date],"mm"),"00")
Why? You're THROWING AWAY DATA unnecessarily.

Note that if you want to do this (and again, it's not necessary) you could use
a single format statement. For month and year, just use
Format([Res-date], "yymm").

But it *is not necessary* to do this for matching purposes. You can use a
range of dates for matching; e.g. to find all the reservations in June 2007,
you could use a query criterion on [Res-date] of

BETWEEN DateSerial([Enter year:], [Enter month number:], 1) AND
DateSerial([Enter year:], [Enter month number:]+1, 0)

This criterion will make use of indexes on the date field so it will run
faster.

Then after I do matching on the interval date of Res Week&Year, I would like
to display on a report the Alpha information as previous discussed. So how
do I get
Access to recognize this as a Date/Time function?
By applying it to a date/time field (Res-Date) rather than converting the date
to a number, the number to a string, the string to another string, the string
to a number...

I can see how you got painted into this corner, but it is *NOT* necessary to
have all these conversions!

John W. Vinson [MVP]

 

[John Spencer MVP]

Sorry, I pressed the wrong key.

That expression goes into a Field "cell" not into a criteria "cell". Also
make sure you are using your field name not one I made up for purposes of
illustration.

BirthdaySort: Format([YOUR DOB FIELD HERE],"mmdd")

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I don't know what I'm doing wrong John, but I put the expression in the
criteria line and it says data type mismatch in criteria expression.

Format("DOBBirthday", "mmdd") is what it shows after I run the query. I
tried the calculated field too and it says "Enter paramater value. In the
field line I have

BirthdaySort: Format([DOBBirthday],"mmdd")

What am I doing wrong?

Use the following expression to sort by
FORMAT(DOBField,"mmdd")
That will yield dates like 0409 (April 9th) and 1212 (December 12th)

Note that you don't have to display the field in this manner. You can have an
additional calculated field that is
Format(DOBField,"d mmm")
This will return dates that look like 9 Apr and 12 Dec
and display this calculated field.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

John: I have a different problem.

I have 660 reps who get birthday choclates each month. The birthday field
is date/time field, so when I do Like "5*", I get all the reps with a
birthday in May, but it sorts by the year they were born. The company wants
all reps that were born each month, sorted by the day of the month.

How can I incorporate that into one expression so it sorts only monthly by
the day of the month?

Many thanks.

Susan

:

John,

Thanks for the heads up on what it is expecting. Unfortunately, I first
take the reservation date and format it to an interval week/yr by this
expression in a prior query.
Res Week&Year: Format([Res-Date],"yy") & Format(Format([Res-Date],"mm"),"00")
Why? You're THROWING AWAY DATA unnecessarily.

Note that if you want to do this (and again, it's not necessary) you could use
a single format statement. For month and year, just use
Format([Res-date], "yymm").

But it *is not necessary* to do this for matching purposes. You can use a
range of dates for matching; e.g. to find all the reservations in June 2007,
you could use a query criterion on [Res-date] of

BETWEEN DateSerial([Enter year:], [Enter month number:], 1) AND
DateSerial([Enter year:], [Enter month number:]+1, 0)

This criterion will make use of indexes on the date field so it will run
faster.

Then after I do matching on the interval date of Res Week&Year, I would like
to display on a report the Alpha information as previous discussed. So how
do I get
Access to recognize this as a Date/Time function?
By applying it to a date/time field (Res-Date) rather than converting the date
to a number, the number to a string, the string to another string, the string
to a number...

I can see how you got painted into this corner, but it is *NOT* necessary to
have all these conversions!

John W. Vinson [MVP]

 

[Susan May]

ok, that worked. Now, how do I only get the birthdays for the month of May?
Can I add to this string?

Thanks

Susan

Sorry, I pressed the wrong key.

That expression goes into a Field "cell" not into a criteria "cell". Also
make sure you are using your field name not one I made up for purposes of
illustration.

BirthdaySort: Format([YOUR DOB FIELD HERE],"mmdd")

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I don't know what I'm doing wrong John, but I put the expression in the
criteria line and it says data type mismatch in criteria expression.

Format("DOBBirthday", "mmdd") is what it shows after I run the query. I
tried the calculated field too and it says "Enter paramater value. In the
field line I have

BirthdaySort: Format([DOBBirthday],"mmdd")

What am I doing wrong?

Use the following expression to sort by
FORMAT(DOBField,"mmdd")
That will yield dates like 0409 (April 9th) and 1212 (December 12th)

Note that you don't have to display the field in this manner. You can have an
additional calculated field that is
Format(DOBField,"d mmm")
This will return dates that look like 9 Apr and 12 Dec
and display this calculated field.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Susan May wrote:
John: I have a different problem.

I have 660 reps who get birthday choclates each month. The birthday field
is date/time field, so when I do Like "5*", I get all the reps with a
birthday in May, but it sorts by the year they were born. The company wants
all reps that were born each month, sorted by the day of the month.

How can I incorporate that into one expression so it sorts only monthly by
the day of the month?

Many thanks.

Susan

:

John,

Thanks for the heads up on what it is expecting. Unfortunately, I first
take the reservation date and format it to an interval week/yr by this
expression in a prior query.
Res Week&Year: Format([Res-Date],"yy") & Format(Format([Res-Date],"mm"),"00")
Why? You're THROWING AWAY DATA unnecessarily.

Note that if you want to do this (and again, it's not necessary) you could use
a single format statement. For month and year, just use
Format([Res-date], "yymm").

But it *is not necessary* to do this for matching purposes. You can use a
range of dates for matching; e.g. to find all the reservations in June 2007,
you could use a query criterion on [Res-date] of

BETWEEN DateSerial([Enter year:], [Enter month number:], 1) AND
DateSerial([Enter year:], [Enter month number:]+1, 0)

This criterion will make use of indexes on the date field so it will run
faster.

Then after I do matching on the interval date of Res Week&Year, I would like
to display on a report the Alpha information as previous discussed. So how
do I get
Access to recognize this as a Date/Time function?
By applying it to a date/time field (Res-Date) rather than converting the date
to a number, the number to a string, the string to another string, the string
to a number...

I can see how you got painted into this corner, but it is *NOT* necessary to
have all these conversions!

John W. Vinson [MVP]

 

[John Spencer MVP]

You can use criteria against the string

Field: Format([YOUR DOB FIELD HERE],"mmdd")
Criteria: Like "05*"

If you wish you can use a parameter prompt in the criteria.

Criteria: Like Format([Enter month Number],"00") & "*"

IF you use that expression you can enter 1 to 12 and should get the
corresponding month.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

ok, that worked. Now, how do I only get the birthdays for the month of May?
Can I add to this string?

Thanks

Susan

Sorry, I pressed the wrong key.

That expression goes into a Field "cell" not into a criteria "cell". Also
make sure you are using your field name not one I made up for purposes of
illustration.

BirthdaySort: Format([YOUR DOB FIELD HERE],"mmdd")

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I don't know what I'm doing wrong John, but I put the expression in the
criteria line and it says data type mismatch in criteria expression.

Format("DOBBirthday", "mmdd") is what it shows after I run the query. I
tried the calculated field too and it says "Enter paramater value. In the
field line I have

BirthdaySort: Format([DOBBirthday],"mmdd")

What am I doing wrong?

:

Use the following expression to sort by
FORMAT(DOBField,"mmdd")
That will yield dates like 0409 (April 9th) and 1212 (December 12th)

Note that you don't have to display the field in this manner. You can have an
additional calculated field that is
Format(DOBField,"d mmm")
This will return dates that look like 9 Apr and 12 Dec
and display this calculated field.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Susan May wrote:
John: I have a different problem.

I have 660 reps who get birthday choclates each month. The birthday field
is date/time field, so when I do Like "5*", I get all the reps with a
birthday in May, but it sorts by the year they were born. The company wants
all reps that were born each month, sorted by the day of the month.

How can I incorporate that into one expression so it sorts only monthly by
the day of the month?

Many thanks.

Susan

:

John,

Thanks for the heads up on what it is expecting. Unfortunately, I first
take the reservation date and format it to an interval week/yr by this
expression in a prior query.
Res Week&Year: Format([Res-Date],"yy") & Format(Format([Res-Date],"mm"),"00")
Why? You're THROWING AWAY DATA unnecessarily.

Note that if you want to do this (and again, it's not necessary) you could use
a single format statement. For month and year, just use
Format([Res-date], "yymm").

But it *is not necessary* to do this for matching purposes. You can use a
range of dates for matching; e.g. to find all the reservations in June 2007,
you could use a query criterion on [Res-date] of

BETWEEN DateSerial([Enter year:], [Enter month number:], 1) AND
DateSerial([Enter year:], [Enter month number:]+1, 0)

This criterion will make use of indexes on the date field so it will run
faster.

Then after I do matching on the interval date of Res Week&Year, I would like
to display on a report the Alpha information as previous discussed. So how
do I get
Access to recognize this as a Date/Time function?
By applying it to a date/time field (Res-Date) rather than converting the date
to a number, the number to a string, the string to another string, the string
to a number...

I can see how you got painted into this corner, but it is *NOT* necessary to
have all these conversions!

John W. Vinson [MVP]

 

[Susan May]

Ok, that worked, but one last question. How can I sort these dates
ascending? The whole reason I was doing this was to get these in order by
their birth day in that month. How can you sort a number in a calculated
field. It's just like the real birthday field, there is no order to this
calculated field. In the real b=day field, it sorted by the year that they
were born, not the day.

Thanks John for you help.

Susan

You can use criteria against the string

Field: Format([YOUR DOB FIELD HERE],"mmdd")
Criteria: Like "05*"

If you wish you can use a parameter prompt in the criteria.

Criteria: Like Format([Enter month Number],"00") & "*"

IF you use that expression you can enter 1 to 12 and should get the
corresponding month.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

ok, that worked. Now, how do I only get the birthdays for the month of May?
Can I add to this string?

Thanks

Susan

Sorry, I pressed the wrong key.

That expression goes into a Field "cell" not into a criteria "cell". Also
make sure you are using your field name not one I made up for purposes of
illustration.

BirthdaySort: Format([YOUR DOB FIELD HERE],"mmdd")

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Susan May wrote:
I don't know what I'm doing wrong John, but I put the expression in the
criteria line and it says data type mismatch in criteria expression.

Format("DOBBirthday", "mmdd") is what it shows after I run the query. I
tried the calculated field too and it says "Enter paramater value. In the
field line I have

BirthdaySort: Format([DOBBirthday],"mmdd")

What am I doing wrong?

:

Use the following expression to sort by
FORMAT(DOBField,"mmdd")
That will yield dates like 0409 (April 9th) and 1212 (December 12th)

Note that you don't have to display the field in this manner. You can have an
additional calculated field that is
Format(DOBField,"d mmm")
This will return dates that look like 9 Apr and 12 Dec
and display this calculated field.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Susan May wrote:
John: I have a different problem.

I have 660 reps who get birthday choclates each month. The birthday field
is date/time field, so when I do Like "5*", I get all the reps with a
birthday in May, but it sorts by the year they were born. The company wants
all reps that were born each month, sorted by the day of the month.

How can I incorporate that into one expression so it sorts only monthly by
the day of the month?

Many thanks.

Susan

:

John,

Thanks for the heads up on what it is expecting. Unfortunately, I first
take the reservation date and format it to an interval week/yr by this
expression in a prior query.
Res Week&Year: Format([Res-Date],"yy") & Format(Format([Res-Date],"mm"),"00")
Why? You're THROWING AWAY DATA unnecessarily.

Note that if you want to do this (and again, it's not necessary) you could use
a single format statement. For month and year, just use
Format([Res-date], "yymm").

But it *is not necessary* to do this for matching purposes. You can use a
range of dates for matching; e.g. to find all the reservations in June 2007,
you could use a query criterion on [Res-date] of

BETWEEN DateSerial([Enter year:], [Enter month number:], 1) AND
DateSerial([Enter year:], [Enter month number:]+1, 0)

This criterion will make use of indexes on the date field so it will run
faster.

Then after I do matching on the interval date of Res Week&Year, I would like
to display on a report the Alpha information as previous discussed. So how
do I get
Access to recognize this as a Date/Time function?
By applying it to a date/time field (Res-Date) rather than converting the date
to a number, the number to a string, the string to another string, the string
to a number...

I can see how you got painted into this corner, but it is *NOT* necessary to
have all these conversions!

John W. Vinson [MVP]

 

[Douglas J. Steele]

Just set the Sort row under the field you added.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Ok, that worked, but one last question. How can I sort these dates
ascending? The whole reason I was doing this was to get these in order by
their birth day in that month. How can you sort a number in a calculated
field. It's just like the real birthday field, there is no order to this
calculated field. In the real b=day field, it sorted by the year that
they
were born, not the day.

Thanks John for you help.

Susan

You can use criteria against the string

Field: Format([YOUR DOB FIELD HERE],"mmdd")
Criteria: Like "05*"

If you wish you can use a parameter prompt in the criteria.

Criteria: Like Format([Enter month Number],"00") & "*"

IF you use that expression you can enter 1 to 12 and should get the
corresponding month.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

ok, that worked. Now, how do I only get the birthdays for the month of
May?
Can I add to this string?

Thanks

Susan

:

Sorry, I pressed the wrong key.

That expression goes into a Field "cell" not into a criteria "cell".
Also
make sure you are using your field name not one I made up for purposes
of
illustration.

BirthdaySort: Format([YOUR DOB FIELD HERE],"mmdd")

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Susan May wrote:
I don't know what I'm doing wrong John, but I put the expression in
the
criteria line and it says data type mismatch in criteria expression.

Format("DOBBirthday", "mmdd") is what it shows after I run the query.
I
tried the calculated field too and it says "Enter paramater value.
In the
field line I have

BirthdaySort: Format([DOBBirthday],"mmdd")

What am I doing wrong?

:

Use the following expression to sort by
FORMAT(DOBField,"mmdd")
That will yield dates like 0409 (April 9th) and 1212 (December 12th)

Note that you don't have to display the field in this manner. You
can have an
additional calculated field that is
Format(DOBField,"d mmm")
This will return dates that look like 9 Apr and 12 Dec
and display this calculated field.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Susan May wrote:
John: I have a different problem.

I have 660 reps who get birthday choclates each month. The
birthday field
is date/time field, so when I do Like "5*", I get all the reps with
a
birthday in May, but it sorts by the year they were born. The
company wants
all reps that were born each month, sorted by the day of the month.

How can I incorporate that into one expression so it sorts only
monthly by
the day of the month?

Many thanks.

Susan

:

On Wed, 22 Aug 2007 05:52:03 -0700, Bunky
<[email protected]>
wrote:

John,

Thanks for the heads up on what it is expecting. Unfortunately,
I first
take the reservation date and format it to an interval week/yr by
this
expression in a prior query.
Res Week&Year: Format([Res-Date],"yy") &
Format(Format([Res-Date],"mm"),"00")
Why? You're THROWING AWAY DATA unnecessarily.

Note that if you want to do this (and again, it's not necessary)
you could use
a single format statement. For month and year, just use
Format([Res-date], "yymm").

But it *is not necessary* to do this for matching purposes. You
can use a
range of dates for matching; e.g. to find all the reservations in
June 2007,
you could use a query criterion on [Res-date] of

BETWEEN DateSerial([Enter year:], [Enter month number:], 1) AND
DateSerial([Enter year:], [Enter month number:]+1, 0)

This criterion will make use of indexes on the date field so it
will run
faster.

Then after I do matching on the interval date of Res Week&Year, I
would like
to display on a report the Alpha information as previous
discussed. So how
do I get
Access to recognize this as a Date/Time function?
By applying it to a date/time field (Res-Date) rather than
converting the date
to a number, the number to a string, the string to another string,
the string
to a number...

I can see how you got painted into this corner, but it is *NOT*
necessary to
have all these conversions!

John W. Vinson [MVP]

 

[Susan May]

Hi Doug:

I tried and that doesn't work in this calculated field.

Just set the Sort row under the field you added.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Ok, that worked, but one last question. How can I sort these dates
ascending? The whole reason I was doing this was to get these in order by
their birth day in that month. How can you sort a number in a calculated
field. It's just like the real birthday field, there is no order to this
calculated field. In the real b=day field, it sorted by the year that
they
were born, not the day.

Thanks John for you help.

Susan

You can use criteria against the string

Field: Format([YOUR DOB FIELD HERE],"mmdd")
Criteria: Like "05*"

If you wish you can use a parameter prompt in the criteria.

Criteria: Like Format([Enter month Number],"00") & "*"

IF you use that expression you can enter 1 to 12 and should get the
corresponding month.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Susan May wrote:
ok, that worked. Now, how do I only get the birthdays for the month of
May?
Can I add to this string?

Thanks

Susan

:

Sorry, I pressed the wrong key.

That expression goes into a Field "cell" not into a criteria "cell".
Also
make sure you are using your field name not one I made up for purposes
of
illustration.

BirthdaySort: Format([YOUR DOB FIELD HERE],"mmdd")

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Susan May wrote:
I don't know what I'm doing wrong John, but I put the expression in
the
criteria line and it says data type mismatch in criteria expression.

Format("DOBBirthday", "mmdd") is what it shows after I run the query.
I
tried the calculated field too and it says "Enter paramater value.
In the
field line I have

BirthdaySort: Format([DOBBirthday],"mmdd")

What am I doing wrong?

:

Use the following expression to sort by
FORMAT(DOBField,"mmdd")
That will yield dates like 0409 (April 9th) and 1212 (December 12th)

Note that you don't have to display the field in this manner. You
can have an
additional calculated field that is
Format(DOBField,"d mmm")
This will return dates that look like 9 Apr and 12 Dec
and display this calculated field.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Susan May wrote:
John: I have a different problem.

I have 660 reps who get birthday choclates each month. The
birthday field
is date/time field, so when I do Like "5*", I get all the reps with
a
birthday in May, but it sorts by the year they were born. The
company wants
all reps that were born each month, sorted by the day of the month.

How can I incorporate that into one expression so it sorts only
monthly by
the day of the month?

Many thanks.

Susan

:

On Wed, 22 Aug 2007 05:52:03 -0700, Bunky
<[email protected]>
wrote:

John,

Thanks for the heads up on what it is expecting. Unfortunately,
I first
take the reservation date and format it to an interval week/yr by
this
expression in a prior query.
Res Week&Year: Format([Res-Date],"yy") &
Format(Format([Res-Date],"mm"),"00")
Why? You're THROWING AWAY DATA unnecessarily.

Note that if you want to do this (and again, it's not necessary)
you could use
a single format statement. For month and year, just use
Format([Res-date], "yymm").

But it *is not necessary* to do this for matching purposes. You
can use a
range of dates for matching; e.g. to find all the reservations in
June 2007,
you could use a query criterion on [Res-date] of

BETWEEN DateSerial([Enter year:], [Enter month number:], 1) AND
DateSerial([Enter year:], [Enter month number:]+1, 0)

This criterion will make use of indexes on the date field so it
will run
faster.

Then after I do matching on the interval date of Res Week&Year, I
would like
to display on a report the Alpha information as previous
discussed. So how
do I get
Access to recognize this as a Date/Time function?
By applying it to a date/time field (Res-Date) rather than
converting the date
to a number, the number to a string, the string to another string,
the string
to a number...

I can see how you got painted into this corner, but it is *NOT*
necessary to
have all these conversions!

John W. Vinson [MVP]

 

[John Spencer MVP]

Since the calculated field dates are mmdd format, you can sort by that.

Of course in a report you use the sorting and grouping dialog (view: Sorting
and Grouping) to set up the order of the items displayed.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Susan May]

What I'm doing John is exporting this query to excel and emailing to the
company that sends out gifts to our reps by birth date. So, I'm still trying
to determine what coding I would need in the query to make the dates start
with 05/01 and end with 05/31.

Thanks.

Susan

 

[Susan May]

Hi Raskew:

It says I'm missing an operator down at the bottom - take a look and modify
for me. The problem is after the WHERE.

SELECT
Month([Birthday]) AS MonBirthday
, Day([Birthday]) AS DayBirthday
, tblContacts.LastName
, tblContacts.FirstName
FROM
tblContacts
WHERE
(((Month([Birthday))=[Enter Month]))
ORDER BY
Month([Birthday])
, Day([Birthday]);

Hi -

Try copying/pasting this query SQL to a new query, changing
field/table names as appropriate.

SELECT
Month([DOB]) AS MonDOB
, Day([DOB]) AS DayDOB
, tblClients1.LastName
, tblClients1.FirstName
FROM
tblClients1
WHERE
(((Month([DOB]))=[Enter Month]))
ORDER BY
Month([DOB])
, Day([DOB]);

When run, it'll prompt you for a month. Enter 1 - 12.

HTH - Bob

What I'm doing John is exporting this query to excel and emailing to the
company that sends out gifts to our reps by birth date. So, I'm still trying
to determine what coding I would need in the query to make the dates start
with 05/01 and end with 05/31.

Thanks.

Susan

Since the calculated field dates are mmdd format, you can sort by that.

[quoted text clipped - 14 lines]

Thanks John for you help.