Search results

Thanks for that. Taking it a step further - is there anyway I can average only those fields of the 10 that have values in? So for one line it might have 5 values, yet another might have 7.

Hello, I have a query which i'm having a problem with. I have ID, Name and 10 numeric fields. I then want to have a 14th field which is the average of the 10 numeric fields. Is this possible? If so, how? I've tried Average: AVG(field1+field2+field3 and so on) but this doesn't work. It gives...

Hello, I am in SQL enterprise manager, and trying to create a function which will give me, among other things, the current year. The trouble I'm having is that I can't get the current date first. I've tried : SELECT GETDATE(), but I get an error saying invalid use of getdate in a function...

I'm afraid it doesn't - it gives me a an odbc call failed error, the gist of it being : argument data ntext is invalid for argument 1 of len function...... In fairness I wouldn't expect it to work, as the field is an expression which simply formats to show the first x characters. I don't...

Hello, I have used the left function to provide me with the left x characters of a field, which works fine. Description: Left([c$products],[diff]) However, I want to query this field to only give me certain descrpitions, but it will not work. Can anyone let me know if this is possible, or if...

Thanks very much - I'll look into that.

Hello, I have a query which pulls data based on a date range. However, instead of using this, I'd like to only get data for last month. I have tried doing this by adding 2 fields : Month: Month(myDate) and Year : Year(myDate) In each of these fields I have added a criteria of ...

Ok - I have a problem which I have narrowed down to the following : If I use the "weekday(mydate)" function on a dataset held in a table it works fine. However, if I use it on a dataset which is a query based on a table, it doesn't work. For example : - I have 100,000 records in tbl_data - I...

I have the data below in a query in Access, and I want it to sort by the Week Begin date. I have put a sort on for 'Ascending' but it doesn't work. (Due to the 29th being after the 28th - regardless of month) Is there any way I can force the sort in this query ? It must be in the same query...

Thank you very much Hayley - That's worked a treat. I really appreciate you help. J.

Hello, I currently have a query which provides me with the Top 10 records. However, it sometimes shows me say 13 records, if the last 4 records have the same number. How can I get round this ? Thanks in advance J.

Thank you, that works a treat. I appreciate all your help. J.

Thanks for the suggestion, but unfortunately I cant do that. I have only read only access to the tables (they are linked in my db), and I want to run queries on the data. J.

Hello, I have the following query which gives me a count by day. However, it sorts by day instead of day/month. For example: 01/01/05 01/02/05 01/03/05 02/01/05 02/02/05 and so on... Can I sort so it will show me all of January, then all of Febuary etc? Thanks. J.

Thanks very much, I can see where I was going wrong now. Cheers. J.

I'm getting the following message when I try to run one of my routines (it imports names and addresssed from a table into my program) I definitely have the DO Statement, so cannot see where I am going wrong. Does anyone have any ideas ? Thanks. J. My code is here : Option Compare Database...

Cheers I'll give that a go. J.

If I run a query from say 1st February 2005 to Today's Date, I put a criteria of "Between 01/02/05 and Date()" This works fine. However, I want to run this alongside another query which provides the same data for the same period last year. So what would the command be if I wanted the same for...

Thank you very much, I appreciate it. J.

Hello, I am trying to get a query working in Access, but am having problems. I wonder if someone can have a look at it, and let me know where I am going wrong ? Firstly, I run 1 grouped query based on a single table of records with a date criteria of yesterday's date. I group by the area...