Search results

Glad you got it sorted out, but it is something to bear in mind. I have a table here with close to a million rows of data. A SELECT query will return the information I wanted in under a second, Find, well, won't (unless I'm lucky enough that whatever I happen to be looking for ends up at the top...

How many records are we talking about in the tables? Using Find (and replace) is horribly inefficient compared to using a SELECT query. Using Find Access starts at the first row and checks each column value for what you are searching for before moving to the next row. The more Columns and Rows...

Perhaps if you shuffle your base query around a little bit? SELECT Liveval.Docket, Liveval.[Job No], NZ(mindate.[ReceivedDate], [Liveval].[ReceivedDate]) AS [FirstReceipt] FROM Liveval LEFT JOIN ( SELECT jobNo, MIN(ReceivedDate) AS RecievedDate FROM dbo_reportData GROUP BY JobNo )as...

Posting the query SQL and the Error message you're getting would help.

SELECT TABLE.[JOB_No], TABLE.[COMPLETED_DATE], TABLE.[ACCEPT], TABLE.[REJECT] FROM TABLE; ORDER BY table.[job_no] You shouldn't need SELECT DISTINCT in this context as there shouldn't be duplicate records.

Use the sort in query design view on the column in question in SQL SELECT column FROM table ORDER BY NumberColumn (DESC) The ORDER BY Defaults to ascending order but DESC tells it to sort descending. A Table should not be regarded as having a sort order. If you make a number column the...

You're trying to place a string in a datefield. #00/00/00# is also invalid in this context because it's not a valid date.

It's because you have the msgbox "" on the same line as the If ... Then Dim DB As DAO.Database, MyRec As DAO.Recordset Set DB = CurrentDb Set MyRec = DB.OpenRecordset("Select * From tble_activity Where quartercode = '" & Me.Quarter_ID & "'") If MyRec.EOF Then If...

I'll chip in with one, because it's driven me batty for years. It's unreliable. It'll work fine in a query or a piece of code until it suddenly doesn't and returns an "Unrecognised Function" error message for no reason I can discern. I'd love someone to shed some light on why this happens...

You use the principles of OOP all the time, if you removed the functionality from Access, You'd have a lot more confusion. Think of a recordset, that's a Class Object that handles a set of data. Knowing nothing else about what I'm doing with some data you can tell me, for example: How to...

OOP = Object Oriented Programming. You're more familiar with it than you realise, even though it only implements a limited subset of the principles and functionality VBA could be considered an OO language.

1) I'd perhaps be placing the contents of the Pivot/crosstab query into a Listview, that gives you a "clickable" interface where the user can select the row that they want to see and allows you to retrieve values from columns in that row to pass elsewhere. 2) For the query itself, no, but most...

How is your Update query running? i.e. do users manually run the query? is there a button that they click on from a form? If there is code on a button it might be surpressing error messages, what happens if you run the query and manually enter those values or even just turn off the error...

*shrugs* I see a 6 column spreadsheet comparing a few columns of numbers to see which one's lowest. Doddle In excel, easy to use, easy to manipulate, zero setup and minimal matainence if that example is indicative of the current 'system'. If that's all it's ever going to do why spend days/weeks...

You'll have to give more context as to what it is that you're trying to achieve. Why does 1001, 2010 etc equate to 10 but 895, 893 and 5677 equate to 150? What's Toto apart from an 80's AOR band or a small dog?

It's a candidate, but in a 5-6 column spreadsheet to compare prices, is it really worth the effort to convert that to a database? If you want to make the case that by putting those 5-6 columns into a database you add the capability to know a lot more information about your suppliers, the...

Another Handy hint :D

Just because I don't know, am too lazy to test it and you might know the answer off the top of your head. How does Environ("username") respond if you've run as when you start access? Is it reporting the windows username or the account that is currently running Access?

No worries, I'm still devoid of enough morning coffee so wasn't sure if I was just going (slightly more) mad :D

Difficult to say from the query itself as there is no current filtering in place and the column name from Data_SDP suggests that it's a 3 month summary?. can we assume that you currently get in your output something along the lines of: Id Name dateof3monthPeriod sum(something) 1 Fred...