I solved weird query thing BUT...

  • Thread starter Thread starter Bill Mitchell
  • Start date Start date
B

Bill Mitchell

I don't know why it works.

When I try to use the value in a control on my form
(based on a numerical field) in the Field Row of my Query
Grid, Access sees only weird Chinese Letters.

However, if I use the Formal syntax, it works:

In other words:

Forms![Main]![ContactID] gives me Chinese Letters.

Format(Forms![Main]![ContactID]) gives me the correct
value of the field.

WEEEEIRD. Anyone know why this is? It's only like this
on numerical fields.
 
Bill Mitchell said:
I don't know why it works.

When I try to use the value in a control on my form
(based on a numerical field) in the Field Row of my Query
Grid, Access sees only weird Chinese Letters.

However, if I use the Formal syntax, it works:

In other words:

Forms![Main]![ContactID] gives me Chinese Letters.

Format(Forms![Main]![ContactID]) gives me the correct
value of the field.

WEEEEIRD. Anyone know why this is? It's only like this
on numerical fields.
Click to expand...

Post the query, Bill. It sounds to me like Access is trying to
interpret a number value as if it were a string, for some reason --
applying the Format function will force the value to be converted to a
string, and thus eliminate the confusion. But I haven't yet been able
to reproduce the problem. What version and SP-level of Access and Jet
are you using?
 
Ok, here's is short little bit of sql that is giving me
the trouble:

INSERT INTO MainInterview ( InterviewClientID )
SELECT [forms]![Main]![ContactID] AS Expr1;

This returns weird symbols and chinese letters.

However, if I go:

INSERT INTO MainInterview ( InterviewClientID )
SELECT Format([forms]![Main]![ContactID]) AS Expr1;

it works great.

I am using MS Access 2003 11.5614.5703

Sounds like some weird corruption in the Jet Database.
Any idea how I can fix it?

Bill


-----Original Message-----
I don't know why it works.

When I try to use the value in a control on my form
(based on a numerical field) in the Field Row of my Query
Grid, Access sees only weird Chinese Letters.

However, if I use the Formal syntax, it works:

In other words:

Forms![Main]![ContactID] gives me Chinese Letters.

Format(Forms![Main]![ContactID]) gives me the correct
value of the field.

WEEEEIRD. Anyone know why this is? It's only like this
on numerical fields.
Click to expand...

Post the query, Bill. It sounds to me like Access is trying to
interpret a number value as if it were a string, for some reason --
applying the Format function will force the value to be converted to a
string, and thus eliminate the confusion. But I haven't yet been able
to reproduce the problem. What version and SP-level of Access and Jet
are you using?

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

(please reply to the newsgroup)


.
Click to expand...
 
Bill Mitchell said:
Ok, here's is short little bit of sql that is giving me
the trouble:

INSERT INTO MainInterview ( InterviewClientID )
SELECT [forms]![Main]![ContactID] AS Expr1;

This returns weird symbols and chinese letters.

However, if I go:

INSERT INTO MainInterview ( InterviewClientID )
SELECT Format([forms]![Main]![ContactID]) AS Expr1;

it works great.

I am using MS Access 2003 11.5614.5703

Sounds like some weird corruption in the Jet Database.
Any idea how I can fix it?
Click to expand...

Hmm, that's pretty much what I tested without seeing the problem, but I
was using Access 2002. I don't have A2K3 installed to test with. Can
you reproduce the problem with other forms and tables? Can you isolate
the problem to a particular form, or a particular table, or a particular
controlsource expression?

Which of the possible formats is your database in? Access 2000? 2002?
2003? I'm talking about the database file format, you understand, not
the version of Access you're using to work with it

If the problem occurs when you use A2002 file format or earlier, do you
want to send me a cut-down copy of your database, containing only the
elements necessary to demonstrate the problem, compacted and then zipped
to less than 1MB in size (preferably much smaller)? I'll have a look at
it, time permitting, and maybe we can pin down whether it's a corruption
issue or a bug in Access 2003. You can send it to the address derived
by removing NO SPAM from the reply address of this message.
 
Back
Top