By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,504 Members | 2,606 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,504 IT Pros & Developers. It's quick & easy.

Incorrect value returned by query in Access 2007

P: n/a
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?
Dec 28 '07 #1
Share this Question
Share on Google+
9 Replies


P: n/a
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" <an*************@gmail.comwrote in message
news:73**********************************@v4g2000h sf.googlegroups.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?
Dec 29 '07 #2

P: n/a
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
Dec 29 '07 #3

P: n/a
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" <an*************@gmail.comwrote in message
news:67**********************************@a35g2000 prf.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
Dec 29 '07 #4

P: n/a
On Dec 29, 6:18 am, anthony <anthony.marr...@gmail.comwrote:
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.
Dec 29 '07 #5

P: n/a
Declaring the parameter works. Many thanks
Dec 29 '07 #6

P: n/a
>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?
Dec 29 '07 #7

P: n/a
On Dec 29, 7:41 am, anthony <anthony.marr...@gmail.comwrote:
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.
Dec 29 '07 #8

P: n/a
lyle <ly************@gmail.comwrote in
news:c6**********************************@q77g2000 hsh.googlegroups.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/
Dec 29 '07 #9

P: n/a
On Dec 29, 6:16 pm, "David W. Fenton" <XXXuse...@dfenton.com.invalid>
wrote:
The .Text property is only available when the control has the focus,
no?
I believe so.
Dec 29 '07 #10

This discussion thread is closed

Replies have been disabled for this discussion.