Bug in query (Microsoft Access)

Bug in query
message from Dana on 1 Jun 2004
Hi, SELECT Forms!form1![form1 subform].Form!CustomerID AS Expr1 but the query displayes it as all kind of signs instead of a number. when i run a create table query, i saw that it gave that field the type "binary". how can i fix that? 2nd question: How can i run an sql query from a VBA code? thanks, dana

david epsom dot com dot au replied to Dana on 2 Jun 2004

It looks like your database is hosed..... Does it look
ok in the textbox? Given that it displays as 'all kinds
of signs', I guess it's no surprising that Access creates
a 'binary' field for the data. What version of Access
are you using? Have you applied all updates and patches?

(david)

"Dana" <anonymous@discussions.microsoft.com> wrote in message
news:16c4001c4482b$575fb460$a501280a@phx.gbl...

anonymous replied to david epsom dot com dot au on 2 Jun 2004

Hi,

It's 2003 ver. and it's the most recent.
what i need to do, is to run an update query, that uses
the value in the text box as an expression. (not a
criteria). for example, the text box in the form shows a
customer id value. i need to run an append query, when
one of the fields im updating is that customer id.
but, it displayes it as signes instead of the number
(id).

can you assist?

thanks

that
code?

Jeff Boyce replied to anonymous on 2 Jun 2004

Any chance that you have table fields that are defined as "lookup" data
types?

Have you made a backup copy and then run Compact/Repair?

Could you post the full text of your SQL statement?

anonymous replied to Jeff Boyce on 2 Jun 2004

INSERT INTO CustomerSelections ( SelectionID, Selected,
CustomerID )
SELECT CustomerSelections.SelectionID,
CustomerSelections.Selected, Forms!Form1![Selections
subform].Form!CustomerID AS Expr1
FROM CustomerSelections
WHERE (((CustomerSelections.CustomerID)=[Forms]![Form1]!
[ID]));

now, im using the customerid text box for a value to be
insert.
i dont have a definition of a lookup (not that i know
of), and i tried also the repair.
i can't seem to solve this problem...

Can you help?

Thanks in advance

as "lookup" data

Jeff Boyce replied to anonymous on 3 Jun 2004

Is there a chance your Form1 doesn't have a control named "ID"? I'll
mention that if the underlying table field and the form control have the
same name, Access might be getting confused.

And if your field name is actually "ID", this may be a reserved word in
Access, causing further confusion. If so, consider changing the table field
to something less ambiguous (?CustomerID).

lind1 replied to Jeff Boyce on 3 Jun 2004

Hi Jeff,

I guess,it's some kind of a bug that can't be
resolved..the name does not seem to be the problem.

can you tell me how can i run the query (sql command)
from the VBA event window. i can assign the control value
to a variable, and use it in that way.

Thanks in advance!

named "ID"? I'll
control have the
reserved word in
changing the table field

Jeff Boyce replied to Dana on 1 Jun 2004

Dana

As I read your SQL statement, you are asking Access to select the value
contained in a form control. But SQL statements are meant to be run against
a set of data. Are you trying to use the value of the text box as a
selection criterion in a query?

Instead of describing 'how' you want to do something, consider posting
'what' you want to accomplish, and see what the 'group readers can offer.
An example might be "I want to see all the rows in my xxxx table that have
the ID that's in my form" ... and I want to show them in another form.

Archived message: Bug in query (Microsoft Access Error Message)