HOq to Get Around Query with dates

[robcarr]

All,

SELECT tblAgentList.AgentName, tblMailItems.ClosedBY, tblMailItems.ItemType, tblMailItems.Quantity, tblMailItems.CaseStatus, tblMailItems.DateLogged, tblMailItems.DateClosed, (DateDiff("n",[datelogged],[dateclosed])-GetSundays([DateLogged],[DateClosed])*1440) AS ahtITEMS, qryAgentEmailandFaxTime.Email, qryAgentEmailandFaxTime.Faxing
FROM qryAgentEmailandFaxTime RIGHT JOIN (tblMailItems LEFT JOIN tblAgentList ON tblMailItems.ClosedBY = tblAgentList.NTLogin) ON qryAgentEmailandFaxTime.Dates = tblMailItems.DateClosed
WHERE (((tblAgentList.AgentName)=[Forms]![frmReporting]![txtagentName]) AND ((tblMailItems.DateClosed) Between [Forms]![frmReporting]![BeginningDate] And [Forms]![frmReporting]![EndingDate]));

this query is gathering info from a couple of sources, the DateClosed field is formatted as general date and the Dates field is formatted as short date, how can I get the query to find the data in the tblauxdaily without altering the field formats, as I believe this is what is stopping email and faxing figures from appearing for the days where that agent did work within the date range.

Any help greatly appreciated as always.

Rob.

Hope this is of use, Rob.

 

[SkipVought]

rob,

Are the dates coming off your form ACTUAL DATES of just Text Stings?

WHERE (((tblAgentList.AgentName)=[Forms]![frmReporting]![txtagentName]) AND ((Format(tblMailItems.DateClosed, "yyyymmdd")) Between Format([Forms]![frmReporting]![BeginningDate],"yyyymmdd") And Format([Forms]![frmReporting]![EndingDate],"yyyymmdd")));

1) If they are ALLREAL DATES then the format is not necessary.

2) Otherwise, massage to get the TEXT in the same format.

Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}

 

[robcarr]

The date beginning,ending and dates are from a textboxs in the forms, Dates in qryAgentEmalandFaxtime is from a table, table is poulated via an import
email and fax show blank still, it finds the 2 records just not with email and fax data from tblauxdaily, I know the agent had fax time for the day

Hope this is of use, Rob.

 

[SkipVought]

Can you modify your sql to test JUST the Between statement in the where clause?

What are the VALUES in BeginningDate & EndingDate?

Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}

 

[robcarr]

The values from in beginningdate and endingdate are from the calendar control. beg and enddate are text boxes on a form.



Hope this is of use, Rob.

 

[robcarr]

I have changed the coding slightly to try and make it easier

SELECT tblAgentList.AgentName, tblMailItems.ClosedBY, tblMailItems.ItemType, tblMailItems.Quantity, tblMailItems.CaseStatus, tblMailItems.DateLogged, tblMailItems.DateClosed, (DateDiff("n",[datelogged],[dateclosed])-GetSundays([DateLogged],[DateClosed])*1440) AS ahtITEMS, tblAuxDaily.Email, tblAuxDaily.Faxing, tblAuxDaily.Dates
FROM tblAuxDaily RIGHT JOIN (tblMailItems LEFT JOIN tblAgentList ON tblMailItems.ClosedBY = tblAgentList.NTLogin) ON tblAuxDaily.Dates = tblMailItems.DateClosed
WHERE (((tblAgentList.AgentName)=[Forms]![frmReporting]![txtagentName]) AND ((tblMailItems.DateClosed) Between [Forms]![frmReporting]![BeginningDate] And [Forms]![frmReporting]![EndingDate]));

but I cannot get it to read any data out of tblauxdaily



Hope this is of use, Rob.

 

[SkipVought]

robb, robb, robb,

the VALUE in your table is a DATE SERIAL NUMBER. The VALUES from your textboxes are STRINGS.

You've gotta CONVERT the DATE to a STRING that is in a FORMAT that is COMPATTABLE with the STRINGS in the textboxes.

Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}