& problem...

[NWO]

Hello.

I have four fileds in a view-only database (can't make any chnages to the
table fileds).

First field is F_Name (20 bytes).
Second filed is L_Name (20 bytes).
Thirs field is Full_Name: [L_Name]&", "&[F_Name]
Fourth field is Date_Entered.

Now when I run the query to show all data with no conditions, the query runs
fine and shows proper results. Problem is, when I apply a criteria for
Date_Entered (i.e. 06/30/2009), the comma appears in the same place (the 20th
place) in all of the Full_Name cells. What I want is the format "Last Name,
First Name", which I get without using criteria in the Date field (actually
any criteria in any field causes this to occur, based on testing of another
query). Why is this occuring and how is the problem fixed?

Thank you.

NWO

 

[KARL DEWEY]

Don't use the third field but in your query use this --
FullName: [L_Name] & ", " & [F_Name]

If you still have the problem use this --
FullName: Trim([L_Name]) & ", " & Trim([F_Name])

 

[Clifford Bass]

Hi,

What is the back end database? I would suspect that your "table" is
really a view in something like Oracle. Some databases allow you to define
fixed-length fields that are always that length, with spaces padded out to
the end of the field after any non-space characters. This would be the
difference between a CHAR and a VARCHAR type of column. So, what can you do
about that? How about constructing your own full name calculated column in
the query?

The_Full_Name: [L_Name] & ", " & [F_Name]

Or maybe:

The_Full_Name: Trim([L_Name]) & ", " & Trim([F_Name])

Now, as to why that inclusion of the spaces happens when you use a
criteria? Well, I really cannot say. Sorry :-(

Clifford Bass

 

[NWO]

Actually, I did both of your suggestions. I mistated the 3rd field. The
Full_Name is an expression in the query, not a field in the table (sorry, I
did not make that clear). So given that I did both of your suggesitons, any
other ideas why this is occuring?

NWO

Don't use the third field but in your query use this --
FullName: [L_Name] & ", " & [F_Name]

If you still have the problem use this --
FullName: Trim([L_Name]) & ", " & Trim([F_Name])

--
Build a little, test a little.

Hello.

I have four fileds in a view-only database (can't make any chnages to the
table fileds).

First field is F_Name (20 bytes).
Second filed is L_Name (20 bytes).
Thirs field is Full_Name: [L_Name]&", "&[F_Name]
Fourth field is Date_Entered.

Now when I run the query to show all data with no conditions, the query runs
fine and shows proper results. Problem is, when I apply a criteria for
Date_Entered (i.e. 06/30/2009), the comma appears in the same place (the 20th
place) in all of the Full_Name cells. What I want is the format "Last Name,
First Name", which I get without using criteria in the Date field (actually
any criteria in any field causes this to occur, based on testing of another
query). Why is this occuring and how is the problem fixed?

Thank you.

NWO

 

[Clifford Bass]

Hi,

Try this in a calculated column just to see what you get:

Last_Name_Test: Replace([L_Name], " ", "*")

Clifford Bass

 

[NWO]

Please explain what this does before I run. I'm thinking, though, that there
must be an uderlying reason why this problem is occuring only when criteria
is present.

Thank you.

Hi,

Try this in a calculated column just to see what you get:

Last_Name_Test: Replace([L_Name], " ", "*")

Clifford Bass

Actually, I did both of your suggestions. I mistated the 3rd field. The
Full_Name is an expression in the query, not a field in the table (sorry, I
did not make that clear). So given that I did both of your suggesitons, any
other ideas why this is occuring?

NWO

 

[Clifford Bass]

Hi,

It will expose, visually, where the spaces are in your L_Name column.
Or maybe that there are not any spaces. Actually, change it to:

Last_Name_Test: "[" & Replace([L_Name], " ", "*") & "]"

So if you have a last name of "Jones " it will show as
"[Jones***************]".

Note, that this does not change the data in the database. By adding
the square brackets you will now be able to see if there are other
non-printing/displaying characters stored in the L_Name field. You could try
it while using criteria and while there is no criteria. See if it produces
different results.

The only reason I can think that criteria would change what is
happening is that Access is doing something different in sending the query to
the back end when there is criteria and where there is not. Which gets back
to my other question: What is the back end? SQL Server, Access, Oracle,
something else? The answer to that may supply a clue as to why the problem
is happening.

Clifford Bass

 

[KARL DEWEY]

I think it would be --
Last_Name_Test: Replace([L_Name], " ", "")

It replaces the spaces that are in your field L_Name, but only in the query
results.

If that does not work then you might check and see what is on the right end
of your L_Name like this --
Asc(Right([L_Name], 1))
If the results is 32 then it is a space. If not then check the value in the
ASCII table in the Help.

Then the replace would be --
Last_Name_Test: Replace([L_Name], Chr(xx), "")
with the xx being the value returned by Asc(Right([L_Name], 1)) above.

--
Build a little, test a little.

Please explain what this does before I run. I'm thinking, though, that there
must be an uderlying reason why this problem is occuring only when criteria
is present.

Thank you.

Hi,

Try this in a calculated column just to see what you get:

Last_Name_Test: Replace([L_Name], " ", "*")

Clifford Bass

Actually, I did both of your suggestions. I mistated the 3rd field. The
Full_Name is an expression in the query, not a field in the table (sorry, I
did not make that clear). So given that I did both of your suggesitons, any
other ideas why this is occuring?

NWO

 

[Clifford Bass]

Hi,

Or, in case it is UNICODE, this:

AscW(Right([L_Name], 1))

Clifford Bass

 

[Clifford Bass]

Hi Again,

Hitting that send too fast.

And it would be for UNICODE:

Replace([L_Name], ChrW(xx), "")

Clifford Bass