UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Update Query Issue, Access 2016    
 
   
OCM
post Sep 23 2019, 07:27 PM
Post#1



Posts: 177
Joined: 12-September 02
From: Eastern, USA


Greetings,

I have a status drop down with options: open, closed, closed in DEPART, closed in DARFS etc.
I was tasked to update all status = open for a given fiscal year to closed in DARFS so users do not have to update it manually. The DB is split (be/fe), and I backup the DB before change was made. Can you please advise as to how to proceed?

Attached is a description of what I did:

TIA

Regard,

Attached File(s)
Attached File  Update_query_problem.doc ( 38.5K )Number of downloads: 6
 
Go to the top of the page
 
MadPiet
post Sep 23 2019, 07:36 PM
Post#2



Posts: 3,353
Joined: 27-February 09



The table with some sample data (maybe 20 rows?) would be a lot more helpful than a Word doc.

(Oh this is where I hate Access... A create table script, a handful of INSERT statements, and an expected result would be plenty here!)
Go to the top of the page
 
arnelgp
post Sep 24 2019, 04:14 AM
Post#3



Posts: 1,510
Joined: 2-April 09
From: somewhere out there...


in design view of your update query, did you specify
the Datatype of Parameters [start date] and [end date] as
Date with Time?

--------------------
Never stop learning, because life never stops teaching.
Go to the top of the page
 
OCM
post Sep 24 2019, 12:34 PM
Post#4



Posts: 177
Joined: 12-September 02
From: Eastern, USA


Hi, thank you both for the reply post.

Arnelgp,

No, I didn't. Attached is how my query looks (before I clicked "Update"

TIA

Regards,
Attached File(s)
Attached File  Sample_query.png ( 9.79K )Number of downloads: 8
 
Go to the top of the page
 
RJD
post Sep 24 2019, 01:15 PM
Post#5


UtterAccess VIP
Posts: 10,126
Joined: 25-October 10
From: Gulf South USA


Hi: PMFJI, but I think, with the slim amount of information provided and no db to test, we are all in "guess" mode right now. Perhaps you could attach a db (all relevant objects, enough data to show the issue, zipped) for us to look at. And step us though what you do to get the incorrect result, and what the correct result should be. This usually results in an "aha" moment and a solution.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
OCM
post Sep 24 2019, 01:24 PM
Post#6



Posts: 177
Joined: 12-September 02
From: Eastern, USA


okay, let me check w/ my manager if it's okay..

TIA

Regards,
Go to the top of the page
 
arnelgp
post Sep 24 2019, 02:31 PM
Post#7



Posts: 1,510
Joined: 2-April 09
From: somewhere out there...


your critia on REFDT is not correct

you need one colum: REFDT
criteria: >= #7/1/2018#

another column (SHOW is untick): REFDT
criteria <=#6/30/2019#


or if you want only one column for REFDT,
use Between

criteria: Between #7/1/2018# AND #6/30/2019#

--------------------
Never stop learning, because life never stops teaching.
Go to the top of the page
 
RJD
post Sep 24 2019, 04:33 PM
Post#8


UtterAccess VIP
Posts: 10,126
Joined: 25-October 10
From: Gulf South USA


Hmmm ... actually the criteria of >=#7/1/2018# And <=#6/30/2019# is correct. Yes, the "Between ... And" criteria can also be used, but OCM's criteria is correct as well.

See the demo attached.

I think the OP's issue lies elsewhere, and we'll probably need to see a db to discover it ... as requested.

HTH
Joe
Attached File(s)
Attached File  DateRangeCriteria.zip ( 18.21K )Number of downloads: 4
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
OCM
post Oct 3 2019, 03:27 PM
Post#9



Posts: 177
Joined: 12-September 02
From: Eastern, USA


Hi,

Sorry for not following up as I was out of town for training. I discussed w/ my manager about posting a sample db. He decided to update the records manually (since there were only few record). As R3D stated the issue lies elsewhere.
For instance, after I clicked update query, it didn't prompt me:

QUOTE
You are about to update # row(s)
Once you click yes, you can’t use the Undo command to reverse the changes. Are you sure you want to update these records?


I also noticed since we upgraded to Wind 10, few queries that I created in Win 7 were missing.

Regards,
Go to the top of the page
 
RJD
post Oct 3 2019, 09:25 PM
Post#10


UtterAccess VIP
Posts: 10,126
Joined: 25-October 10
From: Gulf South USA


QUOTE
I discussed w/ my manager about posting a sample db. He decided to update the records manually (since there were only few record).

At least you fixed the relevant records for now. However, you may face the same issues again, so it really would be helpful to know the underlying cause for the update not working.

QUOTE
For instance, after I clicked update query, it didn't prompt me:

I assume you meant double-click, in the navigation pane? But, again, without seeing your db this will be very difficult for us to resolve.

QUOTE
I also noticed since we upgraded to Wind 10, few queries that I created in Win 7 were missing.

Hmmm ... I cannot see how this could happen simply be switching OS. The queries are not created in Windows, but in Access, right? Dd you switch Access versions as well? And did you have a db backup to recover?

HTH
Joe


--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
OCM
post Oct 4 2019, 12:21 PM
Post#11



Posts: 177
Joined: 12-September 02
From: Eastern, USA


Joe, thanks for the reply post.
QUOTE
Dd you switch Access versions as well?
No, I did not switch Access version.
QUOTE
And did you have a db backup to recover?
Yes, only couple of queries that I noticed were missing
Not directly related to what I experienced, but I happened to land on the following online:
Issue with an access query that works on a windows 7 machine and not on a windows 10 machine."]Visit My Website

Regards,
Go to the top of the page
 
RJD
post Oct 4 2019, 01:25 PM
Post#12


UtterAccess VIP
Posts: 10,126
Joined: 25-October 10
From: Gulf South USA


Well, I have never seen nor heard of Access queries missing when the OS changes (others may wish to confirm or correct this, but I have switched from Win7 to Win10 on several machines without such issues). I went to the site you posted and what I read into the discussion is that the OS change might have messed with Access References. That could certainly cause problems with SQL commands.

To check this, go to any module in your db (in the mal-performing setup) and select Tools>>References from the ribbon (after trying a failing query) and see if any references are marked Missing. If so, uncheck that ref and go find the current version of that ref from the list and check it. And see if that fixes the problem.

You should also do a compact/repair on the FE and BE as well, first, to see if that helps.

This seems fixable, but it is hard for us to determine that without your db.

Good luck with your tests, and let us know what you find.

HTH
Joe


--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    11th November 2019 - 06:10 PM