Re: CAPS and Date/Time formatting

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

.

Relevant Pages

  • Re: mySQL Problem
    ... Don't be an asshole again, ... one of which is that your screen realestate is far less expensive than spending the time it takes to debug non-functioning queries...especially when the query is more complex that what it seems you've written in the course of your experience. ... I said your method of formatting is lousy. ... And in the almost 25 years I've been doing SQL, I can probably count on two hands the number of experienced programmers who write SQL as you did. ...
    (comp.lang.php)
  • Re: Simplest way to create a report to look like a mail merge document
    ... For simple mail merge letters without Word, consider the Query By Form ... You could also send the report to Word for further formatting. ... > called (c/w writing an appropriate data file). ...
    (microsoft.public.access.reports)
  • Re: mySQL Problem
    ... when was i being an asshole at all, jerry? ... queries...especially when the query is more complex that what it seems ... your method of formatting is lousy. ... version of formatting sql v. the way I format mine. ...
    (comp.lang.php)
  • Re: Query is failing in Runtime
    ... I have a query that I call to fill a list box. ... formatted fields - formatting phone from just numbers to include ... strOrder = " ORDER BY LastFirst" ... strOrder = " ORDER BY Address, ...
    (microsoft.public.access.queries)
  • Re: Formatting issue
    ... "Carl Rapson" wrote: ... my query, ... Can you use the UCase function in your query to force upper case? ... Then you won't have to rely on formatting within the table. ...
    (microsoft.public.access.queries)