Multiple Date Input in Single Query

[monsterlucifer]

Hi,

I have a query setup as under (design view):



SQL code as under:

SELECT [4-Consolidated].Name, Count([4-Consolidated].Date) AS TotalLatesFROM [4-Consolidated]
WHERE ((([4-Consolidated].Status)="Late") AND (([4-Consolidated].Date) Between [Start Date] And [End Date]))
GROUP BY [4-Consolidated].Name;

I went ahead and added a macro for calculating work days in a month as under:

Function CalcWorkdays(StartDate, EndDate) As Integer

Dim LTotalDays As Integer
Dim LSaturdays As Integer
Dim LSundays As Integer



On Error GoTo Err_Execute

CalcWorkdays = 0

If IsDate(StartDate) And IsDate(EndDate) Then
If EndDate <= StartDate Then
CalcWorkdays = 0
Else
LTotalDays = DateDiff("d", StartDate - 1, EndDate)
LSaturdays = DateDiff("ww", StartDate - 1, EndDate, 7)
LSundays = DateDiff("ww", StartDate - 1, EndDate, 1)

'Workdays is the elapsed days excluding Saturdays and Sundays
CalcWorkdays = LTotalDays - LSundays

End If

End If

Exit Function

Err_Execute:
'If error occurs, return 0
CalcWorkdays = 0

End Function

I would like to add a column next to TotalLates (existing column) which calculates number of working days based on criteria already appearing under Date column of the query (Between [Start Date] and [End Date]).

Any help in achieving this would be highly appreciated. Thanks.

[ranman256]

You can't enter data into a count query.
you can enter into a select query and THEN count later.

[monsterlucifer]

Can you please elaborate a little. Although I understand your reply but I am unable to figure out, how can I get the result that I am looking for. If you could please guide me a little in the right direction. Thanks.

[ranman256]

to enter data into any table/ query...... turn OFF counting/grouping.
(the SUMMATION symbol in the query design makes your query a counting query)

you cannot edit a query (like yours) that has count or sum or avg.

you CAN write the sum/counts to a table (with a key)
then update the main table with the counts from the new table created.

[monsterlucifer]

How would I write the sum/counts to a table?

For instance, the report above generates the data based on given [start date] and [end date].

I want the above input of dates to workout the [working days] based on the macro.

I am sorry but I am very new to access and still trying to learn as I go ahead so please be easy on me. I also don't know how to write macros. I just came across the site while searching on how to calculate working days between two dates and used the macro from there.

When I only put this in a new query design view:

