Search results

You can't extract the date from hh:mm:ss. Is there another field in the table that records the dates the phone calls were made? ^

Jon K's sample database and the discussions in the following thread may help. Access to Word Automation http://www.access-programmers.co.uk/forums/showthread.php?t=40119 ^

NOT IN cannot be optimised by Access. Try using a Left Join instead of Not In a subquery. (For SQL syntax, see a SQL statement created by the Find Unmatched Query Wizard.) ^

Take a look at this MS article: ACC2000: How to Rank Records Within a Query http://support.microsoft.com/kb/208946/en-us (Please don't write in all capital letters.) ^

Try the Format() function. NewNumber: [Order] & Format([Line#],"0000") ^

WHERE ([DateField] between [Forms]![frmClassReport].[DateFrom] and [Forms]![frmClassReport].[DateTo] or [Forms]![frmClassReport].[DateFrom] is null or [Forms]![frmClassReport].[DateTo] is null) When either [DateFrom] or [DateTo] is null, all records will be returned. ^

Alternatively, you can use the TimeValue() function. SELECT [Calltime], IIf(TimeValue([Calltime]) Between #12/30/1899 8:0:0# And #12/30/1899 12:0:0#,1,0) AS [8-12], IIf(TimeValue([Calltime]) Between #12/30/1899 12:0:1# And #12/30/1899 17:0:0#,1,0) AS [12-5], IIf(TimeValue([Calltime]) Between...

See the Basic Criteria Format in Jon K's sample database: http://www.access-programmers.co.uk/forums/showthread.php?t=103312 ^

My simpler expression: Age: DateDiff("yyyy",[DOB],Date()) +(Format(Date(),"mmdd")<Format([DOB],"mmdd")) should also work. It was taken from Jon K's sample database: http://www.access-programmers.co.uk/forums/showthread.php?t=103312 I think the problem is that the poster probably used a text...

You can put the criteria in a new column in the query grid like this. ------------------------------------ Field: IIf([ShiftSelect]="All Shifts", True, [FieldName]=[ShiftSelect]) Show: uncheck Criteria: True ------------------------------------ See Jon K's thread in the sample database...

An accurate expression for age:- Age: DateDiff("yyyy",[DOB],Date()) + (Format(Date(),"mmdd")<Format([DOB],"mmdd")) You can put >60 in the criteria. ^

The UniqueValue property in a query works only when none of the fields in the query contains more than 254 characters. ^

CraigDolphin, You can directly put the selection criteria in a Totals query. SELECT ResponsibleDepartment, Count(CustomerQueryID) AS CountOfCustomerQueryID FROM [tblCust-Inquiries] WHERE InquiryDate Between [Forms]![fmDatePicker]![txtStartDate] And [Forms]![fmDatePicker]![txtEndDate] GROUP BY...

You can open Qry1 in query design view, select menu Query, Parameters... and declare the data type of each of the parameters [Start Date] and [End Date]. ^

You can put the criteria for the CustomerID field in a new column in the query grid like this:- -------------------------------- Field: [CustomerID]=[Enter Customer ID or Hit Enter to display all customers] Or [Enter Customer ID or Hit Enter to display all customers] Is Null Show: uncheck...

Using a variable to store a running sum will fail when there are more records than one query screen can display because a query needs to refresh/repaint itself when the cursor is moved or when the query is run for a second time. You can easily see for yourself if you add more records with...

Gary, My expression just makes use of two of the characteristics of the + Operator. The following is taken from Access' help file. + Operator Syntax result = expression1 + expression2 IF Then Both expressions are String Concatenate. Either expression is Null result is Null. EMP

It all depends on what you want the select statement to do. When AND is used, both conditions must be true in the record. Otherwise the record is not returned. When OR is used, either one condition needs to be true. ^

Alternatively, you can use the + sign instead of IIF:- Expr1: [Title]+" " & [First]+" " & [Surname]+Chr(13)+Chr(10) & [Organisation]+Chr(13)+Chr(10) & [Address1]+Chr(13)+Chr(10) & [Address2]+Chr(13)+Chr(10) & [Address3]+" " & [Postcode] ^

If referrerID is an autonumber field, you don't need to insert anything into it. sql = "INSERT INTO Referrer (referrerID, name, street, town, " _ & "county, postcode, telephone, fax, email) VALUES " _ & "('', '" & rs("Ref Name") & "', " _...