Date issues in a parameter qry

[Jamie_J]

I am trying to run a parameter qry that pulls all values under the specified range. My problem is that table 'VPS Sales Validated' is in long date format. I have tried reformatting that table using =Format([time],"Short Date") but that isn't working because I only get returns where the value didn't already include the hours and minutes. So Can someone tell me what I need to do in either the qry below or how to reformat the date in main table?

SELECT Sum([VPS Sales Validated].[Master VPS Table_Amount]) AS [SumOfMaster VPS Table_Amount], Sum([VPS Sales Validated].Time) AS SumOfTime
FROM [VPS Sales Validated]
WHERE ((([VPS Sales Validated].[Batch ID])<>0))
HAVING (((Sum([VPS Sales Validated].Time)) Between [Enter Start Date:] And [Enter End Date:]));

 

[Jon K]

I have tried reformatting that table using =Format([time],"Short Date") but that isn't working because I only get returns where the value didn't already include the hours and minutes.

To extract the dates from a date/time field that contains dates and times, you can use the DateValue() function:

SELECT Sum([VPS Sales Validated].[Master VPS Table_Amount]) AS [SumOfMaster VPS Table_Amount], Sum([VPS Sales Validated].Time) AS SumOfTime
FROM [VPS Sales Validated]
WHERE ((([VPS Sales Validated].[Batch ID])<>0))
HAVING (((DateValue([VPS Sales Validated].Time)) Between [Enter Start Date:] And [Enter End Date:]));


though it's not clear why you would sum the [Time] field which contains dates and sometimes times as well (that is, they are only points of time, not periods of time.)
.

 

[Dennisk]

Just a point on dates. Dates are stored as real numbers with the time as a fraction. The format is how that number is displayed. So if you are comparing two dates for equality and one has been set with the Date Function Date() and one with Time and Date and you are displaying both as a short date, the equality would fail eg 3036.233 = 3036.00 .These two dates are not equal but would display as say 01/01/03. Another problem arises when working on PCs with the date time set to British format ie dd/mm/yyyy. When filtering dates with sql statements built up in code I have to swap the day and months around.

 

[Jamie_J]

PERFECT! Thank you so much. It took me 3 days before I finally just posted the question here.