current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite
1

I'm working on creating my first "Web App" using Microsoft Access 2013 and publishing it to my company's sharepoint portal. So far it's working excellently, but I can't seem to wrap my brain around what would otherwise be a very simple issue in any other database solution.

I have a list of dates for attempts to get into client's homes for work, and I want to be able to filter the list to just the dates that were from the last day that we actually worked. In theory this is easy, because I could just query all the records where the work date equals the max date less than today, like:

[Work Date] = (
    SELECT MAX([Work Date]) 
    FROM [Work Attempt] 
    WHERE [Work Date] < Today()
)

However, web apps don't seem to support subqueries in their requery where clause. Even using a data macro doesn't seem to support using functions like MAX.

Is there a way to get a Web App to do what I want it to do here? I can very easily get "Yesterday" by putting

[Work Attempt].[Work Date] = DateAdd(Day, -1, Today())

in the requery where clause, but that doesn't help me because we don't work weekends, and the function then becomes useless on a monday (likewise for "tomorrow" on a friday).

share|improve this question
1  
Try using two separate queries for this. You can reference the second query (containing the desired work date) from the first. Or, use a DMAX function to get the work date. –  Robert Harvey♦ Jul 8 '13 at 19:59
    
I don't think that dmax exists in a web app, but I could be wrong. –  Nathan Cox Jul 9 '13 at 4:00
    
Are you using Access queries, or something else? –  Robert Harvey♦ Jul 9 '13 at 4:15
    
@RobertHarvey I'm using Access 2013 to work on a "Custom Web App" which publishes to sharepoint. Everything I have so far is from editing the action controls on a form (e.g., OnClick, OnLoad). I haven't had to create a separate query yet for anything I've done, and it seems like I'm not supposed to have to. I think that you're supposed to be able to use a "Data Macro" for this type of filtering. –  Nathan Cox Jul 9 '13 at 14:42
    
I looked up data macros (I've never used them before), and they look like the moral equivalent of a "trigger" in SQL Server. They run some code in response to some event like a record save or a record delete. I suppose you could populate a form that way, but what I think you really need is a bona fide query. The query substitutes for the table, and performs all of the filtering you need, behind the scenes. –  Robert Harvey♦ Jul 9 '13 at 15:14

1 Answer 1

up vote 1 down vote accepted

I found a solution to this that is acceptable.

I created a query called Previous Scheduled Day which references my Work Attempt table and uses the aggregate function MAX on the Work Date field, with second hidden instance of Work Date used to limit the data to <Today().

I then created a data macro Get Previous Work Day which chains a SetReturnVal to Look Up A Record In (which references the Previous Work Day query with no where clause).

In the OnClick item for my action bar I first run the data macro, setting the local variable to LastWorkDay. I then call RequeryRecords using 

[Work Attempt].[Work Date]=[LastWorkDay]

as my where clause.

Rinse and repeat for "next work day".

Here's what my "Get Previous Work Day" Data Macro looks like: Get Previous Work Day

Here's what my "OnClick" action looks like: OnClick

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.