Search results

Provided that the data in the field are of date/time data type ie not text strings, you can use the Format() function:- Field: Format([DateTimeField], "hhnnss") Show: uncheck Criteria: Between "090000" and "173000" ^

Try changing the two Nulls to 0 and the Or to + ^

As Jon's sample database shows, the Distinct predicate returns a non-duplicate list. Did you by any chance use the Top 1 predicate instead of Distinct? Top 1 returns the first row from the list. ^

Adding 1 day to ending date works so long as there are no records exactly at mid-night. ^

If the date field contains also the times, see discussions in this thread. http://www.access-programmers.co.uk/forums/showthread.php?t=134835 ^

Date is a function name. It's better to use another name for the field though sometimes putting [Date] in a SQL statement works. When you delimit strDate with the # signs, the date in strDate needs to be a text string in US date format i.e. m/d/yyyy or mm/dd/yyyy. ^

See this link and its notes. http://www.access-programmers.co.uk/forums/showthread.php?t=103312 ^

You need to have a space between the table name and the word WHERE. ^

One more option. You can use the DateValue() function to extract the dates. In query SQL View, you can change the Where clause to:- WHERE DateValue([DateTimeField]) Between [Select Start Date] And [Select End Date] But the following should be more efficient than extracting the dates from the...

Invalid Operation because fld is a field object. What you need is the field's name property. Select Case fld.Name ^

It may take a while because not many people have Access 2007 and less still who have used DistinctRow in it. Quoted from Access 2003 Help: ------------------------------------------------- The following SQL statement shows how you can use DISTINCTROW to produce a list of companies that have at...

I just repeated the Where clause in your subquery. So if the Where clause worked in your original query, it should also work in the subquery. Anyhow I've no problem running the statement. It returned the following results in the attached database. Symbol AlphaDeliveryMonth transdate DClose...

I don't have Access 2007. In all the versions of Access that I have, it seems Distinct filters query results whereas DistinctRow filters records in linked tables. I needed DistinctRow only in delete queries that were joined with another table e.g. DELETE DISTINCTROW Table1.* FROM Table1 INNER...

Please post the SQL statements of the query that works and the one that doesn't. ^

The following may be what you are after. SELECT WZ07.Symbol, WZ07.AlphaDeliveryMonth, DateSerial(Val(Left([ddate],4)),Val(Mid([ddate],5,2)),Val(Right([ddate],2))) AS transdate, WZ07.DClose FROM WZ07 WHERE DateSerial(Val(Left([ddate],4)),Val(Mid([ddate],5,2)),Val(Right([ddate],2))) Between...

Please post the SQL statement of your query, that is the statement in query SQL View. ^

"" is a zero-length string so the whole column becomes text strings, not numbers. You can use Null instead of "" in the IIF. ^

You can use the DateValue() function to extract the dates. SELECT DateValue(T.[Date Time]), sum(Commission) AS TotalCommission tblTransactions AS T WHERE T.[Date Time]<=#04/17/07# GROUP BY DateValue(T.[Date Time]); ^

SellDate is an alias. We can't use aliases of calculated fields in subqueries. You need to replace it with its original expression. (SELECT [Price] FROM tblTransactions WHERE [Ticker]=T.[Ticker] AND [Date]=(Select Top 1 [Date] from tblTransactions where [ticker]=t.[Ticker] Order By [Date]...

Hopefully this is what you are after: SELECT T.Ticker, (Select Top 1 [Date] from tblTransactions where [ticker]=t.[Ticker] Order By [Date] Desc) AS SellDate FROM tblTransactions AS T GROUP BY T.Ticker HAVING Sum(T.Quantity)=0; ^