Search results

You can let the user select the table name from a list box on a form and run the query from a command button on the form. In the On Click event of the command button, you can use code like the following (replacing with the correct names of command button, list box and query):- Private Sub...

BTW, when we use the Format() function on a Date/Time data value, what is returned is a text string. To compare these text strings with >= or <= correctly, we need to Format() the dates with "yyyymmdd", the times with "hhnnss", and a date and a time together with "yyyymmddhhnnss" or "yyyymmdd...

The FORMAT() functions in the query will change the dates and times into text strings and hence return incorrect results. Assuming you are searching for date and time separately (as suggested by your query), try this query:- SELECT * FROM [Downtime Details] WHERE [Downtime Date] BETWEEN...

You can add: Date()-WeekDay(Date())+1 AS StartOfWeek, Date()-WeekDay(Date())+7 AS EndOfWeek which should return the Sunday and Saturday of the current week. But you may have a problem when a year ends and a new year starts because most years do not start on a Sunday and end on a Saturday. .

Assuming you have these two tables: Traffic - recording sComm_Name, dtTracking_Date, iTracking_Count Comm_Name - containing a unique list of sComm_Name you can do it with a series of two queries. qryOne:- SELECT sComm_Name, DatePart("ww",dtTracking_Date) AS iTracking_Week...

If a query is linked to a Totals Query, the query will become non-updatable. Instead of pulling data from a Totals Query, try pulling the data from the table using domain aggregate functions DCount() and DSum(). These functions will not make a query non-updatable, though they are less efficient...

When there's no match, CDbl() works on a Null value and returns #Error. You can put an embedded Nz() function in the converted ID so that when there's no match, it returns a zero. ConvertedID: CDbl(Nz([ID])) The Find Unmatched Query:- SELECT [Table 2].*, [qryTable 1].* FROM [Table 2] LEFT...

See the attached database. I have added two text boxes on the form in your database for user input in MM/DD/YYYY format and used code in their On Exit events to place the dates in system date format in the original text boxes for use by the query. You can hide the two original text boxes by...

In your database, the query returned 384 totalMonthlyHours when I entered 07/01/2007 and 07/16/2007 in the text boxes on a system using US date format and when I entered 01/07/2007 and 16/07/2007 on a system using UK date format. It returned 4704 totalMonthlyHours only when I entered...

Update query and select query have different syntaxes. You can't simply change the word Update to Select in an update query to make it a select query. Besides, there is no preview function in an update query. The safest way is back up your table(s) before running a complicated update query. .

You haven't named the second text box as txtEndDate. It is Text7. .

Access is treating the number 100 in Text105 as text. You need to declare the data type of the parameter Text105. In query Design View, select menu Query, Parameters... In the dialog box, type [Forms]![frm_Data]![text105] in the left column and select either Integer or Long Integer in the...

You can make your function return the numbers in a string delimited by commas such as 1) ,132,142,156, 2) ,132,142, 3) ,132, and use the InStr() function in the Where Clause of the query like this:- SELECT ........... FROM ........... WHERE InStr(yourFunction(), "," & [yourFieldName] & ","); .

I have no problem running your query in the attached database. I don't need to format the text boxes as date. There is one thing in the SQL statement that I don't understand though. It is -- totalMonthlyHours are calculated based on the date range entered on the form rather than based on data...

You can put the DAYS_OPEN expression in one column and its criteria in another column. ------------------------------------- Field: DAYS_OPEN: IIf([Status]="Open",(Date()-[Start]),([Complete]-[Start])) Show: check ------------------------------------- -------------------------------------...

I have just answered a similar question. Take a look at my sample database in post #4 in this thread:= http://www.access-programmers.co.uk/forums/showthread.php?t=133472 .

The code is called a SQL statement. When you open the query in Design View, you can easily switch to SQL View e.g. by selecting menu View, SQL View. .

The query just sorts the UnitPrice in descending order and pulls the top 10 records. SELECT TOP 10 Products.ProductName AS TenMostExpensiveProducts, Products.UnitPrice FROM Products ORDER BY Products.UnitPrice DESC; .

You can do a union query directly on the table:- SELECT serial_n, 100 AS Category, [100] as TheValue FROM [TableName] UNION SELECT serial_n, 500, [500] FROM [TableName] UNION SELECT serial_n, 1000, [1000] FROM [TableName]; .

See the form and query in the attached sample database. For explanation, you can take a look at this thread and its Notes: http://www.access-programmers.co.uk/forums/showthread.php?t=103312 .