WorkingDays: CalcWorkdays(#01/07/15#,#31/07/15#)

It does give me correct count of working days (i.e., excluding Sunday).

[ranman256]

the query and its param are irrelevant.
open your existing query
change it to a MAKE TABLE query
run it and it will make a table.
now you can edit the new table.

[monsterlucifer]

It made the new table. But how do I incorporate the working days?

[Micron]

Did you try making a select query that incorporates your function in a calcualted field and then using that query as a datasource for a Totals query? You would make sure the first query is NOT a totals (counting) query and add a field where at the top, you type something like WrkDays:CalcWorkdays([Start Date],[End Date]). Do not use WorkDays as a field alias - I'm fairly certain that is a reserved name. I think the function call will be able to resolve the dates even though you are using them as parameters in the Date field (BTW, using Date for a field name is bad form). If I'm wrong about that, you may get an error message. Once you get what you want with the select query, you create a second query that uses the first to append to a table or create a new one. Therein lies the clue to your last question. To work with the table you already have, make an update query to update the required fields with your calculation.

[monsterlucifer]

Ok, I tried making a select query.

If I input the following in design view, it works as expected:

WrkDays: CalcWorkdays(#01/07/15#,#31/07/15#)

The result comes out to be 27 (which is right excluding 4 sundays).

Now, when I try to make select query using input fields for dates, the following code returns error as under:

WrkDays: CalcWorkdays(#[Start Date]#,#[End Date)#)

The expression you have entered has an invalid date value.

Then I tried to change as under:

SELECT CalcWorkdays([Start Date],[End Date]) AS WrkDays;

When I run the query, it asks for Start Date & End Date but returns 0 as result which means that it is not working as expected.

Any ideas?

[Micron]

If I input the following in design view, it works as expected: WrkDays: CalcWorkdays(#[Start Date]#,#[End Date)#)

It should not, as written. Missing ] after End Date, one too many ) plus the # are a problem if you are passing a date that is correctly formatted for your system's regional settings. Remove the #'s, add the ] and make sure the dates entered are compatible with your regional settings. Also, two digit years is a format that no one should be using anymore, IMHO. On my pc, this (31/07/15) is interpreted as year 1931.

The expression you have entered has an invalid date value.

Pretty sure it's the #'s around a valid date. Use these only when you are using a literal date string. The prompt converts a valid date string to a date data type (assuming it is convertible to a date).

When I run the query, it asks for Start Date & End Date but returns 0 as result which means that it is not working as expected.

Whenever your function fails, it will return 0 as you have designed it. Use this corrected expression: WrkDays: CalcWorkdays([Start Date],[End Date]), put a break on your function, then run the query and step through to follow the execution. I advise this as a learning excercise to be done before you make changes. Then I would repeat after making these changes:

If IsDate(StartDate) And IsDate(EndDate) Then
If EndDate <= StartDate Then 'if you reverse this, you do not need the next line or else portion
CalcWorkdays = 0 'you already have set function to zero, not needed
Else 
LTotalDays = DateDiff("d", StartDate - 1, EndDate)

If IsDate(StartDate) And IsDate(EndDate) Then
If EndDate > StartDate Then
LTotalDays = DateDiff("d", StartDate - 1, EndDate)

LTotalDays = DateDiff("d", StartDate - 1, EndDate)

Your code will likely fail here as you have not declared the data types for startdate or enddate. Even though it will work up to here, the date is not interpreted by function as a date when I run it. Change to this

Function CalcWorkdays(StartDate as Date, EndDate as Date) As Integer OR

Function CalcWorkdays(StartDate, EndDate) As Integer
Dim StartDate as Date, EndDate as Date 
notice each variable on a multi-declaration line MUST be declared individually, otherwise all others are variants.

[monsterlucifer]

Micron,

Many thanks for looking at this in details for me. I appreciate your indepth and step by step reply thereby providing me chance to learn.

I have no experience in macro / code writing. I picked up the VBA script from the web to which I linked in my first post (quote=...).

Regarding your reply for square bracket, its a typo on this forum. I changed it to square bracket and it returned the result. I went ahead and changed the short date format in my system setting to dd/mm/yyyy. Closed Access and then re-opened it.

I went ahead with the existing script and ran the following code: SELECT CalcWorkdays([Start Date],[End Date]) AS WrkDays; (used the date format as dd/mm/yyyy) but it still returned 0.

I also used this: WrkDays: CalcWorkdays([Start Date],[End Date]), (with comma at the end) which resulted in an error prompt by access. Removed the comma and it ran but yielded 0.

I then went ahead and changed the code as under to the best of my understanding (after reading your post):

Function CalcWorkdays(StartDate, EndDate) As Integer

Dim LTotalDays As Integer
Dim LSaturdays As Integer
Dim LSundays As Integer

On Error GoTo Err_Execute

CalcWorkdays = 0

If IsDate(StartDate) And IsDate(EndDate) Then
If StartDate <= EndDate Then
CalcWorkdays = 0

'Workdays is the elapsed days excluding Saturdays and Sundays
CalcWorkdays = LTotalDays - LSundays

End If

End If

Exit Function

Err_Execute:
'If error occurs, return 0
CalcWorkdays = 0


End Function

Then I tried as under:

Function CalcWorkdays(StartDate as Date, EndDate as Date) As Integer ORDim StartDate As Date, EndDate As Date

On Error GoTo Err_Execute

CalcWorkdays = 0

If IsDate(StartDate) And IsDate(EndDate) Then
If StartDate <= EndDate Then
CalcWorkdays = 0

'Workdays is the elapsed days excluding Saturdays and Sundays
CalcWorkdays = LTotalDays - LSundays

End If

End If

Exit Function

Err_Execute:
'If error occurs, return 0
CalcWorkdays = 0

End Function

Also tried this:

Function CalcWorkdays(StartDate as Date, EndDate as Date) As Integer OR

Function CalcWorkdays(StartDate, EndDate) As Integer
Dim StartDate As Date, EndDate As Date
On Error GoTo Err_Execute

CalcWorkdays = 0

If IsDate(StartDate) And IsDate(EndDate) Then
If StartDate <= EndDate Then
CalcWorkdays = 0

'Workdays is the elapsed days excluding Saturdays and Sundays
CalcWorkdays = LTotalDays - LSundays

End If

End If

Exit Function

Err_Execute:
'If error occurs, return 0
CalcWorkdays = 0


End Function

To which Access returns error as: Compile error. in query expression 'CalcWorkdays([Start Date],[End Date]'.

I am sorry but I am really dumb working with scripts. Thank you again.

[Micron]

Function CalcWorkdays(StartDate as Date, EndDate as Date) As Integer OR
Dim StartDate As Date, EndDate As Date

This was two options. You only use the first OR the second. Try this:

Function CalcWorkdays(StartDate as Date, EndDate as Date) As Integer

On Error GoTo Err_Execute

CalcWorkdays = 0
If IsDate(StartDate) And IsDate(EndDate) Then
   If EndDate > StartDate Then
      LTotalDays = DateDiff("d", StartDate - 1, EndDate)
      LSaturdays = DateDiff("ww", StartDate - 1, EndDate, 7)
      LSundays = DateDiff("ww", StartDate - 1, EndDate, 1)
      'Workdays is the elapsed days excluding Saturdays and Sundays
      CalcWorkdays = LTotalDays - LSundays
    end if
end if
exit function

Err_Execute:
Msgbox "Error " & err.number & ": " & err.description
End Function

This goes in the field row of the query design grid (see your picture above);
WrkDays:CalcWorkdays([Start Date],[End Date])
You may have to remove the field that's already there that you've called Date (a reserved word that you should not use for a field name) to avoid getting prompted 2x for the start and end dates. It doesn't look like you need it anyway. If this function does not work as expected, that does not mean there was an error. You/me have it set to 0 at the very beginning, so if execution does not go as planned, it will remain 0. If it errs out, it will remain 0. You do not have to repeatedly set the same 0 value.

If this still returns 0, you MUST step through the code as I've already suggested and watch what happens. If you cannot figure it out after that, you can post back, but you must post what happens or there isn't much we can do for you. It is not enough to say "it still returns zero" since we cannot guess what's going on.

[monsterlucifer]

I sincerely apologize for getting back late on this. In fact, I had to go out of the city and therefore could not get time to work on this.

Micron, I am highly grateful for your help on this subject. Your code seems to have sorted out the issue for me. Here is the screenshot of the design view. It works as expected.



I just have to input the [Start Date] and [End Date] once while running the query and it serves for both fields. I'll now go ahead and tinker around with the other calculations that I had to perform which came to halt as I was unable to sort this out.

Many thanks once again and I hope that I could get your expert opinion if I get stuck some where else.

[Micron]

Great! Glad I could help. "expert opinion"? Perhaps you are too kind...
Good luck with the rest of your project.