Report not coming up if date is the current day

[bailey11]

I have a payroll time sub report that is tied to the main report by job number and date. If the workdate on the payroll time is prior to today's date, the report works fine.

However if the report date is today's date (the current date) and the time is today's date(the current date), the time sub report won't fill in unless I run the report tomorrow.

We complete time entry at the end of each day and we do the daily report at the end of each day so it is ready for signature before we leave the job each night. Can anyone help me correct this?

 

[bananularstyle]

Please send the query you are using to select the date range.

 

[bailey11]

PARAMETERS [Forms]![frmdailiesentry].[dateDLY] DateTime, [Forms]![frmdailiesentry].[JobNumberDLY] Text ( 255 );
SELECT tblprojects.ProjectName, tbldailies.JobNumberDLY, tbldailies.AreaDLY, tbldailies.DateDLY, tbldailies.RPTBy, tbldailies.
TITLE, tbldailies.[Work Performed],tbldailies.
TITLESBMTBY, tbldailies.
SUBMTDBY, tbldailies.
DATESBT, tbldailies.
DWRId FROM tbldailies INNER JOIN tblprojects ON tbldailies.JobNumberDLY = tblprojects.ProjectNo
WHERE (((tbldailies.JobNumberDLY)=[Forms]![frmdailiesentry].[JobNumberDLY]) AND ((tbldailies.DateDLY)=[Forms]![frmdailiesentry].[dateDLY]));

 

[bailey11]

Did anyone ever come up with an answer on this? I still can't figure it out.

 

[lameid]

Does tbldailes.datedly contain both the date and the time?

If so the following should work...

PARAMETERS [Forms]![frmdailiesentry].[dateDLY] DateTime, [Forms]![frmdailiesentry].[JobNumberDLY] Text ( 255 );
SELECT tblprojects.ProjectName, tbldailies.JobNumberDLY, tbldailies.AreaDLY, tbldailies.DateDLY, tbldailies.RPTBy, tbldailies.
TITLE, tbldailies.[Work Performed],tbldailies.
TITLESBMTBY, tbldailies.
SUBMTDBY, tbldailies.
DATESBT, tbldailies.
DWRId FROM tbldailies INNER JOIN tblprojects ON tbldailies.JobNumberDLY = tblprojects.ProjectNo
WHERE (((tbldailies.JobNumberDLY)=[Forms]![frmdailiesentry].[JobNumberDLY]) AND ((tbldailies.DateDLY)>=[Forms]![frmdailiesentry].[dateDLY]));

In this scenario the issue is that Access is doing an exact comparison and not considering today at 10:00 Am as exactly equal to today.

Although I have to say that I would not expect this to work tomorrow if it does not work today. Unless of course the value on the form is populated with yersterday's date by default and noone is changing it.

That said, the only thing I can think of is that the infomation is not in the database when you are trying to run the report for some reason. If that is the case, someone would almost have to see it to figure out what is going on.

 

[bailey11]

I thought it was the date. I tried what you have above and it would pick up days after that report if they had to run them for previous, but your response made it dawn on me to add a second condition to <dateDLY+1.

Thanks for the help. I am a novice and really appreciate it.

Bailey11

 

[bailey11]

How do I tell the Form COntrol for dateDLY to just enter the date and not the time. THis form field is the problem. I have tried all the different formats I can think of.

 

[bailey11]

Never mind, I was using =now() instead of =date().

Thanks again.

 

[lameid]

You can use an input mask to control the input of the control (input mask property).

I don't like them for dates because invariably someone wants to enter 7/11/06 instead of 7/11/2006 or vice versa.

You could use an after update event on the control to test to see whether it is a date or not.

Right click dateDLY and select properties (same to get to input mask).

Find After update and select the box next to it.

Click on the elipses next to the box (three dots: ...)

Select Code builder and Ok.

You will start with...

Private Sub dateDLY_AfterUpdate()

End Sub

Copy from the below to make it look like the below. Also close save and close. Then it will only allow dates in. you can test it out.

Private Sub dateDLY_AfterUpdate()
On Error GoTo dateDLY_err
Dim blInputOk As Boolean
blInputOk = False
If IsDate(Me!dateDLY) Then
If CDate(Me!dateDLY) = Int(CDate(Me!dateDLY)) Then 'Dates are whole numbers time is decimal part
'Just a Date
blInputOk = True
End If
End If
If blInputOk = False Then
MsgBox "You must enter a date and no time in this field.", vbCritical, "Bad Input"
Me!dateDLY = ""
End If
Exit Sub
dateDLY_err:
MsgBox "Err: " & Err.Number & " " & Err.Description
Err.Clear
Me!dateDLY = ""
End Sub

 

[lameid]

LOL. I completely misunderstood your question and cross posted.