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"
news:733e8b1b-3e6b-40ab-a785-d371ecbe7478@v4g2000hsf.googleg roups.com...
> 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?
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"
news:67ad571e-1ce6-41eb-aaa9-a0c7281ef8de@a35g2000prf.google groups.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
On Dec 29, 6:18 am, anthony
> 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
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
>> I have never used this syntax
How do you reference a value on a form when opening a query?
On Dec 29, 7:41 am, anthony
> >> Access guesses that you don't know what you're doing
>
> And it may well be right! LOL
>
> >> I have never used this syntax
>
> How do you reference a value on a form when opening a query?
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
news:c693d6fa-8579-429d-92ac-4460705c8554@q77g2000hsh.google groups.co
m:
> 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.
The .Text property is only available when the control has the focus,
no?
> 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.
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"
wrote:
> The .Text property is only available when the control has the focus,
> no?
I believe so.