Search results

Leigh, Thanks. I re-read Brent's SQL statement and did realize the mistake that I had made the first time I read the statement. Brent, I'm so sorry for my mistake. Please accept my apology for having misread your SQL statement. .

Try using DLookup() instead of the subquery. UPDATE Scores SET Teamname = DLookup("Teamname","Scores","[ID]=1"); .

You did make a good point in the scenario you described, Brent. However, my saying of avoiding using domain aggregate functions and subqueries in a correlated way if possible is not without reason. The following thread showed a performance issue reported by a poster I recently tried to help by...

You have found the most efficient method as using domain aggregate functions or subqueries in a correlated way is very inefficient and should be avoided if possible. .

The last row with the * in your image signifies that your query is updatable, meaning any changes made in the query will be stored in the table on which the query is based. Since you are running the query from a form, you can make the query open as read only:- Private Sub Command0_Click()...

I would recommend using WayneRyan's GetRand() function posted in this thread (Post #9):- http://www.access-programmers.co.uk/forums/showthread.php?t=141780 Public Function GetRand(SomeField As Variant) As Double Randomize GetRand = Rnd() End Function I have seen that many examples...

Using IIF in query criteria is a little tricky. You can set the criteria in a new column in query Design View like the following:- --------------------------------- Field: IIf([Forms]![Form2]![Combo0]="All", True, Month([DateFieldName])=[Forms]![Form2]![Combo0]) Show: uncheck Criteria: True...

With queries, the closest you can get is something like the following: Fld Dup1 Dup2 Dup3 A A B B B C C C C D D E E E The contents of the first column will be repeated - a limitation of crosstab query. .

To list who will be off each day next Monday to Friday, you will need to have one query for each day and combine them in a master query of employee list with Left Joins. See the query "List Next Week Day By Day" in the attached database. You can see how the Left Joins are created by...

If what you wanted is to return the retest dates, the criteria of your query is incorrect. It should be WHERE (((Table1.Date +Table1.Days) Between [first date] And [End date])) So even if you have declared the data type of the parameters, it still will not work. I have attached a sample...

Access is a little confused when evaluating the criteria. Try helping it by declaring the data type of the two parameters. PARAMETERS [First date] DateTime, [Last date] DateTime; SELECT test1.date, test1.days, [Date]+[Days] AS [Latest re test], * FROM test1 WHERE ((([Date]+[Days]) Between...

In query SQL View (as well as SQL statements in VBA), the date value has to be in American format #2/1/2008# In query Design View, the date value follows the short date format set in the system's regional setting in the control panel. So on a UK system with short date format of dd/mm/yyyy...

Bob, See the attached database, in which I have removed the Short Date format from the Numbers query so that it returns only 0 to 999. Khawar's code still works. I have also added a MyWorkingDays query which is modified from Khawar's WorkingDays query. When MyWorkingDays is run from the Form...

Bob, When you look at the expression for the Dates field in the WorkingDays query, you will see that Khawar ingeniously added the Numbers (that is, 0 to 999) to the From date on the form: Dates: [Numbers] + CDate(forms!form1!fdate) It has nothing to do with the format of the Numbers 0 to...

Try this: Int(sValue +0.5) .

In a crosstab query, you can use Nz(...)+0 in the Transform expression like the following:- TRANSFORM Nz(Count([Number]))+0 AS [Incident Count] .

You can do it with a series of queries. See the queries in the database. You can run qry3 to extract the records and qry4 to obtain the total amounts. .

See the two queries in the database. One is updatable. The other is non-updatable but runs a little faster. Note: Running domain aggregate functions or subqueries in a correlated way in queries are inefficient and will take time if the tables are large. Since the ActualExchangeRates will not...

See the attached database. The query works fine with your sample data. No error message. .

Try this query:- SELECT * FROM [Table1] WHERE DateValue([TimeStamp]) < (Select Top 1 DateValue([TimeStamp]) from [Table1] order by [TimeStamp] desc)-6; .