Search results

Interesting. What's 1=1? Has it any effect on the following AND and OR? I thought I had seen a suggestion on this thread of not including the machine and Option Button choices in the where clause when no machines were selected.

Jon K posted a function in this thread to display time elapsed in ? years ? months ? days (you can easily remove the days if you want.) http://www.access-programmers.co.uk/forums/showthread.php?t=67472 Rose

Try this query:- SELECT RandomWords FROM tblRandomWords GROUP BY RandomWords ORDER BY Min(ID);

That did it. Thank you very much Jon. Rose

Removing null values from an array. Nice example Jon! I have data that are stored horizontally in rows and some fields may contain null values. I'm no good at using arrays. Can you show me how to remove the nulls from an array. Thanks in advance. Rose

ByteMyzer, Since I can't test it in A97, I'm curious to know if your query runs much faster than Jon K's two queries in A2K. I have difficulty understanding your SQL statement, particularly because it doesn't work in the version I am using. However, I can easily build Jon K's two queries in...

Oh you beat me to that. So I can't use one query.

I pasted the query in the database, but when I wanted to save it I also got an error: Syntax error in FROM Clause. When I clicked the OK button, the word SELECT in the subquery is highlighted.

If EMPLOYEE is a text field:- =DLookUp("RTrim([FNAME]) & ' ' & RTrim([LName])","[Table]","[EMPLOYEE] ='" & [AssocID] & "'") If EMPLOYEE is a numeric field:- =DLookUp("RTrim([FNAME]) & ' ' & RTrim([LName])","[Table]","[EMPLOYEE] =" & [AssocID])

Jon K's sample includes also the expression for displaying in hours, minutes and seconds. I am using Access 2000. On my system, I can use Sum() and Avg() on text fields as if they were numeric fields. If you can't do it on your system, you can use one of the conversion functions to convert a...

hh:nn:ss can't display time greater than 24 hours. Jon K posted a simple way in the Sample Databases forum to display aggregate time between two date/time fields in the ? day ? hr ? min ? sec format:- http://www.access-programmers.co.uk/forums/showthread.php?t=62733 The attached database uses...

Thank you Jon K for the sample database. Since November has 30 days, obviously "25 years 10 months 30 days" is actually "25 years 11 months". Your function seems to be able to handle it correctly.

I downloaded Pat's sample and tried to use the Diff2Dates() function with this expression: Time Elapsed: Diff2Dates("ymd", [StartDate], [EndDate], True) But I got an error when I tried to run the query: Wrong number of arguments used with function in query expression 'Diff2Dates("ymd"...

Thank you very much. It worked!

I have a crosstab query. TRANSFORM Sum([tblSales].[Quantity]) AS SumOfQuantity SELECT [tblSales].[Area], Sum([tblSales].[Quantity]) AS Total FROM tblSales WHERE ((([tblSales].[Year])="2003")) GROUP BY [tblSales].[Area] PIVOT [tblSales].[Quarter]; It contains some null values. I need to use...

Jim I'm absolutely sure. In the attached Access 2000 database, I have a table with these sample records: ID DateEntered 1 12/9/2003 2 12/10/2003 3 12/14/2003 4 12/15/2003 5 12/16/2003 6 12/17/2003 When I ran the query: SELECT tblData.ID, tblData.DateEntered FROM tblData WHERE...

I have been using [DateField] between [Date1] And [Date 2] in my queries. The field contains pure dates, with no time element. Both the start date and the end date are always included in my query results. I have no problems at all. I'm a little confused. When should one use [Date 2]+1?

Thank you so much Peter! That fixed it. As the number of columns would dynamically change, in the past it was very difficult to build a report off a crosstab query. Even the report wizard refuses to build such a report (it won't allow us to select any fields from a crosstab query.) You have...

The attached image shows your report along with mine.

I have tried very hard to imitate your report, but I just couldn't put the Quantities in the correct columns. Could you take a look at my report and sub-reports and tell me what I have done wrong? My reports are prefixed with X. They uses your select queries. Thanks in advance.