473,326 Members | 2,111 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

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?
Dec 28 '07 #1
9 2634
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
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
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
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
Declaring the parameter works. Many thanks
Dec 29 '07 #6
>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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

20
by: | last post by:
If I need to check if a certain value does exist in a field, and return either "yes" or "not" which query would be the most effestive?
1
by: Greg Smith | last post by:
I am trying to write a SQL query that will return a record count to my C# application. I wrote the following query: ALTER PROCEDURE up_justification_duplicate AS SELECT COUNT(*) FROM...
3
by: thisisjim | last post by:
I have a table with a number of fields, one being a date. What I'm looking to do is take a date variable, and then select only one row - that being the one that contains the next date value in...
2
by: Mike | last post by:
I have a SqlDataSource that returns a list of companies and their details by ProductID from a stored procedure. It also returns the name of the product associated with the ProductID as the final...
5
by: wugon.net | last post by:
question: db2 LUW V8 UNION ALL with table function month() have bad query performance Env: db2 LUW V8 + FP14 Problem : We have history data from 2005/01/01 ~ 2007/05/xx in single big...
4
by: Pietro Cerutti | last post by:
Hi group, #include <stdio.h> #include <unistd.h> #include <time.h> int main(void) { time_t t1, t2; char *st1, *st2;
1
by: `Zidane Tribal | last post by:
it would appear that using the command 'crc("data")' from the String::CRC returns incorrect results (although, they are at least consistently incorrect). for example, this script..... ...
2
by: jdrake | last post by:
Hi, I have a large table with a 'datetime' column that has date and time values in it. The data is in this format: 2007-10-02 09:54:00.000 The table is called 'profile' and the column...
7
by: thesti | last post by:
hi, i'm trying to select some record from a table in my msaccess.mdb (it's a 2007 ms access). here's the sql query select * from msNews where title like '*a*' when queried it in ms access, i...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.