473,320 Members | 1,936 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,320 software developers and data experts.

Query Drops Data

Hi:

I'm running a simple query on an Access Table from VB6.0. The operator can
make several different selections. Based on their selection, a different,
specific SQL needs to be run. So, when the operator says... "Do it"...the
click procedure goes to a Function (based on their selection)...the specific
function builds the correct SQL and returns it...then it sends that query to
the final procedure to run the sql, retrieve the data and put it on my form.
When I run the program, it returns the data in the first three fields, but
the fourth field is empty...there is data in that field in the table...but
it is not returned. I've checked the SQL that is being passed and it is
being passed correctly.

When I put a "MsgBox SQL" to check out the SQL (after the SQL is developed
and before it is actually run in the final procedure) the SQL is correct and
all of the data is printed on the form (that is when the program pauses and
waits for the operator to click on the message box). When there is no pause
for a message box, the data is not returned...when them message box is
there, the data is returned correctly.

CAn anyone tell me what the heck is happening? And how I might get around
this without having the operator read and close a message box?

Any assistance or suggestion would be helpful.

JP
Mar 25 '06 #1
4 4481

"Joe-Paul" <Ha*********@comcast.net> wrote in message
news:Sd******************************@comcast.com. ..
Hi:

I'm running a simple query on an Access Table from VB6.0. The operator can make several different selections. Based on their selection, a different,
specific SQL needs to be run. So, when the operator says... "Do it"...the
click procedure goes to a Function (based on their selection)...the specific function builds the correct SQL and returns it...then it sends that query to the final procedure to run the sql, retrieve the data and put it on my form. When I run the program, it returns the data in the first three fields, but
the fourth field is empty...there is data in that field in the table...but
it is not returned. I've checked the SQL that is being passed and it is
being passed correctly.

When I put a "MsgBox SQL" to check out the SQL (after the SQL is developed
and before it is actually run in the final procedure) the SQL is correct and all of the data is printed on the form (that is when the program pauses and waits for the operator to click on the message box). When there is no pause for a message box, the data is not returned...when them message box is
there, the data is returned correctly.

CAn anyone tell me what the heck is happening? And how I might get around
this without having the operator read and close a message box?

Any assistance or suggestion would be helpful.

JP

I think we need to see some code.

It is doubtful your SQL is being affected by the msgbox, it has to be
something in how you are attempting to capture or view the value.

Because the returned values are variants, a msgbox can on occasion affect
displayed results by 'fixing' the value to a specific type. But that is
uncommon and just a wild suggestion on my part.

Review how you are capturing the values of that last field. Doing anything
cute?

-ralph
Mar 27 '06 #2
Hi Ralph:

I have data in one table and each record shows the number of trips taken by
each type of vehicle each day, with dispatch times, arrival times, etc. I
want to compare vehicle type 1's "chunk" of data for a given period of time
with the same vehicle's "chunk" of data a year ago. (Compare Jan 1, 2005 to
Feb 1, 2005 with Jan 1, 2006 to Feb 1, 2006). So I "append" the frist set
of data from table one into table two...then update table two with the info
from an SQL getting the corresponding data for the second part of the
comparison. ONce in the second table, I use another SQL to bring the data
into a datagrid. But when I do that, the first time the process runs, the
second amount of data does not show up in the grid... I refresh it, but that
doesn't change anything. If put a "msgbox" in the procedures just before
the grid is loaded...all data shows...without the "msgbox"...the second half
of the data is dropped.

So...yes, I'm doing some funky things with the data as it is moved to set it
up as I need it for the comparison. Each SQL works right...I've checked
each one in Access..and they work... but when I put them together...the
second part of the data is dropped. (If I try to compare over a three year
period, the first and second year show up...but the third year is
dropped.)..

So... I'll just have to see if I can work around this some way.

Thanks
JP

"Ralph" <nt*************@yahoo.com> wrote in message
news:de******************************@arkansas.net ...

"Joe-Paul" <Ha*********@comcast.net> wrote in message
news:Sd******************************@comcast.com. ..
Hi:

I'm running a simple query on an Access Table from VB6.0. The operator

can
make several different selections. Based on their selection, a
different,
specific SQL needs to be run. So, when the operator says... "Do
it"...the
click procedure goes to a Function (based on their selection)...the

specific
function builds the correct SQL and returns it...then it sends that query

to
the final procedure to run the sql, retrieve the data and put it on my

form.
When I run the program, it returns the data in the first three fields,
but
the fourth field is empty...there is data in that field in the
table...but
it is not returned. I've checked the SQL that is being passed and it is
being passed correctly.

When I put a "MsgBox SQL" to check out the SQL (after the SQL is
developed
and before it is actually run in the final procedure) the SQL is correct

and
all of the data is printed on the form (that is when the program pauses

and
waits for the operator to click on the message box). When there is no

