CAPS and Date/Time formatting

Hello! I'm hoping the discussion group can advise me on a couple of
formatting issues-

I have a State field that is 2 characters with a format of " ". Great for
data entry, but when the State in pulled into a report, the State does not
appear in all capital letters. What is the correct formatting for all caps?

I have a Date/Time field with this formatting:
=(Format$([SchedDate],"Long Date",0,0) & " at " &
(Format$([ShiftStart],"Medium Time")))

which gives me: Sunday, July 08, 2007 at 09:30 AM
How can I format for: Sunday, July 8, 2007 at 9:30 AM, removing the leading
zeros?

Thanks for your assistance!

On Thu, 19 Jul 2007 07:40:05 -0700, Stephanie wrote:

Hello! I'm hoping the discussion group can advise me on a couple of
formatting issues-

I have a State field that is 2 characters with a format of " ". Great for
data entry, but when the State in pulled into a report, the State does not
appear in all capital letters. What is the correct formatting for all caps?

The in the Format property merely displays the data in upper case,
but it is still stored however it was entered.

You can (on the report) use an unbound text control:
=Ucase([State])

A better solution is to have a table of State names and use it as the
rowsource of a Combo Box on your form to select the state. There are
only 50 states, so properly capitalizing each state name once is all
you would need do.
In an Update query:

Update StateTable Set StateTable.StateName = UCase([StateName])

Now you avoid your original problem entirely. Whenever you select the
state in the combo box, it's already in upper case.



I have a Date/Time field with this formatting:
=(Format$([SchedDate],"Long Date",0,0) & " at " &
(Format$([ShiftStart],"Medium Time")))

which gives me: Sunday, July 08, 2007 at 09:30 AM
How can I format for: Sunday, July 8, 2007 at 9:30 AM, removing the leading
zeros?

Thanks for your assistance!

=Format([SchedDate],"dddd, mmmm d, yyyy") & " at " &
Format([ShiftStart],"h:nn AM/PM")

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Fred,
Thanks for the reply. The date/time formatting was perfect!

As for the State in CAPS issue- I do like the idea of having a State table,
except we have international members as well. So a Canadian address City,
State, Zip and Country might be:
OTTAWA ON K1A 0B1
CANANDA

And I admit, sometimes ON is spelled out in the db as Ontario.
Then there is an entry for: Ostroda 14-100 POLAND where Ostroda is the
State.

So, I'm not sure what the standard should be for a City, State, Zip and
Country field. I'd appreciate suggestions. I can certainly update the db to
Uppercase (since you showed me how!).

In my query, I'm using:
[City1] & ", " & [StateOrProvince1] & " " & [PostalCode1] AS Address. I
tried using UCase[StateOrProvince1] but that didnt' work...

Thanks for your help!

"fredg" wrote:

On Thu, 19 Jul 2007 07:40:05 -0700, Stephanie wrote:

Hello! I'm hoping the discussion group can advise me on a couple of
formatting issues-

I have a State field that is 2 characters with a format of " ". Great for
data entry, but when the State in pulled into a report, the State does not
appear in all capital letters. What is the correct formatting for all caps?

The in the Format property merely displays the data in upper case,
but it is still stored however it was entered.

You can (on the report) use an unbound text control:
=Ucase([State])

A better solution is to have a table of State names and use it as the
rowsource of a Combo Box on your form to select the state. There are
only 50 states, so properly capitalizing each state name once is all
you would need do.
In an Update query:

Update StateTable Set StateTable.StateName = UCase([StateName])

Now you avoid your original problem entirely. Whenever you select the
state in the combo box, it's already in upper case.



I have a Date/Time field with this formatting:
=(Format$([SchedDate],"Long Date",0,0) & " at " &
(Format$([ShiftStart],"Medium Time")))

which gives me: Sunday, July 08, 2007 at 09:30 AM
How can I format for: Sunday, July 8, 2007 at 9:30 AM, removing the leading
zeros?

Thanks for your assistance!

=Format([SchedDate],"dddd, mmmm d, yyyy") & " at " &
Format([ShiftStart],"h:nn AM/PM")

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

On Thu, 19 Jul 2007 14:06:06 -0700, Stephanie wrote:

Fred,
Thanks for the reply. The date/time formatting was perfect!

As for the State in CAPS issue- I do like the idea of having a State table,
except we have international members as well. So a Canadian address City,
State, Zip and Country might be:
OTTAWA ON K1A 0B1
CANANDA

