Search results

You can do it with a series of three queries. qryOne:- SELECT UseCaseID, Max(NoteDate) AS MaxOfNoteDate FROM UseCaseNotes GROUP BY UseCaseID; qryTwo:- SELECT UseCaseNotes.* FROM UseCaseNotes INNER JOIN qryOne ON (UseCaseNotes.UseCaseID = qryOne.UseCaseID) AND (UseCaseNotes.NoteDate =...

I think it's the double-quotes that you used. You need to use "C", not “C” as seen in your code. .

See the query in the sample database in this thread http://www.access-programmers.co.uk/forums/showthread.php?t=96629 .

It's strange. I suggested this solution to others more than once and I got feedback that it worked. http://www.access-programmers.co.uk/forums/showthread.php?t=64141&highlight=Transform http://www.access-programmers.co.uk/forums/showthread.php?t=79457&highlight=Transform .

Try using Nz(....)+0 in your crosstab query e.g. TRANSFORM Nz(Sum([FieldName]))+0 AS SumOfFieldName .

NumberOfWorkingDays: [EndDate]-[StartDate]+1 -(DateDiff("ww",[StartDate],[EndDate],7)-(Weekday([StartDate])=7)) -(DateDiff("ww",[StartDate],[EndDate],1)-(Weekday([StartDate])=1)) .

You can get the min Rate of each Sorter in a Totals Query and join the query back to the table in another query to retrieve the records. qryOne:- SELECT Sorter, Min(Rate) AS MinOfRate FROM [TableName] GROUP BY Sorter; qryTwo:- SELECT [TableName].* FROM [TableName] INNER JOIN qryOne ON...

Instead of counting and summing fields, you can sum expressions e.g. Profitable (count): -Sum([ProfitLoss]>0) Total $ of losses (sum): Sum(IIf([ProfitLoss]<0, [ProfitLoss], 0)) .

See the query in the database attached. It uses IIF expressions as criteria. The query displays all the fields from the table. If you want the query to display only those fields that the user has checked on the form, you will need to use VBA to build the query on the fly. .

Try these expressions for the two new fields:- NewField1: IIf(Len([FieldName])=5, Left([FieldName],1), "") NewField2: IIf(Len([FieldName])=5, Right([FieldName],3), [FieldName]) .

Try this query:- SELECT [LOOKUP-BARCODES-10].COM_DATA_KEY, [LOOKUP-BARCODES-10].COM_DATA_VALUE, [LOOKUP-BARCODES-20].COM_DATA_VALUE AS [COM_DATA_VALUE (2)] FROM [LOOKUP-BARCODES-10] LEFT JOIN [LOOKUP-BARCODES-20] ON [LOOKUP-BARCODES-10].COM_DATA_KEY = [LOOKUP-BARCODES-20].COM_DATA_KEY; Since...

It's far too complicated for queries. You'd be better off using VBA to update a temporary table. .

You have to move the criteria for the [Planned-Start] field to a Where Clause in the query as it is for record selection. SELECT ........ FROM .......... WHERE [48_Hour_Pull].[Planned-Start] Between DateAdd("d",-3,Date()) And DateAdd("d",0,Date()) GROUP BY ....... HAVING...

You have not applied the filtering condition in your query correctly. The Where Clause in a query filters records. The Having Clause in a Totals Query filters returned results. So to filter records, you have to put the record selection criteria in the Where Clause of the query, not in the...

See MyQuery in the database. .

Make sure the EndIssue field in the table is a Date/Time field, i.e. not a Text field. And make sure the date entered when the query is run follows the system date format, i.e. whether it should be mm/dd/yyyy or dd/mm/yyyy. For parameter queries, it would be better to let the user enter the...

Try this criteira for the Planned Start field: between Date()-1 and DateAdd("s",-1,Date()) When the query is run on 28 January, it should pull all records with Planned Start between 27/1/2008 0:0:0 and 27/1/2008 23:59:59 .

We need more detail on two points: 1) In post #7 you wrote: "I am looking for all records for the last 24 hours. or the last day from midnight to midnight" By "the last day from midnight to midnight", do you mean if the query is run anytime today 28 January, you want it to pull all records...

Date/Time fields are internally double precision numbers. [TimeOut]-[TimeIn] returns the elapsed time in Days. As there are 24 Hours in a day, you can get the elapsed time in hours with the expression: ([TimeOut]-[TimeIn])*24 You can round it to e.g. 4 places of decimal like this...

See the new form and queries in the database. Since the part numbers are 13 digits long, I have used a combo box for the user to select a number instead of using a text box for the user to enter a long number. The query "qryUnionParNom" is used as the Row Source of the combo box. .