Issue With Date Range For Report 1

[nag9127]

I am having a problem with a date parameter for a report. I have my parameters set to >= beginning date and <= ending date, but when I put the same date (mm/dd/yy format) in both fields my report yields nothing. I think this is because the date is time stamped with hours and minutes and perhaps my range is time stamped as well with 00:00:00 even though I am entering the date in mm/dd/yy format. The field my parameter is based on is formatted to short date, but the field still contains the general format information. How can I resolve this, perhaps with a trim function or some other approach. If I put 4/24/07 12:00:00 am to 4/24/07 11:59:59 pm I get today's data, but I don't want to have to do that. I just want to enter 4/24/07 in both fields and see today's activity. Thanks for any help!!!!

 

[PHV]

>= [beginning date] and < (1+[ending date])

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[nag9127]

Thanks PHV....

I am sure your idea will solve my problem, but for some reason I am getting an error telling me the range is too complex to be evaluated. The "1+" part of the expression is causing this response even though it clearly should yield the desired results. Any ideas to get around this?

 

[PHV]

What is the actual SQL code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TheAceMan1]

How are ya nag9127 . . .

. . . or:

[blue]   Between [beginning date] And [ending date][/blue]

See Ya! . . . . . .

 

[nag9127]

SELECT InventoryTransferTable.*
FROM InventoryTransferTable
WHERE (((InventoryTransferTable.DateTransferred)>=[Beginning Date] And (InventoryTransferTable.DateTransferred)<(1+[Ending Date])));

 

[nag9127]

Using "between" rather than >= and < yields the same message pertaining to complex syntax.

 

[PHV]

You may try this:
PARAMETERS [Beginning Date] DateTime, [Ending Date] DateTime;
SELECT InventoryTransferTable.*
FROM InventoryTransferTable
WHERE (((InventoryTransferTable.DateTransferred)>=[Beginning Date] And (InventoryTransferTable.DateTransferred)<(1+[Ending Date])));

Or this:
PARAMETERS [Beginning Date] DateTime, [Ending Date] DateTime;
SELECT *
FROM InventoryTransferTable
WHERE Int(DateTransferred) Between [Beginning Date] And [Ending Date];

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[nag9127]

PHV...

That worked perfectly. I guess Access didn't know what to make of those date fields. Thanks a lot for your help.