And I admit, sometimes ON is spelled out in the db as Ontario.
Then there is an entry for: Ostroda 14-100 POLAND where Ostroda is the
State.

So, I'm not sure what the standard should be for a City, State, Zip and
Country field. I'd appreciate suggestions. I can certainly update the db to
Uppercase (since you showed me how!).

In my query, I'm using:
[City1] & ", " & [StateOrProvince1] & " " & [PostalCode1] AS Address. I
tried using UCase[StateOrProvince1] but that didnt' work...

Thanks for your help!

"fredg" wrote:

On Thu, 19 Jul 2007 07:40:05 -0700, Stephanie wrote:

Hello! I'm hoping the discussion group can advise me on a couple of
formatting issues-

I have a State field that is 2 characters with a format of " ". Great for
data entry, but when the State in pulled into a report, the State does not
appear in all capital letters. What is the correct formatting for all caps?

The in the Format property merely displays the data in upper case,
but it is still stored however it was entered.

You can (on the report) use an unbound text control:
=Ucase([State])

A better solution is to have a table of State names and use it as the
rowsource of a Combo Box on your form to select the state. There are
only 50 states, so properly capitalizing each state name once is all
you would need do.
In an Update query:

Update StateTable Set StateTable.StateName = UCase([StateName])

Now you avoid your original problem entirely. Whenever you select the
state in the combo box, it's already in upper case.


I have a Date/Time field with this formatting:
=(Format$([SchedDate],"Long Date",0,0) & " at " &
(Format$([ShiftStart],"Medium Time")))

which gives me: Sunday, July 08, 2007 at 09:30 AM
How can I format for: Sunday, July 8, 2007 at 9:30 AM, removing the leading
zeros?

Thanks for your assistance!

=Format([SchedDate],"dddd, mmmm d, yyyy") & " at " &
Format([ShiftStart],"h:nn AM/PM")

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail


Regarding: I tried using UCase[StateOrProvince1] but that didnt'
work...
Is that a typo error -- UCase[StateOrProvince1] -- or did you
actually leave off the parenthesis in your code?

[City1] & ", " & UCase([StateOrProvince1]) & " " & [PostalCode1] AS
Address



--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

On Thu, 19 Jul 2007 16:31:36 -0700, fredg wrote:

On Thu, 19 Jul 2007 14:06:06 -0700, Stephanie wrote:

Fred,
Thanks for the reply. The date/time formatting was perfect!

As for the State in CAPS issue- I do like the idea of having a State table,
except we have international members as well. So a Canadian address City,
State, Zip and Country might be:
OTTAWA ON K1A 0B1
CANANDA

And I admit, sometimes ON is spelled out in the db as Ontario.
Then there is an entry for: Ostroda 14-100 POLAND where Ostroda is the
State.

So, I'm not sure what the standard should be for a City, State, Zip and
Country field. I'd appreciate suggestions. I can certainly update the db to
Uppercase (since you showed me how!).

In my query, I'm using:
[City1] & ", " & [StateOrProvince1] & " " & [PostalCode1] AS Address. I
tried using UCase[StateOrProvince1] but that didnt' work...

Thanks for your help!

"fredg" wrote:

On Thu, 19 Jul 2007 07:40:05 -0700, Stephanie wrote:

Hello! I'm hoping the discussion group can advise me on a couple of
formatting issues-

I have a State field that is 2 characters with a format of " ". Great for
data entry, but when the State in pulled into a report, the State does not
appear in all capital letters. What is the correct formatting for all caps?

The in the Format property merely displays the data in upper case,
but it is still stored however it was entered.

You can (on the report) use an unbound text control:
=Ucase([State])

A better solution is to have a table of State names and use it as the
rowsource of a Combo Box on your form to select the state. There are
only 50 states, so properly capitalizing each state name once is all
you would need do.
In an Update query:

Update StateTable Set StateTable.StateName = UCase([StateName])

Now you avoid your original problem entirely. Whenever you select the
state in the combo box, it's already in upper case.


I have a Date/Time field with this formatting:
=(Format$([SchedDate],"Long Date",0,0) & " at " &
(Format$([ShiftStart],"Medium Time")))

which gives me: Sunday, July 08, 2007 at 09:30 AM
How can I format for: Sunday, July 8, 2007 at 9:30 AM, removing the leading
zeros?

Thanks for your assistance!

