Wierd Query Behaviour

R

rico

Has anyone ever seen ⪠these little squares appear in a query field before?!!
they only appear after i put a group clause into the query, and replace what
should be there.

Any ideas?

TIA

Rico
 
C

Carol Grismore

Yep; they're carriage control characters -- tabs, usually, or other
unprintable characters.
 
R

rico

Yes, thanks again!

SELECT Jobs.OrderDate, Jobs.Customer, repaireportQ1.JobNo,
repaireportQ1.jobitemno, repaireportQ1.jobitemname, rReportQ.Note AS Report,
rReportQ.Enteredby AS ReportedBy, rReportQ.NoteDate AS ReportedDate,
rCompleteQ.Note AS Complete, rCompleteQ.Enteredby AS CompletedBy,
rCompleteQ.NoteDate AS CompletedDate

FROM ((repaireportQ1 INNER JOIN rReportQ ON (repaireportQ1.jobitemname =
rReportQ.jobitemname) AND (repaireportQ1.jobitemno = rReportQ.jobitemno) AND
(repaireportQ1.JobNo = rReportQ.JobNo)) INNER JOIN rCompleteQ ON
(repaireportQ1.jobitemname = rCompleteQ.jobitemname) AND
(repaireportQ1.jobitemno = rCompleteQ.jobitemno) AND (repaireportQ1.JobNo =
rCompleteQ.JobNo)) INNER JOIN Jobs ON repaireportQ1.JobNo = Jobs.JobNo

GROUP BY Jobs.OrderDate, Jobs.Customer, repaireportQ1.JobNo,
repaireportQ1.jobitemno, repaireportQ1.jobitemname, rReportQ.Note,
rReportQ.Enteredby, rReportQ.NoteDate, rCompleteQ.Note, rCompleteQ.Enteredby,
rCompleteQ.NoteDate
WITH OWNERACCESS OPTION;

All done in design view to be honest:) i get confused in sql when inner
joins are concerned!
 
D

Dirk Goldgar

rico said:
Has anyone ever seen ? these little squares appear in a query field
before?!! they only appear after i put a group clause into the query,
and replace what should be there.

Any ideas?
Click to expand...

Please post the SQL of the query.
 
D

Dirk Goldgar

rico said:
Yes, thanks again!

SELECT Jobs.OrderDate, Jobs.Customer, repaireportQ1.JobNo,
repaireportQ1.jobitemno, repaireportQ1.jobitemname, rReportQ.Note AS
Report, rReportQ.Enteredby AS ReportedBy, rReportQ.NoteDate AS
ReportedDate, rCompleteQ.Note AS Complete, rCompleteQ.Enteredby AS
CompletedBy, rCompleteQ.NoteDate AS CompletedDate

FROM ((repaireportQ1 INNER JOIN rReportQ ON
(repaireportQ1.jobitemname = rReportQ.jobitemname) AND
(repaireportQ1.jobitemno = rReportQ.jobitemno) AND
(repaireportQ1.JobNo = rReportQ.JobNo)) INNER JOIN rCompleteQ ON
(repaireportQ1.jobitemname = rCompleteQ.jobitemname) AND
(repaireportQ1.jobitemno = rCompleteQ.jobitemno) AND
(repaireportQ1.JobNo = rCompleteQ.JobNo)) INNER JOIN Jobs ON
repaireportQ1.JobNo = Jobs.JobNo

GROUP BY Jobs.OrderDate, Jobs.Customer, repaireportQ1.JobNo,
repaireportQ1.jobitemno, repaireportQ1.jobitemname, rReportQ.Note,
rReportQ.Enteredby, rReportQ.NoteDate, rCompleteQ.Note,
rCompleteQ.Enteredby, rCompleteQ.NoteDate
WITH OWNERACCESS OPTION;
Click to expand...

Are the odd characters appearing in memo fields that you're grouping
by -- the "Note" fields, perhaps? If so, see this KB article:

http://support.microsoft.com/default.aspx?scid=kb;en-us;889490
Contents of the Memo field may appear as control characters when
you use the GROUP BY clause on the Memo field in a query in
Microsoft Access
 
R

rico

Thanks Dirk and thanks Carol, problem solved

Dirk Goldgar said:
Are the odd characters appearing in memo fields that you're grouping
by -- the "Note" fields, perhaps? If so, see this KB article:

http://support.microsoft.com/default.aspx?scid=kb;en-us;889490
Contents of the Memo field may appear as control characters when
you use the GROUP BY clause on the Memo field in a query in
Microsoft Access

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
C

Carol Grismore

(The truth is, nearly everyone is confused by joins.)

I'm guessing that the little squares are appearing in the fields you have
named Report and Complete. I'm also guessing that if you wrote a little
query that just looked at those fields all by themselves, you would see those
little squares. The squares don't show up when you just look at the fields
in an Access table.

The bad news is, the squares occur when people hit "Enter" or "Tab" keys
while they're typing information into text or memo fields. There's not much
you can do to make users stop doing that.

You can confirm the little squares' existence in the data by running
something like this:
select * from rReportQ where where InStr([Note],Chr$(13))>0;

Decoding this little query -- the InStr function returns the position of one
string within another, and the Chr$(13) is the ASCII code for a line-feed.
So in the example we are looking for linefeed characters within the Note
field.

Chr$(10) is a carriage return, and I forget what the code for a tab is.
Linefeed, carriage return and tab are the three most common causes of little
squares in text fields.

Use the example query to look for these characters within your tables. You
can at least confirm that they are what I believe them to be, since as I
said, they don't show up when viewing Access tables.

Good luck. Let me know how it goes.
 
Top