Incorrect value returned by query in Access 2007

In Access 2007, why does a query field designed as Term: [Forms]!
[frmProfiles]![cboTerm] return 00:00:00 when cboTerm actually contains
08SP. This works as expected in Access 2003?

It looks as if Access is treating the combo's value as a time, where you
expect it to be a string.

Firstly, make sure you move the focus out of the combo before you run the
query. Its Value may not be updated until you tab out of it.

Next, open the Immediate Window (Ctrl+G) and enter:
? [Forms]![frmProfiles]![cboTerm]
What answer do you get? It this what you expect? If not, examine the
properties of the combo to see what's going on:
- Which one is the Bound Column?
- What is its Column Count?
- From the Column Widths, determine which column gets displayed (i.e. the
first non-zero-width column.) This may not be the same as the bound column.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"anthony" <anthony.marrian@gmail.comwrote in message
news:733e8b1b-3e6b-40ab-a785-d371ecbe7478@v4g2000hsf.googlegroups.com...

I get the correct value from the immediate window. Also, if I actually
run the query (it's an append query), the correct values are added to
the table. But when I view the query to make sure I'm happy with what
is going to be appended, that particular field displays 00:00:00 and,
as you say, Access thinks it's a date because it puts the date thingy
next to the field. In fact it is a text field limited to four
characters. In this instance it doesn't really matter because the
append works but it is confusing at first and it does not happen in
2003

You could try declaring the parameter.

In query design view, choose Parameters on the Design tab of the ribbon.
Access responds by opening a dialog. Enter:
[Forms]![frmProfiles]![cboTerm] Text

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"anthony" <anthony.marrian@gmail.comwrote in message
news:67ad571e-1ce6-41eb-aaa9-a0c7281ef8de@a35g2000prf.googlegroups.com...

On Dec 29, 6:18 am, anthony <anthony.marr...@gmail.comwrote:When you refer to Forms]![frmProfiles]![cboTerm] in a query string
Access guesses that you don't know what you're doing and that you do
not not want to put the Combo Box Control, a Class Object, into the
query, but rather its default property, viz, its Value.
The Value of a Combo Box is of Type Variant. So Access has to guess
again, this time as to Type.

The Combo Box has another property, Text the Type of which is String.
You might want to try Forms]![frmProfiles]![cboTerm].Text to see if
this helps Access deal with "08SP" as you would like. I guess that it
won't.

My experience in reading CDMA is that the syntax Forms]![FormName]!
[ConrtolName] when used in a query causes many problems. I have never
used this syntax, except in replying here.

Declaring the parameter works. Many thanks

>Access guesses that you don't know what you're doing

And it may well be right! LOL
How do you reference a value on a form when opening a query?

On Dec 29, 7:41 am, anthony <anthony.marr...@gmail.comwrote:I build query strings

eg (vairy code)

Dim SQL$

SQL = "UPDATE Hotsters SET Qualifier = 100 WHERE HairColor = " & Chr
$(34) & me.CboHairColor.Column(0) & Chr$(34)

DBEngine(0)(0).Execute SQL.

lyle <lyle.fairfield@gmail.comwrote in
news:c693d6fa-8579-429d-92ac-4460705c8554@q77g2000hsh.googlegroups.co
m:
The .Text property is only available when the control has the focus,
no?
You can take care of the ambiguities by defining the control
reference as a parameter. This takes care of any issues with the
wrong data type.

One has to be careful with this, as the behavior of these references
in queries changed somewhere between A97 and A2K3, which I
discovered after converting an old app last summer that had been
running reliably since 1998. The control references in the queries
evaluated to Null even when they were not Null. Defining them as
parameters took care of the problem (though I actually replaced the
saved queries with SQL executed in code, since they were append
queries -- I wouldn't have done it that way after about 1999, but
back in 1998, I was still a relative novice).

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

On Dec 29, 6:16 pm, "David W. Fenton" <XXXuse...@dfenton.com.invalid>
wrote:
I believe so.