=Format([SchedDate],"dddd, mmmm d, yyyy") & " at " &
Format([ShiftStart],"h:nn AM/PM")

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail


Regarding: I tried using UCase[StateOrProvince1] but that didnt'
work...
Is that a typo error -- UCase[StateOrProvince1] -- or did you
actually leave off the parenthesis in your code?

[City1] & ", " & UCase([StateOrProvince1]) & " " & [PostalCode1] AS
Address

I meant to add to my previous post...
Using a table of already entered State names (all in upper case) would
prevent inadvertent misspellings. Take a look at your spelling of
"Cananda" above. :-)
It's easy enough to add additional names when needed.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Well, um, gee. Thanks for the reminder! That works well!

"fredg" wrote:
Regarding: I tried using UCase[StateOrProvince1] but that didnt'
work...
Is that a typo error -- UCase[StateOrProvince1] -- or did you
actually leave off the parenthesis in your code?

[City1] & ", " & UCase([StateOrProvince1]) & " " & [PostalCode1] AS
Address



--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

I see your point! A State table might do the trick. Thanks for your time!

"fredg" wrote:


I meant to add to my previous post...
Using a table of already entered State names (all in upper case) would
prevent inadvertent misspellings. Take a look at your spelling of
"Cananda" above. :-)
It's easy enough to add additional names when needed.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Fred,
Just a question about UCase.

In a query where the FROM clause has only one table, I refer to
UCase([StateOrProvince1])
which works well.

In a query where there is a join, the field is now Contacts.StateOrProvince1
and I was unable to force UCase. I must be missing something in the
formatting. Do you have any suggestions? Thanks.

"fredg" wrote:

On Thu, 19 Jul 2007 16:31:36 -0700, fredg wrote:

On Thu, 19 Jul 2007 14:06:06 -0700, Stephanie wrote:

Fred,
Thanks for the reply. The date/time formatting was perfect!

As for the State in CAPS issue- I do like the idea of having a State table,
except we have international members as well. So a Canadian address City,
State, Zip and Country might be:
OTTAWA ON K1A 0B1
CANANDA

And I admit, sometimes ON is spelled out in the db as Ontario.
Then there is an entry for: Ostroda 14-100 POLAND where Ostroda is the
State.

So, I'm not sure what the standard should be for a City, State, Zip and
Country field. I'd appreciate suggestions. I can certainly update the db to
Uppercase (since you showed me how!).

In my query, I'm using:
[City1] & ", " & [StateOrProvince1] & " " & [PostalCode1] AS Address. I
tried using UCase[StateOrProvince1] but that didnt' work...

Thanks for your help!

"fredg" wrote:

On Thu, 19 Jul 2007 07:40:05 -0700, Stephanie wrote:

Hello! I'm hoping the discussion group can advise me on a couple of
formatting issues-

I have a State field that is 2 characters with a format of " ". Great for
data entry, but when the State in pulled into a report, the State does not
appear in all capital letters. What is the correct formatting for all caps?

The in the Format property merely displays the data in upper case,
but it is still stored however it was entered.

You can (on the report) use an unbound text control:
=Ucase([State])

A better solution is to have a table of State names and use it as the
rowsource of a Combo Box on your form to select the state. There are
only 50 states, so properly capitalizing each state name once is all
you would need do.
In an Update query:

Update StateTable Set StateTable.StateName = UCase([StateName])

Now you avoid your original problem entirely. Whenever you select the
state in the combo box, it's already in upper case.


I have a Date/Time field with this formatting:
=(Format$([SchedDate],"Long Date",0,0) & " at " &
(Format$([ShiftStart],"Medium Time")))

which gives me: Sunday, July 08, 2007 at 09:30 AM
How can I format for: Sunday, July 8, 2007 at 9:30 AM, removing the leading
zeros?

Thanks for your assistance!

=Format([SchedDate],"dddd, mmmm d, yyyy") & " at " &
Format([ShiftStart],"h:nn AM/PM")

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail


Regarding: I tried using UCase[StateOrProvince1] but that didnt'
work...
Is that a typo error -- UCase[StateOrProvince1] -- or did you
actually leave off the parenthesis in your code?

[City1] & ", " & UCase([StateOrProvince1]) & " " & [PostalCode1] AS
Address

I meant to add to my previous post...
Using a table of already entered State names (all in upper case) would
prevent inadvertent misspellings. Take a look at your spelling of
"Cananda" above. :-)
It's easy enough to add additional names when needed.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail