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
> Automate Date In One Field Based On Other Date, Access 2013    
 
   
tbs
post Aug 24 2017, 01:50 PM
Post#1



Posts: 244
Joined: 12-February 13



Hello -

I have a table with a field that is called Date of Job performed.

I have a query that calls out open jobs. It shows a list of Date of Job performed. I want it to also show the Week Ending Date in the query.

The Week Ending Date is the end of the week (Saturday) date. For example, today is 8/24/17 (in Date of Job), then the Week Ending Date is 8/26/17.

In this query, how do I build this expression?

Thanks.
Go to the top of the page
 
tbs
post Aug 24 2017, 02:09 PM
Post#2



Posts: 244
Joined: 12-February 13



or how do I build a query that would tell me the date on a Saturday from a given date in a table.

Same example:

if a given date (job performed) in a field is 8/24/17, then the date on a Saturday (week end date) is 8/26/17.

8/27/17 means the week end date is 9/2/17.

8/14/17 means the week end date is 8/19/17.
Go to the top of the page
 
DanielPineault
post Aug 24 2017, 02:13 PM
Post#3


UtterAccess VIP
Posts: 5,154
Joined: 30-June 11



How about: http://www.techrepublic.com/blog/microsoft...-next-saturday/

--------------------
Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...
Go to the top of the page
 
tbs
post Aug 24 2017, 02:29 PM
Post#4



Posts: 244
Joined: 12-February 13



Perfect. Works nicely. Thanks Daniel!
Go to the top of the page
 
tbs
post Aug 30 2017, 09:16 AM
Post#5



Posts: 244
Joined: 12-February 13



Ahhh..... It works on my Front End but when I save the file as ACCDE, I get Undefined function in expression when using that "Next Saturday" function in a query.

Error 3085.
Go to the top of the page
 
DanielPineault
post Aug 30 2017, 09:33 AM
Post#6


UtterAccess VIP
Posts: 5,154
Joined: 30-June 11



On the same machine? Running the same version of Access?

--------------------
Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...
Go to the top of the page
 
tbs
post Aug 30 2017, 09:36 AM
Post#7



Posts: 244
Joined: 12-February 13



Yes - the only difference is it is the Front End vs the ACCDE.

I have a theory and I'm going to test something first. Be right back...
Go to the top of the page
 
tbs
post Aug 30 2017, 09:47 AM
Post#8



Posts: 244
Joined: 12-February 13



Yep, it works now.

I got lazy and used a Marco instead of VB Code when creating a command button to trigger a query that contained "Next Saturday" function.

Apparently this function wouldn't work when using a Macro in ACCDE. It did fine as it should when using DoCmd.Openquery "name of query".

I just thought that was interesting that this simple Macro in ACCDE wouldn't work with this function.
This post has been edited by tbs: Aug 30 2017, 09:50 AM
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    23rd September 2017 - 11:36 AM