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: 292
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: 292
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: 6,270
Joined: 30-June 11



How about: http://www.techrepublic.com/blog/microsoft...-next-saturday/
Go to the top of the page
 
tbs
post Aug 24 2017, 02:29 PM
Post#4



Posts: 292
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: 292
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: 6,270
Joined: 30-June 11



On the same machine? Running the same version of Access?
Go to the top of the page
 
tbs
post Aug 30 2017, 09:36 AM
Post#7



Posts: 292
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: 292
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.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    9th December 2018 - 04:34 PM