pause
for a message box, the data is not returned...when them message box is
there, the data is returned correctly.

CAn anyone tell me what the heck is happening? And how I might get
around
this without having the operator read and close a message box?

Any assistance or suggestion would be helpful.

JP

I think we need to see some code.

It is doubtful your SQL is being affected by the msgbox, it has to be
something in how you are attempting to capture or view the value.

Because the returned values are variants, a msgbox can on occasion affect
displayed results by 'fixing' the value to a specific type. But that is
uncommon and just a wild suggestion on my part.

Review how you are capturing the values of that last field. Doing anything
cute?

-ralph

Mar 27 '06 #3

"Joe-Paul" <Ha*********@comcast.net> wrote in message
news:DK******************************@comcast.com. ..
Hi Ralph:

So I "append" the frist set of data from table one into table two...then
update table two with the info from an SQL getting the corresponding data for
the second part of the comparison. ONce in the second table, I use another
SQL to bring the data into a datagrid. But when I do that, the first time the
process runs, the second amount of data does not show up in the grid... I
refresh it, but that doesn't change anything. If put a "msgbox" in the
procedures just before the grid is loaded...all data shows...without the
"msgbox"...the second half of the data is dropped.


This is clearly a timing problem, which the msgbox "fixes" by putting in a big
fat delay. It has something to do with the way you are using the grid, not with
SQL or the database. Refreshing does not always mean the same thing as Requery,
and sometimes just means the display is repainted. After you are done with all
data modifications, be certain that you are re-running the query that pulls the
data into the grid.

Mar 28 '06 #4
Thanks Steve:

As I was writing the last note for the board, I realized that all I had to
do was use a cross tab query and that might "fix" the problem...rather than
moving the data to a different table...then querrying it... I tried
it...and, by golly, it worked... KISS has always escaped me (Keep it simple
stupid...) :0)...thanks for taking the time to reply.

JP
"Steve Gerrard" <my********@comcast.net> wrote in message
news:Ga******************************@comcast.com. ..

"Joe-Paul" <Ha*********@comcast.net> wrote in message
news:DK******************************@comcast.com. ..
Hi Ralph:

So I "append" the frist set of data from table one into table
two...then update table two with the info from an SQL getting the
corresponding data for the second part of the comparison. ONce in the
second table, I use another SQL to bring the data into a datagrid. But
when I do that, the first time the process runs, the second amount of
data does not show up in the grid... I refresh it, but that doesn't
change anything. If put a "msgbox" in the procedures just before the
grid is loaded...all data shows...without the "msgbox"...the second half
of the data is dropped.


This is clearly a timing problem, which the msgbox "fixes" by putting in a
big fat delay. It has something to do with the way you are using the grid,
not with SQL or the database. Refreshing does not always mean the same
thing as Requery, and sometimes just means the display is repainted. After
you are done with all data modifications, be certain that you are
re-running the query that pulls the data into the grid.

Mar 31 '06 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

13
by: dogu | last post by:
Noob alert. Code is below. File is saved as a .php. What I'm trying to do: User uses 'select' box drop down list to pick a value. Value ($site) is derived from a db query. This works fine....
3
by: Andrew Mayo | last post by:
There is something very strange going on here. Tested with ADO 2.7 and MSDE/2000. At first, things look quite sensible. You have a simple SQL query, let's say select * from mytab where col1 =...
1
by: Rose | last post by:
I have an ACCESS 2000 database and I have a table with a field setup as a MEMO. I am trying to run a query and append the MEMO field to another table - everytime I run the query - it drops data -...
0
by: Simon Devlin | last post by:
Ok, I realize that this is vague, but I'm stuck :-( I've a particular query that when executed from within asp.net times out (after 30 seconds) If I do it from within the SQL Query Analyzer on...
5
by: laurentc | last post by:
Dear all, I have several tables based on exactly the same fields (Key/Date/Price1/Price2). I do some statistics on the prices. However, as I have many different tables (the tables are...
5
by: DougieC | last post by:
Hello all. First off, Im not a programmer so I am some what stumbling through this. 8 years of social science education never trained me to handle real world problems, such as figuring out how to...
3
by: NCRStinks | last post by:
Hi Every1 Having a slight issue on creating a query to combine 2 queries, this year and last years data. The thing is, I have data for this year for all stores, however on last year I dont for...
0
by: csin | last post by:
I have an Access DB backend for the application I am running, I want to use the built in ability in Access to remove duplicate entries... Say I have table1 with fields field1 field2 and field3,...
11
by: msrobotto | last post by:
Hi All, I am using a query to pull records of all files in a given location. The file location is a dropdown list of possible locations: Pending Approval, Pending Correction, Mailed, Paid. I...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
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...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
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: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
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: 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...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

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.