Still Stuck on Alpha Numerics

Hi John,
The MS Access version is Office Pro2003, and I have an XP. I am building
this myself, so I have not used any code or structures from any other project.

The database is stand alone. My computer is not linked either. So far I
have created 3 tables, and one small form to enter user names and
information, and this query to try to group the 5 fields associated with the
numbering system I must use, and be able to sort numerically. Does this
help? Thanks for helping me slog through this.


"John Vinson" wrote:

On Fri, 8 Apr 2005 10:17:07 -0700, HOT FLASH
wrote:

Hi John,
When I enter this “DisplayNum: [FirstAlpha] & ("." + [FirstNumber]) & ("." +
[SecondNumber]) & ("." + [SecondAlpha]) & ("." + [ThirdNumber])
Instead of seeing the following SQl text, it compresses the expression and
removes the spaces.

ok... back to basics here...

What version of Access?
Is this a local Access table, or is it linked from some other data
repository (text, Excel, dBase, SQL/Server,...)?
Are you using a .mdb file or a .adp?

John W. Vinson[MVP]

Hi Ya’ll,
I have been trying different scenarios, and here is what I have come up with.
1) No matter if it is a SELECT, PARMETIZED, UNION or whatever type of query,
the expression does not work as long as the “.� portion of the expression is
included.
2) I already have 1000’s of records. The most that any one field will
contain is:
FirstAlpha = 1, FirstNumber=2, SecondNumber=3; SecondAlpha=1, ThirdAlpha – 2
This make creating an input mask hard, as it will insert the “.� When there
is a null, or in the case of SecondNumber=3, it will insert a “� after the
leading left number and before the middle number.
Try as I might, I cannot find any SQL formatting instructions. If you enter
the expression I was given without the “.�, all of the capitalizations are
reversed.
I have to turn this puppy in today. I do hope you can help me to 1) figure
out the appropriate type of query, and the expression that will ultimately
express the 5 concated (maybe grouped, aggragated?) fields into the proper
format and which will allow sorting as a numeric.
Again, I appreciate all your good thoughts.


"John Vinson" wrote:

On Fri, 8 Apr 2005 09:09:06 -0700, HOT FLASH
wrote:

Hi John, I had a thought. Could it be that the null values are affecting the
query? In which case, would I use a NZ() to replace the nulls with a
zero-length string, and if so how exactly. Thank you!

The + operator should be taking care of the NULLS: ("." + [Alpha])
will return NULL if Alpha is NULL.

I'm really perplexed!

John W. Vinson[MVP]