Search results

From the looks of it, you've opened an Access 97 database in something newer than Access 97. I'm not 100% sure on this, but can you use Tools > Database Utilities > Convert Database > To current Access Database version to fix it? You might want to make a backup first.

In your WHERE clause "today" just means the string "today", not today's date. Replace it with date() (no quotes) and that will return today's date.

Right, it's getting even messier now. I'll just attach the database rather than paste the SQL as I think it's getting even bigger. I've changed your query so that it now makes a table identical to the 'Should-be'_outcome table (apart from the field names). I've changed it so that it now takes...

I get the same error in Design View, so it would seem that you can only create joins like this in the SQL view. I also got a syntax error when trying to go into datasheet view, because a few spaces have managed to creep into the SQL if you copy and paste it from my last post. I've attached...

This is pretty horrible, but it seems to work. I don't know if there's a better way to so what you're asking (at least not with SQL, it might look a bit tidier in VBA). SELECT [Table1].[Part_Number], sum([past_record].[200606]) AS Sum200606, sum([past_record].[200607]) AS Sum200607...

Access doesn't support Full Outer Joins, only left and right ones. I think you will need to make several Left and Right Join queries, and then use a Union Select query to bring all the data together.

Looks like Craig was right, I found this link http://support.microsoft.com/kb/286335 and Name is a reserved word in Access 2002 onwards, which could well be why it's not working for you, but not giving me any problems. So, if you haven't already, use Craig's idea and change the field name to...

Glad you could join us Craig as I'm starting to run out of ideas. I wasn't aware that Name was a reserved word and that query is working ok for me, but I'm using Access 2000, so that may be what's causing the confusion. I was going to ask if you definitely have a Name field in table T1 and a...

I suppose I was being a bit optimistic hoping it would be that simple although I'm getting a bit confused now. Just to confirm, what are your table and field names?

It's T1 not TI

What parameter is it asking for? I have made the following assumptions based on your post, so you will need to change these parts of the query as necessary. Your tables are called T1 and T2 You have a Name field in T1 (that contains John, mike, etc...) and a Members field in T2 (that contains...

Well, I learned something new myself doing this SELECT T1.Name, T2.Members FROM T1 INNER JOIN T2 ON T2.Members like '*'+T1.Name+'*';

Ok, so if I'm understanding you correctly, you want to place the integer values in the table, so using your example: 1.56 - should this be replaced by 1, or should it be replaced by 2 (integer value of 0.56+2.09)? 2.09 0.99 0.33 4.87 Dim iTotal As Double Dim dValue As Double Dim db As...

Right, try this Dim iTotal As Double Dim dValue As Double Dim db As DAO.Database Dim rs As DAO.Recordset Set db = CurrentDb Set rs = db.OpenRecordset("table1", dbOpenDynaset) rs.MoveLast: rs.MoveFirst iTotal = 0 With rs If .Fields("Order") >= 1 Then iTotal = .Fields("Order") -...

SELECT Sum([earnings per day]) FROM [your query name] WHERE #yourdate# between [start date] and [finish date]; This should get you a value for a given day, but it will need a bit more work for weeks and months. I'll post back if I come up with anything new. Edit: Sorry, I haven't really read...

Right, I think the first answer is that if you're having to add a new table on a monthly basis, then your design is all wrong and you should look at having one table that you add to on a monthly basis. However, if you're anything like me, you don't want to hear that, you just want someone to...

So you do. Sorry, still learning myself.

I don't think the nz will work, as clng([order]) won't return null it returns #error. You could use iif([order] is null, 0, clng([order]) instead (replace [order] with whatever your order column is called and 0 with whatever you want it to return when there is no order number.