Seperate Data Entry forms for Each User

[HansV]

If you want to include the last to columns (Start: … and End: …) in the query result, set their Total option to Group By and their Crosstab option to Row Heading.
Otherwise, remove those columns.

[VKKT]

thanks a lot sir,
I will try and come back
regards,
vkkt

[VKKT]

Greetings:
Hi Mr. Hans,

It is not working giving the attached message. This is without start.. end... fields. With these fields the same message appears.

Regards
VKKT

[HansV]

Are you absolutely sure that the name of the form and of the text box have been spelled correctly?

If so: could you attach a stripped down and zipped copy of the database?

[VKKT]

Greetings,
Hi Mr. Hans,

Attached the sample database,

I tried with many criteria but not accepting think there is something wrong in criteria.

Could you please advise a correct one to have a report between 2 dates, there is no form linked actually
Regards,
VKKT

[HansV]

Your database doesn't contain a form, hence the error message!

See the attached version. If you open the report by itself, it will display all dates.
If you open it from the form, it will be filtered on the selected date range.

Time Sheet.zip

[VKKT]

Greetings:

Thanks a lot Mr. Hans for the Solution, it is working very well.

The "frmDatePick" window is not closing automatically after opening the report, what changes need to be done to the script for this to close?

Regards,
VKKT

[HansV]

Change the On Click event procedure for cmdOpenReport as follows:

Code: Select all

Private Sub cmdOpenReport_Click()
    Dim strWhere As String
    If IsNull(Me.txtStartDate) Then
        Me.txtStartDate.SetFocus
        MsgBox "Please select or enter the start date!", vbInformation
        Exit Sub
    End If
    If IsNull(Me.txtEndDate) Then
        Me.txtEndDate.SetFocus
        MsgBox "Please select or enter the end date!", vbInformation
        Exit Sub
    End If
    If Me.txtEndDate < Me.txtStartDate Then
        Me.txtEndDate.SetFocus
        MsgBox "The end date cannot be before the start date!", vbInformation
        Exit Sub
    End If
    strWhere = "TheDate Between #" & Format(Me.txtStartDate, "yyyy-mm-dd") & _
        "# And #" & Format(Me.txtEndDate, "yyyy-mm-dd") & "#"
    On Error GoTo ErrHandler
    DoCmd.OpenReport ReportName:="rptTimeSheet", View:=acViewPreview, WhereCondition:=strWhere
ExitHandler:
    DoCmd.Close acForm, Me.Name
    Exit Sub
ErrHandler:
    If Err.Number = 2501 Then
        ' Report has been canceled
    Else
        MsgBox Err.Description, vbExclamation
    End If
    Resume ExitHandler
End Sub

[VKKT]

Greeting:

Thanks Mr. Hans for your support.

regards
VKKT

[VKKT]

Hi Mr. Hans,

Greetings:

In the attached sample DB if I create a simple query all the dates of the months are showing irrespective of any entries made on that date is there or not, but when I create a crosstab query those dates are not showing where there is not entries on the specific date.

For example, if I delete all the entries on 01-Nov-19, in the crosstab query this date will not appear but in the simple query it is showing.

Is there any way to show all the dates in the Crosstab Query too. This was the purpose the "tblDates" we have created.

For the timesheets I need to show the weekends also even though the respective staff created an entry on that day or not.

Regards,
VKKT

[HansV]

Did you intend to attach a database that demonstrates the problem?

[VKKT]

Hi Mr. Hans,

I am referring the same Database in my above query.

regards,
VKKT

[HansV]

Please attach a database in which some dates are missing, so that I can test it.

[VKKT]

I am very sorry Mr. Hans for wasting your valuable time.

Attached the database where in the data for 1-Nov-19 and 9-Nov-19 there are no entries

These 2 dates are showing in Query1 but not in the Query1_crosstab.

Regards,
VKKT

[HansV]

You have "Is Not Null" as Criteria for the Date_ field; this removes all dates without an entry.
See the attached version for how to include all dates:

Time Sheet.zip

[VKKT]

Thanks a lot Mr. Hans for the response and solution.

I don't remember why I add that criteria.

Regards,
VKKT

[VKKT]

Dear Mr. Hans.
Greetings:
In the attached sample DB in the “Query1_Crosstab-Timesheet” all the dates are not showing (like what is shown in the “Query1_Crosstab) when I change the Project Nos. as column heading.
The changes I have made to the query (Query1_Crosstab) was to have a report exactly like “Query1_Crosstab-TimeSheet-with total”
1.I want to show all the dates in the report with projects as column heading
2.Is there any way to show in the report the “day total” in the same line of the date?
Regards,
VKKT

[HansV]

You should reverse the direction of the joins in the query. See the attached version.

TS.zip

[VKKT]

Greetings:
Thanks a lot Mr. Hans for the solution to my query.
Is there any solution to my 2nd query?
Regards
VKKT

[HansV]

What exactly do you mean by Day Total?