Search results

Niroth, See the function and query in the attached sample database. You have one shift of 11 hours (7am-6pm) and another of 13 hours (6pm-7am). The sample database assumes date and time are stored in the same field as in the OP's sample data. Using clock-in clock-out times in code to filter...

The basic principle is the same. You just have to pass the starting date and ending date to the function and set the proper CStart and CEnd in the code. Public Function getMinutes2(dDateStart, dDateEnd, dStart, dEnd) As Double Dim CStart As Date Dim CEnd As Date CStart =...

You can first combine the two queries in a Union Query and then build a Totals Query based on the Union Query. Assuming your queries are named "Query1" and "Query2", qryUnion:- SELECT [Year], [Month], [CE], [Debit], Null as [Budget] FROM [Query1] UNION ALL SELECT [Year], [Month], [CE], Null...

Assuming ID1 and ID2 are numeric fields i.e. not text fields, SELECT Logo FROM tblLogo Where forms!frmTest!Text0 Between [ID1] And [ID2] .

Type/Paste this function in a module. Public Function getMinutes(dDate, dStart, dEnd) As Double Dim CStart As Date Dim CEnd As Date CStart = dDate CEnd = dDate + #11:59:59 PM# If dStart <= CEnd And dEnd >= CStart Then CStart = IIf(CStart > dStart, CStart...

Try this Crosstab Query, replacing with the correct table name:- TRANSFORM First([TableName].[Status]) AS [Status] SELECT [Doc#] FROM [TableName] GROUP BY [Doc#] PIVOT [Team#]; Note: It's better not to use special characters in field names. .

You can build a Totals Query based on the table and use the Avg aggregate function on the Duration field to arrive at the average duration. It is possible because date/time data type is internally a double precision number. See the query in the attached database. The AvgOfDuration field in the...

Try this Totals Query, replacing with the correct table name:- SELECT [UserName], DateValue([TableName].[Date_Clocked]) AS [Date_Clocked], Min(TimeValue([TableName].[Date_Clocked])) AS [StartTime], Max(TimeValue([TableName].[Date_Clocked])) AS [EndTime] FROM [TableName] GROUP BY [UserName]...

If I read your question correctly, I think you can join the teradata table with the made table like this:- SELECT [teradata table].* FROM [teradata table] INNER JOIN [YourMadeTable] ON Left([teradata table].[FieldName],3)=[YourMadeTable].[FieldName] Alternatively, you can pull the teradata...

Assuming the values are numeric and you want the crosstab query to display a numeric zero for each blank, you can use the Nz() function in the Transform Clause of the SQL Statement like this:- TRANSFORM Nz(.....)+0 AS ..... SELECT .......... FROM .......... GROUP BY .......... PIVOT .......... .

Since you are using the function in a correlated way with the [Park] field in the main query, you need to put in the Criteria, too. TOTALS: ECOUNT("DOCID","TIC_HVUSER1", "[Park]='" & [Park] & "'", True) .

The following modification should be able to pick up the number 12 from 12items (as well as retain leading zeros, if any). Public Function getNum(Mixed As String) As String Dim Num As Double Num = Val(Mixed) Do While Num = 0 If InStr(Mixed, " ") Then Mixed = Mid(Mixed...

Since the length of the sentence is not fixed, you will have to use VBA. See the query and the getNum() function in the module in the attached database. .

SELECT DISTINCT * INTO tblClientcontacts FROM tblRaw .

Try this Totals Query, replacing with the correct table name:- SELECT IIf(InStr([To],","), Left([To], InStr([To],",")-1), [To]) AS [FirstRecipient], Count(*) AS [Count] FROM [TableName] GROUP BY IIf(InStr([To],","), Left([To], InStr([To],",")-1), [To]) .

You can use DLookup() instead of adding the Control Type table in the query. SELECT Table1.Acct, IIf(DLookup("[Control]","[Control type]")=4, Table2.[table 2], Table1.[table 1]) AS Control, Table1.[table 3] AS Movement FROM Table1 LEFT JOIN Table2 ON Table1.Acct = Table2.Acct .

IIf([FieldName] In (1,3,5,7), "Book", IIf([FieldName] In (2,4,6,8), "Chair", Null)) If it is a text field, you need to put the numbers in quotes i.e. ("1","3","5","7") .

Type/Paste this Totals Query in the SQL View of a new query (replacing with the correct table name):- SELECT PART_FAM, FAM_GROUP, OVS_CODE, Format([TRANS_DATE],"mmm yyyy") AS [Month], Sum(TRANS_AMT) AS MonthlySales FROM [TableName] GROUP BY PART_FAM, FAM_GROUP, OVS_CODE...

You can use the InStr() function in the criteria. Switch the query to SQL View and change the Where Clause to the following (using the correct name of the file number field): SELECT .......... FROM ........... WHERE InStr("," & Replace([Enter FILE NUMBER(S):], " ", "") & ",", "," &...

EMP's expressions work. There are no syntax errors. See the attached database. And BETWEEN ... AND ... doesn't need to be put inside a pair of parenthesis. .